postgres://internals

WAL & Durability

// the problem

When a transaction COMMITs, Postgres promises the change will survive a power cut — even though the modified page is still sitting in memory, not yet written to the table's file. How can it promise durability without flushing your data? The answer is the Write-Ahead Log: before anything is considered committed, a compact record of the change is appended to a sequential log and fsync'd to disk. The data pages catch up later, at their leisure.

Step through it below. Write a row (the buffer goes dirty and a WAL record appears), Commit (the WAL record becomes durable — but the data file still hasn't changed), then pull the plug with Crash and Recover.

fig.01 · write-ahead logdurability = WAL fsync'd at commit

lsn

durable

checkpoint

dirty

status

running

buffer · in memory

volatile — lost on crash

row 1100

row 2200

wal · on disk

sequential, fsync'd at commit

empty

data files · on disk

flushed lazily at checkpoint

row 1100

row 2200

write → dirty buffer + WAL record · commit → WAL fsync'd (durable, page still dirty) · crash → memory gone · recover → replay committed WAL

Watch what survives a crash. The buffer (memory) is wiped, so any un-flushed page is gone — but the WAL is on disk. Recovery replays the committed WAL records and reconstructs exactly the committed state, even for changes that were never written to the data files. Uncommitted writes are left behind: their WAL records may exist, but with no commit record the transaction is treated as aborted, so recovery never makes them visible (VACUUM cleans them up later).

The rule: log first, then write

Every change follows the same order:

Modify the page in the buffer (it's now dirty — differs from disk).
Append a WAL record describing the change to the log.
On COMMIT, fsync the WAL up to this point. Now it's durable.
Sometime later, a background process flushes the dirty page to the data file.

The critical invariant — “write-ahead” — is that the WAL record hits durable storage before the data page does. That's what lets recovery always rebuild a consistent state.

// why it matters · why this is fast

One COMMIT = one small sequential append + fsync to the WAL, instead of random writes scattered across the table's pages. Sequential I/O is far cheaper, and many transactions' data-page writes get batched and written once, later. Durability and speed.

Every WAL record has an LSN (Log Sequence Number) — its byte position in the log. You can watch the log grow as you write:

sql · live postgresrun me first — watch the LSN advance

SELECT pg_current_wal_lsn() AS lsn_before;

CREATE TABLE IF NOT EXISTS ledger (id int, amount int);
INSERT INTO ledger SELECT g, g * 10 FROM generate_series(1, 1000) g;

SELECT pg_current_wal_lsn() AS lsn_after;

SELECT pg_current_wal_lsn() AS lsn_before;

CREATE TABLE IF NOT EXISTS ledger (id int, amount int);
INSERT INTO ledger SELECT g, g * 10 FROM generate_series(1, 1000) g;

SELECT pg_current_wal_lsn() AS lsn_after;

⌘/ctrl + enter

The LSN advanced — those inserts were logged to the WAL before they were durable anywhere else.

Checkpoints bound recovery

If recovery had to replay the entire WAL from the beginning of time, startup after a crash would take forever. A checkpoint fixes that: it flushes all dirty pages to the data files and records “everything up to this LSN is safely on disk.” Recovery then only has to replay WAL after the last checkpoint. Checkpoints happen on a timer and on WAL volume.

sql · live postgreseditable — run it

SHOW wal_level;             -- minimal / replica / logical
SHOW synchronous_commit;    -- on = fsync WAL before returning from COMMIT
CHECKPOINT;                 -- force one now (flush dirty pages)

SHOW wal_level;             -- minimal / replica / logical
SHOW synchronous_commit;    -- on = fsync WAL before returning from COMMIT
CHECKPOINT;                 -- force one now (flush dirty pages)

⌘/ctrl + enter

// gotcha · full-page writes and torn pages

The first time a page is modified after a checkpoint, Postgres writes the entire page image to the WAL, not just the change. This protects against torn pages — a crash mid-write leaving a page half-old, half-new (an 8 KB page isn't written atomically by most disks). It's a real chunk of WAL volume, and why checkpoints that are too frequent hurt.

The same log powers replication and PITR

Because the WAL is a complete, ordered record of every change, it's the foundation for far more than crash recovery:

Streaming replication — ship the WAL to a standby server and replay it there to keep a live, up-to-date replica (read replicas, high availability, failover).
Point-in-Time Recovery (PITR) — archive the WAL, and you can restore a base backup and replay the log to any moment, e.g. just before a bad DELETE.
Logical replication — decode the WAL into row-level changes to feed other systems (the basis of change-data-capture and tools built on it).

wal_level controls how much detail is logged: replica for physical replication, logical to also allow logical decoding.

// why it matters · the durability/latency dial

synchronous_commit trades durability for latency. on (default) waits for the WAL fsync before COMMIT returns — no committed transaction is ever lost. Set it off and commits return sooner but a crash can lose the last fraction of a second of committed transactions (the data stays consistent — you just lose the very newest commits). It's a per-transaction knob for workloads that can tolerate that.

Your turn

// challengewrite it yourself

Show the current write position in the WAL (its LSN).

SELECT pg_current_wal_lsn() AS lsn;

SELECT pg_current_wal_lsn() AS lsn;

⌘/ctrl + enter

// challengewrite it yourself

Is Postgres configured to fsync the WAL before COMMIT returns? Show the setting that controls it.

SHOW synchronous_commit;

SHOW synchronous_commit;

⌘/ctrl + enter

// what you now understand

01Durability comes from the WAL: a change is logged and fsync'd at COMMIT before the data page is flushed — write-ahead means the log hits disk first.
02A COMMIT is one small sequential WAL append + fsync, not random data-page writes — durable and fast.
03On a crash the buffer is lost but the WAL survives; recovery replays committed WAL records to rebuild state, and drops uncommitted ones.
04Each WAL record has an LSN (byte position); checkpoints flush dirty pages and bound how much WAL recovery must replay.
05Full-page writes after a checkpoint protect against torn pages, at the cost of WAL volume.
06The same WAL drives streaming/logical replication and point-in-time recovery; wal_level sets how much is logged.
07synchronous_commit dials durability vs latency — off returns faster but can lose the newest commits on a crash.

// self-test

A transaction COMMITs, then the server loses power one millisecond later — before the modified page was ever written to the table's file. Is the change lost?

// self-test

Why does Postgres take checkpoints instead of just keeping all WAL forever?

// go deeper

Write-Ahead Logging (WAL) — the durability guarantee and how it works
WAL Configuration — checkpoints, full-page writes, synchronous_commit
Streaming Replication — shipping WAL to standbys
Continuous Archiving & PITR — restore to any point in time

nextThe Buffer Pool