DEV Community

SoftwareDevs mvpfactory.io
SoftwareDevs mvpfactory.io

Posted on • Originally published at mvpfactory.io

PostgreSQL Write-Ahead Log Internals for High-Throughput Mobile Backends

#webdev #programming 
---
title: "PostgreSQL WAL Tuning for High-Throughput Mobile Backends"
published: true
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: postgresql, api, architecture, cloud
canonical_url: https://mvpfactory.co/blog/postgresql-wal-tuning-mobile-backends
---
Enter fullscreen mode Exit fullscreen mode

What We Are Tackling Today

By the end of this article you will know exactly which three PostgreSQL WAL parameters control your write throughput, how to read pg_stat_bgwriter to diagnose pressure before your users complain, and what a production-ready baseline configuration looks like for a mobile backend that survives burst traffic.

The defaults are tuned for safety on 2003 hardware. Let me show you the pattern I use in every project to get them right.

Prerequisites

  • A running PostgreSQL 14+ instance
  • superuser or pg_monitor role access
  • Basic familiarity with postgresql.conf
  • Ideally: a metrics pipeline (Prometheus, Datadog) to capture the before/after

Why Mobile Traffic Is the Hardest Case

Mobile traffic is pathologically bursty. A push notification lands, 50,000 devices wake up simultaneously, and your backend flushes sessions, writes events, and updates state — all in a 10-second window. That exact write pattern exposes checkpoint pressure, and checkpoint pressure manifests as latency spikes. Mobile users will not forgive latency spikes.

Most teams optimise the application layer obsessively and ignore the storage layer entirely. Let us fix that.

Step 1 — Stop Using 64KB wal_buffers in Production

The default wal_buffers = -1 auto-tunes to roughly 1/32nd of shared_buffers. On a typical 4GB shared_buffers setting that is 128MB. But many teams set the value explicitly and leave it at the historical default of 64KB. That is wrong for any serious workload.

Under burst writes, WAL buffer contention forces backends to wait for buffer space. You will 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
Enter fullscreen mode Exit fullscreen mode

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.

Step 2 — Spread Checkpoint I/O With checkpoint_completion_target

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

Setting Behaviour Risk
0.5 (default) 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 cost
1.0 Maximum spreading Checkpoint overlap on extreme workloads

This is 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
Enter fullscreen mode Exit fullscreen mode

Step 3 — Understand 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.

Here is the gotcha that will save you hours: never disable fsync. The data loss risk is not theoretical — the PostgreSQL team published warnings about cloud storage behaviours for exactly this reason. You can tune the sync method:

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
Enter fullscreen mode Exit fullscreen mode

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

Step 4 — Read pg_stat_bgwriter Like a Pro

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

SELECT
  checkpoints_req,        -- forced checkpoints: bad, means WAL filled up
  checkpoints_timed,      -- scheduled checkpoints: good
  buffers_checkpoint,
  buffers_clean,
  maxwritten_clean,       -- bgwriter throttle hits: indicates buffer pressure
  buffers_backend         -- backends writing directly: the worst case
FROM pg_stat_bgwriter;
Enter fullscreen mode Exit fullscreen mode

Reset counters before benchmarking a change:

SELECT pg_stat_reset_shared('bgwriter');
Enter fullscreen mode Exit fullscreen mode

Watch for checkpoints_req rising faster than checkpoints_timed, buffers_backend > 0 consistently, and maxwritten_clean spiking during your mobile traffic bursts. Each tells you something specific.

Production Baseline Configuration

Here is the minimal setup to get this working for a mobile write-optimised backend:

-- postgresql.conf
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
bgwriter_lru_maxpages = 200
bgwriter_delay = 50ms
Enter fullscreen mode Exit fullscreen mode

Gotchas

  • Do not trust auto-tuned wal_buffers in production. Verify with SHOW wal_buffers after every deploy that touches postgresql.conf.
  • checkpoints_req above 5% of total checkpoints during peak means your max_wal_size is too small, not that checkpoint_completion_target is wrong.
  • ZFS-specific: disabling full_page_writes is safe on ZFS with atomic writes. Everywhere else, leave it on.
  • The docs do not mention this, but buffers_backend > 0 consistently is a four-alarm fire — it means your background writer cannot keep pace with incoming writes and your backends are doing disk I/O directly.

Wrap-Up

Export buffers_backend and checkpoints_req to your metrics pipeline today. These two counters will surface WAL pressure before your mobile users ever notice the degradation. Tune wal_buffers first, spread checkpoint I/O with 0.9, then validate against pg_stat_bgwriter — in that order.

Further reading: PostgreSQL WAL configuration docs · pg_stat_bgwriter reference

Top comments (0)