You’re staring at a query that won’t finish, or a deploy that hangs on a migration, or a UI request that just sits there. Postgres is almost certainly waiting on a lock that another transaction holds. The classic query for finding the culprit comes from the PostgreSQL wiki — it joins pg_locks against itself and against pg_stat_activity to show every blocked session and the session that’s blocking it:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS current_statement_in_blocking_process FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.granted; |
A few useful additions.
The modern shortcut: pg_blocking_pids. Since PostgreSQL 9.6 there’s a built-in function that does the same join in a single line — pg_blocking_pids(pid) returns an array of PIDs that are blocking the given PID. The whole “who’s blocking whom” query becomes:
1 2 3 4 5 6 7 8 | SELECT a.pid AS blocked_pid, a.usename AS blocked_user, a.query AS blocked_statement, pg_blocking_pids(a.pid) AS blocking_pids, a.wait_event_type, a.wait_event FROM pg_stat_activity a WHERE cardinality(pg_blocking_pids(a.pid)) > 0; |
Shorter, easier to remember, and it handles fast-path locks correctly (the wiki query above can occasionally miss those). Use this on PG 9.6+ unless you specifically need the lock-type detail the wiki query produces.
How to read the result. blocked_pid is the session that’s stuck. blocking_pid is the one holding the lock. The catch: current_statement_in_blocking_process is whatever the blocker is running right now — often idle in transaction or some unrelated follow-up query, not the statement that originally took the lock. So if you see SELECT 1 in the blocker’s column, don’t be confused — the real cause is that this transaction has been holding locks for a while; the displayed query is just whatever it last ran. Look at state and xact_start in pg_stat_activity to see how long the transaction has been open.
Once you’ve found the blocker. Two functions, escalating in force:
1 2 3 4 5 | -- Polite: ask the session to cancel its current query (transaction stays open) SELECT pg_cancel_backend(12345); -- Forceful: kill the entire backend connection (transaction rolls back) SELECT pg_terminate_backend(12345); |
pg_cancel_backend sends a SIGINT-like signal — the running statement is interrupted but the connection survives. pg_terminate_backend drops the whole connection. Try cancel first; reach for terminate when the session is wedged or holding locks while idle. Both require either superuser or membership in the pg_signal_backend role (PG 13+).
Don’t just kill — find out why. Killing the blocker stops the immediate pain, but if the same query keeps blocking everyone, the fix is upstream — a missing index, a transaction that stays open across a slow external call, an unbatched bulk update, or schema migrations during traffic. The query above shows you which session; the actual investigation usually lives in pg_stat_activity‘s state, xact_start, and application_name columns. 🔎