PostgreSQL: find the query that's blocking everything

0. Protect your own session

SET statement_timeout = '10s';
SET lock_timeout = '1s';

This stops your diagnostic queries from joining the queue themselves.

1. Who is blocked, and by whom?

SELECT blocked.pid AS blocked_pid, now() - blocked.query_start AS blocked_for,
       left(blocked.query, 80) AS blocked_query,
       blocking.pid AS blocking_pid, blocking.state AS blocking_state,
       now() - blocking.xact_start AS blocking_xact_age,
       left(blocking.query, 80) AS blocking_query
FROM pg_stat_activity blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity blocking ON blocking.pid = b.pid
ORDER BY blocked_for DESC;

pg_blocking_pids() does the hard work of matching lock modes, so you don't need the old self-join on pg_locks. Run it as a role with pg_monitor. Otherwise other users' queries show as <insufficient privilege>.

2. Find the root blocker

Blocking forms chains: A blocks B, and B blocks C, D and E. Killing B does nothing. You want the pid that blocks others but isn't blocked itself:

SELECT pid, state, now() - xact_start AS xact_age, left(query, 100) AS query
FROM pg_stat_activity
WHERE pid IN (SELECT unnest(pg_blocking_pids(pid)) FROM pg_stat_activity)
  AND cardinality(pg_blocking_pids(pid)) = 0;

3. Recognise the pattern

4. Act, carefully

SELECT pg_cancel_backend(<pid>);     -- cancel the current query (gentle)
SELECT pg_terminate_backend(<pid>);  -- kill the session and roll back its transaction

Try cancel first. It does nothing to an idle in transaction session, because there's no running query to cancel, so those need terminate. Terminating rolls back the session's work, so make sure you know what the transaction was doing. A half-done data migration rolled back is fine. A payment that was mid-way through needs checking.

5. Prevent it

• ALTER DATABASE app SET idle_in_transaction_session_timeout = '60s'; kills forgotten transactions automatically.
• In migrations, use SET lock_timeout = '3s'; and retry, so a migration can never sit in the queue blocking everything.
• Don't make network calls inside database transactions.
• Alert on the age of the oldest transaction, e.g. max(now() - xact_start) over 5 minutes.