Reviewing a Migration for the Table With Four Hundred Million Rows

The migration was two lines. Add a column with a default, create an index. On the staging database it ran in ninety milliseconds and the reviewer approved it. On production, against four hundred million rows, the first line took an ACCESS EXCLUSIVE lock and the site went down for the duration.

Migrations are the one kind of code where the diff tells you almost nothing about the risk. The risk lives in the interaction between the statement, the table size, the lock it takes and the traffic hitting the table while it runs. That is what a migration review has to examine.

This is the checklist I run, written against Postgres 16.3, with notes where other engines differ.

1. What lock does the statement take, and for how long

Every DDL statement takes a lock, and the dangerous part is the queue. An ALTER TABLE that needs ACCESS EXCLUSIVE does not just block writers, it waits behind every lock currently held, including the long running transaction nobody noticed. So a "fast" ALTER can sit in the lock queue, and while it waits, every subsequent query queues behind it, producing a full outage from a statement that never actually ran.

The review questions: does this statement take a heavyweight lock, and is the table hot? For adding a column, Postgres 11 and later make ADD COLUMN with a non volatile default nearly instant, because the default is stored as metadata rather than rewritten. That is a fact worth knowing, because the same statement on an old version rewrites the table.

The mitigation I want to see for risky DDL: set a short lock_timeout so the statement fails fast instead of queueing, and retry during a quiet window:

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN region text NOT NULL DEFAULT 'us';

2. Index creation must be concurrent

CREATE INDEX takes a lock that blocks writes for the duration of the build at four hundred million rows. The concurrent variant does not:

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_region ON orders (region);

It is slower and it can fail partway, leaving an INVALID index that must be dropped and rebuilt, but it does not block writers. If a migration creates an index without CONCURRENTLY on a large table, that is a blocking review comment, not a preference.

The same applies to constraint validation. Adding a CHECK or FK scans the table and holds locks. The pattern is to add the constraint NOT VALID, then VALIDATE it separately, which takes a lighter lock.

3. Backfills are deployments, not statements

If the change requires updating existing rows, a single UPDATE over four hundred million rows is a long transaction holding locks and generating enormous WAL, and it will also trigger a long autovacuum afterwards, which is the pressure in postgres autovacuum not keeping up.

The review wants a batched backfill. Update in ranges of tens of thousands of rows, with a pause, in a loop, resumable if it dies. The migration should be structured as code that can run for hours safely, not as one statement that must finish in the deploy window.

4. The two phase expand and contract for renames and splits

Renaming a column or splitting one into two is the classic place reviews miss the coupling. If the migration renames and the application deploys expecting the new name, any instance still running the old code breaks the moment the migration lands.

The safe shape is expand, migrate, contract. Add the new column, deploy code that writes both, backfill, deploy code that reads the new, then drop the old in a later release. If a migration does a rename and a drop in one step, the review should flag the missing expand and contract phases, because it couples the deploy to the migration in a way that cannot roll back one at a time.

5. Rollback is part of the migration

The question I always ask: if this breaks production at step two of three, what runs to get us back? If the answer is "we restore a backup", the migration is not reviewed, it is a gamble.

Some migrations are genuinely hard to reverse, and that is acceptable if it is a stated, accepted decision. What is not acceptable is an unexamined one. The review should see the rollback path or an explicit acknowledgement that there is none and why that is safe.

6. The table's hotness matters more than its size

A four hundred million row table that is rarely touched is less risky than a forty million row table that every request reads. The review should consider traffic, not just row count. Ask what reads and writes the table at peak, and whether the lock windows overlap with peak.

The shape of a good migration review comment

Not "looks good" and not "this is dangerous". Instead: "CREATE INDEX without CONCURRENTLY on orders will block writes for the build. At our size that is minutes. Use CONCURRENTLY and accept the INVALID index retry path, or schedule the build in a maintenance window with the lock timeout pattern."

That comment is actionable because it names the lock, the scale and the fix. Migration review is mostly about knowing the lock each statement takes and refusing to let a heavyweight lock touch a hot table in a deploy window.

The rule

Review a migration as if the table is at production size and production traffic, because it will be. Name the lock each statement takes, require CONCURRENTLY and NOT VALID where they exist, treat backfills as long running resumable jobs, and insist on a rollback story. The two line diff is never the risk. The risk is the four hundred million rows behind it, and the queue of queries waiting on a lock you chose.

The downstream cost of a blocking migration is the locked table incident in database migration locked table, which is what happens when this review is skipped.