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