Post-Mortem: Debugging PostgreSQL WAL Bloat and Checkpoint Latency Spikes Under Heavy Write Load

Production databases under high write pressure inevitably expose the hidden friction between checkpoint scheduling, file system flush rates, and write-ahead log (WAL) generation. Last month, our primary PostgreSQL 16 cluster experienced a sudden latency cliff. TPS dropped from forty thousand to near zero, while disk queues maxed out. This post-mortem covers how we diagnosed the root cause, adjusted kernel and database parameters, and stabilized the cluster.

The Incident: Checkpoint Spikes and WAL Accumulation

The symptoms were deceptive. CPU utilization hovered around forty percent, but disk latency ('await' times in 'iostat') spiked past two thousand milliseconds. The Postgres error log was flooded with messages regarding checkpoint completion taking too long:

TEXT

LOG: checkpoint complete: wrote 45212 buffers (86.3%); 0 WAL file(s) added, 0 removed, 184 recyle(s); write=182.431 s, sync=12.112 s

Postgres was configured with default checkpoint parameters. When the 'max_wal_size' was reached or the 'checkpoint_timeout' interval elapsed, the checkpointer process flushed dirty pages to disk aggressively. Because the buffer pool was large and I/O throttling was absent, the OS page cache was completely overwhelmed, starving concurrent read and write queries of I/O operations per second (IOPS).

Diagnosing I/O Bottlenecks and Buffer Cache Pressure

To understand why the checkpointer was causing IO stalls, we inspected the active configuration using 'psql' and queried internal statistics views:

SQL

SELECT name, setting, unit, source FROM pg_settings 
WHERE name IN ('checkpoint_completion_target', 'max_wal_size', 'min_wal_size', 'shared_buffers');

The default 'checkpoint_completion_target' of '0.5' meant Postgres tried to complete writing all dirty buffers within fifty percent of the checkpoint interval. Under heavy writes, this forces a massive burst of disk writes at the end of every cycle. We also evaluated kernel-level dirty page throttling via 'sysctl'. The default Linux 'vm.dirty_background_ratio' and 'vm.dirty_ratio' values allowed too many pages to accumulate before forcing kernel flushes, leading to massive writeback storms when the kernel finally stepped in.

Parameter	Default Value	Tuned Production Value	Impact
'checkpoint_completion_target'	'0.5'	'0.9'	Spreads I/O load across 90% of the window
'max_wal_size'	'1GB'	'64GB'	Reduces checkpoint frequency during write peaks
'vm.dirty_background_ratio'	'10'	'3'	Starts kernel background writeback earlier
'vm.dirty_ratio'	'20'	'10'	Prevents application thread blocking on dirty pages

Remediation and Configuration Tuning

To flatten the I/O profile, we executed a multi-layered remediation plan involving both PostgreSQL configuration changes and operating system kernel tuning.

1. Adjusting Checkpoint Spread

By increasing 'checkpoint_completion_target' to '0.9', we forced Postgres to write dirty buffers more slowly and continuously throughout the checkpoint cycle, smoothing out the IOPS requirement.

INI

# postgresql.conf adjustments
checkpoint_timeout = 30min
max_wal_size = 64GB
min_wal_size = 4GB
checkpoint_completion_target = 0.9
checkpoint_warning = 30s

2. Linux Kernel Virtual Memory Tuning

We updated '/etc/sysctl.conf' to ensure the kernel flushes dirty pages incrementally rather than hoarding them into massive batches that choke the storage controller.

BASH

# Apply runtime kernel memory adjustments
sysctl -w vm.dirty_background_ratio=3
sysctl -w vm.dirty_ratio=10
sysctl -w vm.swappiness=1

3. Evaluating Synchronous Commit Trade-offs

For non-financial event logs and ephemeral session tables where durability could tolerate a sub-second window of data loss upon hard server crashes, we set 'synchronous_commit = off'. This decoupled client transaction confirmation from the physical 'fsync' to disk, boosting transaction throughput by over three hundred percent.

SQL

ALTER SYSTEM SET synchronous_commit = 'off';
SELECT pg_reload_conf();

Monitoring WAL Generation Rate

To prevent future surprises, we deployed Prometheus exporters tracking WAL generation velocity. You can monitor active WAL generation in real time using the following query:

SQL

SELECT 
  pg_wal_lsn_diff(pg_current_wal_lsn(), '0/00000000') AS total_wal_bytes,
  date_trunc('minute', now()) AS observation_time;

Keeping a close eye on WAL segment recycling ensures that your storage array never hits 100 percent capacity due to a blocked replication slot or an overly aggressive write spike.