Handbook
The Postgres Migration Safety Handbook
A reference for the schema changes that take production down. Every entry names the lock a statement takes, cites the manual for it, and ends with a lab you can run against a throwaway PostgreSQL in under two minutes.
It is framework-neutral — Rails, Django, Alembic, Flyway, Liquibase, Prisma, Ecto, sqlx or hand-written .sql makes no difference to what PostgreSQL locks. It is written by the people who build MigrationPilot, but it is not a product manual: each entry ends with a note on which rule catches the problem, and you can skip that section and still get everything else.
- 20
- entries
- 39
- dated public incidents
- 17/20
- graded High
- 2026-08-11
- last verified
Locks, and the queue behind them
The mechanism underneath most migration outages: one statement waits, and everything arriving after it waits too.
- 02Missing lock_timeout and the lock queuecriticalACCESS EXCLUSIVE
This is the entry that matters most.
- 16Long transactions versus DDLcriticalACCESS EXCLUSIVE
Entry 02 covers the mechanism: a blocked DDL statement blocks everything behind it.
- 20Multi-statement DDL and lock accumulationcriticalACCESS EXCLUSIVE
Locks in PostgreSQL are held until the end of the transaction, never released early.
- 01Non-concurrent index creationcriticalSHARE
CREATE INDEXblocks every write to the table until it finishes.
Constraints without the outage
NOT NULL, CHECK, foreign keys and UNIQUE all default to a full scan under a lock. All four have the same two-step fix.
- 03SET NOT NULL and the full table scancriticalACCESS EXCLUSIVE
ALTER TABLE ... SET NOT NULLhas to prove no row violates the constraint. - 04The CHECK-then-NOT NULL patterncriticalACCESS EXCLUSIVE (brief) / SHARE UPDATE EXCLUSIVE (scan)
This is the canonical safe pattern, and it is worth understanding as a shape rather than a recipe, because the same shape solves foreign keys (entry 08) and unique constraints (entry 09) too.
- 05PostgreSQL 18: NOT NULL NOT VALIDwarningACCESS EXCLUSIVE (brief) / SHARE UPDATE EXCLUSIVE (validation)
PostgreSQL 18 removed the need for the
CHECK-constraint dance in entry 04. - 08Foreign keys without NOT VALIDcriticalSHARE ROW EXCLUSIVE
Adding a foreign key scans the whole child table to verify every existing row has a matching parent — and it takes locks on two tables while it does.
- 09Unique constraint scanscriticalACCESS EXCLUSIVE
ALTER TABLE ... ADD CONSTRAINT ... UNIQUEbuilds an index over the whole table underACCESS EXCLUSIVE.
Rewrites you did not ask for
The ALTERs that quietly copy the whole table and every index on it — and the one that stopped in PostgreSQL 11.
- 06Volatile defaults and table rewritescriticalACCESS EXCLUSIVE
"Adding a column with a default rewrites the table" is folklore that was true until 2018 and is now wrong in a way that matters.
- 07ALTER COLUMN TYPE rewritescriticalACCESS EXCLUSIVE
ALTER TABLE ... ALTER COLUMN ... TYPEusually rewrites the entire table and every index on it, underACCESS EXCLUSIVE.
Statements your framework will break
Migration tools wrap everything in a transaction by default. These three statements have opinions about that.
- 10ALTER TYPE ... ADD VALUE in a transactioncriticaln/a (statement error)
Adding a value to an enum is the smallest schema change there is, and it breaks more migration frameworks than anything else in this handbook — because almost every framework wraps migrations in a transaction by default, and this statement has rules about transactions.
- 11CONCURRENTLY inside a transaction blockcriticaln/a (statement error)
Every safe-migration guide tells you to use
CREATE INDEX CONCURRENTLY. - 12Invalid indexes after a failed CONCURRENTLYwarningSHARE UPDATE EXCLUSIVE
When
CREATE INDEX CONCURRENTLYfails, it does not clean up after itself.
Changes you cannot take back
DROP and RENAME finish instantly, which is exactly why they get waved through review.
- 13DROP COLUMN blast radiuswarningACCESS EXCLUSIVE
ALTER TABLE ... DROP COLUMNis fast. - 14DROP TABLE and CASCADE blast radiuscriticalACCESS EXCLUSIVE
DROP TABLEis the only operation in this handbook with no recovery path. - 15RENAME breaks running application codewarningACCESS EXCLUSIVE
ALTER TABLE ... RENAME COLUMNis instant, holds a lock for microseconds, rewrites nothing, and is fully reversible.
Past the edge of one table
Replication, backfills and partitions: migrations whose cost lands somewhere other than the table you named.
- 17Replication-breaking operationscriticalACCESS EXCLUSIVE (DROP CONSTRAINT) / SHARE ROW EXCLUSIVE (DISABLE TRIGGER)
Two migrations that look local to one table, and are not: dropping a primary key, and disabling triggers.
- 18Unbatched backfillswarningROW EXCLUSIVE
A single
UPDATEover a whole table does not take a scary lock —ROW EXCLUSIVEdoes not block readers. - 19Partition ATTACH and DETACHcriticalACCESS EXCLUSIVE / SHARE UPDATE EXCLUSIVE
Partition maintenance is usually automated — a nightly job adding tomorrow's partition and dropping last month's — which means it runs unattended, against a busy table, at whatever hour you picked.
The evidence standard
Most writing about unsafe migrations is a chain of blog posts citing blog posts. Behaviour changes between major versions; the posts do not. These are the rules every entry here is held to.
1Version claims are pinned to release notes.
Not "recent versions of Postgres".
2Lock claims are pinned to the manual.
Not to another blog post.
3Every entry has a lab you can run.
Real Docker, real output, under two minutes.
4Incidents are real, dated, and fetched.
Or the entry says none was found.
5Confidence is graded, not implied.
High or Medium. There is no Low.
Where no public postmortem could be found, the entry says so rather than inventing a plausible story. No incident here is composited or inferred. The markdown behind these pages lives in docs/handbook, and node docs/handbook/validate.mjs checks every entry against the standard above.