Active session history in Postgres

The gap in the toolkit

pg_stat_statements answers what has been expensive since the last reset. pg_stat_activity answers what is happening right now. Between them is the question people actually ask during an incident review, which is what was happening at a particular minute yesterday, and neither view can answer it.

Other databases solve this with a sampler that records what every active session is doing at a fixed interval and keeps the samples. PostgreSQL has every ingredient for that and does not assemble them. pg_stat_activity already reports, for each backend, the statement, the state, the transaction start, and, crucially, the wait event: the named thing the backend is currently blocked on, or nothing at all if it is executing.

Sampling that view on a timer and storing the rows gives you a session history. The technique is simple enough that the interesting part is not the code, it is knowing what the resulting data does and does not mean.

Building one

A table and an insert. Keep the query identifier in every sample: it is the column that lets a sample from last Tuesday be joined to what that statement usually costs, and it has been in the activity view since PostgreSQL 14.

CREATE TABLE session_samples (
  sample_time      timestamptz  NOT NULL,
  pid              integer,
  datname          name,
  usename          name,
  application_name text,
  backend_type     text,
  state            text,
  wait_event_type  text,
  wait_event       text,
  query_id         bigint,
  xact_start       timestamptz,
  query_start      timestamptz
);

INSERT INTO session_samples
SELECT clock_timestamp(), pid, datname, usename, application_name,
       backend_type, state, wait_event_type, wait_event, query_id,
       xact_start, query_start
FROM pg_stat_activity
WHERE state IS DISTINCT FROM 'idle'
  AND pid <> pg_backend_pid();

Run it once a second from pg_cron, from a sidecar, or from anything that can hold a connection and a loop. One second is the conventional interval and it is a reasonable default: fine enough to catch a stall that lasts a few seconds, coarse enough that the sampling itself is invisible.

Three details in that statement are deliberate. clock_timestamp() rather than now(), because now() returns the transaction start and every row in a long-lived sampling session would carry the same value. Excluding idle keeps the table to sessions that are actually consuming something. And excluding your own backend keeps the sampler out of its own history, where it would otherwise be the single most active session in the cluster.

query_id only has a value when compute_query_id is on, which by default means it is on if pg_stat_statements is loaded and off otherwise. Without it you can still see what the database was doing, but you cannot join back to the statement that was doing it, which is most of the value. Turn it on. Two separate operations hide behind that one name: loading the library through shared_preload_libraries is what populates query_id and needs a restart, while CREATE EXTENSION pg_stat_statements in the database you are sampling needs no restart and is what makes the view itself exist for the join at the end of this page.

CREATE EXTENSION pg_stat_statements;

Reading it

The fundamental interpretation is short: a sampled backend with a null wait_event was running on a CPU, and a backend with a wait event was blocked on the thing that event names. Grouping by that single distinction turns a shapeless hour into a diagnosis.

SELECT coalesce(wait_event_type, 'CPU')                     AS wait_class,
       coalesce(wait_event, 'executing')                    AS event,
       count(*)                                             AS samples,
       round(100.0 * count(*) / sum(count(*)) OVER (), 1)   AS pct
FROM session_samples
WHERE sample_time >= now() - interval '1 hour'
GROUP BY 1, 2
ORDER BY samples DESC
LIMIT 20;

The wait_class column is the first thing to read, and the classes mean genuinely different things. Lock is contention on a heavyweight lock and sends you to lock contention and blocking trees. LWLock is contention on an internal structure, most often buffer mapping or the write-ahead log insert locks, which points at concurrency rather than at any one statement. IO is the storage. IPC is waiting on another process, typically a parallel worker. Client means the backend is waiting for the application to say something, and time spent there is not the database’s fault. Timeout is a deliberate sleep, which is what autovacuum’s cost delay looks like from here.

From PostgreSQL 17 you can attach the server’s own description of each event rather than keeping your own glossary:

SELECT s.wait_event_type, s.wait_event, e.description, count(*) AS samples
FROM session_samples s
LEFT JOIN pg_wait_events e
       ON e.type = s.wait_event_type
      AND e.name = s.wait_event
WHERE s.sample_time >= now() - interval '1 hour'
GROUP BY 1, 2, 3
ORDER BY samples DESC;

Average active sessions

The single most useful derived number is how many sessions were doing something, on average, per sample. With one sample per second it falls out of a count:

SELECT date_trunc('minute', sample_time)                AS minute,
       round(count(*)::numeric
             / count(DISTINCT sample_time), 2)          AS avg_active_sessions,
       count(*) FILTER (WHERE wait_event IS NULL)       AS on_cpu_samples
FROM session_samples
WHERE sample_time >= now() - interval '6 hours'
GROUP BY 1
ORDER BY 1;

Compare it against the core count of the machine. Average active sessions well below the core count means the server has headroom and any slowness is in a specific wait, not in capacity. Consistently above it means requests are queueing for CPU, and no amount of query tuning on one statement will fix a machine that is oversubscribed. It is also the number that survives a burstable instance whose reported CPU percentage is measured against a ceiling the platform can withdraw, because it counts what the database believes it is doing rather than what the hypervisor reports; that failure mode is one of the reasons the engine a cluster runs on matters, which is the subject of DBExplore for Amazon RDS.

Joining to the statement text closes the loop:

SELECT s.query_id,
       left(t.query, 70)                           AS query,
       count(*)                                    AS samples,
       count(*) FILTER (WHERE s.wait_event IS NULL) AS cpu_samples,
       mode() WITHIN GROUP (ORDER BY s.wait_event)  AS commonest_wait
FROM session_samples s
LEFT JOIN pg_stat_statements t ON t.queryid = s.query_id
WHERE s.sample_time >= now() - interval '1 hour'
GROUP BY s.query_id, t.query
ORDER BY samples DESC
LIMIT 15;

What sampling gets wrong

Be honest about the limitations, because they decide what you can conclude.

Sampling sees the states a backend spends time in, not the ones it passes through. A statement that runs in two milliseconds a million times an hour may never appear, while a statement that runs once for a minute appears sixty times. That is usually the right bias, and it is the wrong one when the problem is a high-frequency short query, which is the shape a caller in a loop produces: ranking by buffer accesses rather than by time is what finds those, and observability for AI agents is where that ranking lives.

A wait event is a point observation with no duration attached. Fifty samples of the same event on the same backend probably mean one fifty-second wait, and might mean fifty separate one-second waits that happened to land under the sampler. Confirm long waits against waitstart in pg_locks or against the log rather than inferring duration from sample counts.

The sample is not atomic. pg_stat_activity is read backend by backend, so two rows in the same sample are not from exactly the same instant. At a thousand connections that skew becomes measurable, and it is also the point at which the sampling query itself stops being free.

Finally, storage grows without bound and the data ages badly. Keep raw samples for days, roll them up to per-minute aggregates for months, and delete the raw rows; nobody has ever needed second-level resolution from last quarter.

PostgreSQL 18 makes one part of this materially better. Its per-backend I/O statistics, reachable through pg_stat_get_backend_io(), let you attribute reads and writes to an individual backend rather than inferring them, which is exactly the number sampling could never supply. What changed and what it costs is covered in what PostgreSQL 18 changed in monitoring.