One ALTER TABLE can block every query on a Postgres table

· updated · Vladimir Chemeris, the developer of HeapDeck

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 the API requests that touch orders time out, connections climb toward max_connections, and the migration still runs. The migration waits for a lock, and the rest of the traffic waits behind the migration.

The lock it needs

Most forms of ALTER TABLE take an ACCESS EXCLUSIVE lock on the table. That mode conflicts with all other lock modes, including the ACCESS SHARE lock a plain SELECT takes. So the ALTER can start once no other transaction holds a lock on orders.

A transaction holds its locks until commit or rollback. A transaction that ran SELECT … FROM orders ten minutes ago and now sits 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.

The readers stop too

A waiting migration turns into an outage through the lock queue. PostgreSQL checks a new SELECT’s request for ACCESS SHARE against the locks other sessions hold and against the requests 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. the queries on orders that arrived after it, waiting behind the ALTER.

At this point the ALTER holds no lock on orders, and each query on the table waits.

Seeing the chain

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

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 session, blocking others and blocked by nobody, is the root.

Picking what to stop

You have two ways out, and the cheaper one to redo wins.

Cancel the ALTER. The queue drains at once: the readers waited behind it and nothing else. The migration fails, and you run it again later with the protections below. pg_cancel_backend(<pid of the ALTER>) does the job, 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, because the root runs no statement. You need pg_terminate_backend(), which closes the session and rolls its transaction back, and the application that owned it sees a dropped connection.

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

Keeping it from happening again

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

On the phone

We designed HeapDeck’s Locks screen around this case. 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 the wait time of each. The button on the root counts the sessions that wait on the root itself, here the ALTER. For a root sitting idle in transaction, the button is Terminate root, because a cancel has no statement to stop. To cancel the migration instead, swipe its row in Activity.

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

Questions

Can you run ALTER TABLE without locking?

Not without any lock: most forms take ACCESS EXCLUSIVE, even if only for milliseconds. The trouble starts when the ALTER has to wait for it. lock_timeout makes it give up and retry, so it doesn't hold up the queue.