PostgreSQL background writer and checkpoint IO spikes after increasing shared_buffers on primary

Environment

PostgreSQL 13 on Debian 11, 48 GB RAM, NVMe RAID on controller with battery-backed write cache; workload: OLTP with peak 2000 TPS; changed shared_buffers from 4GB to 16GB and restarted PostgreSQL at 02:00 during low traffic

Symptoms

After the change, periodic IO spikes observed: write latency climbs to >50ms and CPU kernel time increases during checkpoints; application experiences transient slow queries and timeouts during bursts; spikes every 5–10 minutes correlate with bgwriter/checkpoint activity

Observations

postgresql.conf: shared_buffers=16GB, checkpoint_timeout=5min, max_wal_size=1GB (default prior). vmstat/iostat show large synchronous write bursts at checkpoint times; pg_stat_bgwriter shows increased checkpoints_timed and buffers_backend_wal writes; pg_wal usage grows then flushes; autovacuum appears unaffected. No OS-level OOMs. Disk write cache setting is enabled on RAID controller. WAL fsync reports occasional 'write' latency in pg_stat_activity timings.

Already attempted

Rolled shared_buffers back to 8GB in a test instance — write spikes reduced but not eliminated; increased max_wal_size to 4GB on a dev replica — checkpoint frequency reduced and IO spikes smoothed in replica; ensured commit_delay/commit_siblings left at defaults; upgraded PostgreSQL minor version — no change.

Question

Why did increasing shared_buffers to 16GB cause larger, more disruptive checkpoint IO bursts, and what server-side tunables and storage checks will reduce write latency and checkpoint impact without rolling back shared_buffers entirely?

Post timeline

COMMUNITY RESPONSES

Verified community troubleshooting

0 replies

No answers yet..

Showing 0 of 0 community responses

AI DIAGNOSTICS

Incident analysis

Updated 1w ago• Trouble ID

Most likely causes

Inadequate max_wal_size leading to forced checkpoints during buffer pressure 95%

Checkpoint frequency too high relative to max_wal_size causing full buffer flushes 80%

bgwriter settings (bgwriter_lru_maxpages, bgwriter_lru_multiplier) not scaled for larger shared_buffers 75%

Summary

Increasing shared_buffers increases dirty shared buffer accumulation, which can cause more data to be written out during checkpoints if checkpoint-related settings (max_wal_size, checkpoint_timeout, bgwriter settings) are not tuned. With small max_wal_size and frequent timed checkpoints, larger buffers lead to heavier flush operations. The storage subsystem and its write cache behavior (battery-backed or write-back) also affect perceived latency. Balancing shared_buffers with checkpoint tuning and ensuring WAL sizing and bgwriter settings are appropriate reduces IO bursts.

Suggested Fixes

Diagnostic step 1

Increase max_wal_size (e.g., to 4–8GB) and adjust checkpoint_timeout to reduce forced checkpoint frequency. Make changes in postgresql.conf and reload/restart in maintenance window. Monitor pg_stat_bgwriter for checkpoint activity.

Diagnostic step 2

Tune bgwriter parameters: increase bgwriter_lru_maxpages and bgwriter_lru_multiplier so background writer spreads out writes. Test incrementally and monitor iostat/pg_stat_bgwriter.

Diagnostic step 3

Verify storage: check RAID controller cache policy (write-back with battery or capacitor), battery health, and firmware. If controller flushes are slow, involve storage admin; collect iostat with finer interval during checkpoint to correlate.