Checkpoints requested, and the ones run

The ratio everybody graphs, and what is in it

Of all the numbers a PostgreSQL cluster produces, the split between checkpoints that happened on a timer and checkpoints that happened because something asked for one is among the most graphed. The reasoning behind it is sound: a checkpoint on a timer is the server pacing itself, a checkpoint on request usually means the log volume hit its ceiling before the timer came round, and a cluster where the second outnumbers the first is a cluster whose checkpoint settings are being overruled by its workload.

What nobody says out loud is that both of those counters count intentions rather than events. A checkpoint can be requested, counted, and then not happen, because the server looks at what has changed since the last one and decides there is nothing worth writing. Until 18 the view had no way to express that, so a cluster that requested a hundred checkpoints and performed sixty reported a hundred, and every rate, ratio and interval computed from it was wrong by however many were skipped.

Version 18 adds a third counter for the ones that completed. It also adds a counter for a different kind of write the checkpointer does, which is less important and worth a paragraph at the end.

On 17 the new column simply is not there.

SELECT num_timed, num_requested, num_done FROM pg_stat_checkpointer;

ERROR:  column "num_done" does not exist
LINE 1: SELECT num_timed, num_requested, num_done FROM pg_stat_check...
                                         ^

Watching an idle cluster skip its own checkpoints

The cleanest demonstration is a cluster with nothing to do. The checkpoint interval here is thirty seconds rather than the usual five minutes, the counters are cleared, and then nothing happens for a little over a minute.

SELECT pg_stat_reset_shared('checkpointer');
SELECT pg_sleep(75);
SELECT num_timed, num_requested, num_done,
       num_timed + num_requested AS what_the_old_pair_implies
FROM pg_stat_checkpointer;

 pg_stat_reset_shared 
----------------------
 
(1 row)

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

 num_timed | num_requested | num_done | what_the_old_pair_implies 
-----------+---------------+----------+---------------------------
         2 |             0 |        1 |                         2
(1 row)

Two checkpoints came due. One of them ran, because the database had been created shortly before and there was something to write. The second found nothing changed since the first and did not happen at all.

The two counters available on 17 report two. The number of checkpoints this cluster performed in that window is one. Anything derived from the pair is out by a factor of two on an idle cluster, which sounds like a corner case until you consider how many clusters in a fleet are idle most of the night and how many capacity conversations start with a number averaged over a week.

The direction of the error is worth being precise about, because it is consistent and it is the flattering direction. The old counters always report at least as many checkpoints as happened, never fewer. So a cluster looks like it is checkpointing more often than it is, an interval computed from the counts looks shorter than it is, and a conclusion that checkpoint pressure is high is reached slightly too easily. That is the opposite of most monitoring errors, which tend to hide problems rather than invent them, and it means the correction on 18 will make some clusters look better rather than worse.

The same three checkpoints on both servers

Forced checkpoints are never skipped, which is the other half of the picture and is why this is easy to miss in testing. Here the same load and the same three explicit checkpoint commands run on each version.

SELECT pg_stat_reset_shared('checkpointer');
CREATE TABLE order_lines (line_id bigint, body text);
INSERT INTO order_lines SELECT g, md5(g::text) FROM generate_series(1, 60000) AS g;
CHECKPOINT;
CHECKPOINT;
CHECKPOINT;
SELECT num_timed, num_requested, num_timed + num_requested AS assumed_completed,
       buffers_written
FROM pg_stat_checkpointer;

 pg_stat_reset_shared 
----------------------
 
(1 row)

CREATE TABLE
INSERT 0 60000
CHECKPOINT
CHECKPOINT
CHECKPOINT
 num_timed | num_requested | assumed_completed | buffers_written 
-----------+---------------+-------------------+-----------------
         0 |             3 |                 3 |             636
(1 row)

And on 18, where the third column is measured rather than assumed:

SELECT pg_stat_reset_shared('checkpointer');
CREATE TABLE order_lines (line_id bigint, body text);
INSERT INTO order_lines SELECT g, md5(g::text) FROM generate_series(1, 60000) AS g;
CHECKPOINT;
CHECKPOINT;
CHECKPOINT;
SELECT num_timed, num_requested, num_done, buffers_written, slru_written
FROM pg_stat_checkpointer;

 pg_stat_reset_shared 
----------------------
 
(1 row)

CREATE TABLE
INSERT 0 60000
CHECKPOINT
CHECKPOINT
CHECKPOINT
 num_timed | num_requested | num_done | buffers_written | slru_written 
-----------+---------------+----------+-----------------+--------------
         0 |             3 |        3 |             631 |            1
(1 row)

Three requested, three performed, and the assumption and the measurement agree exactly.

SELECT num_requested - num_done AS requests_the_server_declined
FROM pg_stat_checkpointer;

 requests_the_server_declined 
------------------------------
                            0
(1 row)

That is the shape of the trap. An explicit checkpoint command carries a flag that says do this whether or not you think it is necessary, so a test that issues checkpoints by hand will never see a skip, will show the old and new counters in perfect agreement, and will support the conclusion that the new column is redundant. The skips happen to checkpoints the server schedules for itself, on clusters nobody is looking at, which is exactly where a monitoring error survives longest.

A fourth checkpoint on top of real work confirms that the counters track together when there is something to do.

INSERT INTO order_lines SELECT g, md5(g::text) FROM generate_series(60001, 120000) AS g;
CHECKPOINT;
SELECT num_timed, num_requested, num_done, slru_written FROM pg_stat_checkpointer;

INSERT 0 60000
CHECKPOINT
 num_timed | num_requested | num_done | slru_written 
-----------+---------------+----------+--------------
         0 |             4 |        4 |            2
(1 row)

The other new counter

The last column in those outputs counts a kind of write the checkpointer does that has never had a number before. Alongside the buffer pool, the server keeps several small caches in front of on-disk structures that are not tables: the commit log, the subtransaction map, the multixact files and a few others. A checkpoint flushes those too, and the count of buffers it wrote from them was previously folded into nothing at all.

It is a small number on almost every cluster, as the outputs above show, and that is the useful thing about it. On a workload that nests savepoints deeply or takes many concurrent row-level share locks, those caches become hot, and a checkpoint that used to write a couple of their buffers starts writing hundreds. Nothing else in the checkpoint statistics moves when that happens, because the buffer count is about the buffer pool. The new column is the only place the change is visible, which makes it worth collecting precisely because it is boring most of the time.

The standby counters have had this all along

There is an oddity in the shape of this view that makes the change easier to understand and slightly harder to forgive.

A standby does not perform checkpoints; it performs restartpoints, which do the same job against log it is replaying rather than log it is generating. The view has counted those separately for several releases, and it has counted them with three columns rather than two: how many came due on a timer, how many were requested, and how many were actually performed. The third of those has been there since before this release.

So the view already contained the distinction that the primary-side counters lacked, sitting one row over, in columns that most people never look at because most clusters they run are not standbys. Restartpoints are skipped far more often than checkpoints are, which is presumably why the completed count was thought necessary there first: a standby replaying a quiet period has nothing to write and declines almost every restartpoint that comes due.

Two things follow. If you already collect the standby counters and compute a completion rate from them, the primary-side change gives you the same calculation on the primary and you can use the same expression for both. And if you have ever wondered why the standby numbers look so different from the primary numbers on an otherwise identical cluster, part of the answer is that one pair was counting attempts and the other trio was counting outcomes.

What to change in the monitoring

• Graph the completed count, and keep the requested count beside it rather than instead of it. The gap between them is a real signal: it says the server is being asked for checkpoints it does not think are needed, which on most clusters means something is calling for them on a schedule that does not match the workload.
• Compute the effective checkpoint interval from the completed count. This is the number that feeds a full-page-image argument, and on any cluster with idle periods the old version of it was optimistic.
• Re-baseline rather than splice at the upgrade. The completed count is not a continuation of the pair above it, and a panel that switches source mid-series shows a step change that will be attributed to whatever else shipped that week.
• Add the cache-write counter and forget about it until a workload changes shape. It costs nothing to collect and there is no other way to see the thing it measures.

Rates computed from these counters

One more consequence, because it is where the numbers actually get used.

The interval between checkpoints is the quantity that decides how many full-page images a workload writes, which in turn decides a large share of the write-ahead log volume, which decides the archiving bill and the replication bandwidth. It is computed by dividing an elapsed window by a checkpoint count, and on 17 that count is the sum of two counters that include skipped checkpoints.

An idle cluster skipping half its checkpoints therefore reports an interval half as long as the real one. Halving a checkpoint interval in a full-page-image calculation roughly doubles the estimated image volume, and the estimate then disagrees with the volume the log counters actually report, by a factor nobody can account for. That disagreement is a familiar and unexplained annoyance on quiet clusters, and this is a large part of where it came from.

Using the completed count makes the two agree. It also makes the number smaller, which is worth flagging before somebody presents it: a cluster whose reported checkpoint frequency drops by half after an upgrade has not changed behaviour, and the old figure was the wrong one.

What it costs

Nothing here needs enabling. The checkpointer counters live in shared memory, they are maintained whether or not anyone reads them, and the view assembles one row on request.

The one operational note concerns the reset used throughout this page. Clearing the checkpointer counters is a write to shared state that everything else watching the cluster shares, and the view carries a single reset stamp for all of its columns, so a nightly job that clears them makes every long-window average on the cluster start over. On a cluster with more than one team watching it, that is worth agreeing rather than assuming, and two reads and a subtraction avoid the question entirely.