Right, let’s talk about two parameters that sound like they’re about the most boring parts of PostgreSQL but are actually the secret handshake to a well-behaved database: checkpoint_completion_target and wal_buffers. If you just set these and forget them, you’re probably leaving performance and stability on the table. I’m here to make sure that doesn’t happen.

Think of a checkpoint as PostgreSQL’s big spring cleaning. It’s the moment where all the dirty data pages in memory (in shared_buffers) are finally scrubbed clean and written out to disk. This is a Good Thing™ because it ensures data durability and gives us a known-good recovery point. The problem? This cleaning spree is I/O intensive and can absolutely hammer your disk, causing everyone else’s queries to slow to a crawl while the database is busy scrubbing. This is what we call “checkpoint spikes.”

This is where checkpoint_completion_target comes in. It’s not a knob for how often checkpoints happen (that’s primarily controlled by max_wal_size), but rather how the work is spread out. By default, it’s set to a very conservative 0.5. This tells PostgreSQL: “Try to finish the checkpoint process in the latter half of the time between checkpoints.” In practice, this often means it ends up doing a lot of the I/O in a rush at the end, which is… not great.

The modern, sane advice is to set this to 0.9. Why? This tells the database: “You have almost the entire time between checkpoints to finish writing this data. Please, for the love of all that is holy, spread the I/O out evenly.” This is like giving your obsessive-compulsive cleaner the whole day to clean instead of just the last 30 minutes. The result is a much smoother I/O profile and far fewer punishing spikes. It doesn’t make the checkpoints faster; it makes them smoother, which is what you actually want.

Tuning checkpoint_completion_target

Setting this is dead simple. You’re just changing one value in your postgresql.conf. The trick is monitoring to see if your change is having the desired effect.

-- Check your current setting
SHOW checkpoint_completion_target;

-- To change it, edit postgresql.conf. A good starting point:
checkpoint_completion_target = 0.9

The real proof is in the pg_stat_bgwriter view. After a day or two of normal load, run this query. You want to see a low ratio of checkpoints_timed to checkpoints_req (meaning most checkpoints are happening on schedule, not because you’re panicking and running out of WAL space) and you want to feel the smooth I/O with your own soul. Or, you know, look at your disk utilization graph and see a flat line instead of a seismograph.

SELECT
    checkpoints_timed,
    checkpoints_req,
    buffers_checkpoint,
    buffers_clean,
    maxwritten_clean
FROM pg_stat_bgwriter;

Configuring wal_buffers

Now, let’s talk about wal_buffers. This is the amount of shared memory set aside to buffer Write-Ahead Log (WAL) data before it’s written to disk. Every transaction you commit gets its WAL data dumped in here first. It’s a staging area.

The default is… weird. It’s -1, which means it’s automatically set based on shared_buffers (specifically, 1/32 of it, capped at 16MB). This is one of those “one size fits none” compromises. For a massive shared_buffers setting, 1/32nd might be huge and wasteful. For a small setup, it might be too small.

A transaction that generates more WAL than fits in wal_buffers will force a flush before the commit is even finished, which adds latency. If you do a lot of bulk loads or large transactions, a tiny wal_buffers can be a real performance killer.

The best practice? Just set it explicitly. For the vast majority of systems, 16MB is the sweet spot. It’s large enough to handle pretty much any single transaction without forcing an early flush, and it’s a trivial amount of RAM for any modern server. Don’t overthink this one.

-- The automatic default value
SHOW wal_buffers;

-- To set it explicitly, add to postgresql.conf:
wal_buffers = 16MB

The Interaction and Common Pitfalls

Here’s the key insight: these two parameters work in concert. wal_buffers is the small, fast staging area for incoming writes. The checkpoint process, guided by checkpoint_completion_target, is the larger, scheduled cleanup operation that moves things from the shared buffers to their permanent home on disk.

The most common pitfall is leaving checkpoint_completion_target at its default of 0.5. This is arguably PostgreSQL’s most conservative and outdated default. It’s like buying a sports car and never shifting out of second gear. You’re just inviting I/O storms for no good reason.

Another edge case is using a ridiculously large wal_buffers (like 1GB) because more must be better, right? Wrong. The WAL buffers are just a transit lounge. The data sits there very briefly before being written to the WAL segments on disk. Allocating hundreds of megabytes to it is almost always a waste of perfectly good RAM that could have been given to shared_buffers or the OS disk cache. Stick to 16MB unless you have a very specific and measured reason to do otherwise, like you’re literally doing multi-gigabyte single-statement inserts. (And if you are, let’s have a separate talk about your life choices.)

So, to recap: set checkpoint_completion_target = 0.9 to smooth out your I/O, and set wal_buffers = 16MB to avoid pointless write stalls. It’s two lines in a config file that can make a world of difference. Go be a hero.