DDL and the lock queue · PostgreSQL

This is the lesson that explains why “we only added a column” and “the site was down for four minutes” are both true statements about the same event.

The measurement

Three sessions against one table, started two seconds apart.

A runs a SELECT that takes twenty seconds. It holds ACCESS SHARE.
B runs ALTER TABLE … ADD COLUMN. It wants ACCESS EXCLUSIVE, which conflicts with A, so it waits.
C runs a plain SELECT count(*). It wants ACCESS SHARE — which is compatible with the only granted lock on the table.

Service impact possiblethe queue, observed from a fourth session— Captured on 18.6. Read the granted column and the blocked_by column together.

$ psql -U postgres -c "SELECT a.pid, a.state, l.mode, l.granted, left(a.query,42) AS query, pg_blocking_pids(a.pid) AS blocked_by FROM pg_locks l JOIN pg_stat_activity a ON a.pid=l.pid JOIN pg_class c ON c.oid=l.relation WHERE c.relname='lockdemo' ORDER BY a.pid"

 pid  | state  |        mode         | granted |                   query                    | blocked_by
------+--------+---------------------+---------+--------------------------------------------+------------
1615 | active | AccessShareLock     | t       | SELECT pg_sleep(20);                       | {}
1622 | active | AccessExclusiveLock | f       | ALTER TABLE lockdemo ADD COLUMN note text; | {1615}
1629 | active | AccessShareLock     | f       | SELECT count(*) FROM lockdemo;             | {1622}
(3 rows)

Service impact possiblewhat happened to each— Real output from 18.6. Session C had lock_timeout set to 6s.

$ psql -U postgres -c "SET lock_timeout='6s'; SELECT count(*) FROM lockdemo"

-- session C, an ordinary read:
ERROR:  canceling statement due to lock timeout
Time: 6000.623 ms (00:06.001)

-- session B, the migration:
ALTER TABLE
Time: 18023.558 ms (00:18.024)

Session C wanted a lock that conflicts with nothing currently held, and it was refused.

Why

PostgreSQL grants locks in request order. When a request arrives it is placed at the back of the queue, and it is granted only when it conflicts with nothing ahead of it — held or waiting.

Session C conflicts with B’s pending ACCESS EXCLUSIVE request, which sits ahead of it. So C waits, even though B holds nothing.

The alternative — granting compatible requests immediately — would mean a stream of SELECTs could starve an ALTER TABLE indefinitely. The ordered queue is the correct design. It is also why one waiting strong lock stops a table completely.

The shape of the incident

Every element of this is ordinary.

A reporting query, or an idle-in-transaction session, is holding ACCESS SHARE on a busy table. Nobody notices; it blocks nothing.
A deployment runs ALTER TABLE. The operation itself is metadata only and would take microseconds.
The ALTER cannot start. It queues.
Every subsequent query against that table queues behind the ALTER. Reads included.
Connections pile up. The pool exhausts. Errors spread to services that never touch this table, because they cannot get a connection.
The reporting query finishes. The ALTER completes in microseconds. Everything drains.

The post-mortem says “a schema migration caused a four-minute outage”, and the migration was one metadata change that ran instantly. The outage was the queue.

Two things would have prevented it, and both are one line:

SET lock_timeout = '3s';

and not leaving long-running or idle-in-transaction sessions on tables that are about to receive DDL.

Writing migrations that cannot do this

Always set `lock_timeout`

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;

If the lock is not available within three seconds, the statement fails with ERROR: canceling statement due to lock timeout and the queue it was building dissolves immediately. Failing fast is strictly better than waiting, because the cost of waiting is borne by everyone else.

Then retry, from the shell rather than from inside a transaction:

for attempt in 1 2 3 4 5; do
  if psql -v ON_ERROR_STOP=1 -c "SET lock_timeout='3s'; ALTER TABLE orders ADD COLUMN note text;"; then
    echo "applied on attempt ${attempt}"
    break
  fi
  echo "attempt ${attempt} could not acquire the lock; retrying"
  sleep 10
done

lock_timeout inside a transaction protects the first statement only

BEGIN;
SET lock_timeout = '3s';
ALTER TABLE a ADD COLUMN x int;   -- protected
ALTER TABLE b ADD COLUMN y int;   -- also protected, but...
COMMIT;

Each statement gets its own three seconds, which is fine. The problem is what happens between them: the transaction holds ACCESS EXCLUSIVE on a for the entire time it spends acquiring and altering b.

A migration touching six tables in one transaction holds the strong lock on the first one until the sixth completes. Every table’s queue builds for the duration of the whole migration.

Where atomicity across the tables is not required, use one transaction per table. Where it is required, accept that the lock duration is the whole migration and schedule accordingly.

Prefer the split form

Several operations have a form that separates the brief strong-lock part from the long-running part.

Instead of	Do
`CREATE INDEX`	`CREATE INDEX CONCURRENTLY`
`ADD CONSTRAINT`	`ADD CONSTRAINT … NOT VALID`, then `VALIDATE CONSTRAINT`
`SET NOT NULL` directly	Add a `CHECK (col IS NOT NULL) NOT VALID`, validate it, then `SET NOT NULL`
`ALTER COLUMN TYPE`	Add a new column, backfill in batches, swap
`DROP COLUMN` on a hot table	Schedule it; it is brief but still `ACCESS EXCLUSIVE`
`REINDEX`	`REINDEX CONCURRENTLY`

The SET NOT NULL row deserves the detail, because it is the one people get wrong most often. Setting it directly takes ACCESS EXCLUSIVE and holds it for a full table scan. The three-step version takes the strong lock only briefly each time:

-- 1. brief ACCESS EXCLUSIVE, no scan
ALTER TABLE orders ADD CONSTRAINT orders_note_nn CHECK (note IS NOT NULL) NOT VALID;

-- 2. SHARE UPDATE EXCLUSIVE, scans the table, blocks nothing
ALTER TABLE orders VALIDATE CONSTRAINT orders_note_nn;

-- 3. brief ACCESS EXCLUSIVE; PostgreSQL uses the validated constraint
--    as proof and skips the scan
ALTER TABLE orders ALTER COLUMN note SET NOT NULL;

-- 4. the CHECK is now redundant
ALTER TABLE orders DROP CONSTRAINT orders_note_nn;

Clear the table before you touch it

-- anything old enough to make a strong lock wait
SELECT pid, state, now() - xact_start AS xact_age, left(query, 60) AS query
  FROM pg_stat_activity
 WHERE datname = current_database()
   AND state <> 'idle'
   AND now() - xact_start > interval '30 seconds'
 ORDER BY xact_start;

If anything is there, deal with it before the DDL rather than after the queue has formed.

CREATE INDEX CONCURRENTLY: what concurrency actually costs

It avoids the long lock by scanning the table twice and waiting for older transactions in between, and it has real constraints.

It is substantially slower than a plain index build: it makes two passes over the table and waits for older transactions between them.
It cannot run inside a transaction block. A migration framework that wraps everything in a transaction will refuse it.
It can fail and leave an invalid index behind, which the planner ignores but every write still maintains — a pure cost. Check for them:

SELECT indexrelid::regclass AS index_name, indrelid::regclass AS table_name
  FROM pg_index WHERE NOT indisvalid;

Drop and rebuild any it finds. An invalid index left from a failed build months ago is a common and entirely invisible drag on write throughput.

It waits for transactions that started before it. A long-running transaction elsewhere in the database delays it indefinitely, without blocking anything — so a CREATE INDEX CONCURRENTLY that appears hung is usually waiting on a session that has nothing to do with the table.
On a partitioned table it must be run per partition, then the parent index attached.

Why the queue is ordered, and the one exception

The alternative to an ordered queue is granting any request that conflicts with nothing currently held. That sounds better and is unusable: on a table with continuous read traffic, an ALTER TABLE would never find a moment with no ACCESS SHARE holders, and would wait forever. Ordering guarantees that a strong request eventually reaches the front.

There is one exception, and it is worth knowing because it produces confusing observations.

A session already holding a lock on a relation can acquire a stronger one on the same relation without going to the back of the queue, if it does not conflict with what is already granted to others. This is lock upgrading within a transaction, and it is why:

BEGIN;
SELECT * FROM orders WHERE id = 1;   -- ACCESS SHARE
ALTER TABLE orders ADD COLUMN x int; -- ACCESS EXCLUSIVE, same transaction

does not deadlock against itself.

It is also a source of deadlocks between transactions. Two sessions that each hold ACCESS SHARE and each want to upgrade to ACCESS EXCLUSIVE form a cycle: each waits for the other’s ACCESS SHARE to be released, and neither will release it. That deadlock is detected and one is aborted, but it is a genuinely surprising way to reach a deadlock from code that contains no explicit locking at all.

The practical rule: do not read from a table earlier in a transaction that will later alter it, unless the read is essential. Take the strong lock first, explicitly, if you need both:

BEGIN;
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;
SELECT * FROM orders WHERE id = 1;
ALTER TABLE orders ADD COLUMN x int;
COMMIT;

Taking the strongest lock first is the same discipline as consistent ordering in lesson IX-04, applied to lock strength rather than to object identity.

What to take from this

Locks are granted in request order. A waiting strong request blocks every later request, including compatible ones.
Measured: a plain SELECT refused after 6 seconds while the only granted lock was another plain SELECT.
The outage duration is set by whatever was already running, not by the DDL.
SET lock_timeout before every DDL statement, and retry from outside the transaction.
Multi-table migrations in one transaction hold the first table’s lock for the whole migration.
Prefer split forms: CONCURRENTLY, NOT VALID then VALIDATE.

Cross-course references

Git, CI/CD & GitOps — Part LVII (Approval gates) covers requiring a stated lock and a stated lock_timeout before a migration merges, and Part LIX (Rollback) covers what rolling back a partially applied migration actually means.
Ansible for Production Sysadmins — Part XLVIII (Maintenance windows and rollback) covers running the same statement across an estate with a bounded blast radius.