Review database migrations in a PR
A migration that drops a column the old deploy still reads takes the site down for the length of a rollout. Review the migration for compatibility with both the old and new code, and for the lock it takes on a live table.
Procedure
- Locate the migration and read it as a sequence of DDL, not as an afterthought:
gh pr diff 482 -- '*migrations*'orgit diff origin/main...HEAD -- '**/migrations/**'. - Require the expand/contract pattern for any rename or type change:
- expand: add the new nullable column, backfill in batches, make the app write both,
- contract: after the old code is gone, drop the old column in a later release.
- Reject a destructive change in the same PR as the code that stops using the field — those must be separate deploys.
- Check the lock each statement takes. On Postgres,
ALTER TABLE ... ADD COLUMN ... DEFAULT xis fast since 11, butSET NOT NULLandALTER COLUMN TYPEtake anACCESS EXCLUSIVElock and rewrite the table; on a table over a few million rows that is a production stall. - For indexes, require
CREATE INDEX CONCURRENTLYon Postgres (outside a transaction) so writers are not blocked. - Confirm the backfill is batched with a limit and a sleep, not a single
UPDATEover the whole table. - Verify the down migration exists and actually reverses the change, or document why forward-only is intended.
Pitfalls
- Adding a
NOT NULLcolumn with no default to a populated table, which fails outright. - A migration that runs inside the same transaction as the index build, so
CONCURRENTLYis rejected. - Renaming a column in place, breaking the previous app version mid-rollout.
- A backfill that runs unbounded and holds row locks for minutes under load.
Verification
gh pr diff 482 -- '**/migrations/**'
# Dry-run against a production-sized copy:
alembic upgrade head --sql | grep -iE 'ACCESS EXCLUSIVE|NOT NULL|CONCURRENTLY'
Report the DDL statements, the lock level each takes, and whether the change is expand-only or contains a contract step. A contract step bundled with the consuming code is blocking.
Worked example
A PR renames users.name to users.full_name and updates the query in the same commit. Mid-rollout the old pods still read name, which no longer exists, and return 500s. The expand/contract fix: release 1 adds full_name nullable, backfills in batches of 1,000, and writes both columns; release 2 switches reads to full_name; release 3 drops name.