Finding the Blocking Session with pg_blocking_pids

A migration is hanging, or the application has started timing out on one table, and you need to know who is responsible in the next sixty seconds. pg_stat_activity shows a crowd of sessions in wait_event_type = 'Lock', but a crowd is not an answer: most of them are victims, waiting on other victims, and only one or two sessions at the root of the chain are actually holding what everyone needs. pg_blocking_pids(pid) is the PostgreSQL function that answers the question directly — it returns the pids blocking a given session — and with one recursive query it gives you the whole tree. This guide is the triage runbook for the lock problems described in DDL Lock Management & Timeouts: find the root, decide what to do with it, and keep enough evidence to prevent a repeat.

Symptom / Error Signatures

Reach for this runbook when you see any of:

• Many rows in pg_stat_activity with wait_event_type = 'Lock' and a query_start that keeps getting older.
• A migration step in CI that has produced no output for longer than its normal runtime.
• Log lines from log_lock_waits, for example process 4187 still waiting for AccessExclusiveLock on relation 16402 of database 16384 after 1000.089 ms, followed by DETAIL: Process holding the lock: 4102. Wait queue: 4187, 4190, 4191, 4193.
• Application errors that are timeouts on otherwise trivial queries — canceling statement due to statement timeout on a primary-key lookup is a strong hint that the query spent its time in a lock queue.

Root Cause Analysis

pg_blocking_pids(pid) returns an array of the process ids that hold a lock the given session is waiting for, or that are ahead of it in the queue with a conflicting request. That second clause is what makes lock chains confusing: when an application SELECT is queued behind a waiting ALTER TABLE, pg_blocking_pids names the ALTER, even though the ALTER holds nothing yet. The function reports the immediate blocker, not the root, so you have to walk the chain upward until you reach a session that is not itself waiting.

The raw source of truth is pg_locks, which lists every lock held or requested, with granted = true/false. pg_blocking_pids exists because joining pg_locks against itself correctly for all lock types is notoriously hard; the function does it inside the lock manager and is the supported way to get the answer since PostgreSQL 9.6.

Immediate Mitigation

1. Print the chain from every root. This recursive query starts from sessions that block others but are not themselves blocked, and walks down to their victims.

-- PostgreSQL 9.6+ · read-only · run as a role with pg_read_all_stats (or superuser)
WITH RECURSIVE tree AS (
  SELECT a.pid, 0 AS depth, ARRAY[a.pid] AS path
  FROM pg_stat_activity a
  WHERE cardinality(pg_blocking_pids(a.pid)) = 0
    AND EXISTS (SELECT 1 FROM pg_stat_activity w WHERE a.pid = ANY (pg_blocking_pids(w.pid)))
  UNION ALL
  SELECT w.pid, t.depth + 1, t.path || w.pid
  FROM tree t
  JOIN pg_stat_activity w ON t.pid = ANY (pg_blocking_pids(w.pid))
  WHERE NOT w.pid = ANY (t.path)
)
SELECT repeat('  ', t.depth) || t.pid AS pid_tree, a.usename, a.state,
       now() - a.xact_start AS xact_age, left(a.query, 60) AS query
FROM tree t JOIN pg_stat_activity a USING (pid)
ORDER BY t.path;

2. Decide based on what the root is doing. Use the table above. If the tree’s second level is a waiting DDL, cancelling the DDL immediately frees every application session below it and costs nothing, because the DDL has not started work.

3. Cancel before you terminate. pg_cancel_backend stops the current statement but keeps the connection; it does nothing to an idle in transaction session, because there is no statement to cancel. For those, pg_terminate_backend closes the connection and rolls back the transaction.

-- PostgreSQL · requires pg_signal_backend role membership (or superuser)
-- WARNING: terminate rolls back the root's open transaction; confirm it is safe to lose.
SELECT pg_cancel_backend(4187);      -- the waiting migration: frees the application sessions
SELECT pg_terminate_backend(4102);   -- the idle-in-transaction root: frees the migration

4. Capture evidence before it disappears. Once the root is gone, so is the information about it. Save the tree output, the root’s application_name, client_addr and backend_start, and the query text, then attach it to the incident.

-- PostgreSQL · read-only · run before terminating the root
SELECT pid, usename, application_name, client_addr, backend_start, xact_start,
       state_change, state, query
FROM pg_stat_activity WHERE pid = 4102;

Permanent Fix / Long-Term Pattern

Triage fixes today’s stall; the long-term work is to make stalls short and rare. Short: every migration sets lock_timeout, so the second level of the tree — the DDL — gives up on its own before the application notices, as covered in setting lock_timeout and retrying DDL safely. Rare: the roots are removed. Set idle_in_transaction_session_timeout for application roles, find the code paths that leak transactions from the captured application_name and query text, and move long reports off the primary.

Make the chain query part of your tooling rather than something typed under pressure. Save it as a view (CREATE VIEW ops.lock_tree AS ...) owned by a monitoring role, graph the count of sessions waiting on locks per table as described in monitoring long-running migrations in production, and alert when a session has been at depth one or deeper for more than a few seconds during a deploy.

Verification Checklist

• The recursive lock-tree query runs successfully in staging and is saved in the on-call runbook or as a view.
• On-call engineers have pg_signal_backend membership (or a break-glass procedure) so they can cancel and terminate sessions.
• A staging drill with an open transaction and a waiting ALTER TABLE produced the expected two-level tree.
• idle_in_transaction_session_timeout is set for application roles, confirmed with SHOW idle_in_transaction_session_timeout in an application session.
• Lock-wait counts per table are graphed and alert during deploy windows.

Frequently Asked Questions

Why does pg_blocking_pids name a session that holds no locks? Because it also reports sessions ahead in the queue with conflicting requests. A queued ALTER TABLE holds nothing yet, but new SELECTs wait behind it, so it is reported as their blocker. Walk the chain upward to find the session that actually holds the lock.

Is pg_blocking_pids expensive to call on a busy server? It takes a brief lock on the lock manager’s shared state, so calling it for every session in a tight loop on a very large server is not free. Running the tree query a few times during an incident is fine; for continuous monitoring, sample every few seconds rather than continuously.

What is the difference between pg_cancel_backend and pg_terminate_backend? Cancel interrupts the current statement and leaves the session connected, so the application can continue. Terminate closes the connection and rolls back any open transaction. Cancel is useless against an idle-in-transaction session because there is no running statement.

Can autovacuum be the root blocker? Yes. Autovacuum takes SHARE UPDATE EXCLUSIVE, which conflicts with most ALTER TABLE forms. Ordinary autovacuum yields automatically when it blocks another lock request; anti-wraparound vacuum does not, and cancelling it only makes it restart later, so it is usually better to wait or to schedule the migration after it finishes.