Why one ALTER TABLE can stop every query in Postgres

A deploy runs a migration:

ALTER TABLE orders ADD COLUMN refunded_at timestamptz;

Adding a nullable column is a catalog change that takes milliseconds on any table size. A minute later every API request that touches orders is timing out, connections climb toward max_connections, and the migration is still running. It isn’t slow. It is waiting, and everything else is waiting behind it.

The lock it needs

Almost every form of ALTER TABLE takes an ACCESS EXCLUSIVE lock on the table. That mode conflicts with every other lock mode, including the ACCESS SHARE lock a plain SELECT takes. So the ALTER can only start once no other transaction holds any lock on orders.

Locks are held until the end of the transaction, not the end of the statement. A transaction that ran SELECT … FROM orders ten minutes ago and is now idle in transaction, because the application opened it and went off to call another service, still holds its ACCESS SHARE lock. The ALTER waits for it.

Why the readers stop too

A waiting migration becomes an outage because of how the queue works. When a new SELECT asks for ACCESS SHARE on orders, PostgreSQL doesn’t only check the locks that are held. It also checks the requests already waiting in line. The waiting ACCESS EXCLUSIVE request conflicts with the new ACCESS SHARE, so the new SELECT queues behind the ALTER. Without that rule, a steady stream of readers could keep an exclusive lock waiting forever.

The result is a chain three levels deep:

1. the old transaction, holding ACCESS SHARE, often idle in transaction;
2. the ALTER TABLE, waiting for ACCESS EXCLUSIVE;
3. every query on orders that arrived after it, waiting behind the ALTER.

Nothing holds an exclusive lock yet, and nobody can read the table.

Seeing the chain

pg_blocking_pids(pid) reports both kinds of blocking: a session that holds a conflicting lock, and a session that is ahead in the queue with a conflicting request. That makes the whole chain visible:

SELECT pid,
       pg_blocking_pids(pid)  AS blocked_by,
       state,
       now() - xact_start     AS xact_age,
       left(query, 60)        AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND state <> 'idle'
ORDER BY xact_start;

In the output, the readers list the ALTER’s pid in blocked_by, the ALTER lists the old transaction, and the old transaction lists nothing. That one, blocking others and blocked by nobody, is the root.

What to stop first

You have two ways out, and the choice depends on what is cheaper to redo.

Cancel the ALTER. The queue drains at once: the readers were only waiting behind it. The migration fails and has to run again later, ideally with the protections below. pg_cancel_backend(<pid of the ALTER>) is enough, because the ALTER is a running statement.

End the root transaction. The ALTER gets its lock, finishes in milliseconds, and the readers follow. If the root is idle in transaction, pg_cancel_backend() does nothing, since there is no running statement to cancel. You need pg_terminate_backend(), which closes the session and rolls its transaction back, and the application that owned it will see a dropped connection.

If the root is a report someone forgot, ending it is usually the better choice. If it belongs to work that must finish, cancel the migration and talk to whoever owns the deploy.

Making it not happen again

• Give migrations a lock_timeout. With SET lock_timeout = '3s'; before the ALTER, the migration gives up after three seconds instead of building a queue. Retry it a few times with a pause in between; most of the time the blocking transaction is gone by the second try.
• Bound idle transactions. idle_in_transaction_session_timeout ends sessions that sit in an open transaction longer than you allow. Setting it for application roles, for example to a few minutes, removes the most common root of these chains.
• Keep long reads off the primary. Reports and exports that hold a transaction open for minutes belong on a replica.
• Build indexes concurrently. CREATE INDEX CONCURRENTLY takes a lock that doesn’t block reads or writes, at the cost of a slower build.

On the phone

This is the case HeapDeck’s Locks screen was designed around. The Blocked tile on Health turns red once three sessions wait. Locks then shows the old transaction as ROOT BLOCKER, the ALTER under it, and the readers under the ALTER, with how long each has waited. Cancel root says how many sessions it would free; for an idle root, Terminate root is in the menu next to it. To cancel the migration instead, swipe its row in Activity.

More about the screen: finding blocking queries from your iPhone.