Why does a fast ALTER TABLE take down production without lock_timeout?

Exclusive-lock DDL without a lock_timeout guard

Note: this works and blocks nothing, but a performance regression is likely.

What happens

Measured on Postgres 16 with a twenty million row table: one session held an ordinary open transaction with a finished SELECT, a second ran ALTER TABLE ... ADD COLUMN, a third ran a sub-millisecond point read. The ALTER waited for AccessExclusiveLock behind the idle session for 28.1 s, and the point read, which conflicts with nothing the idle session holds, waited 26.1 s because it was queued behind the ALTER. With SET lock_timeout = '2s' the ALTER gave up after two seconds and the point read ran in 0.1 s.

Why it is dangerous on a populated table

The migration itself is instant. What takes production down is the queue: every query on the table lines up behind the waiting ALTER, the connection pool fills, and the outage lasts as long as whatever the ALTER is waiting on. A lock_timeout turns that into a retry.

Measured

Both forms ran against the same table (20M rows, 1.7 GB) on Postgres 18.6, with one session inserting a row and another reading one row every 50 ms. Worst wait is the slowest of those probe calls while the statement ran. This run is from Sep 16, 2026; the whole benchmark has every scenario and how to run it.

	Statement	Time	Worst write wait	Worst read wait	Lock
Fires	ALTER TABLE ... ADD COLUMN behind an idle transaction, no lock_timeout	10.0 s	10.0 s	10.0 s	AccessExclusiveLock
Safe	SET lock_timeout = '2s', then the same ALTER TABLE	gave up after 2.0 s	2.0 s	2.0 s	none seen

Safe: canceling statement due to lock timeout

Fires on

ALTER TABLE orders ADD COLUMN region text;

The safe pattern

SET lock_timeout at the top of every migration that takes strong locks. If the lock cannot be acquired quickly the migration fails fast and can be retried at a quieter moment, instead of queueing, with all new traffic queueing behind it.

SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN region text;
-- can't get the lock in 5s → fail fast, retry later; traffic never stalls

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

Fires (2)

detach plain

-- A plain DETACH takes ACCESS EXCLUSIVE on the parent — needs the guard.
ALTER TABLE events DETACH PARTITION events_2024;

no guard

ALTER TABLE orders ADD COLUMN region text;

Stays silent (4)

detach concurrently

-- DETACH PARTITION CONCURRENTLY holds only SHARE UPDATE EXCLUSIVE:
-- no lock_timeout guard is needed, nothing queues behind it.
ALTER TABLE events DETACH PARTITION events_2024 CONCURRENTLY;

drop index concurrently

-- DROP INDEX CONCURRENTLY takes SHARE UPDATE EXCLUSIVE by design.
DROP INDEX CONCURRENTLY idx_orders_region;

validate only

-- VALIDATE CONSTRAINT alone takes SHARE UPDATE EXCLUSIVE only —
-- the scan never blocks traffic, guard or no guard.
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_check;

with lock timeout

SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN region text;

How to check locally

Catch this before it ships

This rule runs in the hosted service on Startup and above: add --remote with a team token, or use the GitHub Action. No install, nothing leaves your machine:

npx bolvrk check migration.sql --remote

Browse all 135 rules

← BV033 BV035 →