One long SELECT, one ALTER TABLE, and every query behind them

The version of this I keep meeting goes something like: a migration ran at ten past two, it added a nullable column, the change itself is instant because Postgres has stored those as metadata since version 11, and yet for about four seconds every request to the site timed out. Nobody can find a slow query, because there wasn't one. The migration shows up in the logs having taken a millisecond or two.

I wanted to watch that happen under conditions I controlled, so I built the smallest version of it I could: four sessions, one table, and a stopwatch on each.

Four sessions and a stopwatch

The table is orders, eight million rows, 1473 MB, on PostgreSQL 18.6 in a container on my laptop. Session A runs a deliberately slow read that takes just under four seconds. Session B runs ALTER TABLE orders ADD COLUMN note text. Sessions C1 and C2 run the identical statement as each other, a single-row primary key lookup, the cheapest thing in the database.

The only difference between C1 and C2 is when they arrive.

arrives t+0.0s  A  long SELECT                waited   3838.8 ms
arrives t+0.3s  C1 reader BEFORE the ALTER    waited      1.2 ms
arrives t+0.5s  B  ALTER TABLE                waited   3340.8 ms
arrives t+1.0s  C2 reader AFTER the ALTER     waited   2841.2 ms

Same query, same kind of connection, seven hundred milliseconds apart. One of them takes 1.2 milliseconds and the other takes 2.8 seconds, and the thing that separates them is whether they got in before or after a statement that was not going to touch anything they needed.

For completeness, here is what the ALTER costs when nothing is in its way, five runs:

2.47 ms   1.38 ms   1.83 ms   1.30 ms   1.33 ms

The reader is not waiting for the long query

This is the part that took me a while to accept, because the intuitive model is wrong in a specific way. C2 wants an AccessShareLock. A already holds an AccessShareLock. Those two are compatible. Nothing in Postgres's lock conflict table says a reader should wait for another reader.

Catching the three of them mid-block and asking the server directly:

pid=230   wait: none    blocked_by=[]      SELECT count(*) FROM orders WHERE md5(payload)
pid=231   wait: Lock    blocked_by=[230]   ALTER TABLE orders ADD COLUMN note text
pid=232   wait: Lock    blocked_by=[231]   SELECT id FROM orders WHERE id=42

pid=230   AccessShareLock       granted=true
pid=231   AccessExclusiveLock   granted=false
pid=232   AccessShareLock       granted=false

pg_blocking_pids(232) returns 231, not 230. The reader is queued behind the ALTER, and the ALTER is queued behind the long read. Postgres will not let a compatible request overtake an incompatible one that is already waiting, because if it did, a steady stream of readers on a busy table would starve the ALTER indefinitely. The queue is fair, and fairness here means the reader pays for the writer's wait.

Which also means the damage scales with how many clients arrive during the window rather than with anything about the operation. With ten readers behind the same blocked ALTER, all ten waited between 2.6 and 3.1 seconds, and all ten were released within a few milliseconds of each other when the long read finally finished.

Under something resembling load

Eight clients, each fetching one row by primary key every 50 milliseconds, for twenty seconds. At t=8 the long read starts, at t=8.5 the ALTER arrives.

The blue band of ordinary sub-millisecond reads simply stops. During the 3.3 seconds the ALTER spent waiting, eight reads completed, one per client, each of them the request that happened to be in flight. Peak latency was 3,292 milliseconds against a baseline around 0.4. Over the full twenty seconds the run served 2,680 reads, against 3,048 in the otherwise identical run further down where the ALTER gave up after a second.

If you are watching a dashboard, this does not look like a lock problem. It looks like the database stopped answering and then started again.

Who gets to skip the queue

One group is immune, and working out which one explains the shape of the outage.

A session that was already inside a transaction which had read orders before the ALTER arrived already holds its AccessShareLock. It does not need to ask again, so it does not join the queue:

txn already holding AccessShareLock :      0.7 ms
fresh connection, same query        :   2805.9 ms

So the requests that keep working are the ones already in flight, and the requests that hang are the new ones. On a web service backed by a connection pool, that is precisely the distribution that makes the incident confusing, since a long-lived worker mid-transaction sails through while every new request piles up behind the same lock.

It is the lock level, not the DDL

It would be easy to take the wrong lesson here and start avoiding schema changes. The problem is narrower than that. Running the same experiment with different statements in the middle:

Plain CREATE INDEX takes a ShareLock, which does not conflict with readers at all, so a table can be indexed under read traffic without anybody noticing, though writes queue for the whole build. CREATE INDEX CONCURRENTLY takes a weaker lock again and blocks neither. Only the statements that demand AccessExclusiveLock produce the effect in the first table, and that set is small and knowable: most ALTER TABLE forms, DROP, TRUNCATE, REINDEX, VACUUM FULL.

The fix is one line and it is not the one people reach for

The instinct is to make the migration faster, which does nothing, since it was already taking a millisecond. What you want is for it to give up rather than hold the door.

SET lock_timeout = '1s';
ALTER TABLE orders ADD COLUMN note text;

With that set, the median reader stall across trials went from 2812 ms to 501 ms, and the reader's wait becomes bounded by your timeout rather than by the longest query running on the table. The ALTER fails, loudly, with text you can match on in a deploy script:

ERROR:  canceling statement due to lock timeout

If you want to fail instantly instead of waiting at all, LOCK TABLE ... NOWAIT returns in under a millisecond:

ERROR:  could not obtain lock on relation "orders"

Both of these turn an availability incident into a failed migration that you retry, which is a trade almost everyone would take if they had been asked.

What I got wrong

Three things, and the first one was useful.

Partway through I opened a session inside a transaction to test the queue-skipping behaviour and forgot to roll it back. That session sat idle in transaction holding its lock forever, so the ALTER behind it never completed, so every reader behind the ALTER never completed, and my whole test rig hung. I spent a few minutes assuming my threading was broken before looking at pg_stat_activity and finding I had reproduced the production version of this bug by accident, with a forgotten transaction as the root cause instead of a slow query.

The second was smaller but more embarrassing. My first NOWAIT test returned instantly with LOCK TABLE can only be used in transaction blocks, and I very nearly wrote that down as the result. The connection was in autocommit. That error is my client's fault and says nothing at all about locking.

The third is the one worth generalising. My first batch of five lock_timeout trials came back 3 successes and 2 failures, which would have supported a genuinely interesting and completely false claim about the timeout being unreliable. Those five trials ran immediately after I had force-terminated four wedged backends from the hang above. Repeating the identical loop in a clean process gave 10 out of 10, then 5 out of 5, then 5 out of 5, all within a millisecond of each other. Five runs on a database you have just yanked connections out of is not a measurement, and an inconsistent result is a reason to go and look rather than a finding.

What to do about it

Set lock_timeout in whatever runs your migrations. Not statement_timeout, which governs execution rather than lock acquisition, and not a global value in postgresql.conf, because you want ordinary queries to keep waiting normally. A second or two on the migration session, with a retry around it, is the whole change.

Then go and look for the long queries on your largest tables, because those are the fuse. A schema change is only dangerous in proportion to the longest thing already running on the table, and on a table where nothing runs longer than 50 milliseconds, none of this is worth worrying about.

And if you ever get an incident where new requests hang while in-flight ones finish normally, and no individual query is slow, pg_blocking_pids will tell you in one query which session everyone is actually waiting on. In my case it named a statement that had not managed to do anything at all yet.