PostgreSQL Checkpoint Spikes & Disk I/O Smoothing: Tuning max_wal_size and checkpoint_completion_target

The Checkpoint I/O Stall Problem

When high-throughput write applications—such as event collectors, financial settlement logs, or telemetry ingestion engines—scale to thousands of writes per second, databases often exhibit severe, periodic latency spikes. Queries that normally complete in 2ms suddenly spike to 800ms or time out entirely every 5 to 15 minutes. In nine out of ten PostgreSQL deployments, these recurring p99 latency spikes are triggered by unmitigated checkpoint write bursts.

In PostgreSQL, write operations (INSERT, UPDATE, DELETE) modify data in memory first, marking shared memory pages as "dirty." To guarantee durability under the ACID model, the modification is immediately appended sequentially to the Write-Ahead Log (WAL). The dirty shared buffer, however, remains in RAM until a checkpoint occurs. During a checkpoint, PostgreSQL must flush all modified pages in shared buffers out to disk storage (tablespaces), issue an fsync() syscall, and write a checkpoint record to the WAL.

1. Timed Checkpoints vs. Forced Checkpoints

A checkpoint is triggered by one of two conditions:

1. Timed Checkpoint: checkpoint_timeout has elapsed (default 5 minutes). This is healthy and expected.
2. Size-Based Checkpoint (Forced): The volume of WAL generated since the last checkpoint exceeds max_wal_size. This is dangerous and causes severe disk contention.

You can diagnose your checkpoint health by inspecting pg_stat_bgwriter:

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

Golden Rule: In a tuned production system, checkpoints_req (forced checkpoints) should account for less than 5% of total checkpoints. If checkpoints_req approaches or exceeds checkpoints_timed, your WAL buffer limits are undersized, forcing the server into panic write cycles.

2. The Mathematics of Spread Checkpoints: `checkpoint_completion_target`

Early PostgreSQL installations wrote dirty buffers as fast as the I/O subsystem could handle them, completely saturating NVMe and SSD storage controllers and starving read queries. Modern PostgreSQL introduces Spread Checkpoints controlled by checkpoint_completion_target.

The write duration budget is calculated as:

Target Write Duration = checkpoint_timeout × checkpoint_completion_target

3. Production Configuration Template

Below is a production-hardened tuning profile for an 8-core, 32GB RAM database server on NVMe storage handling 5,000+ continuous writes/sec:

# /etc/postgresql/16/main/postgresql.conf
# Checkpoint Smoothing & WAL Budgeting

checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
max_wal_size = 32GB
min_wal_size = 4GB

# Background Writer Tuning
bgwriter_delay = 20ms
bgwriter_lru_maxpages = 200
bgwriter_lru_multiplier = 3.0

# WAL Generation Buffers
wal_buffers = 64MB
wal_compression = lz4

4. Coordinating Linux Virtual Memory Dirty Ratios

Smoothing PostgreSQL checkpoints is only half the battle: the Linux kernel must also be prevented from holding too many dirty pages in page cache before issuing synchronous flush writebacks. On your database host, align kernel dirty limits:

# /etc/sysctl.d/99-postgresql-dirty.conf
# Flush dirty memory continuously instead of massive burst writebacks
vm.dirty_background_ratio = 3
vm.dirty_ratio = 10

By coupling checkpoint_completion_target = 0.9 with tuned Linux kernel writeback limits, write I/O is smoothly distributed across the 15-minute window, slashing p99 latency jitter by over 80%. Check our PostgreSQL & pgBouncer Config Sizer for automated configuration generation.