Postgres WAL tuning for high-throughput event streams

When you’re pushing thousands of events per second into a database, the default configuration of your Write-Ahead Logging (WAL) is almost certainly going to be your first bottleneck. I remember staring at a dashboard during a particularly nasty spike, watching disk I/O wait climb to 40% while the application latency drifted into the multi-second range. It wasn't the query logic; it was the database trying to keep up with the physical reality of logging every single mutation to disk.

If you’re building high-throughput event streaming systems, Postgres is a rock-solid choice, but it requires specific mechanical sympathy. You have to stop treating the WAL as a black box and start tuning it for your specific I/O profile.

Why WAL tuning matters for high-throughput

Postgres uses WAL to ensure atomicity and durability. Every change—every INSERT, UPDATE, or DELETE—must be written to the WAL on disk before the transaction is considered committed. In a high-traffic environment, this creates a massive sequential write load. If your checkpoints happen too frequently, you’re constantly flushing dirty buffers and creating spikes in I/O. If they happen too infrequently, your recovery time after a crash becomes unmanageably long.

We first tried simply throwing more IOPS at the EBS volumes. That helped for about three hours before the checkpoint stalls caught up with us again. It was a classic case of masking a configuration issue with hardware. We eventually realized that tuning the WAL was the only way to stabilize the latency tail.

Strategies for Postgres WAL tuning

To get the most out of your setup, you need to look at three primary knobs: max_wal_size, checkpoint_timeout, and min_wal_size.

1. Increase max_wal_size: The default is often too low for modern high-write systems. By bumping this to 4GB or even 8GB, you allow the database to delay checkpoints during heavy bursts. This smooths out the I/O profile significantly.
2. Extend checkpoint_timeout: If your disk subsystem can handle it, push this to 15 or 30 minutes. The trade-off is longer recovery time, but the gain in throughput is immediate.
3. Adjust checkpoint_completion_target: Aim for a value of 0.9. This spreads the I/O load across the entire interval between checkpoints, preventing that nasty "checkpoint spike" that kills your p99 latencies.

If you’re still seeing contention, you might want to look into write-buffer coalescing to reduce the sheer volume of commits, or potentially offload non-critical data to a different storage tier.

Monitoring the impact

You shouldn't guess if your changes are working. Use the pg_stat_bgwriter view to track how often your checkpoints are happening. If you see checkpoints_timed being significantly lower than checkpoints_req, your system is forcing checkpoints because it's running out of WAL space before the timer hits.

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

If checkpoints_req is high, your max_wal_size is too small for your current event volume. It’s that simple.

When to look beyond WAL

Sometimes, even perfectly tuned WAL isn't enough. If your table is bloated or you’re fighting lock contention on indexes, the logging volume becomes secondary to architectural issues. I’ve found that using partial indexes can significantly reduce the amount of work the database performs during index updates, which in turn reduces the total bytes written to the WAL.

I'm still skeptical about running extremely aggressive WAL settings on standard spinning disks. We’ve moved almost everything to NVMe-backed storage, which changes the math on how "expensive" a write is. If you're still on mechanical drives, you’ll need to be much more conservative with these settings to avoid thrashing the head.

At the end of the day, there is no "perfect" configuration. Every time I think I’ve found the golden ratio for checkpoint_timeout, a change in our traffic pattern or a new indexing requirement forces me to re-evaluate. Start by measuring your current checkpoint frequency, make one change at a time, and watch your I/O wait graphs. Don't chase the perfect number; chase the stable latency.