Purpose
Renders active PostgreSQL lock blocking chains as a visual indented tree at the moment the script runs. Head blockers — sessions whose locks are causing others to wait — appear at the root labelled HEAD. Blocked sessions appear as indented branches, with further indentation for chains where a blocked session is itself blocking others downstream.
Each row includes the PID, username, application name, session state, wait event, time waiting, and the query currently held or running. A companion query shows the lock types and relations involved for every session in the active chain.
This script complements the blocking lock capture script, which shows flat blocker/blocked pairs. Use this one when you need to understand the full chain structure — particularly in multi-level blocking scenarios where terminating the wrong session will not resolve the contention.
Supported Platforms and Versions
PostgreSQL 13 through 18. Uses pg_stat_activity, pg_locks, and pg_blocking_pids() (available since PostgreSQL 9.6).
Required Permissions
pg_monitor role — grants SELECT on pg_stat_activity across all sessions, including other users’ queries.
Without pg_monitor, a superuser or a role with explicit SELECT granted on pg_stat_activity is required. Without sufficient privileges, sessions owned by other roles will appear with NULL query text.
Risk Level
Low. Read-only. No changes to locks, sessions, or data.
Script
Blocking tree
-- ============================================================
-- PostgreSQL Blocking Tree
-- Permissions : pg_monitor (or superuser)
-- Risk : Read-only
-- Note : Returns results only when active blocking exists
-- ============================================================
WITH RECURSIVE blocking_tree AS
(
-- Anchor: head blockers — sessions not blocked by anyone
-- that are blocking at least one other session
SELECT
sa.pid,
sa.usename,
sa.application_name,
sa.client_addr,
sa.state,
sa.wait_event_type,
sa.wait_event,
sa.query_start,
sa.xact_start,
LEFT(sa.query, 300) AS query_snippet,
ARRAY[sa.pid] AS path,
0 AS depth,
LPAD(sa.pid::text, 10, '0') AS sort_key
FROM pg_stat_activity AS sa
WHERE cardinality(pg_blocking_pids(sa.pid)) = 0
AND EXISTS (
SELECT 1 FROM pg_stat_activity AS sa2
WHERE sa.pid = ANY(pg_blocking_pids(sa2.pid))
)
UNION ALL
-- Recursive: sessions blocked by a node already in the tree
SELECT
sa.pid,
sa.usename,
sa.application_name,
sa.client_addr,
sa.state,
sa.wait_event_type,
sa.wait_event,
sa.query_start,
sa.xact_start,
LEFT(sa.query, 300),
bt.path || sa.pid,
bt.depth + 1,
bt.sort_key || LPAD(sa.pid::text, 10, '0')
FROM pg_stat_activity AS sa
JOIN blocking_tree AS bt ON bt.pid = ANY(pg_blocking_pids(sa.pid))
WHERE sa.pid <> ALL(bt.path) -- guard against cycles
)
SELECT
REPEAT(' ', depth)
|| CASE WHEN depth = 0 THEN 'HEAD --- ' ELSE '|------- ' END
|| pid::text
|| ' [' || COALESCE(state, 'unknown') || ']'
|| ' ' || COALESCE(usename, '')
|| ' ' || COALESCE(application_name, '')
|| ' waited: ' || COALESCE(
ROUND(EXTRACT(EPOCH FROM (NOW() - query_start))::numeric, 1)::text || 's',
'?')
|| ' >> ' || COALESCE(query_snippet, '(no query text)')
AS blocking_tree,
pid,
usename,
application_name,
client_addr,
state,
wait_event_type,
wait_event,
NOW() - xact_start AS xact_duration,
NOW() - query_start AS query_duration,
depth
FROM blocking_tree
ORDER BY sort_key;Lock detail for all sessions in the active chain
Run this alongside the tree to see which lock types and relations are involved:
SELECT
sa.pid,
sa.usename,
sa.state,
l.locktype,
l.relation::regclass AS relation,
l.mode,
l.granted,
CASE WHEN NOT l.granted THEN 'WAITING' ELSE 'HOLDING' END AS lock_status,
NOW() - sa.query_start AS duration
FROM pg_locks AS l
JOIN pg_stat_activity AS sa ON sa.pid = l.pid
WHERE sa.pid IN (
SELECT pid FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0 -- blocked sessions
UNION
SELECT UNNEST(pg_blocking_pids(pid))
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0 -- their blockers
)
ORDER BY sa.pid, l.granted DESC, l.locktype;Safe Execution Guidance
- Run in psql or any PostgreSQL client with
pg_monitorprivileges. - Both queries are point-in-time snapshots — blocking chains can resolve between execution and display. Run immediately during a reported slowdown, not after.
- An empty result set from the tree query means no blocking chain exists at this moment. This is the expected result on a healthy instance.
- If the same blocking chain persists across multiple runs, the head blocker is not releasing its transaction. Escalate — the resolution may require terminating the session.
Expected Output
One row per session in the active blocking chain, formatted as a tree:
HEAD --- 42 [idle in transaction] appuser myapp waited: 47.3s >> UPDATE orders SET ...
|------- 67 [active] appuser myapp waited: 46.1s >> SELECT * FROM orders ...
|------- 71 [active] reporter reporting waited: 45.8s >> SELECT COUNT(*) ...
|------- 88 [active] appuser myapp waited: 12.2s >> UPDATE orders ...HEAD ---rows are the root blockers holding locks that all downstream sessions are waiting for.|-------rows are sessions blocked by the session directly above them in the tree.- Deeper indentation levels indicate multi-level chains.
xact_durationandquery_durationfrom the raw columns help distinguish sessions that have been in their transaction a long time from sessions that only recently became blocked.
Interpreting Results
HEAD session in idle in transaction state: The blocker opened a transaction, acquired locks, and is no longer actively running a query but has not committed or rolled back. This is the most common cause of PostgreSQL lock contention. The application is holding a transaction open while waiting on application-side logic, a network call, or user input. The fix is application-level — ensure transactions commit or roll back promptly and are not held open across slow external operations.
HEAD session in active state: The blocker is still executing a query. The contention will resolve when the query finishes — unless the query itself is long-running due to a performance problem, in which case investigate its execution plan.
Multi-level chains: A blocked session is itself blocking others downstream. Resolving only the immediate blocker of a downstream session will not help — the head blocker must be resolved first.
lock_mode_held vs lock_mode_wanted: From the lock detail query, compare what the head blocker holds against what the blocked sessions want. Most contention involves RowExclusiveLock (held by UPDATE/DELETE) conflicting with ShareLock or another RowExclusiveLock. DDL operations (ALTER TABLE, VACUUM FULL, REINDEX) acquire AccessExclusiveLock, which conflicts with everything including reads.
relation in lock detail is NULL: The lock is not on a relation — it may be a transaction ID lock (transactionid locktype), a virtual transaction lock, or an advisory lock.
Investigating the head blocker in detail:
-- Full session detail for the HEAD PID
SELECT pid, usename, application_name, client_addr,
backend_start, xact_start, query_start, state,
wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE pid = <HEAD_PID>;Terminating the head blocker (disruptive — confirm before executing):
-- Attempt graceful termination (cancels current query, may not release transaction)
SELECT pg_cancel_backend(<HEAD_PID>);
-- Force session termination and transaction rollback
SELECT pg_terminate_backend(<HEAD_PID>);pg_cancel_backend() sends SIGINT and cancels the current query but leaves the connection alive. If the session is idle in transaction, cancellation has no effect — pg_terminate_backend() is required to force the rollback and release the locks.
Terminating a session rolls back its open transaction. Confirm the operational impact — particularly for long-running transactions — before proceeding.
Rollback Steps
Not applicable — read-only script. Session termination via pg_terminate_backend() is a separate, explicitly destructive action documented above.