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) — literally its byte offset in the log, printed as two hex halves, high/low. Because an LSN is a byte position, subtracting two of them gives the exact number of bytes of WAL a change produced. Insert 1,000 rows and weigh the log they wrote:

sql · live postgresrun me first — how much WAL do 1,000 inserts generate?

DROP TABLE IF EXISTS mark;
CREATE TABLE IF NOT EXISTS ledger (id int, amount int);
SELECT pg_current_wal_insert_lsn() AS lsn INTO TEMP mark;   -- remember the WAL position

INSERT INTO ledger SELECT g, g * 10 FROM generate_series(1, 1000) g;

SELECT pg_wal_lsn_diff(pg_current_wal_insert_lsn(), lsn)                 AS wal_bytes,   -- ~64,000
     pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_insert_lsn(), lsn)) AS human_size   -- ~63 kB
FROM mark;

DROP TABLE IF EXISTS mark;
CREATE TABLE IF NOT EXISTS ledger (id int, amount int);
SELECT pg_current_wal_insert_lsn() AS lsn INTO TEMP mark;   -- remember the WAL position

INSERT INTO ledger SELECT g, g * 10 FROM generate_series(1, 1000) g;

SELECT pg_wal_lsn_diff(pg_current_wal_insert_lsn(), lsn)                 AS wal_bytes,   -- ~64,000
     pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_insert_lsn(), lsn)) AS human_size   -- ~63 kB
FROM mark;

⌘/ctrl + enter

About 64 bytes per row — each insert's WAL record is a small header plus the new tuple. That log was written before the data pages changed on disk; the whole durability guarantee rides on it. The WAL is physically a series of 16 MB segment files, and an LSN names a position inside one of them:

sql · live postgreseditable — run it

SELECT pg_current_wal_lsn()                 AS lsn,            -- hex high/low
     pg_walfile_name(pg_current_wal_lsn()) AS segment_file;   -- the 16 MB file it's in

SELECT pg_current_wal_lsn()                 AS lsn,            -- hex high/low
     pg_walfile_name(pg_current_wal_lsn()) AS segment_file;   -- the 16 MB file it's in

⌘/ctrl + enter

Checkpoints bound recovery

If recovery had to replay the entire WAL from the beginning of time, crash startup would take forever. A checkpoint bounds it: a background checkpointer process flushes all dirty pages to the data files and records “everything up to this LSN is safely on disk.” Recovery then only replays WAL written after the last checkpoint. That's the core tension — the more often you checkpoint, the faster recovery but the more full-page writes you pay (below). Checkpoints fire on whichever comes first: a timer (checkpoint_timeout, default 5 min) or WAL volume (max_wal_size); to avoid an I/O spike, the writes are spread across the interval rather than dumped at once.

sql · live postgreseditable — run it

SHOW checkpoint_timeout;    -- time-based trigger (default 5min)
SHOW max_wal_size;          -- WAL-volume trigger (checkpoint sooner if exceeded)
SHOW wal_level;             -- minimal / replica / logical
CHECKPOINT;                 -- force one now (flush all dirty pages)

SHOW checkpoint_timeout;    -- time-based trigger (default 5min)
SHOW max_wal_size;          -- WAL-volume trigger (checkpoint sooner if exceeded)
SHOW wal_level;             -- minimal / replica / logical
CHECKPOINT;                 -- force one now (flush all dirty pages)

⌘/ctrl + enter

// gotcha · full-page writes: why one byte can cost 8 KB of WAL

The first time a page is modified after a checkpoint, Postgres writes the entire 8 KB page image to the WAL, not just the row that changed — so a one-byte UPDATE right after a checkpoint can add 8 KB to the log. Why? Disks don't write an 8 KB page atomically, so a crash mid-write can leave it torn (half-old, half-new) — a delta record couldn't repair that, but a full image can. This is a major reason WAL volume balloons, and exactly why checkpointing too often backfires: each checkpoint resets the clock, turning more writes back into expensive full-page writes.

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 has more than two settings

synchronous_commit is a per-transaction knob trading durability for latency — a ladder, not a switch:

on (default) — wait for the local WAL fsync (and, with synchronous replication, for a standby to confirm) before COMMIT returns. No committed transaction is ever lost.
local — flush locally but don't wait for any standby.
off — don't even wait for the local flush; COMMIT returns as soon as the record is in the WAL buffer, with a background flush moments later. A crash can lose the last fraction of a second of committed transactions — but the database is never corrupted, only slightly behind.

Because it's per-transaction, you can keep most work fully durable and set synchronous_commit = off only for the transactions that can tolerate it (bulk loads, regenerable logs).

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.
04An LSN is a byte offset in the log (hex high/low), so subtracting two LSNs measures WAL bytes — 1,000 inserts ≈ 64 KB (~64 B/row). The WAL is physically 16 MB segment files.
05Checkpoints (fired by checkpoint_timeout or max_wal_size) flush dirty pages and bound recovery to WAL-since-the-last-checkpoint; the writes are spread to avoid an I/O spike.
06The first change to a page after a checkpoint writes the whole 8 KB page image (a full-page write) to survive torn pages — so a one-byte update can cost 8 KB of WAL, and too-frequent checkpoints inflate the log.
07The same WAL drives streaming/logical replication and point-in-time recovery; wal_level (minimal/replica/logical) sets how much is logged.
08synchronous_commit is a per-transaction ladder (on / local / off): off returns before the local flush and can lose the newest commits on a crash, but never corrupts the database.

// 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