The Lock Queue (ALTER TABLE & Lock Waits).

🧠The myth

"Postgres 11+ made ADD COLUMN ... DEFAULT instant — no table rewrite. So this migration is safe to run at 2pm:"

ALTER TABLE orders ADD COLUMN channel text DEFAULT 'web' NOT NULL;

You ship it. The migration itself finishes in 4ms. And for 90 seconds, every single query against orders hangs — including plain SELECTs that touch nothing you changed.

✅Actually

The rewrite was never the only risk. The real one is the lock queue.

ALTER TABLE needs an ACCESS EXCLUSIVE lock — conflicts with everything, including SELECT. Postgres grants locks roughly FIFO, and here's the part people miss:

A waiting ACCESS EXCLUSIVE blocks every request that arrives behind it.

So the chain is:

1. Some analytics SELECT has been running 90s, holding ACCESS SHARE.
2. Your ALTER asks for ACCESS EXCLUSIVE → can't get it → waits.
3. A new SELECT arrives. ACCESS SHARE vs ACCESS SHARE is compatible... but it still queues behind your waiter.
4. Now every new query queues. Connection pool saturates. The outage is not your 4ms DDL — it's the convoy behind it.

Your migration didn't take a lock. It took a hostage line.

🔍See it live (two psql sessions)

-- session A
BEGIN; SELECT * FROM orders LIMIT 1;   -- leave open

-- session B
ALTER TABLE orders ADD COLUMN channel text;  -- hangs

-- session C
SELECT 1 FROM orders LIMIT 1;          -- also hangs. this is the bug.

Then inspect:

SELECT pid, wait_event_type, state,
       now() - xact_start AS age, left(query, 60)
FROM pg_stat_activity
WHERE wait_event_type = 'Lock' ORDER BY xact_start;

The oldest non-waiting transaction at the top of that list is your actual culprit — not the ALTER.

🛡️The fix: fail fast instead of queueing

SET lock_timeout = '2s';
SET statement_timeout = '0';
ALTER TABLE orders ADD COLUMN channel text DEFAULT 'web' NOT NULL;

If the lock isn't free in 2s, the DDL errors out and the queue drains instantly. Then retry in a loop — 10 attempts with backoff. Each attempt costs at most 2s of convoy instead of 90.

Caveat worth knowing: lock_timeout applies per lock acquisition, not per statement. A DDL that takes several locks can exceed it in total. Keep statements small and single-table.

⚠️What people get wrong

• Thinking "instant DDL" = "safe DDL". Lock acquisition is the risk, not the work.
• Using statement_timeout for this. It also kills the DDL mid-rewrite on slow tables. lock_timeout only bounds the waiting.
• Forgetting CREATE INDEX needs SHARE (blocks writes) — use CONCURRENTLY, which takes a weaker lock but cannot run inside a transaction and leaves an INVALID index if it fails.
• Validating FKs in one shot. ADD CONSTRAINT ... NOT VALID then VALIDATE CONSTRAINT separately — the second takes only SHARE UPDATE EXCLUSIVE.
• Long-lived idle transactions from an ORM. Set idle_in_transaction_session_timeout = '30s' so a forgotten BEGIN can't become a migration landmine.

🔢The numbers that matter

A 90s open transaction + a 1-second deploy window = up to 90s of full-table stall. With lock_timeout=2s and retries: worst case ~2s per attempt, and the table is readable between attempts.

🏋️Your exercise

Run the three-session repro above. While session C is hanging, query pg_locks joined to pg_stat_activity and answer: which pid holds AccessShareLock in granted = true state, and which pids show granted = false? Then set lock_timeout = '1s' in session B and re-run — time how fast session C recovers.

Bonus: does ALTER TABLE ... ADD COLUMN with a volatile default (e.g. DEFAULT now()) still skip the rewrite? Check the docs before guessing.