Postgres pg_stat_activity: Reading the Room Before You Query

pg_stat_activity shows you every backend currently known to Postgres. Most engineers look at it after something breaks. You should look at it before you run anything that matters.

Here is the minimal query worth memorizing:

SELECT pid, state, wait_event_type, wait_event, query_start, query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY query_start;

That gives you a snapshot of every client connection: what state it is in, what it is waiting on (if anything), and how long it has been in that state.

The state column

Five values matter:

• active — query is currently running
• idle — connection is open, no query running, waiting for a command
• idle in transaction — inside a BEGIN block, not currently executing a statement. This one bites you.
• idle in transaction (aborted) — inside a failed transaction block, all further commands will error until ROLLBACK
• fastpath function call — executing a fast-path function, rare in practice

The dangerous state is idle in transaction. A backend in this state holds locks, blocks autovacuum on affected tables, and eats a connection slot. If you see many of these, something upstream is failing to close transactions.

Finding the problems

Long-running queries — anything that has been active longer than you expect:

SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < now() - interval '30 seconds'
ORDER BY duration DESC;

Blocked backends — waiting for a lock someone else holds:

SELECT pid, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
  AND state = 'active';

Pair this with pg_blocking_pids(pid) to trace the chain. If pid 12345 is blocked, SELECT pg_blocking_pids(12345) gives you the PIDs holding the lock it needs.

Idle-in-transaction sessions older than a threshold:

SELECT pid, state, now() - state_change AS idle_duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '5 minutes'
ORDER BY idle_duration DESC;

Five minutes is generous. In a healthy system under normal load you should see zero rows here, or rows measured in seconds.

The wait_event columns

wait_event_type tells you the category. wait_event tells you the specific thing. Common combinations:

• Lock / relation — waiting for a table-level lock
• Lock / tuple — waiting for a row-level lock
• LWLock / WALWrite — waiting for WAL write, usually harmless unless sustained
• Client / ClientRead — Postgres is idle, waiting for the client to send a query. Common in connection pools.
• IO / DataFileRead — waiting on disk, might indicate buffer cache pressure

When wait_event_type is NULL and state is active, the backend is CPU-bound — running without blocking. That is your baseline active execution profile.

backend_type filter

Without filtering on backend_type = 'client backend', you see autovacuum workers, the WAL sender, the background writer, the checkpointer. These are normal and healthy. They will clutter your output unless you want to diagnose background processes specifically.

What pg_stat_activity does not tell you

It shows current state, not history. A query that ran for 45 seconds and finished is gone. For historical query performance, that is pg_stat_statements. For understanding why a specific query is slow, that is EXPLAIN (ANALYZE, BUFFERS). For per-query resource consumption, that is pg_stat_statements again, specifically the shared_blks_hit, temp_blks_read, and total_exec_time columns.

pg_stat_activity shows you the room. It does not explain what happened before you walked in.

---

Find more writing at anethoth.com. Browse indie SaaS projects at builds.anethoth.com.