Find Long-Running Queries and Idle-in-Transaction Sessions in PostgreSQL
Lists queries running longer than a minute, their wait events, and idle-in-transaction sessions that block vacuum. The first script to run during a performance incident.
SELECT pid, usename, datname, state,
now() - query_start AS query_age,
now() - xact_start AS xact_age,
wait_event_type, wait_event,
left(query, 200) AS query
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND state <> 'idle'
AND now() - query_start > interval '1 minute'
ORDER BY query_start;`idle in transaction` sessions block vacuum. To terminate them (write action), run `SELECT pg_cancel_backend(pid);`, or `pg_terminate_backend(pid)` if it does not respond.
- This script is designed for read-only diagnostics; it does not alter schema or modify data directly.
- Prior to executing on production systems under high load, verify the target database version (Min. Version: 9.6).
- Exercise caution with mutating actions (e.g., terminating sessions or dropping indexes) referenced in the notes section.
Other POSTGRESQL Diagnostic Scripts
View All (100)PostgreSQL Lock Chain: Who Is Blocking Whom?
Uses pg_blocking_pids to pair blocked and blocking sessions with their queries and wait times. Shows the root cause of a lock chain in seconds.
PostgreSQL Dead Tuples, Bloat and Autovacuum Health Check
Shows dead tuple ratio and last vacuum/analyze times per table. Helps you find tables where autovacuum settings are not keeping up.
Find Unused Indexes in PostgreSQL
Lists never-scanned indexes that are neither unique nor primary keys, with their sizes. Surfaces cleanup candidates to cut write overhead and disk usage.
Top 20 Most Expensive PostgreSQL Queries with pg_stat_statements
Ranks queries by total time, call count and mean time. Tells you which query to tune first.