WAL - Commit Hits Disk Before the Table Does

Day 10 said durability means “committed data survives crashes.” Day 23 said followers replay the leader’s write stream. Day 24 said UPDATE creates versions on the heap. Today is the log that makes all three true: the write-ahead log. Log first. Data pages later. Replay on crash.

You already know COMMIT is not free. Day 10 named the reason in one line: PostgreSQL writes a write-ahead log (WAL) and fsyncs it before it reports success. Today we open that black box — what order the bytes hit disk, what synchronous_commit actually trades, and when UNLOGGED tables are (and are not) allowed to skip the cost.

The problem: random page writes are a terrible crash diary

Suppose COMMIT meant “update every heap page and every index leaf in place, then return.” A crash mid-write leaves torn pages — half a row here, an index pointer there. Recovery would need to guess which pages finished. That is slow and fragile.

Databases invert the order:

1. Append a compact description of the change to a sequential log (the WAL).
2. Flush that log to durable storage (the line that makes COMMIT safe).
3. Only then (lazily, in the background) write dirty heap/index pages to their files.

If the process dies after step 2 but before step 3, recovery replays the log and rebuilds the missing page changes. The log is the source of truth for “what committed.” The data files catch up later.

The rule: log before data

Write-ahead means: a change must be on durable WAL before the corresponding data-page write is allowed to become the only copy of that change. PostgreSQL’s checkpointer and background writer flush dirty buffers over time; checkpoints push a consistent “everything before this LSN is on disk” mark. Between checkpoints, the WAL is what makes recent commits durable.

That is also what Day 23 ships to replicas: physical replication is largely shipping and applying WAL. The leader’s durability log is the follower’s catch-up stream.

-- Mental model (not exact internal API)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- dirty buffers in RAM
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- more dirty buffers
COMMIT;
-- 1) WAL records for both updates + commit record
-- 2) fsync WAL (when synchronous_commit = on)
-- 3) return success to client
-- 4) heap pages may still be dirty in shared buffers

synchronous_commit: when “success” is allowed to return

Default: synchronous_commit = on. COMMIT waits until the WAL flush for that transaction completes. Client sees success ⇒ crash cannot lose that commit (assuming fsync works).

synchronous_commit = off is asynchronous commit: PostgreSQL still writes WAL records, but may return success before the flush finishes. A crash in that window can drop recent “successful” transactions. You buy latency; you sell a short durability window.

This is not the same as fsync = off (which is a last-resort footgun that can corrupt the whole cluster). Async commit is a documented, scoped tradeoff for workloads that can lose a few recent commits.

UNLOGGED tables: skip heap WAL on purpose

A normal (LOGGED) table’s heap changes are WAL-logged. An UNLOGGED table is not: bulk inserts can advance almost no WAL for the heap. After an unclean shutdown, PostgreSQL truncates UNLOGGED tables. Use them for rebuildable caches, staging, or session scratch data — never for money, orders, or anything you cannot recompute.

CREATE UNLOGGED TABLE session_cache (
  key   text PRIMARY KEY,
  value jsonb NOT NULL
);
-- Fast writes. Empty after crash recovery.
-- Not a substitute for a durable cache with its own persistence story.

Worked example: crash before vs after WAL flush

Walk one INSERT through time. Assume default synchronous_commit=on and fsync=on.

Moment	WAL durable?	Heap page on disk?	After crash
Change in memory only	No	No	Lost (never committed)
WAL record written, not yet fsynced	No	Maybe dirty in cache	Lost — COMMIT has not returned
WAL fsynced · COMMIT returns	Yes	Often still not yet	Survives — recovery replays WAL onto heap
Background writer / checkpoint later	Yes	Yes	Survives (less redo needed)

Interview line: “COMMIT means the log is durable, not that every table page was fsynced.” If the process dies after COMMIT but before the heap write, PostgreSQL still has the diary entry and rebuilds the page on restart.

Decision table: three speed knobs (and one you never touch)

Knob	What changes	What you risk	Use when
synchronous_commit=on (default)	COMMIT waits for WAL flush	Higher commit latency	Money, inventory, identity — truth
synchronous_commit=off	Still logs; may return before flush	Recent “OK” commits can vanish on crash	Telemetry, disposable events, session overrides
UNLOGGED table	Skips heap WAL	Table truncated after unclean restart	Rebuildable caches / staging only
fsync=off	Skips durable flush discipline	Cluster corruption risk	Never in production

Async commit and UNLOGGED both look “~1.5×” in our lab. They are not the same product: one can lose recent commits on a LOGGED table; the other can wipe the whole table. Choose the risk you mean.

Checkpoints and full-page writes (why WAL volume spikes)

After a checkpoint, the first modification to a page often writes a full page image into WAL (full_page_writes, on by default) so recovery can repair torn pages. That is why bulk-load WAL volume is “tens of KB” and run-dependent — not a fixed per-row constant. Checkpoints also flush dirty pages so redo starts from a known base. You do not need to tune this on day one; you need to know why LSN jumps are noisy.

Benchmark — the price of waiting for WAL

On PostgreSQL 18.4 in our Podman lab (til-postgres), we measured 500 single-row commits per run (a procedure that COMMITs after each INSERT). 30-run medians:

Mode	Median (ms)	µs / commit	vs durable
LOGGED + synchronous_commit=on	3587.703	7175.4	1.0× baseline
LOGGED + synchronous_commit=off	2392.958	4785.9	1.50× faster
UNLOGGED table	2440.399	4880.8	1.47× faster

Same client path for every mode. Absolute milliseconds are machine-local (container disk, WSL). The durable claim is the ratio: waiting for WAL flush on every commit cost about 1.5× here.

Volume story (supporting): a single multi-row INSERT of 500 rows into a LOGGED table advanced on the order of tens of KB of WAL. The same shape into an UNLOGGED table advanced 0 bytes of heap WAL in a clean run. Async commit does not remove those LOGGED records — it only stops waiting for the flush before returning.

What breaks? — Anti-patterns

1. Setting fsync = off “for speed.” You are not tuning latency — you are risking unrecoverable corruption. Fix: leave fsync=on. Use async commit or batching if you need speed.

2. Using synchronous_commit=off for money paths. “COMMIT returned OK” can still vanish after a crash. Fix: keep default on for financial / inventory writes; scope off only to disposable telemetry.

3. Storing orders in UNLOGGED tables. Crash recovery empties them. Fix: UNLOGGED only for rebuildable data; LOGGED for truth.

4. One-row autocommit loops for bulk load. You pay the commit/WAL path hundreds of thousands of times. Fix: multi-row INSERT / COPY inside fewer transactions (Day 10: keep transactions short, but not pathologically tiny).

How it connects

• Day 10 (ACID / durability): WAL + fsync is the mechanism behind “committed means permanent.”
• Day 23 (replication): followers apply the leader’s WAL stream — same log, second purpose.
• Day 24 (MVCC): new row versions are heap changes; those changes are described in WAL so crash recovery and replicas see the same versions.
• Day 8 (pooling): each client commit still hits the WAL path; pooling connections does not remove fsync cost per transaction.
• Day 25 (length-prefix framing): WAL is also a sequential, append-only byte stream with framed records — same “describe length then payload” family of ideas at storage scale.

Transfer questions

1. Your analytics pipeline can lose the last few seconds of events on crash, but user checkout cannot. How would you set synchronous_commit (or session overrides) for each path without using fsync=off?
2. A replica is “behind.” Using Day 23 + today: what stream is it applying, and why does a long checkpoint or heavy WAL generation on the leader increase lag?
3. You switch a staging table to UNLOGGED and bulk load gets faster. After a kill -9 of PostgreSQL, the table is empty. Is that a bug? What did you trade?

What you should be able to do

• State the write-ahead rule: durable log record before relying on data-page writes alone.
• Explain why COMMIT latency includes a WAL flush under default settings.
• Contrast synchronous_commit=on vs off without confusing them with fsync=off.
• Name a correct use of UNLOGGED tables and one forbidden use.
• Connect WAL to replication (Day 23) and MVCC versions (Day 24).

Your teach step

Close this lesson. From memory, write “Explain like I’m 10” and a 60-second LinkedIn version. Hit: log first / data later, COMMIT waits for WAL flush by default, async commit and UNLOGGED are deliberate tradeoffs, replication ships the same log. Post it, paste the link.

Questions? Ask the agent — checkpoints, full_page_writes, or how this shows up in pg_stat_wal are fair game.