SQL Migration Lock Check · guide
The post-mortem that produced this page reads the same way every time: a one-line ALTER TABLE shipped on a Tuesday afternoon, the site returned errors for four minutes, and the statement itself ran in three milliseconds. Nobody was careless, and the migration was correct. What went wrong is a property of how PostgreSQL grants locks, and it is almost invisible until you have seen it once.
Because it spent those four minutes waiting to start, not running.
ALTER TABLE needs an ACCESS EXCLUSIVE lock — the strongest one, which conflicts with everything including the ACCESS SHARE that a plain SELECT holds. Your migration arrived while some long query was still reading the table, so it could not proceed and joined the lock queue. That much is expected and harmless.
The part that turns a wait into an outage is what happened behind it.
PostgreSQL grants table locks in request order, and it will not reorder them to let a compatible pair through. Two SELECTs can share a table happily — but if an ACCESS EXCLUSIVE request is sitting between them in the queue, the second SELECT waits behind it rather than joining the first.
This is deliberate. Without it, a steady stream of readers on a busy table would starve a writer forever. The consequence, though, is that one blocked DDL statement converts into a full table stall:
t=0 long analytics SELECT starts (ACCESS SHARE, holds)
t=30s ALTER TABLE arrives (ACCESS EXCLUSIVE, queued)
t=31s ordinary SELECT arrives (queued behind the ALTER)
t=32s ordinary SELECT arrives (queued)
... every request on this table now queues ...
t=4m analytics SELECT finishes
t=4m ALTER TABLE runs (3ms)
t=4m the backlog drains
Between t=31s and t=4m the table was perfectly readable and nobody could read it. Your monitoring shows a latency cliff on one table, no slow query, and a migration that "took four minutes" — which sends the investigation at the migration, where there is nothing to find.
lock_timeout fix this?It puts a bound on the waiting rather than on the work:
SET lock_timeout = '3s'; ALTER TABLE users ADD COLUMN ...;
Now PostgreSQL gives up if the lock is not available within three seconds, raising an error instead of queueing indefinitely. Nothing is blocked for longer than three seconds, the migration fails cleanly, and you retry — most retries succeed immediately, because the slow query has since finished.
Without it, the default is to wait forever. Which means the worst-case duration of your migration is the worst-case duration of any query on that table, a number you do not control and probably do not know.
Pair it with statement_timeout for the statements that do real work. They bound different things: lock_timeout bounds how long you wait to begin, statement_timeout bounds how long the work itself may take.
Locking and rewriting are separate costs, and keeping them apart is most of what makes a migration reviewable. Everything in the first row is dangerous only because of the queue above; everything in the third holds the lock for minutes by design.
| Cost | Lock | Statements |
|---|---|---|
| Metadata only — instant | ACCESS EXCLUSIVE, briefly | DROP COLUMN, RENAME, ADD COLUMN with a constant or STABLE default, DROP INDEX, attaching a pre-built index with USING INDEX |
| Full scan — reads every row | ACCESS EXCLUSIVE, held throughout | SET NOT NULL (without a validated CHECK), ADD CONSTRAINT / foreign key without NOT VALID |
| Full rewrite — writes every row | ACCESS EXCLUSIVE, held throughout | ALTER COLUMN TYPE, ADD COLUMN with a VOLATILE default (gen_random_uuid(), random()), VACUUM FULL, CLUSTER |
| Index build — blocks writes only | SHARE | CREATE INDEX without CONCURRENTLY |
That last row is the one that gets missed. A SHARE lock lets reads through, so a health check that issues a SELECT keeps passing while every write to the table hangs. The symptom reaches you as connection-pool exhaustion or request timeouts, with nothing anywhere mentioning an index.
Two of these have exact fast paths worth memorising. ADD COLUMN ... DEFAULT has been metadata-only since PostgreSQL 11 unless the default is volatile — so DEFAULT now() is free and DEFAULT gen_random_uuid() rewrites the table, from statements that look the same at a glance. And SET NOT NULL skips its scan on PostgreSQL 12+ if a validated CHECK (col IS NOT NULL) already proves the fact, which turns a locked scan into three cheap steps:
ALTER TABLE users ADD CONSTRAINT users_email_nn
CHECK (email IS NOT NULL) NOT VALID; -- instant
ALTER TABLE users VALIDATE CONSTRAINT users_email_nn;
-- scans, but under SHARE UPDATE EXCLUSIVE:
-- reads and writes continue
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
-- finds its proof, skips the scan
The VALIDATE step has to be in its own transaction. Inside one transaction no lock is released until commit, so doing both in a BEGIN block holds the table for the whole validation and buys you nothing at all.
CREATE INDEX CONCURRENTLY fail?Almost certainly because it ran inside a transaction block, which PostgreSQL does not allow:
ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block
Most migration frameworks wrap each migration file in a transaction by default, so the correct fix gets rejected by the tooling that was trying to help. You have to opt out per migration — disable_ddl_transaction! in Rails, atomic = False in Django, -x in golang-migrate, an autocommit block in Alembic.
The second thing to know is that a failed concurrent build leaves an INVALID index behind. It takes space, it is not used for queries, and it must be dropped before you retry:
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
Every pattern above is visible in the SQL text, which means it can be checked in a pull request rather than discovered in production. That is what this tool does — paste a migration, or wire the CLI into CI with --fail-on-risk. It reads the file and nothing else: no connection string, no credentials, no network calls.