PostgreSQL Checkpoints on Fast NVMe: Tuning max_wal_size and checkpoint_completion_target

We migrated our 4TB primary PostgreSQL database from cloud-managed block storage to dedicated enterprise NVMe drives capable of sustaining over 80,000 write IOPS. We expected our p99 query latency to become completely flat.

Instead, our monitoring graphs revealed an ugly periodic pattern: every five minutes like clockwork, payment write latency spiked from 1.2ms to over 420ms, producing a jagged sawtooth graph that routinely tripped our customer-facing SLAs.

The root cause was PostgreSQL’s checkpoint process. Whenever a checkpoint was triggered, the checkpointer dumped gigabytes of dirty shared buffers into the operating system page cache faster than the storage controller could commit them, stalling concurrent foreground fsync calls.

What a checkpoint actually does under the hood

In PostgreSQL, whenever a query modifies data (INSERT, UPDATE, DELETE), the change is written immediately to the Write-Ahead Log (WAL) on disk for durability, but the corresponding data pages in shared_buffers are simply marked as dirty in memory.

A checkpoint is a periodic synchronization barrier. The checkpointer process writes all dirty shared buffers out to disk, marks the checkpoint location in the WAL control file, and removes old WAL segments that are no longer needed for crash recovery.

Checkpoints are triggered by two conditions: either the elapsed time since the last checkpoint reaches checkpoint_timeout (default 5 minutes), or the volume of WAL generated since the last checkpoint reaches max_wal_size (default 1GB).

The disaster of default max_wal_size

On any write-intensive system, the default max_wal_size = 1GB is catastrophic. Under a write load of 30MB/sec, a 1GB WAL buffer is filled in approximately 35 seconds.

When max_wal_size is exceeded, PostgreSQL triggers an emergency checkpoint immediately. These are called ‘requested’ checkpoints. Because they are driven by WAL pressure rather than elapsed time, PostgreSQL cannot spread the disk writes smoothly, resulting in massive, uncontrolled bursts of I/O that saturate storage queues.

You can diagnose whether your checkpoints are timed or forced by querying pg_stat_checkpoints:

-- Checking checkpoint health and forced vs timed ratio
SELECT 
  checkpoints_timed,
  checkpoints_req,
  checkpoint_write_time,
  checkpoint_sync_time,
  buffers_checkpoint,
  buffers_backend
FROM pg_stat_checkpoints;

If checkpoints_req is greater than 5% of checkpoints_timed, your max_wal_size is set far too low. On modern servers with fast NVMe drives, max_wal_size should typically be set between 32GB and 64GB.

checkpoint_completion_target: Spreading the write load

The checkpoint_completion_target parameter controls how long PostgreSQL takes to complete a checkpoint. It is expressed as a fraction of checkpoint_timeout.

If checkpoint_timeout is 15 minutes (900 seconds) and checkpoint_completion_target is set to 0.9, the checkpointer will throttle its I/O so that the writes are spread over 13.5 minutes (810 seconds). This keeps the I/O rate smooth, leaving ample storage bandwidth for concurrent transactions.

Setting this to 0.9 is considered mandatory for modern production workloads. In PostgreSQL 14+, the default was finally changed to 0.9, but older versions or inherited configs frequently retain the obsolete 0.5 default.

The kernel connection: Linux dirty page writeback

Even with PostgreSQL properly tuned, latency sawteeth can persist if the Linux kernel’s memory subsystem is misconfigured.

When PostgreSQL writes dirty buffers, it calls write(), which deposits data into the Linux page cache. The kernel only initiates disk writes once the volume of dirty memory exceeds vm.dirty_background_ratio (default often 10%). On a server with 256GB of RAM, 10% is 25GB of dirty memory.

When the threshold is crossed, the kernel flusher wakes up and dumps 25GB in a single overwhelming blast. To prevent this, tune the kernel to begin flushing in small, continuous trickles using bytes rather than percentages:

# /etc/sysctl.d/99-postgresql.conf
# Flush dirty memory early and continuously
vm.dirty_background_bytes = 67108864    # 64 MB
vm.dirty_bytes = 268435456              # 256 MB

The practical standard

High-level database architecture is not about drawn boxes on an infrastructure diagram. It is about how the engine manages shared resources under concurrency — memory, latches, write-ahead logs, and lock tables. When things break at 2 AM, the fix is rarely adding another replica or throwing more CPU at the host. The fix is understanding the underlying resource bottleneck, measuring the exact wait event, and applying the architectural constraint that makes the system predictable.