Tuning PostgreSQL checkpoints

One day we noticed that our Postgres logs were littered with lines similar to these:

// from my own local testing
LOG:  checkpoints are occurring too frequently (23 seconds apart)
HINT:  Consider increasing the configuration parameter "max_wal_size".

After a bit of analysis, we realised over 90% of checkpoints were triggered by the volume of WAL generated, rather than a timer. On one environment, it was 99%. They were set to run every 5 minutes, but on the primary of a large environment they were running about every 15 seconds.

The main cause was max_wal_size, which was at the default 1GB. It suited a small server, but couldn't scale as the deployment grew.

What checkpoints are

Postgres doesn't write changes straight to the disk. It writes them to the write-ahead log (WAL) first, then keeps the changed pages in memory. If it crashes, it replays the WAL to get those changes back.

A checkpoint is when Postgres finally writes those pages to disk. After that, older WAL isn't needed for recovery. Two things can start one:

• A timed checkpoint starts when the time defined as checkpoint_timeout is reached.
• A requested checkpoint starts when enough WAL has built up. This also resets the timer, so if WAL always triggers the checkpoint first, a timed one never fires.

A few requested checkpoints can be good, they help during a storm of writes. If most of them are requested, max_wal_size is too small for the workload. A good target is about 90% timed.

There are three main checkpoint config parameters:

• max_wal_size - This is the maximum size to let the WAL grow to during automatic checkpoints. If WAL reaches this size, a requested checkpoint is triggered.
• checkpoint_timeout - This is the maximum time between two automatic checkpoints. If this time is reached, a timed checkpoint is triggered.
• checkpoint_completion_target - This specifies the target for checkpoint completion as a fraction of the total time between checkpoints, taken as a value between 0 and 1. A value of 0.9 with a 5 minute checkpoint_timeout means the work of the checkpoint should be spread evenly across 5 * 0.9 = 4 minutes 30 seconds.

Full-page writes

Frequent checkpoints also make Postgres write more WAL. After any checkpoint, the first update to any page logs the entire 8KB page to guarantee crash safety, known as a full-page write.

This has a much higher impact on disk and CPU resources than a smaller, row-level delta would. This means that frequent checkpoints are much more resource intensive than infrequent ones.

New config

We tested this config for about two days (it was PG13 at the time):

• max_wal_size went from 1GB to 8GB
• checkpoint_completion_target went from 0.5 to 0.9.

We assumed a rate of 1GB of WAL a minute, which is 5GB over the timeout. Adding a 1.5x margin gives 7.5GB, which we rounded up to 8. We didn't want to go much higher because more WAL means a longer recovery after a crash.

The chart below shows a model of both setups over 10 minutes. Each line shows the WAL built up since the last checkpoint, and drops when a new checkpoint starts.

The 1GB line hits its trigger every time. The 8GB line is still under its trigger when the 5 minute timer hits.

After a couple of days under load, these were the numbers on the primary:

Requested checkpoints still spread their writes out, but 15 seconds doesn't leave much room to do it. With about 5 minutes between checkpoints, peak disk write fell by about two thirds, and peak write IOPS by about 90%.

The average write rate rose slightly, though the load wasn't identical in the two periods. We changed both settings together, so I can't say how much of a drop came from each one.

When a checkpoint starts

I said earlier that a checkpoint starts when WAL reaches max_wal_size, but that's not actually true. Postgres can't remove old WAL until the next checkpoint finishes, and more WAL arrives while it runs, so it starts early to stay under the limit. The trigger is roughly:

max_wal_size / (1 + checkpoint_completion_target)

With a target of 0.9, that's close to half of what you set. Our 8GB meant a checkpoint at about 4.2GB, which is less than the 5GB we planned for. It still worked because the real WAL rate stayed under 4.2GB every 5 minutes.

Testing it locally

I also tried this on my own PC, with Postgres 18.1 in a container with a 3GB pgbench database and the same 5 minute timeout. Each run is 10 minutes of writes at a different max_wal_size, and each one ends by killing Postgres to time the restart.

Recovery time

After a crash, Postgres replays the WAL since the last checkpoint, so more WAL means a longer restart. In the table above, going from 1GB to 8GB added about a minute. So the longest restart you can accept sets the upper limit for max_wal_size.

Shared buffers

A checkpoint also cleans changed pages out of memory, so less frequent checkpoints leave more of them there. In my local runs, with max_wal_size at 8GB and 1GB of shared_buffers, the buffers filled up and clients had to write pages out themselves.

Latency stalled over a second, again and again. Raising the buffers to 4GB mostly stopped this. One run had no stalls, and the other had one long stall. It's worth checking this before raising max_wal_size.

Checking your own setup

1. Search your logs for "too frequently".
2. Read the checkpoint counts twice, some time apart, and compare them. The counters run from the last reset, so one reading can hide a recent change.
3. Take pg_current_wal_lsn() twice to get your WAL rate in normal running. Expect the rate to fall after you raise max_wal_size.
4. Multiply that rate by checkpoint_timeout, then by (1 + checkpoint_completion_target), then add a margin.
5. Set it, and check that most checkpoints are now timed.
6. Kill Postgres under load on a test system and time the restart.

-- Postgres 17 and later
SELECT num_timed, num_requested FROM pg_stat_checkpointer;

-- Before Postgres 17
SELECT checkpoints_timed, checkpoints_req FROM pg_stat_bgwriter;