Free tool
Where is the checkpoint trigger, and what does moving it cost?
The server does not start a checkpoint at max_wal_size. It starts one a good way short of it, and the same distance is what a crash has to replay — so the setting that quiets your checkpoints lengthens your outage.
Run it on your numbers
pg_stat_wal.wal_bytes over the hours since it was last reset, or the rate your archive grows at.
pg_stat_wal.wal_fpi over the same window as the WAL figure. The first write to a page after a checkpoint logs the whole page.
It spreads the writes out, and it also divides into max_wal_size when the server works out where the size trigger goes.
Fixed at initdb. The trigger is counted in whole segments, so this is what the rounding is rounding to.
Measure it on a standby rather than guessing: replay is single-threaded and is usually far slower than the write path that produced the WAL.
The number in the objective somebody signed, not the one you hope for.
The trigger is not where the setting says it is
Everyone reads max_wal_size as
the amount of write-ahead log that accumulates before a checkpoint is forced, and works out
the interval by dividing it by the WAL rate. That arithmetic comes out roughly twice as
generous as the server's. The server divides the setting by one plus
checkpoint_completion_target,
rounds the result down to whole segments, and then asks for a checkpoint one segment before
even that.
At the shipped defaults that is 512 megabytes rather than the gigabyte on the label. The logic is reasonable once you see it: the server keeps one checkpoint cycle of log, and it expects to spend most of the next cycle writing out the last one, so it has to start early enough that both fit inside the limit. It is only surprising because nothing prints it. The first number above is what your settings actually produce.
What each checkpoint costs in full pages
The first time a page is written after a checkpoint, the whole page goes into the log rather than the few bytes that changed. That is how a crash in the middle of a page write is survivable, and it is why WAL volume is not proportional to the size of your updates. On a busy server it is routine for a third of everything written to be these images, and they are concentrated in the moments right after each checkpoint.
Checkpoints further apart therefore cost less in full pages, and the tool declines to tell you how much less. There are two honest bounds and no single number between them. If the longer cycle dirties the same pages it would have dirtied anyway, the saving is roughly proportional to the change in frequency. If every cycle reaches pages it has not touched before, there is no saving at all. Which one you are is a property of your workload, and no reading of the PostgreSQL source will settle it.
The half of the trade that never gets printed
Raising max_wal_size until checkpoints are timed is standard advice and it is usually right. What it also does is move the redo point further back, and the redo point is exactly where crash recovery starts replaying from. The distance the checkpoint trigger sits at is the distance a crash has to replay. That is why both figures above come from the same number, and why the tool asks for a replay rate: recovery is single-threaded and is normally much slower than the machinery that produced the log in the first place.
So there is no free setting here, only a choice about which cost you would rather carry. More frequent checkpoints mean more full pages written every hour of every day. Less frequent ones mean a longer outage on the day something falls over. Put the recovery objective somebody signed into the box above, and the tool says which end of that range you are allowed to stand at.
Nobody wants to be the one who ran the query too late.
DBExplore watches the numbers behind these checks on every cluster it is pointed at, and asks before it changes anything. We onboard design partners in small batches.