The statistics views that matter, and the ones that changed · PostgreSQL

There are dozens of statistics views. A small number answer questions you will actually ask.

The map

View	Answers
`pg_stat_activity`	What is happening now
`pg_stat_database`	Cluster-level health: cache, deadlocks, temp files, sessions
`pg_stat_all_tables`	Which tables are churned, scanned, vacuumed
`pg_stat_all_indexes`	Which indexes are used, and which are not
`pg_statio_all_tables`	Where physical reads go
`pg_stat_checkpointer`	Checkpoint frequency and cost
`pg_stat_bgwriter`	Background writer only, since 17
`pg_stat_wal`	WAL generation, lesson XII-02
`pg_stat_io`	I/O by backend type and context, lesson XII-05
`pg_stat_replication`	Standbys, Part XIV
`pg_stat_archiver`	Archiving, lesson XIII-05
`pg_stat_statements`	Query aggregate cost, lesson XVI-05

The split that breaks dashboards

Read-only / Safepg_stat_bgwriter on 18.6, in full— EXECUTION VALIDATED on 18.6. Three columns plus stats_reset.

$ SELECT * FROM pg_stat_bgwriter;

-[ RECORD 1 ]----+------------------------------
buffers_clean    | 1354
maxwritten_clean | 9
buffers_alloc    | 61370
stats_reset      | 2026-08-27 22:14:46.039111+00

Read-only / Safepg_stat_checkpointer, where the rest went— EXECUTION VALIDATED on 18.6 on a node that was a standby until recently.

$ SELECT * FROM pg_stat_checkpointer;

-[ RECORD 1 ]-------+------------------------------
num_timed           | 4
num_requested       | 2
num_done            | 3
restartpoints_timed | 41
restartpoints_req   | 0
restartpoints_done  | 5
write_time          | 298152
sync_time           | 44
buffers_written     | 13170
slru_written        | 23
stats_reset         | 2026-08-27 22:14:46.039111+00

The restartpoint_* columns are worth noticing: this node was a standby until the promotion in Part XV, and restartpoints are what a standby performs in place of checkpoints. restartpoints_timed 41 against restartpoints_done 5 is the normal pattern — most are skipped because replay has not advanced far enough to make one useful.

`pg_stat_database`

Read-only / Safethe columns worth alerting on— EXECUTION VALIDATED on 18.6 after a pgbench run.

$ SELECT datname, numbackends, xact_commit, xact_rollback, blks_hit, blks_read,
     round(100.0*blks_hit/NULLIF(blks_hit+blks_read,0),2) AS cache_hit_pct,
     deadlocks, temp_files, pg_size_pretty(temp_bytes) AS temp,
     conflicts, checksum_failures
FROM pg_stat_database WHERE datname='postgres';

 datname  | numbackends | xact_commit | xact_rollback | blks_hit | blks_read | cache_hit_pct | deadlocks | temp_files |  temp   | conflicts | checksum_failures
----------+-------------+-------------+---------------+----------+-----------+---------------+-----------+------------+---------+-----------+-------------------
postgres |           1 |       50559 |             1 |  1447898 |     20762 |         98.59 |         0 |          4 | 9808 kB |         0 |                 0

Column	Alert when
`deadlocks`	Increasing at all. Part IX
`temp_files` / `temp_bytes`	Increasing. `work_mem` too small, lesson XI-05
`checksum_failures`	Non-zero. Ever. Part XVIII
`conflicts`	Increasing on a standby. Lesson XIV-07
`xact_rollback`	Rising share — application errors
`cache_hit_pct`	Falling, as a trend

Read-only / Safethe session-outcome columns— EXECUTION VALIDATED on 18.6.

$ SELECT datname, session_time::int, active_time::int, idle_in_transaction_time::int, sessions, sessions_abandoned, sessions_fatal, sessions_killed FROM pg_stat_database WHERE datname='postgres';

 datname  | session_time | active_time | idle_in_transaction_time | sessions | sessions_abandoned | sessions_fatal | sessions_killed
----------+--------------+-------------+--------------------------+----------+--------------------+----------------+-----------------
postgres |       521821 |      506687 |                    11760 |      271 |                  0 |              0 |               0

sessions_abandoned, sessions_fatal and sessions_killed at zero is what health looks like. Any of them climbing is a real signal: abandoned means clients disconnected without closing, fatal means the server ended them, killed means an administrator did.

idle_in_transaction_time against active_time — 11.8 s against 506.7 s here — is the ratio that surfaces the problem in lesson VII-05 before it becomes bloat.

Why every counter is cumulative, and the three ways that misleads

Almost every number in these views is cumulative since stats_reset. That has consequences people trip over repeatedly.

A value is meaningless; a rate is a signal. deadlocks = 47 tells you nothing without knowing whether that is 47 since yesterday or 47 since the cluster was built in 2019. Every monitoring system should store the counter and graph the derivative.

Resets destroy history and are easy to cause. pg_stat_reset(), pg_stat_statements_reset(), and — importantly — pg_stat_reset_shared('bgwriter') all zero counters. A monitoring system computing a rate from a counter that just reset sees a large negative delta; a naive one reports a huge spike or a negative rate. Watch stats_reset itself and treat a change in it as “discard this interval”.

A crash resets them too. After an unclean shutdown, cumulative statistics are discarded, because they are held in memory and written out on clean shutdown only. A cluster that restarted overnight has counters starting from that restart, which looks like a quiet night in a rate graph rather than the outage it was.

There is a separate concern in pg_stat_activity, which is not cumulative — it is a snapshot, and a query lasting 5 ms is invisible to a monitor sampling every 10 seconds. That is not a defect: it is why pg_stat_statements exists, aggregating across executions so that short frequent queries are counted rather than sampled. The two views answer different questions, and using the wrong one produces a confident wrong answer about where time goes.

One PostgreSQL 15 change is worth knowing when reading older advice: the statistics collector process was removed and cumulative statistics now live in shared memory. The practical effect is that statistics are current rather than lagging by up to half a second, so pg_stat_activity and the cumulative views no longer disagree about very recent events the way they used to.

What to take from this

A dozen views answer real questions. Learn those, not all of them.
pg_stat_bgwriter has three columns. Checkpoint metrics moved to pg_stat_checkpointer in 17.
Alert on deadlocks, temp_files, conflicts, and checksum_failures — the last at any non-zero value.
Cache hit ratio is a trend. A page-cache hit counts as a miss.
sessions_abandoned / _fatal / _killed climbing is a real signal.
Counters are cumulative. Graph rates, and watch stats_reset.
A crash discards statistics; the gap looks like quiet.

Cross-course references

Observability for Production Sysadmins — Part IX (Exporters) and Part LIX (Database observability) cover turning these views into series, and Part III (Metrics fundamentals) covers why a cumulative counter must be rated rather than graphed raw.
Linux for Production Sysadmins — Part XLIV (Central monitoring) covers where the resulting series are stored and who can query them.

The map

The split that breaks dashboards

pg_stat_database

What to take from this

Cross-course references

Quiz

`pg_stat_database`