The Write Behind the Write
Every change in PostgreSQL goes to WAL first — the Write-Ahead Log.
Checkpoints are how PostgreSQL flushes those changes from memory to the actual data files. Done well, checkpoints happen quietly in the background and nobody notices. Done badly — too frequent, too large, happening all at once — and you get I/O spikes that crush performance across the entire server.
This script reads from pg_stat_bgwriter to show how often checkpoints are happening, whether they are being triggered on schedule or by pressure, and how much WAL is being generated.
CheckpointandWALHealth.sql
Loading…
Reading the Results
Checkpoint Stats (pg_stat_bgwriter)
| Column | What It Tells You |
|---|---|
checkpoints_timed | Checkpoints triggered by checkpoint_timeout — expected, healthy |
checkpoints_req | Checkpoints triggered by WAL volume or explicit request — a warning sign if high |
checkpoint_write_time | Total ms spent writing dirty buffers to disk during checkpoints |
checkpoint_sync_time | Total ms spent in fsync — high values indicate I/O subsystem pressure |
buffers_checkpoint | Buffers written by checkpoints |
buffers_clean | Buffers written by the background writer between checkpoints |
buffers_backend | Buffers written directly by backend processes — bad; means checkpoints can’t keep up |
maxwritten_clean | Times the bgwriter stopped because it hit bgwriter_lru_maxpages |
WAL Generation
| Column | What It Tells You |
|---|---|
wal_records | Total WAL records generated since last reset |
wal_bytes | Total WAL volume in bytes |
wal_buffers_full | Times WAL had to be written because the WAL buffer was full |
What to Watch For
| Signal | What It Means |
|---|---|
checkpoints_req > checkpoints_timed | WAL is filling up faster than the timeout fires — increase max_wal_size |
High buffers_backend | Backends are writing their own buffers — bgwriter cannot keep up with dirty pages |
High checkpoint_sync_time | Disk I/O is the bottleneck during fsync — storage performance issue |
maxwritten_clean climbing | bgwriter is hitting its rate limit — tune bgwriter_lru_maxpages |
wal_buffers_full > 0 | WAL buffer is too small — consider increasing wal_buffers |
| Short interval between checkpoints | Combined with high checkpoints_req, indicates write pressure needing tuning |
Quick Tuning Reference
| Parameter | Effect |
|---|---|
checkpoint_timeout | Time between scheduled checkpoints (default 5 min) — increase to reduce frequency |
max_wal_size | Max WAL size before a forced checkpoint — increase if checkpoints_req is high |
bgwriter_lru_maxpages | Max pages bgwriter writes per round — increase if maxwritten_clean is high |
wal_buffers | WAL buffer size in shared memory — increase if wal_buffers_full > 0 |
Gareth Winterman