The default that freezes your counters

Why the number on your screen stopped moving

Somebody is investigating a slow cluster. They open a session, start a transaction because they want a consistent view of several things at once, and poll a counter. The counter does not change. They poll it again a minute later and it still does not change, on a cluster that is visibly busy.

The counter is fine. The session asked for a consistent view and got one, and the consistency extends further than anybody expected.

PostgreSQL 15 introduced a setting that names this behaviour and lets you turn it off. It arrived with the move of cumulative statistics into shared memory, which the Monitoring section of the PostgreSQL 15 release notes records as one entry, and the setting itself is not called out separately there. That is a small documentation gap with a large practical footprint, because the setting governs what every monitoring query on the server sees.

The behaviour, on a server that has no name for it

Before showing the setting, it is worth establishing that the behaviour is not new. PostgreSQL 14 does the same thing and gives you no way to ask about it.

SHOW stats_fetch_consistency;

ERROR:  unrecognized configuration parameter "stats_fetch_consistency"

The freeze is demonstrable anyway. Requesting a checkpoint increments a counter that a different process maintains, so it is a change the reading session did not make and cannot be accused of hiding from itself.

BEGIN;
SELECT checkpoints_req AS first_read FROM pg_stat_bgwriter;
CHECKPOINT;
SELECT pg_sleep(1);
SELECT checkpoints_req AS second_read FROM pg_stat_bgwriter;
SELECT pg_stat_clear_snapshot();
SELECT checkpoints_req AS after_clearing FROM pg_stat_bgwriter;
COMMIT;

On 14:

BEGIN
 first_read 
------------
         11
(1 row)

CHECKPOINT
 pg_sleep 
----------
 
(1 row)

 second_read 
-------------
          11
(1 row)

 pg_stat_clear_snapshot 
------------------------
 
(1 row)

 after_clearing 
----------------
             12
(1 row)

COMMIT

On 15, with its default settings:

BEGIN
 first_read 
------------
          1
(1 row)

CHECKPOINT
 pg_sleep 
----------
 
(1 row)

 second_read 
-------------
           1
(1 row)

 pg_stat_clear_snapshot 
------------------------
 
(1 row)

 after_clearing 
----------------
              2
(1 row)

COMMIT

Identical behaviour. A checkpoint happened between the first and second reads, the second read did not see it, and discarding the cached snapshot explicitly is what made the new value visible. Both versions cache; only one of them will admit to it.

The setting

SELECT name, setting, enumvals, context FROM pg_settings WHERE name = 'stats_fetch_consistency';

          name           | setting |       enumvals        | context 
-------------------------+---------+-----------------------+---------
 stats_fetch_consistency | cache   | {none,cache,snapshot} | user
(1 row)

Three accepted values, changeable per session, no restart involved. The default is the middle one, and the one worth knowing about on the first day is the one that turns the caching off.

SET stats_fetch_consistency = 'none';
BEGIN;
SELECT checkpoints_req AS first_read FROM pg_stat_bgwriter;
CHECKPOINT;
SELECT pg_sleep(1);
SELECT checkpoints_req AS second_read FROM pg_stat_bgwriter;
COMMIT;

SET
BEGIN
 first_read 
------------
          2
(1 row)

CHECKPOINT
 pg_sleep 
----------
 
(1 row)

 second_read 
-------------
           3
(1 row)

COMMIT

Same transaction, same statements, and the second read moves. Nothing had to be cleared and nothing had to be committed.

The third accepted value extends the caching rather than removing it, and this page does not demonstrate it: showing the difference between the two caching modes requires contriving a change to one statistics object while a transaction is already holding another, and a demonstration that needs contriving is not a demonstration an operator can check. The two shown above are the ones a monitoring decision actually turns on.

Why the default is what it is

Caching is the right default and it is worth understanding why, because the temptation after reading the above is to change it globally.

A monitoring query that reads three views and computes a ratio across them wants all three read at one instant. Without caching, the views are read at slightly different times, the arithmetic is done across a moving target, and a derived value can come out impossible: a hit ratio above one hundred percent, a difference between two counters that goes negative. Those artefacts are rare, they are baffling when they appear, and they are exactly what caching prevents.

The cost is the behaviour at the top of this page, and it lands on a narrow set of uses: interactive investigation inside an open transaction, and any collector that holds a transaction open across multiple polls.

What to actually change

Not the server default, in almost every case. Three narrower moves cover the real needs.

• For interactive work, set the session to the live mode when you open the psql session you are going to investigate from. It costs one line and it stops the confusion at the source.
• For a collector, keep the default and make each poll its own transaction. Autocommit already does this: every statement is a transaction, and the cache is discarded at its end.
• For a collector that genuinely needs several views at one instant, keep the default and keep the explicit transaction. That is the case the default was designed for, and it is the right shape.

The function that discards the snapshot is the escape hatch for everything else, and it is worth knowing it exists on 14 too. A long-lived session that has been open for hours and reads statistics occasionally will keep serving whatever it cached the first time in its current transaction, on either version.

The trap for a monitoring tool

Any collection layer that opens a connection, begins a transaction, and then loops is reading the same instant forever. This is not hypothetical: it is the natural shape of a collector written by somebody being careful about consistency, and it produces a metric series that is perfectly flat and perfectly wrong. A flat series is not an obvious failure, because plenty of counters are legitimately flat on an idle cluster.

The diagnostic is straightforward. If a counter you know to be moving reads the same value twice from the same connection, check whether that connection is inside a transaction. If it is, the collector is the problem, not the server.

There is a second surface this applies to that is not a cumulative counter at all. Per-backend information, which includes the activity view and the per-session functions PostgreSQL 16 added around subtransactions, is snapshotted per transaction under the same rules and is not governed by the setting on this page. The clearing function covers both, which is a reason to prefer it over reasoning about which surface obeys which rule.

Three places this shows up in real tooling

The behaviour is easy to describe and surprisingly hard to recognise in the wild, because in each of these cases the symptom looks like something else.

The first is a dashboard panel that is flat while a neighbouring panel moves. If both are fed by the same collector and the collector holds a transaction, everything it reads is from one instant, and the panels that appear to move are the ones computing a rate against a clock rather than against the data. The two are fed by the same frozen numbers and only one of them shows it.

The second is a psql session used for investigation. Somebody types BEGIN out of habit, or runs a query that starts a transaction and never ends it, and then watches a counter with a repeating query. The counter is stuck, they conclude the cluster is idle, and they go and look somewhere else. This is the case the live mode exists for, and setting it at the start of an investigation session costs nothing.

The third is subtler and catches careful people. A monitoring query that deliberately wraps several reads in a transaction to get a consistent picture is doing the right thing, and it will get a consistent picture that is as old as the transaction. If that transaction also does something slow first, such as a catalog sweep over a database with a very large number of relations, then every counter it reads afterwards is from before the sweep started. The reading is consistent and it is not current.

In all three the diagnosis is the same question: is this connection inside a transaction, and how long has it been there. The activity view answers both, and the answer is usually surprising to whoever wrote the collector.

There is one more consequence worth naming, because it changes what a reset means. Clearing a counter is a write to the statistics system and takes effect immediately, but a transaction that has already cached the counter keeps serving the old value until it ends or clears its snapshot. So a session that resets and then reads back, inside one transaction, can see the value it just cleared. That is not a bug and it is exactly the behaviour described above, applied to an operation where it is far more surprising, because the session performing the write is the one being shown the stale number.

What it costs

The live mode costs a fresh read of each counter rather than a cached one, which on any ordinary query is not a cost worth measuring. The caching modes cost the memory of whatever the transaction has touched, which for a sweep of every table in a large database is not nothing and is released at commit.

The setting itself needs no restart and no privilege beyond what a session already has over its own settings, so it can be tried in one connection and abandoned in the next. That is unusually low-risk for a change that alters what monitoring sees, and it is the argument for trying the live mode the next time a counter appears to be stuck rather than assuming the cluster is idle. On a fleet that still has 14 clusters in it, the same investigation needs the clearing function instead, which is one of several differences the upgrade out of 14 brings at once.