Find and stop long-running or stuck queries in PostgreSQL with pg_stat_activity, pg_cancel_backend, and pg_terminate_backend

Documented steps

1. List backends ordered by age: SELECT pid, usename, state, wait_event_type, wait_event, now() - query_start AS runtime, query FROM pg_stat_activity WHERE state <> 'idle' ORDER BY query_start;
2. Interpret state: 'active' = executing now; 'idle in transaction' = open transaction holding locks/snapshots while doing nothing (a common source of bloat and blocking); wait_event shows what the backend is waiting on (locks, I/O, etc.).
3. Cancel just the current query (SIGINT, connection survives): SELECT pg_cancel_backend(<pid>);
4. Kill the whole session (SIGTERM): SELECT pg_terminate_backend(<pid>); — an optional second argument, e.g. pg_terminate_backend(pid, 5000), waits up to that many milliseconds for the backend to actually exit and returns false on timeout.
5. Prevent recurrences with timeouts: SET statement_timeout = '5min'; aborts any statement running longer; SET idle_in_transaction_session_timeout = '10min'; kills sessions that sit in an open transaction; idle_session_timeout covers idle sessions with no transaction.
6. Prefer setting timeouts per role (ALTER ROLE app_user SET statement_timeout = ...) or per session rather than globally in postgresql.conf.
7. Official docs: https://www.postgresql.org/docs/current/monitoring-stats.html, https://www.postgresql.org/docs/current/functions-admin.html, https://www.postgresql.org/docs/current/runtime-config-client.html