Table locks & DDL

Row locks are what you fight over in application code. Table locks are what take your site down during a deploy. Every statement (even a plain SELECT) locks every table it touches; the modes usually don't conflict, so you never feel it. Then a migration shows up.

A SELECT takes ACCESS SHARE, the weakest table lock. DROP TABLE, TRUNCATE, and most forms of ALTER TABLE take ACCESS EXCLUSIVE. The manual's rule for ALTER TABLE is that "An ACCESS EXCLUSIVE lock is acquired unless explicitly noted", and ACCESS EXCLUSIVEconflicts with all modes, ACCESS SHARE included. So the strongest table lock and the weakest one can't coexist, which is the entire story of a DDL outage compressed into one sentence.

On its own that's fine: the ALTER waits for running queries to finish, then does its (often fast) work. The disaster needs one more ingredient: the lock queue.

The outage, reproduced

Picture three sessions. A long-lived transaction has already read the table and is sitting there holding ACCESS SHARE. A migration asks for ACCESS EXCLUSIVE and can't have it, so it waits. And here's the twist: once that migration is waiting, every plain SELECT that arrives after it has to queue too.

Session A

Session B

Session C

SELECT … (in a long txn)→ holds ACCESS SHARE on accounts

ALTER TABLE accounts …→ ⏳ waits — ACCESS EXCLUSIVE conflicts with A

SELECT … (a plain read)→ ⏳ blocked behind B's queued ACCESS EXCLUSIVE

COMMIT→ B's ALTER runs, then C's read drains

A

B

C

SELECT (a long-lived transaction)

ALTER TABLE … ADD COLUMN→ ⏳ waits

SELECT — just a read!→ ⏳ waits

COMMIT

⏵ ALTER TABLE … ADD COLUMN→ completes

⏵ SELECT — just a read!→ completes

A is any long-lived transaction that has touched the table — a report, a stuck job…

A> BEGIN;
BEGIN

A> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     100 
(1 row)

The migration needs ACCESS EXCLUSIVE, so it waits for A. Expected. But now —

B> ALTER TABLE accounts ADD COLUMN note text;
⏳ B is waiting for a lock…

— every new query on the table queues behind the waiting ALTER. This is the outage.

C> SELECT balance FROM accounts WHERE id = 1;
⏳ C is waiting for a lock…

M> SELECT waiter.application_name AS waiter, blocker.application_name AS blocker
   FROM pg_stat_activity waiter
   JOIN pg_stat_activity blocker ON blocker.pid = ANY (pg_blocking_pids(waiter.pid))
   WHERE waiter.wait_event_type = 'Lock'
   ORDER BY waiter.application_name, blocker.application_name;
 waiter | blocker 
--------+---------
 B      | A       
 C      | B       
(2 rows)

Only when A ends does the pile-up drain — migration first, then the reads.

A> COMMIT;
COMMIT

⏵ B resumes:
ALTER TABLE

⏵ C resumes:
 balance 
---------
     100 
(1 row)

Verified against PostgreSQL 18.4 · Run it yourself · Scenario source

The cruel part: the ALTER itself is instant. What kills you is a waiting ALTER, because every later query (reads included) must queue behind its ACCESS EXCLUSIVE request. One forgotten BEGIN in a console session plus one routine migration equals a full table outage.

Never run DDL without lock_timeout

A migration that fails fast and retries is a non-event; a migration that queues is an outage.

The fix: lock_timeout + retry

A

B

C

SELECT (long-lived txn)→ holds ACCESS SHARE

ALTER TABLE …→ gives up after 100ms ← 55P03 canceling statement due to lock timeout

SELECT→ instant, nothing queued behind the ALTER

COMMIT

ALTER TABLE … (retry sails through)

A> BEGIN;
BEGIN

A> SELECT balance FROM accounts WHERE id = 1; -- the same long transaction as before
 balance 
---------
     100 
(1 row)

Same migration — but this time it gives up after 100ms instead of camping in the queue.

B> SET lock_timeout = '100ms';
SET

B> ALTER TABLE accounts ADD COLUMN note text;
ERROR:  55P03: canceling statement due to lock timeout

No waiting ALTER in the queue means no outage: C's read is instant.

C> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     100 
(1 row)

A> COMMIT;
COMMIT

Retry the migration when it can actually get the lock — now it sails through.

B> ALTER TABLE accounts ADD COLUMN note text;
ALTER TABLE

Verified against PostgreSQL 18.4 · Run it yourself · Scenario source

Set lock_timeout once in your migration tool, per session rather than in postgresql.conf. The manual advises against setting it globally because it would trip every session, not only your migrations. A migration that can't get its lock now fails fast and retries instead of parking a ACCESS EXCLUSIVE request at the head of the queue.

That leaves one number to respect: the blast radius. It's the longest transaction currently touching the table multiplied by all the traffic on that table, which is why long-running transactions and migrations are natural enemies. It also helps to know that not every ALTER TABLE is equally dangerous: the manual lists the lock each form takes, and some are gentle. SET STATISTICS needs only SHARE UPDATE EXCLUSIVE, which blocks neither reads nor writes. A plain CREATE INDEXlocks out writes but not reads; CREATE INDEX CONCURRENTLY exists to avoid even that. When you do want to watch a pile-up form live, the wait-chain query from the transcript is unpacked in the next lesson on monitoring locks.

Further reading

• PostgreSQL docs: Table-Level Locks, all eight modes and their conflict table
• PostgreSQL docs: ALTER TABLE
• The same lesson on MySQL