Why should you care?

PostgresVerse checkout slow — on-call filters pg_stat_activity for long running queries blocking pool.

See it live — copy this example

Run in pgAdmin or psql.

SELECT pid, usename, datname, state, wait_event_type, wait_event,
       now() - query_start AS runtime, left(query, 80) AS query_snip
FROM pg_stat_activity
WHERE datname = 'PostgresVerse'
  AND state <> 'idle'
ORDER BY runtime DESC;

What happened?

• Lists non-idle backends on PostgresVerse sorted by runtime.
• wait_event shows IO, Lock, or Client waits.
• query_snip helps identify culprit SQL.

Practice next

1. Run query during normal load.
2. Start pg_sleep(30) in another session; find it in activity view.
3. Note idle in transaction session with old xact_start.
4. Filter application_name from JDBC pool.
5. Join pg_locks on pid for blocked/blocking tree.

Remember

First stop for who is running what. wait_event_type diagnoses waits not just slow CPU. Cancel vs terminate — cancel is gentler.

PostgresVerse incident triage

P95 spike; on-call finds analytics query at 400s runtime and cancels it.

Outcome: Checkout recovers in 2 minutes without restart.