Checkpoints, Background Writer, WAL Writer, Full-Page Writes, and I/O Spikes

Learning outcomes

After Chapter 11 tuning, ServiceHub query latency is healthy, but write latency still spikes periodically. The operating-system graphs show bursts of writes, and pg_stat_wal.wal_fpi rises quickly. The right question is not “Which background process is bad?” It is how PostgreSQL spreads dirty-page writes, where durability synchronization happens, and how checkpoint cadence changes both recovery distance and WAL volume.

1. Dirty buffers are not automatically a problem

When a backend changes a row, the corresponding page in shared buffers becomes dirty: memory contains a newer version than the table file. PostgreSQL deliberately does not synchronously write every dirty page at commit. WAL makes that unnecessary. Dirty pages can later be written by backends, the background writer, or the checkpointer, depending on pressure and timing.

SELECT * FROM pg_stat_bgwriter;SELECT  num_timed, num_requested, num_done,  write_time, sync_time, buffers_written, stats_resetFROM pg_stat_checkpointer;

These views are cumulative. A large lifetime number does not diagnose the current incident without a time window and workload context.

2. A checkpoint defines where crash REDO can begin

At a checkpoint, PostgreSQL ensures that data files contain the effects of WAL records preceding the checkpoint's redo horizon, then records checkpoint information in WAL. After a crash, recovery can start from the redo location associated with the most recent valid checkpoint instead of replaying the entire history of the cluster.

SELECT name, setting, unit, context, sourceFROM pg_settingsWHERE name IN (  'checkpoint_timeout','checkpoint_completion_target',  'checkpoint_warning','checkpoint_flush_after',  'max_wal_size','min_wal_size','full_page_writes')ORDER BY name;

checkpoint_timeout and WAL volume can trigger automatic checkpoints. max_wal_size is a soft checkpoint-oriented limit, not a hard WAL-directory quota. checkpoint_completion_target tells PostgreSQL to spread checkpoint writing across a fraction of the expected checkpoint interval; reducing it tends to concentrate I/O rather than make the work disappear.

The distinction between num_timed and num_requested in pg_stat_checkpointer helps explain why checkpoints happen. Timed checkpoints are driven by the timeout; requested checkpoints include explicit requests and WAL-pressure-driven requests. PostgreSQL can also skip a checkpoint if the server has been idle, so compare num_done with requested/timed counts instead of assuming every request performed work.

Checkpoint frequency is therefore a three-way engineering tradeoff: more frequent checkpoints can shorten crash REDO distance, but may increase full-page-image churn and synchronization work; less frequent checkpoints can smooth normal operation but need more WAL capacity and can lengthen recovery. The acceptable balance comes from recovery objectives and measured I/O, not a universal interval.

3. Full-page images protect against torn page writes

With full_page_writes=on, PostgreSQL WAL-logs a full image of a data page the first time that page is modified after a checkpoint. If a crash interrupts a page write, replay can restore the complete page image before applying later row-level WAL records. Subsequent modifications of the same page before the next checkpoint normally do not need another first-change full-page image.

SELECT wal_records, wal_fpi, wal_bytes, stats_resetFROM pg_stat_wal;

Do not expect a particular FPI count from a SQL statement. The exact page set depends on table layout, indexes, concurrent writes, hint bits, checksums, and prior checkpoint/page state.

4. Controlled experiment: checkpoint, update, observe

This experiment requires superuser or membership in pg_checkpoint because the SQL CHECKPOINT command is privileged. Run it only on the disposable ServiceHub lab; PostgreSQL explicitly does not intend forced checkpoints as normal operating practice.

DROP TABLE IF EXISTS app.ch12_checkpoint_lab;CREATE TABLE app.ch12_checkpoint_lab (    id bigint PRIMARY KEY,    status text NOT NULL,    payload text NOT NULL) WITH (fillfactor = 90);INSERT INTO app.ch12_checkpoint_labSELECT g, 'queued', repeat(md5(g::text), 20)FROM generate_series(1, 30000) AS g;ANALYZE app.ch12_checkpoint_lab;

CHECKPOINT;SELECT pg_current_wal_insert_lsn() AS first_checkpoint_lsn \gsetSELECT wal_fpi AS fpi_before, wal_bytes AS bytes_before FROM pg_stat_wal \gset

UPDATE app.ch12_checkpoint_labSET status = 'assigned'WHERE id % 3 = 0;

SELECT  wal_fpi - :'fpi_before'::bigint AS fpi_delta,  wal_bytes - :'bytes_before'::numeric AS wal_bytes_delta,  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_insert_lsn(), :'first_checkpoint_lsn'::pg_lsn)) AS lsn_distanceFROM pg_stat_wal;

You should usually observe positive WAL and FPI deltas, but the exact counts are not acceptance criteria. The mechanism is the lesson: a fresh checkpoint re-arms full-page-image protection for the first later modification of each page.

5. Why overly frequent checkpoints can amplify I/O and WAL

If checkpoints occur very frequently, more pages cross a new checkpoint boundary and become candidates for another full-page image on their next modification. Frequent checkpoints can therefore increase WAL volume. They also force dirty-buffer write/sync work more often, which can concentrate storage pressure if completion is not spread effectively.

SELECT  num_timed, num_requested, num_done,  write_time, sync_time, buffers_written, stats_resetFROM pg_stat_checkpointer;SELECT wal_records, wal_fpi, wal_bytes, wal_buffers_full, stats_resetFROM pg_stat_wal;

6. Background writer and WAL writer are different mechanisms

The background writer writes dirty shared buffers mainly to keep clean buffers available and reduce the chance that client backends must do their own writes. It does not replace checkpoint synchronization. The WAL writer handles WAL-buffer write/flush activity on its own cadence and is especially relevant when transactions use asynchronous commit. Do not infer data-page durability from background-writer counters alone.

SELECT name, setting, unit, contextFROM pg_settingsWHERE name IN (  'bgwriter_delay','bgwriter_lru_maxpages','bgwriter_lru_multiplier',  'wal_writer_delay','wal_writer_flush_after')ORDER BY name;

7. Optional I/O timing evidence

PostgreSQL 18 exposes I/O accounting in pg_stat_io. Timing columns are meaningful only when the corresponding timing collection has been enabled for the whole measurement window. Treat them as evidence, not as retroactive measurements.

SELECT backend_type, object, context, reads, writes, write_time, fsyncs, fsync_timeFROM pg_stat_ioWHERE backend_type IN ('checkpointer','background writer','client backend')   OR object = 'wal'ORDER BY backend_type, object, context;

8. Production judgment and cleanup

A production checkpoint review should compare checkpoint frequency, checkpoint reason, write/sync time, buffers written, WAL FPI rate, WAL bytes, storage latency, client-backend writes, and recovery objectives over the same interval. Tune only after identifying the limiting mechanism. Raising max_wal_size may reduce checkpoint frequency but can increase recovery distance and disk requirements; it is not a free performance switch.

DROP TABLE IF EXISTS app.ch12_checkpoint_lab;

Authoritative references

Durability behavior depends on PostgreSQL version, storage semantics, configuration source, replication topology, and the exact failure mode. The references below are the primary source for the mechanisms used in this lesson.

• PostgreSQL 18 — WAL Configuration
• PostgreSQL 18 — Write Ahead Log Configuration Parameters
• PostgreSQL 18 — Resource Consumption / Background Writer
• PostgreSQL 18 — pg_stat_bgwriter, pg_stat_checkpointer, pg_stat_wal, pg_stat_io
• PostgreSQL 18 — CHECKPOINT