The lock-queue failure mode: why instant DDL can freeze the database

A team runs ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active' at 14:00. On PG 16, this statement takes 8 ms. Yet the users table is unavailable for 90 seconds, connection pool exhausts, and the site returns 503. The migration itself was instant. So what happened?

The Postgres lock matrix

Every table operation takes a lock. There are eight lock modes from weakest to strongest:

ACCESS SHARE → ROW SHARE → ROW EXCLUSIVE → SHARE UPDATE EXCLUSIVE → SHARE → SHARE ROW EXCLUSIVE → EXCLUSIVE → ACCESS EXCLUSIVE

Key facts:

• SELECT takes ACCESS SHARE (weakest).
• Most DDL (ALTER TABLE, DROP TABLE, TRUNCATE) takes ACCESS EXCLUSIVE (strongest — conflicts with everything).
• CREATE INDEX CONCURRENTLY and VALIDATE CONSTRAINT take SHARE UPDATE EXCLUSIVE — does not block normal DML.

The lock matrix decides what can run in parallel. ACCESS EXCLUSIVE conflicts with every other lock mode, including ACCESS SHARE. That means any DDL with ACCESS EXCLUSIVE must wait for all current readers to finish — and blocks all new readers while it waits.

The FIFO lock queue — the actual failure mode

Postgres grants locks in arrival order (FIFO per object). This creates the canonical migration incident:

The critical insight: new SELECTs queue behind the waiting ALTER because granting them out of order would starve the ALTER. The ALTER’s ACCESS EXCLUSIVE request is in the queue; the lock manager refuses to grant lighter locks to later arrivals that conflict with it. The table is frozen for every new query for the full 60 seconds of the slow SELECT — even though the migration itself runs in 8 ms.

The fix: SET lock_timeout

-- At the top of every migration session:
SET lock_timeout = '2s';
SET statement_timeout = '30s';

With lock_timeout = '2s':

• The migration waits at most 2 seconds for the lock.
• If not acquired, the migration aborts cleanly and releases its position in the queue.
• New SELECTs immediately get their lighter locks — table is unfrozen.
• The migration tool retries with exponential backoff (1s, 2s, 4s, 8s…).
• Eventually the slow SELECT finishes or a quiet window opens, and the migration succeeds.

Without lock_timeout (Postgres default is 0 — wait forever): one slow query can freeze a table for its entire duration.