The Statistics Views — What to Monitor

Postgres has been counting almost everything it does since the cluster was created, and all of it is available as ordinary views you can select from, join and graph. Since 15 those counters live in shared memory rather than being funnelled through a separate collector process, and a copy is written to the pg_stat directory at shutdown so the numbers survive a clean restart. Reading them costs nothing worth measuring.

The difficulty is not finding metrics. It is that there are several hundred of them, and roughly a dozen predict an outage while the rest describe one after it has happened. This topic is that dozen, where each number lives, and, just as important, which of these views are running totals and which are photographs of this instant, because alerting on the wrong kind produces both false alarms and blind spots.

Database-Level Counters

pg_stat_database has one row per database and is the widest-angle view there is. It carries xact_commit and xact_rollback, blks_hit against blks_read, deadlocks, temp_files and temp_bytes, conflicts for queries cancelled by recovery on a standby, checksum_failures, and session accounting including idle_in_transaction_time. Two of those columns are worth an alert on their own and the rest are dashboard material.

SELECT deadlocks, temp_files, pg_size_pretty(temp_bytes) AS spilled,
       conflicts, checksum_failures,
       round(idle_in_transaction_time / 3600000) AS idle_txn_hours
  FROM pg_stat_database WHERE datname = 'cartwheel';

 deadlocks | temp_files | spilled | conflicts | checksum_failures | idle_txn_hours
-----------+------------+---------+-----------+-------------------+----------------
       412 |      41207 | 3841 GB |         0 |                 0 |            914

Four hundred and twelve deadlocks describes an application access pattern rather than a database fault, and Chapter 6 has the ordering rule that removes it. The temporary-file numbers are work_mem reporting that it does not fit the workload. conflicts stays at zero on a primary and only becomes interesting on pg-replica-a. And checksum_failures is the one column where any value other than zero ends the conversation about performance and starts one about hardware. Clusters initialized on 18 have data checksums on by default, so the counter is armed without anybody having enabled it.

Table and Index Level

pg_stat_user_tables is where day-to-day tuning happens. seq_scan against idx_scan tells you whether a table is being read the way you designed it to be, and since 16 last_seq_scan and last_idx_scan say when each last happened, which turns "is this index used" from an argument into a date. n_live_tup and n_dead_tup are the bloat signal, n_mod_since_analyze is how far the statistics have drifted since the planner's last briefing, and last_autovacuum with last_autoanalyze says whether maintenance is keeping up at all. Since 18 the view also totals the time spent, in total_autovacuum_time and total_autoanalyze_time.

A handful of readings from that view change what you do next. A steadily climbing seq_scan on a 40-million-row table is a missing or unusable index. An n_mod_since_analyze of four million on orders means the planner is working from a description of a table that no longer exists, which is the mechanism behind Cartwheel's Saturday and Chapter 9's subject. A last_autovacuum that is null or days old on a hot table is the leading indicator of the bloat Chapter 7 measures. The companion view, pg_stat_user_indexes, gives idx_scan per index, which is where an unused-index audit gets its evidence and its date.

Activity and Waits

pg_stat_activity is a different kind of object: one row per backend, describing this moment. It answers what is running, what each backend is waiting for, and how long it has been at it, through state, wait_event_type and wait_event, xact_start, query_start and backend_type. It also carries query_id, which joins straight to pg_stat_statements — so the statement burning the server right now can be looked up in the aggregate ranking without matching text.

SELECT pid, state, wait_event_type, wait_event,
       now() - xact_start           AS xact_age,
       pg_blocking_pids(pid)        AS blocked_by,
       left(query, 34)              AS query
  FROM pg_stat_activity
 WHERE backend_type = 'client backend' AND state != 'idle'
 ORDER BY xact_start;

  pid  |        state        | wait_event_type | wait_event | xact_age  | blocked_by
-------+---------------------+-----------------+------------+-----------+------------
 21884 | idle in transaction |          Client | ClientRead | 02:41:09  | {}
 30112 | active              |            Lock | relation   | 00:00:22  | {21884}
 30119 | active              |            Lock | relation   | 00:00:19  | {21884}

One backend has been idle inside an open transaction for two hours and forty-one minutes, and two others are queued behind it on a relation lock. pg_blocking_pids() names the culprit directly, which is the difference between a two-minute incident and an hour of guessing. Two of these columns deserve permanent attention: wait_event_type, because Lock, IO and LWLock lead to completely different investigations, and the oldest xact_start on the cluster, because a transaction that old is holding back the vacuum horizon for every table in it, for reasons Chapters 6 and 7 make painfully concrete.

I/O and Checkpoints

pg_stat_io, added in 16, is the view that made I/O attributable. Its rows are keyed by three dimensions: backend_type, object (relation, temp relation or wal) and context (normal, vacuum, bulkread, bulkwrite, init). Each row counts reads, writes, extends, hits, evictions, reuses and fsyncs, with times attached when track_io_timing is on. That decomposition answers a question that was previously unanswerable from inside the database: whether the disk is busy because of client queries, because autovacuum is working through a backlog, or because a bulk load is streaming through its own ring buffer.

Checkpoint accounting moved in 17, so anything written before then reads checkpoint counts out of the wrong view. pg_stat_checkpointer now holds num_timed, num_requested and num_done, the restartpoint equivalents on a standby, write_time, sync_time and buffers_written. pg_stat_bgwriter keeps four columns: buffers_clean, maxwritten_clean, buffers_alloc and its own stats_reset.

SELECT num_timed, num_requested,
       round(100.0 * num_requested / nullif(num_timed + num_requested, 0), 1) AS forced_pct,
       pg_size_pretty(buffers_written * 8192::bigint) AS written,
       round(write_time / 1000) AS write_secs
  FROM pg_stat_checkpointer;

 num_timed | num_requested | forced_pct | written  | write_secs
-----------+---------------+------------+----------+------------
      1184 |          3971 |       77.0 | 3150 GB  |      38412

A checkpoint that happens because checkpoint_timeout elapsed is a scheduled, spread-out write. One that happens because WAL filled up is unscheduled, and 77% of Cartwheel's checkpoints are the second kind — the server is being pushed into flushing by write volume rather than by the clock, several times more often than it planned to. That ratio is a real warning and it is trivially graphable. What to do about it is a WAL sizing decision, and Chapter 12 owns both the mechanism and the settings.

Replication and WAL

The replication side is covered by three views, and the primary holds all of them. pg_stat_replication has a row per connected standby with write_lag, flush_lag and replay_lag as intervals plus the LSNs behind them, which is where Cartwheel's 30-second dashboard tolerance becomes a number you can alert on. pg_replication_slots shows how much WAL each slot is forcing the primary to retain and the slot's xmin, which is a vacuum-horizon risk wearing a different hat. pg_stat_wal totals generation volume in wal_records, wal_fpi, wal_bytes and wal_buffers_full. Chapter 13 turns these into a failover procedure. Until then they are two graphs and one alert on the free space of pg_wal, which is a filesystem a retained slot can fill on its own.

The Dozen Worth Alerting On

An alert should mean an engineer has to act, which limits the list severely. Transaction id age, because wraparound arrives as a shutdown rather than a warning. The age of the oldest open transaction, because it stops vacuum cluster-wide. Replication lag and WAL retained by a slot. Dead tuples on the two or three tables that matter. Connection saturation, measured at the pooler as well as at the database. The deadlock rate, temporary bytes written, and the ratio of forced to timed checkpoints. Free space on the pg_wal filesystem and on the data directory, separately, because they fill for different reasons. And the two numbers that are not in any of these views at all: application p95 latency and error rate.

Everything else belongs on a dashboard, and some of it belongs in the log instead, because a counter records that something happened and a log line records which thing. log_checkpoints has been on by default since 15; log_lock_waits is off and worth turning on, since it writes a line whenever a session waits longer than deadlock_timeout for a lock; log_autovacuum_min_duration defaults to 10 minutes and is usually better at zero; log_temp_files = 0 names the statements behind those 41,207 spills. One last detail for whoever writes the collector: stats_fetch_consistency defaults to cache, which pins the numbers for the rest of the transaction, and a sampling agent should set it to none so each read returns the current value.