Reading pg_stat_activity as an instrument · PostgreSQL

pg_stat_activity has one row per backend and about thirty columns. Six of them do nearly all the diagnostic work, and two of those are routinely confused with each other.

The four timestamps

Column	Set when	`now() - column` answers
`backend_start`	The connection was established	How old is this connection?
`xact_start`	The current transaction began	How old is this transaction?
`query_start`	The current statement began	How long has this statement run?
`state_change`	`state` last changed	How long has it been in this state?

Choosing between them is the whole skill.

Transaction age (xact_start) is what predicts vacuum obstruction, lock retention and wraparound risk. A transaction open for nineteen hours is a problem regardless of whether it is currently executing anything.

Statement age (query_start) is what identifies a slow query. In a session that is idle in transaction, query_start refers to a statement that already finished, so ageing it produces a number that looks alarming and means nothing.

State age (state_change) is what tells you how long a session has been idle in transaction — the number that actually matters for that state.

Connection age (backend_start) is mostly useful for spotting connections older than a configuration change, which the backend-context lesson in Part III showed matters.

Sorting by query_start to find the longest-running query

ORDER BY query_start returns sessions whose last statement began longest ago. For an active session that is the running statement and the answer is correct. For an idle in transaction session it is a statement that completed hours ago, and that session will sort to the top of the list while doing nothing at all.

The result is an investigation aimed at a session that is not slow, while the genuinely slow statement sits further down. Filter by state before ageing:

-- Longest-running statements: only sessions actually executing one
SELECT pid, now() - query_start AS query_age, left(query, 60) AS query
  FROM pg_stat_activity
 WHERE state = 'active' AND backend_type = 'client backend'
 ORDER BY query_start;

-- Oldest transactions: regardless of state, because that is the point
SELECT pid, state, now() - xact_start AS xact_age, left(query, 60) AS last_query
  FROM pg_stat_activity
 WHERE xact_start IS NOT NULL
 ORDER BY xact_start;

Two queries, two questions, two different answers.

Identity: naming the culprit rather than a number

A PID identifies a process. It does not tell anyone which deployment to roll back.

Column	Gives you
`application_name`	Whatever the client declared. Free, and usually empty
`client_addr`	Where the connection came from
`client_hostname`	Only populated if `log_hostname` is on
`usename`	The role, which is a coarse proxy for the workload
`backend_type`	Client backend, or which background process

application_name is the one worth insisting on. It costs nothing, it is settable from any connection string, and it converts “PID 41822 is blocking the deployment” into “the invoice reconciliation job is blocking the deployment”.

# In a connection string
postgresql://user@host/db?application_name=invoice-reconciliation

# Or as an environment variable
export PGAPPNAME=invoice-reconciliation

# Or per session
psql -c "SET application_name = 'schema-migration-2026-08'"

Where connections arrive through a pooler, client_addr becomes the pooler’s address for every session, and application_name becomes the only remaining way to tell workloads apart. That is worth arranging before you need it.

Parallel workers

A single query can occupy several backends. leader_pid attributes them.

SELECT coalesce(leader_pid, pid) AS leader,
       pid, leader_pid IS NOT NULL AS is_worker,
       state, wait_event_type, wait_event, left(query, 50) AS query
  FROM pg_stat_activity
 WHERE backend_type IN ('client backend', 'parallel worker')
 ORDER BY leader, is_worker, pid;

Without this, a parallel query looks like several unrelated sessions running identical SQL, and a count of active sessions overstates the concurrency. leader_pid is null for the leader and set to the leader’s PID for each worker, so grouping on coalesce(leader_pid, pid) counts queries rather than processes.

The triage query worth memorising

psql -U postgres -c \
  "SELECT pid,
          usename,
          application_name,
          client_addr,
          state,
          coalesce(wait_event_type || ':' || wait_event, '-') AS waiting_on,
          now() - xact_start   AS xact_age,
          now() - state_change AS in_state,
          backend_xid, backend_xmin,
          left(query, 50) AS query
     FROM pg_stat_activity
    WHERE backend_type = 'client backend'
      AND pid <> pg_backend_pid()
    ORDER BY xact_start NULLS LAST"

Read it in this order: state groups the sessions, waiting_on explains the active ones, xact_age finds the long transactions, backend_xid and backend_xmin say whether those transactions matter for cleanup, and application_name names who to talk to.

Why the view is a snapshot, and when that bites

pg_stat_activity is not a live feed. It is assembled from the shared backend array plus the cumulative statistics, and the statistics half is subject to a consistency setting most people never encounter.

By default, stats_fetch_consistency is cache: the first access to statistics within a transaction caches them, and subsequent accesses in the same transaction return the same values. In an interactive psql session each statement is its own transaction, so this is invisible and repeated queries return fresh data.

Inside an explicit BEGIN, it is very visible. A monitoring script that opens a transaction and polls pg_stat_activity in a loop gets the same answer every time, indefinitely, and concludes that nothing is changing. Setting stats_fetch_consistency to none fetches fresh values on each access, which is the right setting for a monitoring connection and the wrong one for a query that joins several statistics views and needs them to agree with each other.

There is a second, simpler caveat. The query column is truncated at track_activity_query_size bytes, 1024 by default. A long query is cut off, and if the distinguishing part of two similar statements lies beyond the cut they become indistinguishable in the view. Raising it costs shared memory per connection slot and requires a restart, so it is a deliberate choice rather than a default worth changing everywhere.

Production discipline

Filter by state before ageing query_start. In an idle-in-transaction session it refers to a finished statement and will sort to the top for no reason.
Use xact_start for transaction age and state_change for how long a session has been idle in transaction. They answer different questions.
Set application_name in every connection string. Behind a pooler it becomes the only way to tell workloads apart.
Group by coalesce(leader_pid, pid) when counting concurrency, or a parallel query is counted several times.
Set stats_fetch_consistency = 'none' on monitoring connections that poll inside a transaction, or they will read the same cached values forever.

Cross-course references

Observability for Production Sysadmins — Part LIX (Database observability) covers exporting these columns as metrics, and Part XII (PromQL foundations) covers turning the cumulative parts into rates.
Linux for Production Sysadmins — Part VI (Processes) covers mapping the pid column onto the host process table.
Git, CI/CD & GitOps — Part CXIV (Deployment markers) covers correlating a session’s application_name with the deployment that started it.