A pending PostgreSQL lock blocks every reader, and it took us down twice

Twice this year our API stopped answering because of a PostgreSQL lock that had not even been granted yet. In June a pg_dump we started as a safety net before a deploy stalled every query on one table for about 13 minutes, and in September a write transaction stuck waiting on a cache call sat idle for 9 minutes and did the same thing to the same table.

Both came from the same piece of PostgreSQL behaviour: once an ACCESS EXCLUSIVE request is waiting in a table's lock queue, every request that arrives after it waits too, including plain SELECTs that would happily run alongside each other.

The rule both incidents share

A SELECT takes an ACCESS SHARE lock on the table it reads. Any number of sessions can hold ACCESS SHARE on the same table at once; the only lock mode it conflicts with is ACCESS EXCLUSIVE, which is what ALTER TABLE asks for.

PostgreSQL grants locks in request order. If an ALTER TABLE can't get its exclusive lock because somebody else holds a lock on the table, it waits in the queue, and the next SELECT that turns up conflicts with the waiting request and queues behind it. So does the one after that. The ALTER itself might be a no-op that would finish in milliseconds once granted, so all of the harm comes from the time it spends waiting.

That gives every one of these outages the same three parties:

• a holder: something holding any lock on the table for a long time, even a harmless shared one
• a waiter: DDL that wants ACCESS EXCLUSIVE on that table
• everyone else: ordinary readers, arriving at the rate your traffic sends them

Take any one of them away and nothing happens: a short-lived holder only delays the ALTER by a few milliseconds, with no DDL the holder and the readers share the table indefinitely, and if nobody reads the table, the ALTER can wait as long as it likes without anyone noticing.

In both of our incidents the table was teams, which sits on the team-resolution path of virtually every authenticated request. Once readers started queuing on it, the queue only ever grew.

June: a backup that wedged the API for 13 minutes

On 22 June 2026 we were rolling out a routine release. Our deploys are blue/green: the new version starts in its own slot next to the live one. Before starting, we kicked off a pg_dump of production as a backup safety net. It was a code-only deploy that didn't touch data, so in hindsight the dump was never needed.

pg_dump takes ACCESS SHARE on every table it dumps and holds those locks for the whole of its consistent snapshot. This one ran for about 14 minutes, most of it spent in the COPY of our largest vector table.

That was the holder. The waiter was our own startup code. The API service runs idempotent additive migrations every time it starts, in the style of ALTER TABLE ... ADD COLUMN IF NOT EXISTS, and one of them was:

ALTER TABLE teams ADD COLUMN slug TEXT

These are normally instant no-ops, but they still request a brief ACCESS EXCLUSIVE lock. This one queued behind the dump's shared lock, and from there it went downhill in two directions at once:

1. The new API slot hung in startup, after module load but before the web server bound its port. Its health check was refused instantly, and then Docker marked the container unhealthy.
2. Every teams query from the live API, the old version that was still serving all traffic, queued behind the pending ALTER. Our API health check is deep, so a wedged event loop reads as unhealthy. The live slot's health check started returning 504, and the public API went to 504s and refused connections.

The web app stayed at 200 the whole time, because its Next.js server-rendered shell doesn't need the API to render. The RAG service and all the workers stayed healthy as well, because none of them touch teams.

The healthy RAG service and workers are what pointed us at the cause. If PostgreSQL or Redis had fallen over, everything that talks to them would have gone red. Only the API was sick, and the API is the only thing that reads teams. That points at a lock on one table rather than a shared-infrastructure outage.

Finding the blocker with pg_stat_activity

We diagnosed it with one query against pg_stat_activity. It lists every session that is waiting on a lock, is blocked by another session, or has been running for more than two minutes, and shows who is blocking it:

SELECT pid,
       state,
       wait_event_type,
       left(query, 55),
       now() - query_start,
       pg_blocking_pids(pid),
       application_name
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
   OR cardinality(pg_blocking_pids(pid)) > 0
   OR now() - query_start > '2min'::interval
ORDER BY 5 DESC;

pg_blocking_pids() is what makes this fast. It returns the process IDs a session is waiting on, so the chain becomes explicit: the readers show as waiting on the ALTER, and the ALTER shows as waiting on something else. In June that something else was a session whose application_name was pg_dump, and the ALTER TABLE teams row listed its PID as the blocker.

We fixed it by terminating the dump:

SELECT pg_terminate_backend(<pg_dump_pid>);

Terminating the dump released its shared lock, the ALTER completed in milliseconds, and the lock queue drained. Both API slots recovered within seconds: the new one finished starting up and went healthy (its health check came back 200 in 2 ms), and the live one's queued queries ran. The public API went back to 200.

September: a cache call inside a write transaction

On 9 September 2026 the web app itself went unreachable. The report we got was that "accessing the chatbots caused it to crash."

The obvious suspect was the release we'd just shipped (v2.15.0), which batched the queries behind the knowledge-base list, squarely on the hot path, but it wasn't involved. The new code issues a read-only grouped COUNT(*) over its own short-lived connection, takes about 6 ms, takes no exclusive locks, opens no long transaction and never touches teams. Chatbot requests were victims: they resolve a team first, so they queued on teams like everything else.

The real chain looked like this:

Redis stalls
  └─ a cache delete() blocks with no deadline, inside an open write transaction
       └─ the transaction sits `idle in transaction` for 9 minutes, holding its locks
            └─ ALTER TABLE teams ADD COLUMN slug queues for ACCESS EXCLUSIVE
                 └─ every SELECT on teams queues behind the pending lock
                      └─ request workers exhausted; /health cannot be answered

The holder this time was a write transaction that invalidated a cache entry in Redis before it committed. The Redis client for that cache had been built with the library defaults, and redis-py's default is socket_timeout=None: block forever. When Redis stalled, the delete() never returned, so the transaction never committed or rolled back. From PostgreSQL's side the session sat idle in transaction for 9 minutes, doing nothing while it held its locks.

The waiter was a migration runner, and it's the part that made this incident hard to see coming. That runner doesn't run at boot. It runs lazily, on the first database call in a process from the module that owns it, which is why it fired about 17 hours after the container started, with no deploy anywhere near it. When it does run, it issues 14 DDL statements against two of our busiest tables.

Five of those 14 were ALTER COLUMN ... TYPE NUMERIC widenings on columns that were already NUMERIC. The column already being the right type makes the rewrite free, but PostgreSQL still takes ACCESS EXCLUSIVE to check. With lock_timeout = 0, a statement that couldn't get its lock waited forever.

All three of the relevant PostgreSQL timeouts were 0 (unset) on production: idle_in_transaction_session_timeout, lock_timeout and statement_timeout. Either of the first two would have put a ceiling on this. An idle-in-transaction timeout would have ended the holder's session; a lock timeout would have failed the waiter and let the readers through.

The two incidents had the same table and the same kind of waiter, an idempotent ALTER that had no real work to do. The holders differed: in June it was doing legitimate work for a long time, and in September it was doing nothing at all, waiting on the network with a transaction open.

What we changed

The fixes shipped in v2.15.2 and fall into three groups. One removes the trigger, so a slow Redis can no longer hold a transaction open:

• The cache's Redis client now has connect and read/write deadlines of 2 s, with retry_on_timeout=False. That cache has a 5 s TTL, so if it can't answer in 2 s we treat the call as a miss.
• Invalidation moved to after the commit, so it isn't inside a transaction at all. This turned out to be more correct as well as safer: a rolled-back write no longer evicts a cached value that is still valid.

Another limits the blast radius in the migration runner, so a stuck transaction can no longer stall the site:

The pre-check matters most, because it removes the waiter entirely in the normal case. The five no-op NUMERIC widenings from the incident would never be sent at all. In plain SQL, the per-statement lock timeout has this shape:

BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE teams ADD COLUMN IF NOT EXISTS slug TEXT;
COMMIT;

If the lock isn't available within 3 s, the statement errors out and leaves the queue, and the readers behind it carry on.

We deliberately left statement_timeout off by default on that connection pool. A background job shares the same engine and runs long batches that have needed more than 300 s in the past, so capping statements would swap one outage for another.

The last is a server-wide safety net, applied unconditionally by our deployment tooling:

ALTER SYSTEM SET idle_in_transaction_session_timeout = '2min';
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET deadlock_timeout = '1s';
SELECT pg_reload_conf();

pg_reload_conf() means no restart is needed. With log_lock_waits on, any session that waits on a lock longer than deadlock_timeout gets a log line, so the next pile-up leaves evidence after 1 s instead of us reconstructing it from pg_stat_activity in the middle of an outage. They don't sit behind our PostgreSQL performance-tuning switch, because they guard availability and that switch is for performance tuning. We also deliberately did not set a server-wide lock_timeout; it lives on the DDL statements that need it.

The fixes came with tests: 12 covering migration lock safety and 8 proving that a blocked cache call can no longer block a transaction.

The rules we run by now

These came out of the two incidents, and none of them is specific to our stack.

1. Every DDL statement that runs against a live table gets a lock_timeout, and no-op DDL is detected from the system catalogue and skipped before it asks for a lock. An idempotent statement still joins the lock queue like any other.
2. No network calls inside a database transaction. Cache invalidation, HTTP calls and queue publishes go after the commit, and every client has a deadline, because a transaction waiting on something other than the database can hold its locks indefinitely.
3. Never overlap a pg_dump with a deploy's startup DDL. A code-only deploy doesn't need a fresh dump. If a data-mutating step does need one, take it before the deploy starts and let it finish, or take it after the new version has cut over and no startup DDL is in flight.
4. Treat anything that holds a table-wide lock for minutes as a potential holder: pg_dump, a long COPY, a big Apache AGE write. Startup migrations that are instant no-ops on a quiet database will stall behind any of them.
5. When the API is failing and everything else is green, look at lock state before anything else. The query above answers the question in one go, and pg_blocking_pids() points straight at the session to deal with.