PostgreSQL Mastery lessons / Module 25: WAL and checkpoints

WAL, checkpoints and crash recovery

Level 5: Internals25 min

Every change is first written to the write-ahead log. Data pages are flushed later, in the background, and after a crash PostgreSQL replays WAL from the last checkpoint. The same WAL stream powers replication and point-in-time recovery.

This lesson is interactive. Run every example on a real PostgreSQL 18 server in your browser, read EXPLAIN ANALYZE plans, and take the quiz.

Open the interactive lesson

You will learn to

Trace a write from shared_buffers to WAL to the data files
Explain LSNs, checkpoints, full-page writes and synchronous_commit
Measure WAL volume and tune checkpoints

conceptThe concept

Open the interactive lesson to use this diagram.

Each WAL record has a log sequence number (LSN), a byte position in the WAL stream such as 0/2507E90. WAL is written to 16 MB segment files in pg_wal. A checkpoint flushes all dirty pages to the data files and records a point from which recovery can start; WAL before it is no longer needed for crash recovery (but may be kept for replicas or archiving).

Checkpoints happen every checkpoint_timeout (5 min) or when max_wal_size (1 GB) of WAL accumulates, whichever comes first. After a checkpoint, the first change to each page writes the whole page into WAL (full-page write) to protect against torn pages, so very frequent checkpoints inflate WAL volume.

synchronous_commit = off lets COMMIT return before the WAL flush: a crash can lose the last few hundred milliseconds of commits but never corrupts data. It is a per-transaction choice, useful for low-value writes such as logs.

whyWhy it matters

WAL is the heart of durability, replication and backups. Checkpoint tuning affects write latency spikes, WAL volume, recovery time and replication bandwidth.

Use caseReal-world scenario

A write-heavy system shows "checkpoints are occurring too frequently" in the log and WAL volume of 2 TB per day. Raising max_wal_size to 16 GB and checkpoint_timeout to 15 min cuts full-page writes and halves the WAL.

Try itExamples

Run and edit these in the interactive lesson.

Where is the WAL right now?

SELECT pg_current_wal_lsn() AS lsn,
       pg_walfile_name(pg_current_wal_lsn()) AS segment_file;
SHOW wal_level;
SHOW max_wal_size;
SHOW checkpoint_timeout;
SHOW synchronous_commit;

How much WAL does a change generate?

BEGIN;
CREATE TEMP TABLE wal_mark ON COMMIT DROP AS SELECT pg_current_wal_lsn() AS lsn;
UPDATE orders SET total = total WHERE id <= 2000;
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), lsn)) AS wal_for_2000_updates FROM wal_mark;
ROLLBACK;

Updating 2,000 rows to the same value still writes new tuple versions, and the first touch of each page after a checkpoint logs the full page.

Cumulative WAL statistics

SELECT wal_records, wal_fpi AS full_page_images, pg_size_pretty(wal_bytes) AS wal_bytes
FROM pg_stat_wal;

Unlogged tables skip WAL

BEGIN;
CREATE UNLOGGED TABLE scratch (id int, v text);
CREATE TEMP TABLE wal_mark ON COMMIT DROP AS SELECT pg_current_wal_lsn() AS lsn;
INSERT INTO scratch SELECT g, md5(g::text) FROM generate_series(1, 5000) g;
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), lsn)) AS wal_for_5000_unlogged_rows FROM wal_mark;
ROLLBACK;

internalsUnder the hood

wal_level decides how much is logged: minimal (crash recovery only), replica (default, enough for physical replication and archiving) or logical (adds information for logical decoding). Unlogged tables skip WAL entirely: fast, but truncated after a crash and not replicated.

Avoid / preferDo this, not that

Avoid

max_wal_size = 256MB  # on a write-heavy server

Prefer

max_wal_size = 16GB
checkpoint_timeout = 15min

A small max_wal_size forces frequent checkpoints, and each one triggers full-page images for every page touched afterwards, multiplying WAL volume.

Avoid

fsync = off  # it is so much faster

Prefer

synchronous_commit = off  # per transaction, for data you can afford to lose

fsync off can corrupt the whole cluster after a crash. synchronous_commit off only risks losing the last moments of commits.

PitfallsCommon mistakes

Setting fsync = off in production for speed. A crash can corrupt the whole cluster; synchronous_commit = off is the safe trade-off.
Leaving max_wal_size at 1 GB on a busy server, so checkpoints run every minute and full-page writes dominate WAL.
Assuming WAL disk usage is bounded by max_wal_size. Replication slots and failing archiving keep WAL indefinitely.

LimitsWhen not to use it

Do not use unlogged tables for anything you cannot rebuild: they are truncated after a crash and are not replicated to standbys.

SecuritySecurity notes

WAL contains every change, including sensitive values. Encrypt archives, restrict replication roles, and remember that logical decoding exposes row data to whoever can read a replication slot.

ChallengeTry it yourself

Measure how much WAL an UPDATE of 1,000 orders generates, inside a transaction you roll back.

Show one solution

One solution

BEGIN;
CREATE TEMP TABLE wal_start AS SELECT pg_current_wal_insert_lsn() AS lsn;
UPDATE orders SET total = total WHERE id <= 1000;
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_insert_lsn(), (SELECT lsn FROM wal_start))) AS wal_generated;
ROLLBACK;

InterviewQuestions you should be able to answer

What is the WAL and why does PostgreSQL need it?

The write-ahead log records every change before the changed data pages are written. At commit only the WAL must be flushed (sequential I/O), while dirty pages are written later by the background writer and checkpointer. After a crash, recovery replays WAL from the last checkpoint to bring data files to a consistent state. The same stream feeds streaming replication, WAL archiving for PITR, and logical decoding.

What happens at a checkpoint and how do you tune them?

All dirty buffers are written to the data files and fsynced, and a checkpoint record is written; crash recovery can start from there. Checkpoints are triggered by checkpoint_timeout or max_wal_size. Too frequent checkpoints cause I/O spikes and many full-page images in WAL; too rare ones lengthen crash recovery. Typical tuning: raise max_wal_size so most checkpoints are time-triggered, set checkpoint_timeout to 10-30 min, and keep checkpoint_completion_target at 0.9 to spread the writes.

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

← Locks, deadlocks and safe DDL Memory and the settings that matter →