PostgreSQL · Troubleshooting · DBA-018
How to Find Blocking Sessions in PostgreSQL
When PostgreSQL queries suddenly hang, find the waiting session, identify its blocker, and trace the chain before taking action.
The short answer
Query pg_stat_activity and use pg_blocking_pids() to identify which PostgreSQL sessions are waiting for locks and which sessions are blocking them. Inspect the blocker’s transaction state before deciding whether anything needs to be canceled or terminated.
SQL: Find blocked and blocking PostgreSQL sessions
Run this diagnostic in the database environment you are investigating. It reports a row for each blocked session and blocker relationship detected at the time of the query.
SELECT
blocked.pid AS blocked_pid,
blocked.usename AS blocked_user,
now() - blocked.query_start AS blocked_query_age,
left(blocked.query, 80) AS blocked_query,
blockers.pid AS blocking_pid,
blocking.usename AS blocking_user,
blocking.state AS blocking_state,
left(blocking.query, 80) AS blocking_query
FROM pg_stat_activity AS blocked
CROSS JOIN LATERAL
unnest(pg_blocking_pids(blocked.pid)) AS blockers(pid)
LEFT JOIN pg_stat_activity AS blocking
ON blocking.pid = blockers.pid
ORDER BY blocked.query_start NULLS LAST;Based on SQL Recipes for DBAs, recipe DBA-018. The query uses one call to pg_blocking_pids() per inspected session and retains PID 0 for prepared-transaction blockers.
How to read the results
idle in transaction, which can mean a session is holding locks while no longer actively executing SQL.A single blocked session can have more than one blocker. Follow the relationships instead of automatically intervening against the first PID you see. A blocker can itself be waiting on another session.
What to check next
- Confirm the symptoms. Are users seeing lock waits, timeouts, or queries that appear to hang? This query reports lock blocking at one moment in time.
- Look at the blocker’s state. If it is
idle in transaction, investigate why its transaction remains open and whether the application is failing to commit or roll back. - Trace the chain. If the blocking PID also appears as a blocked PID, move upstream to understand the root of the congestion.
- Verify before intervening. Recheck the PID and transaction context, consider the business impact, and follow your production incident process. Do not terminate a session simply because it appears in this output.
Related DBA tasks
These neighboring recipes in SQL Recipes for DBAs help complete the investigation: DBA-015 (active sessions), DBA-016 (long-running transactions), DBA-019 (inspect locks), and DBA-017 (cancel a query or terminate a session when justified).
Continue learning · Baldwin Apps
More practical solutions in SQL Recipes for DBAs
Find engine-specific recipes for sessions, locks, performance, permissions, backups, maintenance, and troubleshooting. Explore the book and its existing companion materials.