What Is Running Right Now: Activity and $currentOp

-- ── Everything running right now, longest first ──────────────────
SELECT
  pid,
  now() - query_start        AS running_for,
  state,                     -- active | idle | idle in transaction
  wait_event_type,           -- Lock | IO | LWLock | Client | NULL (= running)
  wait_event,
  left(regexp_replace(query, '\s+', ' ', 'g'), 60) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
  AND pid <> pg_backend_pid()
ORDER BY query_start;

--  pid  | running_for | state  | wait_event_type | wait_event   | query
-- ------+-------------+--------+-----------------+--------------+-------------
--  4412 | 00:04:12    | active | (null)          | (null)       | SELECT count(*)...
--  4419 | 00:02:08    | active | Lock            | transactionid| UPDATE orders...
--  4421 | 00:01:55    | active | IO              | DataFileRead | SELECT * FROM events...
--  4422 | 00:14:02    | idle in transaction | Client | ClientRead  | BEGIN
--
-- Four rows, four completely different problems:
--   4412  wait_event NULL        -> genuinely burning CPU. It is working.
--   4419  Lock/transactionid     -> blocked. Find the blocker (below).
--   4421  IO/DataFileRead        -> reading from disk. Missing index, or
--                                   a working set larger than RAM.
--   4422  idle in transaction    -> THE dangerous one. Holding a snapshot
--                                   open for 14 minutes, blocking vacuum
--                                   and possibly holding locks. Module 9.

-- ── Who is blocking whom ─────────────────────────────────────────
SELECT
  blocked.pid          AS blocked_pid,
  blocked.query        AS blocked_query,
  blocking.pid         AS blocking_pid,
  blocking.state       AS blocking_state,
  now() - blocking.xact_start AS blocking_txn_age,
  blocking.query       AS blocking_query
FROM pg_stat_activity blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity blocking ON blocking.pid = b.pid
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

-- pg_blocking_pids() does the whole lock-graph walk for you. Before it
-- existed people wrote 40-line self-joins against pg_locks; do not.

-- ── The two ways to stop something ───────────────────────────────
SELECT pg_cancel_backend(4419);     -- cancel the QUERY. Polite. Try first.
SELECT pg_terminate_backend(4419);  -- kill the CONNECTION. Rolls back the
                                    -- whole transaction. Use when cancel
                                    -- does not take.

-- ── The single most useful alerting query ────────────────────────
-- Long-running idle-in-transaction sessions are silent poison: they hold
-- a snapshot open, so vacuum cannot reclaim anything newer than them,
-- and the table bloats until performance collapses. See module 8.
SELECT pid, now() - xact_start AS txn_age, state, left(query, 50)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - xact_start > interval '5 minutes';