Here is how a migration that does no work at all takes a table offline.
ALTER TABLE orders ADD COLUMN note text;
A nullable column, no default. This is a catalog change; on its own it holds its lock for a few milliseconds. It does not matter how big the table is. What matters is who else is holding a lock on the table when it asks for its own, and that is something the migration author can not see and did not think about, because the statement is so obviously harmless.
The queue
ALTER TABLE needs ACCESS EXCLUSIVE, the lock that conflicts with every other lock including the ACCESS SHARE a plain SELECT takes. So if any transaction is holding any lock on the table, the ALTER waits. A long report query. A transaction some worker opened, ran one select in, and then went to make an HTTP call. An interactive psql session someone forgot about at lunch.
Now the important part. Postgres grants locks in order. Once the ALTER is in the queue, every new request that conflicts with ACCESS EXCLUSIVE queues behind it, and everything conflicts with ACCESS EXCLUSIVE. Every read. Every write. The table is not locked by the ALTER, which has not started yet. It is locked by the ALTER's request, on behalf of a transaction that is doing nothing.
We reproduced it on Postgres 16 with a 20 million row table. Session one opens a transaction, runs one cheap select, and idles. Session two runs the ALTER. Session three runs an ordinary point read.
pid | state | wait_type | wait_event | query
-------+--------+-----------+------------+---------------------------------------------
10542 | active | Timeout | PgSleep | BEGIN; SELECT count(*) FROM orders WHERE id < 10
10549 | active | Lock | relation | ALTER TABLE orders ADD COLUMN note text;
10556 | active | Lock | relation | SELECT count(*) FROM orders WHERE id = 5;
pid | mode | granted | query
-------+---------------------+---------+------------------------------------------
10542 | AccessShareLock | t | BEGIN; SELECT count(*) FROM orders WHERE
10549 | AccessExclusiveLock | f | ALTER TABLE orders ADD COLUMN note text;
10556 | AccessShareLock | f | SELECT count(*) FROM orders WHERE id = 5
The point read at the bottom wants the same AccessShareLock the idle session already holds, and those two do not conflict with each other at all. It is waiting anyway, because the ALTER is in front of it. In our run the idle transaction held on for thirty seconds, the ALTER finished after 28.1 s, and the point read, a query that takes under a millisecond, waited 26.1 s. Multiply by every connection in the pool and that is the outage: not a slow migration, a fast one, stuck behind something irrelevant, with the whole application stuck behind it.
The one line
SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN note text;
Same scenario, same idle transaction. The ALTER waited two seconds, gave up with canceling statement due to lock timeout, and left the queue. The point read that had been queued behind it ran immediatly: 0.1 s including the round trip. Nothing was locked, nothing was changed, and the migration can be retried in a loop until it gets a gap, which on most systems is the very next attempt.
Two details that matter. lock_timeout is not statement_timeout: the latter limits how long the statement may run, which for an index build or a backfill is the wrong thing to cap, while the former only limits how long it may wait for a lock, which is exactly the dangerous part. And set it per session or per migration, not globally; the application's own queries should not be cancelled for waiting on a lock, only the schema change should.
-- a retry loop the migration runner can own
DO $$
BEGIN
FOR i IN 1..10 LOOP
BEGIN
SET LOCAL lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN note text;
RETURN;
EXCEPTION WHEN lock_not_available THEN
PERFORM pg_sleep(1);
END;
END LOOP;
RAISE 'could not take the lock after 10 attempts';
END $$;
Why almost nobody adds it
Because the statement without it works. i have added this line to more migrations than i can count and it has never once been the wrong call, but every time, in development, in staging, and in production nine times out of ten, the version without it works too. The tenth time is the one with the idle transaction, and by then the migration has been copy-pasted from the previous nine. The guard is not a fix for a bug in the SQL. It is an admission that the SQL will run in an environment the author can not see, which is true of every migration and is the whole reason BV034 exists: it fires on exclusive-lock DDL that has no lock_timeout in scope, so the line gets added before the tenth time, not after.
Originally published at bolvrk.com. Bolvrk is a deterministic checker for database migrations and committed credentials: free CLI, GitHub App and Action.
