Detecting and stopping a runaway query

A backend has been executing a query for far longer than expected — CPU is pegged, IOPS are saturated, or locks are accumulating while the rest of the system degrades.

Diagnose it

-- Queries running longer than 5 minutes:
SELECT pid,
       usename,
       application_name,
       state,
       wait_event_type,
       wait_event,
       now() - query_start   AS query_age,
       now() - xact_start    AS txn_age,
       query_id,
       LEFT(query, 250)      AS query
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < now() - INTERVAL '5 minutes'
ORDER BY query_age DESC NULLS LAST;

Check whether it is waiting on I/O (wait_event_type = 'IO'), a lock
(wait_event_type = 'Lock'), or actively on CPU (wait_event IS NULL
and state = 'active').

Why it happens

Common causes: a missing index causing a sequential scan on a large table, a bad query
plan from stale statistics, a query spawned by a reporting tool without a timeout, or an
intentionally long-running batch job that has grown beyond its expected data size.
Wait events narrow down whether the bottleneck is CPU, disk, or lock contention.