How do I inspect PostgreSQL sessions waiting on locks?
A slow PostgreSQL request may be waiting for another transaction rather than consuming CPU. Activity state, wait information and blocker process IDs help distinguish these cases. Collect a bounded sample and identify the owning application before considering cancellation or termination.
Before you start
- An approved monitoring account with appropriate visibility.
- Connection to the database affected by the incident.
Instructions
1. Inspect a bounded activity sample
The query restricts results to the current database and omits SQL text, which may contain sensitive values. Your monitoring role controls how much information about other sessions is visible.
SELECT pid, usename, state, wait_event_type, wait_event,
pg_blocking_pids(pid) AS blockers
FROM pg_stat_activity
WHERE datname = current_database()
AND pid <> pg_backend_pid()
ORDER BY pid
LIMIT 20;Reads session state and blocking process IDs without cancelling work.
2. Correlate repeated observations
Compare a second small sample with the application’s incident time. A wait can be normal and brief; a persistent blocker and growing user impact need closer review. Avoid continuous high-frequency polling of lock internals.
3. Identify the transaction owner
Use the process ID and application context to coordinate the next action. An idle transaction can hold locks while waiting for its client, and terminating it can roll back work. Correct the application transaction boundary when that is the recurring cause.
Check the result
- The delayed request is linked to a specific wait or blocker, or evidence shows that another bottleneck needs investigation.
Troubleshooting
- Columns about other sessions are hidden
- Use the organisation’s approved monitoring role or ask the database administrator for a focused extract. Do not give the application a superuser role to obtain diagnostics.
Official references
Use the documentation for your installed version when a flag, service name or platform feature differs.