The Query That Would Not Finish
In PostgreSQL, a long-running query is never just a slow query.
It holds locks. It prevents autovacuum from cleaning up the tables it touches. If it is inside an open transaction, it pins the transaction ID horizon for the entire database — meaning dead tuples from every other table cannot be cleaned up either. Leave it long enough and you get table bloat, lock queues, and eventually a transaction ID wraparound risk.
This script pulls everything currently active in pg_stat_activity ordered by runtime, so you can see what has been running too long and decide whether to investigate or kill it.
Loading…
Reading the Results
| Column | What It Tells You |
|---|---|
pid | Process ID — use this to cancel or terminate |
usename | Database user running the query |
application_name | Reporting application name from the connection string |
client_addr | Source IP — helps trace back to the application server |
state | active = running now; idle in transaction = open transaction, not executing |
wait_event_type | Category of what the session is waiting on — Lock, IO, Client, etc. |
wait_event | Specific wait — e.g. relation, DataFileRead, ClientRead |
duration | How long this query or state has been running |
query | The SQL text (may be truncated at track_activity_query_size characters) |
What to Watch For
| Signal | What It Means |
|---|---|
state = idle in transaction for > 1 minute | An open transaction doing nothing — holds locks, blocks vacuum |
wait_event_type = Lock | The session is blocked waiting for a lock held by another session |
wait_event = ClientRead | Session is waiting for the client — slow application, network issue |
| Single query running for hours | Missing index, bad plan, or runaway analytical query on a transactional database |
application_name showing a batch job | Scheduled job running longer than expected — may need query optimisation |
| Queries from many different PIDs all blocked | One root blocker — find it with the Blocking and Lock Waits script |
Cancelling and Terminating
Loading…
Use pg_cancel_backend first — it sends a signal the query can handle cleanly. Use pg_terminate_backend for sessions that are idle in transaction and will not respond to cancel.
To automatically kill queries beyond a threshold, set statement_timeout at the role or session level:
Loading…
Gareth Winterman