Migration review (PostgreSQL)

A migration is risky when it takes a strong lock on a busy table, rewrites the table, or changes the schema in a way the currently deployed code can't handle. Review every statement against the checks below, then report.

Steps

1. Find the migration files in the change and read every statement, including ones generated by the ORM.
2. For each statement, look up the lock it takes in the lock reference and ask: how big is this table, and how hot is it?
3. Run the checks below. For each problem, propose the safe rewrite, not just the warning.
4. Check deploy order: will the app version running during the deploy still work against the new schema?
5. Report using the format at the end.

Checks

Locks and rewrites

• CREATE INDEX on an existing table blocks writes for the whole build. Use CREATE INDEX CONCURRENTLY. It can't run inside a transaction, so if the tool wraps each migration in one, put it in its own non-transactional migration. A failed concurrent build leaves an INVALID index: drop it and retry.
• ADD COLUMN ... DEFAULT <constant> is metadata-only since PG 11. A volatile default (gen_random_uuid(), clock_timestamp(), random()) rewrites the whole table. Add the column without a default, then backfill in batches.
• ALTER COLUMN ... TYPE usually rewrites the table under ACCESS EXCLUSIVE. Exceptions are binary-compatible changes like widening varchar(n) or varchar to text. Otherwise: add a new column, dual-write, backfill, switch reads, drop the old one.
• SET NOT NULL scans the table under ACCESS EXCLUSIVE. Safe sequence (PG 12+): ADD CONSTRAINT c CHECK (col IS NOT NULL) NOT VALID, then VALIDATE CONSTRAINT c, then SET NOT NULL (skips the scan), then drop c.
• New foreign keys and check constraints: add them NOT VALID, then VALIDATE CONSTRAINT in a separate step, which only takes SHARE UPDATE EXCLUSIVE.

Lock queueing

• Any ACCESS EXCLUSIVE statement waits behind long-running queries, and every query behind it waits too. That's how a "fast" ALTER TABLE takes a site down. Require SET lock_timeout = '5s' (or similar) before it, and a retry plan.
• Large UPDATE/DELETE backfills must run in batches (for example 1-10k rows per transaction, keyed by primary key), never as one statement.

Compatibility with running code

• Renaming a column or table breaks the old app version mid-deploy. Use expand/contract: add the new name, write to both, migrate reads, drop the old name in a later release.
• Dropping a column or table: ship the code that stops using it first, drop in a later release. With an ORM, make sure the model no longer selects it.
• Adding a NOT NULL column without a default fails if the table has rows, and breaks old code that inserts without it.

Data loss

• DROP, TRUNCATE, type narrowing (bigint to int, text to varchar(50)) and USING casts can destroy data. Ask whether a backup or a reversible path exists.
• Down migrations that drop data are fine to flag but rarely run. Focus on the up path.

Report format

### Migration review: <file>

| # | Statement | Risk | Lock / effect | Fix |
|---|-----------|------|---------------|-----|
| 1 | CREATE INDEX idx_orders_user ON orders(user_id) | high | SHARE, blocks writes during build | CREATE INDEX CONCURRENTLY in its own migration |

**Deploy order:** <safe as is / needs a code release first because ...>
**Verdict:** safe to merge / safe with the fixes above / needs a redesign

Rate risk by table size and traffic: a blocking lock on a 500-row lookup table is low; the same on orders is high. Say which assumption you made if you don't know the table size.