A PostgreSQL statistics surprise hiding in plain sight

PostgreSQL has a parameter called stats_fetch_consistency that controls how statistics views like pg_stat_checkpointer return data. It was added in PostgreSQL v15, and its default behavior can produce results that look stale — even in the middle of a transaction.

Consider this scenario: you issue five checkpoints and then inspect pg_stat_checkpointer in the same transaction:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

-- start a transaction

BEGIN;

SELECT num_timed, num_requested FROM pg_stat_checkpointer;

num_timed │ num_requested

═══════════╪═══════════════

        52 │             4

(1 row)

-- explicitly trigger a checkpoint

CHECKPOINT;

SELECT num_timed, num_requested FROM pg_stat_checkpointer;

num_timed │ num_requested

═══════════╪═══════════════

        52 │             4

(1 row)

The num_requested column hasn't moved. Only after you commit does the view reflect the new value:

1

2

3

4

5

6

7

8

COMMIT;

SELECT num_timed, num_requested FROM pg_stat_checkpointer;

num_timed │ num_requested

═══════════╪═══════════════

        52 │             5

(1 row)

This isn't an isolation-level issue. It's the default for stats_fetch_consistency, which is cache. Under that setting, PostgreSQL caches statistics data for an object the first time you access it in a transaction, and keeps serving the cached values until the transaction ends.

Three settings, six affected views

The parameter applies to these statistics views:

  • pg_stat_archiver — WAL archiver statistics
  • pg_stat_bgwriter — background writer statistics
  • pg_stat_checkpointer — checkpointer statistics
  • pg_stat_io — I/O statistics per backend type
  • pg_stat_slru — SLRU cache statistics
  • pg_stat_wal — WAL statistics

The three possible values behave as follows:

  • none — no caching at all; every access reads current statistics
  • cache — on first access to an object's statistics in a transaction, PostgreSQL reads and caches that object's data for the remainder of the transaction
  • snapshot — on first access to any object's statistics in a transaction, PostgreSQL reads statistics for all objects in the database and caches them for the transaction

Setting stats_fetch_consistency to none avoids the stale-read surprise entirely.

When each setting makes sense

The parameter was introduced in PostgreSQL v15 via commit 5891c7a8ed, as part of moving database statistics tracking into shared memory. For ad-hoc queries, none is usually the safer choice — there's no reason to tolerate cached values when you're just poking at a view once.

Monitoring systems that query a broad set of statistics inside a single transaction might benefit from snapshot, which guarantees consistent values across all objects for that transaction. Otherwise, none is again a reasonable default for monitoring, assuming each object is queried only once.

In most cases, the choice matters little in practice. If you do want to change it, none is a sensible starting point.