What is pg_stat_activity?

pg_stat_activity is a system view in PostgreSQL with one row for every server process connected to the cluster, including client backends, background workers and sessions that are connected but idle. It reports what each one is doing at the moment you query it: its state, when that state last changed, what it is waiting on, and the text of its current or most recent query. When a database is slow and nobody knows why, this is the view people open first.

What the columns actually tell you

A handful of columns carry nearly all of the diagnostic weight.

state is the headline. The values you will meet in practice are active (executing a query), idle (waiting for the next command from the client), idle in transaction (inside an open transaction with no statement running), and idle in transaction (aborted) (the same, after a statement in that transaction errored). Two more exist: fastpath function call, and disabled, which appears only when track_activities has been turned off for that backend.

wait_event_type and wait_event say what is blocking progress, and they are independent of state. This catches people out: a session can be active and still waiting, because active means a query is running rather than a query is progressing. A wait_event_type of Lock means it is queued behind another session.

xact_start, query_start and state_change are the timestamps that turn a snapshot into a duration. now() - query_start on an active session is how long that query has been running. now() - state_change on an idle in transaction session is how long it has been holding its transaction open.

query is the statement text, truncated at 1024 bytes by default under track_activity_query_size.

The three queries worth memorising

Long-running queries, oldest first:

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

Sessions holding a transaction open and doing nothing, which are the usual cause of bloat and blocked cleanup:

SELECT pid, usename, application_name, now() - state_change AS idle_for, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY idle_for DESC;

Who is blocking whom, using pg_blocking_pids() rather than joining lock tables by hand:

SELECT pid, pg_blocking_pids(pid) AS blocked_by, wait_event_type, query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Two things that make it look broken

You can only see your own sessions. Ordinary users get full detail for their own sessions only, and other rows appear with the query text hidden. Superusers, and roles granted the built-in pg_read_all_stats role, see everything. A monitoring user that appears to show an empty database is almost always missing that grant.

The query text stops mid-statement. That is the 1024-byte default truncation, not a corrupted row. Raising track_activity_query_size fixes it, at the cost of more shared memory per connection and a restart to take effect.

Where it stops helping

pg_stat_activity is a snapshot of now, and it keeps no history at all. That single property is what limits it during an incident.

By the time somebody is looking, the session that caused the problem has frequently disconnected, and the view shows only the surviving symptoms: connections queued behind a lock that has since been released, or a connection count that has already recovered. Nothing in the view says what was running twenty minutes ago, which is usually the question being asked.

Nor does it tell you why the query got slow. A statement that has run acceptably for a year and started timing out today looks identical in this view to one that has always been slow. The change that caused it, a deploy, a migration, a plan flip after a statistics update, a config change on the instance, is not something pg_stat_activity records. Answering that means correlating the database's behaviour with the change history around it, which is what root cause analysis covers as a general problem.

Sampling the view on an interval and storing the result is the usual workaround. pg_stat_statements complements it by aggregating query performance over time rather than reporting the current instant, and the two together answer far more than either alone.