Cancel long-idle Postgres transactions

idle in transaction is the state where an application opened a transaction, did some work, and then wandered off without committing. The connection holds row-level locks and blocks autovacuum on the tables it touched. One bad deploy and you wake up to a disk-full page from bloat.

These days I set idle_in_transaction_session_timeout at the database level and move on, but on legacy clusters where I cannot change that easily, this is the query I run to find and cancel them.

-- Find them:
SELECT
  pid,
  usename,
  application_name,
  state,
  now() - state_change AS idle_for,
  left(regexp_replace(query, '\s+', ' ', 'g'), 100) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '5 minutes'
ORDER BY idle_for DESC;

-- Cancel the transaction (gentle):
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '15 minutes';

-- Terminate the connection (harder):
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '30 minutes';

pg_cancel_backend sends a signal that cancels the current statement and rolls the transaction back. The app sees an error on its next statement; most well-behaved libraries reconnect. pg_terminate_backend is the hammer.