- Book: Database Playbook
- Also by me: Thinking in Go (2-book series) — Complete Guide to Go Programming + Hexagonal Architecture in Go
- My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
- Me: xgabriel.com | GitHub
Your migration adds one nullable column. That has always been a catalog change with no table rewrite, and since PostgreSQL 11 even a column with a constant default skips the rewrite. The ALTER TABLE docs say so directly: "In neither case is a rewrite of the table required." Then the deploy goes out and your API stops answering queries on that table until something gives.
GoCardless wrote up the same lock queue with a different DDL: "around 15 seconds of unexpected API downtime during a planned database migration." They were adding foreign keys, and their post explains that this also locks the referenced tables, which were "some of the most heavily used." The ALTER itself "was fast to execute." It had to wait for a lock behind a long-running read, and "read/write queries issued by calls to our API piled up behind it, and clients timed out."
You can build that pile-up on your laptop in five minutes, then cap it at a two-second stall.
Why an instant ALTER still waits
Two facts from the Postgres docs combine into the outage.
First, the lock. The ALTER TABLE page says: "An ACCESS EXCLUSIVE lock is acquired unless explicitly noted." ADD COLUMN has no note, so it takes the strongest lock there is. The explicit locking chapter says ACCESS EXCLUSIVE "conflicts with locks of all modes," including the ACCESS SHARE lock that every plain SELECT takes. Fast or not, the ALTER cannot start while any transaction holds even a read lock on the table.
Second, the queue. Lock requests wait in line. The docs for pg_blocking_pids() define two kinds of blocking: a "hard block" from a session that holds a conflicting lock, and a "soft block" from a session that "is waiting for a lock that would conflict with the blocked process's lock request and is ahead of it in the wait queue."
That soft block is the whole story. A new SELECT wants ACCESS SHARE, which is perfectly compatible with the slow SELECT already running. It still waits, because the ALTER is ahead of it in the queue and the ALTER's ACCESS EXCLUSIVE conflicts with everything. So does every INSERT, UPDATE and DELETE that arrives after it. And the docs say the ALTER will wait for as long as it takes: a transaction seeking a lock "will wait indefinitely for conflicting locks to be released" unless a deadlock is detected.
One slow reader holds the gate. The ALTER waits at it, and everything behind the ALTER waits too.
Reproduce it with four psql sessions
You need Docker and psql, which the image already ships. Start a throwaway Postgres 18:
docker run -d --name pglock \
-e POSTGRES_PASSWORD=pg postgres:18
Open four terminals and run docker exec -it pglock psql -U postgres in each. Three of them build the pile-up. The fourth watches.
In terminal 1, create a table with some rows:
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
total numeric(10,2) NOT NULL
);
INSERT INTO orders (total)
SELECT (random() * 100)::numeric(10,2)
FROM generate_series(1, 100000);
Still in terminal 1, start the slow reader. This stands in for a report query, an analytics export or a forgotten console session. It holds ACCESS SHARE on orders for five minutes:
SELECT pg_sleep(300) FROM orders LIMIT 1;
In terminal 2, run the migration:
ALTER TABLE orders ADD COLUMN note text;
It hangs. In terminal 3, play the app:
SELECT count(*) FROM orders;
That hangs too, even though it has no conflict with terminal 1. Every new statement that touches orders, reads and writes alike, now waits for a five-minute sleep it has nothing to do with.
Watch the queue in pg_locks and pg_blocking_pids()
In terminal 4, list who is waiting on whom. The pg_locks docs recommend pg_blocking_pids() over a hand-written self-join of pg_locks, so run it against pg_stat_activity:
SELECT pid,
pg_blocking_pids(pid) AS blocked_by,
wait_event_type,
left(query, 45) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
You should get two rows. The ALTER's pid lists terminal 1's pid in blocked_by: that is the hard block. The count(*) lists the ALTER's pid, not terminal 1's: that is the soft block, and it is the row that explains the outage. Both show a wait_event_type of Lock.
Now look at the raw locks on the table:
SELECT pid, mode, granted, waitstart
FROM pg_locks
WHERE relation = 'orders'::regclass
ORDER BY granted DESC, waitstart;
Terminal 1 holds AccessShareLock with granted true. The ALTER wants AccessExclusiveLock with granted false, and the count(*) wants AccessShareLock, also not granted. The waitstart column (added in Postgres 14) tells you how long each one has been stuck. One caveat from the docs: pg_locks "does not expose information about which processes are ahead of which others in lock wait queues," so use pg_blocking_pids() when you need the order.
Go back to terminal 1 and press Ctrl+C to cancel the sleep. The ALTER gets its lock, finishes, and releases it, and the count(*) in terminal 3 returns right after. The migration was never the slow part.
Fix one: lock_timeout plus a retry loop
The ALTER cannot skip the line, but it can refuse to stand in it for long. lock_timeout aborts "any statement that waits longer than the specified amount of time while attempting to acquire a lock," and the limit "applies separately to each lock acquisition attempt." Put it at the top of the migration file:
-- 0042_add_order_note.sql
SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN IF NOT EXISTS note text;
Re-run the reproduction: start the slow reader in terminal 1 again, then paste those two lines into terminal 2 instead of the bare ALTER. The count(*) in terminal 3 still waits, but for at most two seconds. Then the ALTER fails with SQLSTATE 55P03, which the error code table names lock_not_available, leaves the queue, and the reads go through.
Pick the number as the longest pause your app can absorb, because that is what every query behind the ALTER pays on each attempt. Published defaults vary a lot: Squawk's example uses 1s, strong_migrations suggests 10.seconds, and PostgresAI's retry write-up uses 50ms.
The migration still has to land, so wrap it in a retry loop with a growing, jittered delay. This wrapper belongs in your deploy pipeline, where psql and $DATABASE_URL point at the real database, not in the Docker repro:
#!/usr/bin/env bash
# retry-ddl.sh FILE: run a migration until it gets its lock
set -u
file=$1
for attempt in $(seq 1 10); do
psql "$DATABASE_URL" -X -1 -v ON_ERROR_STOP=1 -f "$file"
rc=$?
[ "$rc" -eq 0 ] && exit 0
[ "$rc" -ne 3 ] && exit "$rc" # not a SQL error: stop
delay=$(( attempt * 2 + RANDOM % 3 ))
echo "attempt $attempt failed, retry in ${delay}s" >&2
sleep "$delay"
done
echo "gave up after 10 attempts" >&2
exit 1
-1 wraps the file in one transaction, so a failed attempt rolls back cleanly. Per the psql docs, exit status 3 means "an error occurred in a script and the variable ON_ERROR_STOP was set," so connection failures stop the loop instead of retrying. The loop does retry any SQL error, not only lock timeouts, which is why the file uses IF NOT EXISTS and why it gives up after ten tries. If you would rather keep the retry inside the database, the PostgresAI post has a PL/pgSQL DO block that catches lock_not_available with exponential backoff.
Wait two seconds, step out of line, retry later. Each attempt costs the reads behind it at most two seconds.
One trap: lock_timeout is per lock, not per migration. If one transaction alters orders and then waits on customers, it holds ACCESS EXCLUSIVE on orders the whole time it waits. Keep one table's DDL per file.
Fix two: put a ceiling on long readers
The retry loop keeps the app alive while a slow reader exists. It does not help the migration land if the reader runs for an hour. Cap long readers per role instead of globally, since the docs warn that setting statement_timeout in postgresql.conf "would affect all sessions":
ALTER ROLE reporting SET statement_timeout = '30s';
ALTER ROLE app SET idle_in_transaction_session_timeout = '60s';
statement_timeout cancels the five-minute pg_sleep kind of reader. idle_in_transaction_session_timeout ends the other common culprit: a session that ran a SELECT inside BEGIN and never committed, which holds its ACCESS SHARE lock until the transaction ends. Both apply to new sessions for that role.
Before a deploy, look for anything old enough to stall you:
SELECT pid, now() - xact_start AS age, state,
left(query, 45) AS query
FROM pg_stat_activity
WHERE xact_start < now() - interval '1 minute'
ORDER BY xact_start;
The one line to require in generated migrations
ORMs and coding agents are good at writing the ALTER TABLE. Neither knows what else is running against your database at deploy time, and nothing about an ADD COLUMN statement looks dangerous in a diff. So make the safety line mandatory and check for it mechanically. Every migration file starts with:
SET lock_timeout = '2s';
Put that rule in the instructions file your coding agent reads, run the file through the retry wrapper, and enforce it in CI with Squawk, which has a rule for exactly this, require-lock-timeout, and flags a bare alter table t add column c boolean;. On Rails, strong_migrations sets the same timeout for every migration from one config line.
Then run the reproduction once more with the line in place and watch terminal 3. Two seconds of waiting, then the reads come back.
If this was useful
How a store behaves while you change its schema under live traffic belongs in the decision about which store to use. That is the kind of trade-off I wrote the Database Playbook around. If you are picking a store for a new system, or about to let an ORM or an agent own your migrations, it is meant to help you ask those questions early.
Top comments (0)