Which lock does your migration actually take?

Every DDL statement in Postgres takes a lock. Knowing which one is the difference between a deploy nobody notices and an incident channel.

There are eight table-level lock modes. For migrations, four matter.

The four that matter

ADD FOREIGN KEY is the only ADD CONSTRAINT form that gets the weaker lock. The docs are explicit about this, and it is easy to over-generalize: ADD CHECK takes ACCESS EXCLUSIVE and blocks reads, even with NOT VALID.

"Blocks reads" means SELECT queues. "Blocks writes" means INSERT/UPDATE/DELETE queue.

SHARE UPDATE EXCLUSIVE is the one you want. It lets normal traffic through entirely — it only conflicts with other DDL and with VACUUM. Every zero-downtime migration pattern is, fundamentally, a way to do the expensive part under SHARE UPDATE EXCLUSIVE instead of something stronger.

Duration is the real variable

The lock mode tells you who blocks. The duration tells you how much it matters.

ALTER TABLE ... DROP COLUMN takes ACCESS EXCLUSIVE — the strongest lock there is. It is also essentially instant, because dropping a column is a catalog operation: Postgres marks the attribute dropped and moves on. It does not rewrite the table.

ALTER TABLE ... ADD COLUMN c uuid DEFAULT gen_random_uuid() takes the same ACCESS EXCLUSIVE lock, and holds it while rewriting every row on disk.

Same lock, wildly different outcome. So there are really three duration classes worth naming:

• instant — a catalog write. Microseconds. The lock mode barely matters.
• scan — Postgres reads every row to validate something. Proportional to table size.
• rewrite — Postgres writes every row to a new heap. Proportional to table size, and slower than a scan.

A migration is dangerous when a strong lock meets a long duration.

Common statements, by what they actually do

CREATE INDEX — SHARE, for the full build. Reads continue, every write blocks. Use CONCURRENTLY to drop to SHARE UPDATE EXCLUSIVE; the build takes longer and cannot run inside a transaction, and a failure leaves an INVALID index you must drop and rebuild:

SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

ADD COLUMN with a non-volatile default — ACCESS EXCLUSIVE, instant, on PG 11+. Catalog-only.

ADD COLUMN with a volatile default — ACCESS EXCLUSIVE, full rewrite. The dangerous one, covered in its own post.

DROP COLUMN — ACCESS EXCLUSIVE, instant. The lock is not the problem here; the problem is that the data is gone and any deployed code still selecting that column starts erroring immediately. Ship the code that stops using it first, drop it in a later migration.

SET NOT NULL — ACCESS EXCLUSIVE plus a full validation scan. This is not instant on PG 12+, contrary to a common misreading of the release notes. What PG 12 added is the ability to skip the scan when a valid CHECK constraint already exists that proves no NULL can be present (a CHECK (col > 0) qualifies just as well as an explicit IS NOT NULL):

ALTER TABLE users ADD CONSTRAINT email_nn CHECK (email IS NOT NULL) NOT VALID;  -- instant
ALTER TABLE users VALIDATE CONSTRAINT email_nn;   -- scans under SHARE UPDATE EXCLUSIVE
ALTER TABLE users ALTER COLUMN email SET NOT NULL; -- PG 12+ sees the CHECK, skips the scan
ALTER TABLE users DROP CONSTRAINT email_nn;

The scan still happens. It just happens under a lock that lets traffic through. Do not drop the CHECK in the same statement that sets NOT NULL — the docs carve that case out explicitly. A separate later statement is fine.

On PG 11 and older there is no scan-free path.

On PG 18 the four-step dance collapses to two, because NOT NULL became a first-class constraint with its own NOT VALID path:

ALTER TABLE users ADD CONSTRAINT email_nn NOT NULL email NOT VALID;  -- instant
ALTER TABLE users VALIDATE CONSTRAINT email_nn;  -- SHARE UPDATE EXCLUSIVE

Note the syntax: it is ADD CONSTRAINT ... NOT NULL <column> NOT VALID, not SET NOT NULL ... NOT VALID — the latter is a syntax error. After the VALIDATE, the column's attnotnull is set and no cleanup constraint is left behind. The CHECK workaround above is for 12 through 17.

ADD FOREIGN KEY — SHARE ROW EXCLUSIVE on both tables while it validates. Reads continue, writes block on the referencing table and the referenced one. The second table is the part that surprises people. Use NOT VALID:

ALTER TABLE orders ADD CONSTRAINT orders_user_fk
  FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;  -- brief lock, new rows checked
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;    -- SHARE UPDATE EXCLUSIVE

NOT VALID means "enforce this going forward, don't check what's already here." The VALIDATE step does the scan afterwards under a lock that does not block traffic.

DROP CONSTRAINT — ACCESS EXCLUSIVE, brief. If it is a foreign key, Postgres locks the referenced table exclusively too. The asymmetry is worth internalizing: adding an FK takes SHARE ROW EXCLUSIVE, dropping one freezes both tables outright.

ALTER COLUMN TYPE — ACCESS EXCLUSIVE with a rewrite, unless the conversion is binary-coercible (or an unconstrained domain over the new type, and the USING clause doesn't change contents). varchar(50) → varchar(100) and varchar → text are free. int → bigint is a rewrite. Narrowing is a rewrite too — only widening is exempt.

Avoiding the rewrite does not always avoid an index rebuild: Postgres still rebuilds indexes unless it can prove the new one is logically equivalent, so a collation change forces one even when the heap is untouched.

VACUUM FULL — ACCESS EXCLUSIVE for the entire rebuild. Never in a migration. Use pg_repack if you need to reclaim space online.

The lock queue

Lock modes explain what happens when a migration runs. They do not explain the most common way migrations cause outages, which is what happens while a migration waits.

When your ALTER TABLE requests ACCESS EXCLUSIVE and something is holding a conflicting lock — an open transaction, a long analytics SELECT, an idle-in-transaction connection someone forgot about — your ALTER TABLE waits.

And every query that arrives after it, and conflicts with it, waits behind it — even though the query at the front of the queue wouldn't have blocked them.

This is documented, though not on the locking page where you would look for it. It is in the description of pg_blocking_pids(): one process blocks another if it "holds a lock that conflicts" (a hard block) or "is waiting for a lock that would conflict with the blocked process's lock request and is ahead of it in the wait queue" — a soft block. The soft block is the pileup.

Worth being precise about what is not promised: the docs describe queue position, not a fairness or strict-FIFO guarantee, and the lock manager does reorder in some cases to break deadlocks.

So an instant migration blocks the whole table for as long as the query in front of it runs. The migration itself was never the problem; being stuck in the queue was. This is why an ADD COLUMN that takes microseconds in staging can produce a ten-minute outage in production.

The fix is one line:

SET lock_timeout = '5s';

Now the migration gives up instead of holding the queue open. You get a failed deploy, which you retry, instead of an incident. Put it in front of every DDL statement you run against production.

Two details: the default is 0, meaning no timeout at all, so this is opt-in. And the limit applies to each lock acquisition separately, not to the statement as a whole — a statement touching several tables can wait 5s per table. Note also that this is community practice rather than official Postgres guidance; the docs describe lock_timeout but never recommend it for migrations.

The short version

Ask two questions of every migration statement:

1. Which lock, and does it block reads? ACCESS EXCLUSIVE blocks everything. SHARE and SHARE ROW EXCLUSIVE block writes. SHARE UPDATE EXCLUSIVE blocks nothing.
2. How long does it hold it? Catalog write, full scan, or full rewrite.

Strong lock plus long duration is an outage. Strong lock plus instant is fine. Weak lock plus long duration is fine. And whatever the answer, set a lock_timeout, because the queue does not care how fast your statement would have been.