1
# Migration Review
2
3
## 1. Read what it actually does
4
5
List every statement and what each one touches. Watch for the ones that hide work:
6
7
- A `NOT NULL` addition with a default may rewrite the whole table.
8
- A type change may rewrite the whole table.
9
- An added foreign key takes a lock on both tables and validates every existing row.
10
- A plain `CREATE INDEX` holds a write lock for the whole build.
11
12
## 2. Blast radius
13
14
For each statement, answer with a number, not an adjective:
15
16
- How many rows does it touch?
17
- What lock does it take, and on what?
18
- How long will it hold that lock, at production size?
19
- What queries block behind that lock, and what happens to them? A blocked query
20
  usually means a timeout, then a retry storm, then an outage that looks unrelated.
21
22
If you cannot answer the timing question, the migration has not been rehearsed and is
23
not ready to approve.
24
25
## 3. Deploy compatibility
26
27
The single most common cause of a migration incident. During a deploy, the old code and
28
the new schema coexist, in both orders.
29
30
Answer both:
31
32
- Does the **currently deployed** code still work **after** this migration?
33
- Does the **new** code work **before** it?
34
35
If either is no, the change must be split across releases. The general pattern is
36
expand, migrate, contract:
37
38
1. **Expand.** Add the new thing. Nothing reads it yet.
39
2. **Migrate.** Backfill, then move readers and writers over.
40
3. **Contract.** Remove the old thing, in a later deploy.
41
42
Each of these three is a separate, independently deployable change.
43
44
## 4. Backfills
45
46
A backfill inside a migration holds a transaction open for its whole duration. Take it
47
out.
48
49
- Batch it, with a bounded size per batch and a pause between.
50
- Make it resumable from a cursor, so an interruption at 60% is not a restart.
51
- Make it idempotent, so a rerun is harmless.
52
- Run it separately from the schema change, and monitor it.
53
54
## 5. Rollback
55
56
- Is there a down migration, and has it actually been run against a copy?
57
- If the change is irreversible, is that written down and accepted?
58
- What is the recovery path if the migration fails halfway through? Know whether your
59
  engine wraps DDL in a transaction. Postgres largely does. MySQL largely does not, so a
60
  half-applied migration is a real state you must plan for.
61
- If the answer is "restore from backup", know how long a restore takes and say that out
62
  loud before approving.
63
64
## 6. Rehearse
65
66
Run it against a restored copy at production scale. Record how long it took. A
67
migration that has never been rehearsed at real size is an estimate, and estimates of
68
lock duration are consistently wrong in the unsafe direction.
69
70
## 7. Approve with the numbers attached
71
72
State the row count, the lock, the rehearsed duration, the deploy ordering, and the
73
rollback. An approval without those five is a guess with a signature on it.
74