PostgreSQL WAL tuning for high-throughput mobile backends

Meta description: Deep dive into WAL internals: tune checkpoint_completion_target, wal_buffers, and fsync to eliminate latency spikes in your mobile API under burst traffic. Backed by real metrics.

Tags: backend api architecture microservices cloud

---

TL;DR

Most teams treat PostgreSQL WAL configuration as a black box until latency spikes kill their mobile app’s UX. The defaults are tuned for safety on 2003 hardware, not for modern NVMe-backed cloud instances handling thousands of concurrent mobile writes. Fix wal_buffers, raise checkpoint_completion_target, understand full_page_writes, and watch pg_stat_bgwriter — in that order.

---

Why mobile backends make PostgreSQL sweat

Mobile traffic is pathologically bursty. A push notification lands, 50,000 devices wake up and hammer your API simultaneously. Your backend flushes sessions, writes events, and updates state — all in a 10-second window. That’s exactly the write pattern that exposes checkpoint pressure, and checkpoint pressure manifests as the one thing mobile users will not forgive: latency spikes.

Most teams optimize the application layer obsessively and ignore the storage layer entirely.

---

The WAL pipeline you need to understand

PostgreSQL writes changes to the Write-Ahead Log before touching actual data pages. This guarantees durability. But three variables control how expensive that guarantee is at scale:

1. WAL buffer saturation — how fast dirty WAL pages flush to disk
2. Checkpoint frequency and spread — how aggressively PostgreSQL syncs data pages
3. fsync strategy — what “durable write” actually means on your hardware

Each gets its own section below.

---

wal_buffers: Stop using 64KB in production

The default wal_buffers = -1 auto-tunes to ~1/32nd of shared_buffers, which on a typical 4GB shared_buffers setting gives you 128MB. But many teams set this explicitly and leave it at the historical default of 64KB. That’s wrong for any serious workload.

Under burst writes, WAL buffer contention forces backends to wait for buffer space. You’ll see this as elevated LWLock:WALBufMapping wait events in pg_stat_activity.

-- postgresql.conf
wal_buffers = 64MB   -- explicit, predictable, covers ~1s of heavy write throughput

The math is unambiguous. On a workload generating 200MB/s of WAL, a 64KB buffer drains in 0.3ms. A 64MB buffer gives your I/O subsystem room to breathe across a realistic write burst.

---

checkpoint_completion_target: Spread the pain

PostgreSQL checkpoints sync all dirty shared buffers to disk. At default checkpoint_completion_target = 0.5, it tries to complete that work in 50% of the checkpoint interval — creating a concentrated I/O burst precisely when your mobile API needs consistent latency.

Setting	Behavior	Risk
0.5 (default)	Checkpoint I/O concentrated in first half	Latency spikes under burst writes
0.9 (recommended)	I/O spread across 90% of interval	Smoother throughput, marginal recovery time cost
1.0	Maximum spreading	Can cause checkpoint overlap on extreme workloads

Set this to 0.9. It’s the single highest-impact WAL tuning change for most mobile backends.

checkpoint_completion_target = 0.9
checkpoint_timeout = 10min   -- give the spreader room to work
max_wal_size = 4GB           -- scale with your burst write volume

---

full_page_writes and your fsync strategy

full_page_writes = on (the default) causes PostgreSQL to write entire 8KB pages on first modification after a checkpoint. This protects against partial page writes if the OS crashes mid-write. On systems with atomic sector writes (most NVMe drives) this doubles write amplification.

Never disable fsync. The data loss risk isn’t theoretical — it’s why the PostgreSQL team published CVE-2018-1058-adjacent warnings about cloud storage behaviors. You can tune the fsync method, though:

fsync = on                    -- non-negotiable
wal_sync_method = fdatasync   -- avoids unnecessary metadata flushes on Linux
full_page_writes = on         -- keep this unless you have battery-backed write cache

On ZFS or similar filesystems with atomic writes, disabling full_page_writes is safe and measurably reduces write amplification.

---

Reading pg_stat_bgwriter like a pro

This is your ground truth. Query it before and after tuning:

SELECT
  checkpoints_req,        -- forced checkpoints: bad, means WAL filled up
  checkpoints_timed,      -- scheduled checkpoints: good
  buffers_checkpoint,     -- pages written during checkpoints
  buffers_clean,          -- background writer activity
  maxwritten_clean,       -- bgwriter throttle hits: indicates buffer pressure
  buffers_backend         -- backends writing directly: the worst case
FROM pg_stat_bgwriter;

Watch for:

• checkpoints_req rising faster than checkpoints_timed → increase max_wal_size
• buffers_backend > 0 consistently → your bgwriter is falling behind; tune bgwriter_lru_maxpages
• maxwritten_clean spiking during mobile traffic bursts → your buffer pool is too small for the write pattern

Reset the counters with SELECT pg_stat_reset_shared('bgwriter') before benchmarking a configuration change.

---

Production configuration baseline

-- postgresql.conf — mobile backend write-optimized baseline
wal_buffers = 64MB
checkpoint_completion_target = 0.9
checkpoint_timeout = 10min
max_wal_size = 4GB
min_wal_size = 1GB
wal_sync_method = fdatasync
synchronous_commit = on       -- local; adjust only if you have replicas
bgwriter_lru_maxpages = 200   -- more aggressive background writing
bgwriter_delay = 50ms

---

3 actionable takeaways

1. Set wal_buffers = 64MB explicitly. Don’t rely on auto-tuning in production. Verify with SHOW wal_buffers and watch for WALBufMapping lock waits to confirm the change helped.

2. Raise checkpoint_completion_target to 0.9 and monitor checkpoints_req. If required checkpoints exceed 5% of total checkpoints during your peak traffic window, also increase max_wal_size until the ratio normalizes.

3. Instrument pg_stat_bgwriter in your metrics pipeline. Export buffers_backend and checkpoints_req to Prometheus or Datadog. These two counters will alert you to WAL pressure before your mobile users ever notice the latency degradation.