PostgreSQL session and blocking investigation cheat sheet
Inspect database identity, connection counts, wait events, blocking relationships and long transactions using bounded SQL queries.
PostgreSQL 14–18 with a connected SQL client. Broader session visibility may require pg_read_all_stats or an approved monitoring role.
Updated · PostgreSQL · 6 min read
| Command | What it does |
|---|---|
SELECT current_database(), current_user, inet_server_addr(), inet_server_port(); | Confirm database and server identity |
SELECT datname, state, count(*) FROM pg_stat_activity GROUP BY datname, state ORDER BY count(*) DESC; | Summarize connections by state |
SELECT pid, usename, state, wait_event_type, wait_event, now()-query_start AS query_age FROM pg_stat_activity WHERE state <> 'idle' AND pid <> pg_backend_pid() ORDER BY query_start LIMIT 20; | Inspect a bounded set of active waits |
SELECT pid AS waiting_pid, pg_blocking_pids(pid) AS blocking_pids FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0 LIMIT 20; | Identify current blocking relationships |
SELECT pid, usename, state, now()-xact_start AS transaction_age FROM pg_stat_activity WHERE xact_start IS NOT NULL AND pid <> pg_backend_pid() ORDER BY xact_start LIMIT 20; | Find long-lived transactions |
SELECT datname, numbackends, deadlocks, stats_reset FROM pg_stat_database WHERE datname = current_database(); | Inspect database counters with their reset time |
Walkthrough
Connect to the intended database using your normal secure client. Run these SELECT statements in autocommit for fresh samples. Query text and session metadata can expose application data; no sessions are cancelled or terminated.
Step 1: Confirm database and server identity
SELECT current_database(), current_user, inet_server_addr(), inet_server_port();
Match database, user and endpoint with the incident. A local Unix-domain socket can return a null network address. A connection proxy or service alias can route to different servers, so record the identity before comparing sessions.
Step 2: Summarize connections by state
SELECT datname, state, count(*) FROM pg_stat_activity GROUP BY datname, state ORDER BY count(*) DESC;
Compare active, idle and idle-in-transaction counts with the normal connection pool. An idle session is not necessarily a leak. Visibility depends on privileges, and this snapshot does not reveal historical peaks by itself.
Step 3: Inspect a bounded set of active waits
SELECT pid, usename, state, wait_event_type, wait_event, now()-query_start AS query_age FROM pg_stat_activity WHERE state <> 'idle' AND pid <> pg_backend_pid() ORDER BY query_start LIMIT 20;
Use wait_event_type and wait_event to separate lock waiting from other waits. Query age includes time waiting and does not prove CPU use. The monitoring query excludes its own backend but may still include unrelated long-running maintenance.
Step 4: Identify current blocking relationships
SELECT pid AS waiting_pid, pg_blocking_pids(pid) AS blocking_pids FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0 LIMIT 20;
Record waiting and blocking process IDs while the issue is occurring. Relationships can change between samples. A blocker may itself be waiting on another transaction, so trace the chain with the database owner instead of terminating the first PID found.
Step 5: Find long-lived transactions
SELECT pid, usename, state, now()-xact_start AS transaction_age FROM pg_stat_activity WHERE xact_start IS NOT NULL AND pid <> pg_backend_pid() ORDER BY xact_start LIMIT 20;
Compare transaction age and state with the incident. An idle-in-transaction session can retain locks or delay cleanup, but age alone does not establish impact. Associate it with application ownership before any corrective action.
Step 6: Inspect database counters with their reset time
SELECT datname, numbackends, deadlocks, stats_reset FROM pg_stat_database WHERE datname = current_database();
Read deadlocks as a cumulative counter since stats_reset, not a live count of blocked queries. A blocking wait is not automatically a deadlock. Compare timed samples and database logs when establishing whether new deadlocks occurred.
WORKED INCIDENT
Practical case: Requests stall behind an idle transaction
Fictional teaching scenario; observations are examples, not captured production output.
Situation
Application updates hang while the database is reachable and most connections appear idle.
Investigation sequence
1. Confirm database and server identity
SELECT current_database(), current_user, inet_server_addr(), inet_server_port();Confirm the affected database and endpoint before interpreting process IDs.
2. Inspect a bounded set of active waits
SELECT pid, usename, state, wait_event_type, wait_event, now()-query_start AS query_age FROM pg_stat_activity WHERE state <> 'idle' AND pid <> pg_backend_pid() ORDER BY query_start LIMIT 20;Identify sessions reporting a lock wait during the user-visible stall.
3. Identify current blocking relationships
SELECT pid AS waiting_pid, pg_blocking_pids(pid) AS blocking_pids FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0 LIMIT 20;Map each waiter to its blocker. In this fictional example several sessions point to one PID.
4. Find long-lived transactions
SELECT pid, usename, state, now()-xact_start AS transaction_age FROM pg_stat_activity WHERE xact_start IS NOT NULL AND pid <> pg_backend_pid() ORDER BY xact_start LIMIT 20;Inspect transaction age and state for that PID. The example blocker is idle in transaction from an application session.
Example findings
Illustrative findings: several active requests wait on Lock; their blocking PID is the same; that backend is idle in transaction with an older xact_start.
Interpret the findings
The blocking relationship supports a transaction-lifecycle problem. Being idle alone is not the cause; the open transaction and its retained locks matter. Review the application path that failed to finish the transaction.
Verify the outcome
After the owner completes or rolls back the transaction through the approved process, repeat the blocking query and a normal application request. Confirm the waits clear and the pattern does not immediately recur.
Escalation handoff
Provide the database, waiting/blocking PIDs, transaction age and incident times to the DBA and application owner. Share SQL text only through an approved private channel.
PostgreSQL activity statistics