Lesson 0009 · Durability
Postgres promises your commit survives a crash without flushing every data page — by writing the change to a log first. One log, redo-only, and the reason InnoDB needs two where Postgres needs one.
Closed book. The second one is the hinge for today's big contrast.
Postgres picks a Seq Scan over your index. The most accurate framing is:
Where does Postgres keep the old version of a row after an UPDATE?
Here's the problem durability has to solve. A committed transaction must survive a crash — but its changes might only be sitting in dirty pages in memory, not yet on disk. The naive fix (flush every changed data page at every commit) would be brutally slow: those pages are scattered, so it's random I/O on the hot path of every write.
Write-Ahead Logging flips the order. Before a data page may be written to disk, the small log record describing that change must be flushed first. — PostgreSQL Docs: "WAL's central concept is that changes to data files… must be written only after those changes have been logged, that is, after WAL records describing the changes have been flushed to permanent storage."
Why that helps: the WAL is one sequential file. At commit, you flush only the WAL — not the scattered data pages — and you're safe. — PostgreSQL Docs: "Using WAL results in a significantly reduced number of disk writes, because only the WAL file needs to be flushed to disk to guarantee that a transaction is committed, rather than every data file changed by the transaction." A sequential append to one file beats random writes to many — the same trade every durable database makes.
After a crash, Postgres replays the WAL to re-apply any committed change that hadn't reached the data files yet. — PostgreSQL Docs: "any changes that have not been applied to the data pages can be redone from the WAL records. (This is roll-forward recovery, also known as REDO.)" You met this exact mechanism as InnoDB's redo log in MySQL lesson 0007 — the WAL is Postgres's redo log.
If recovery replayed all WAL ever written, restart would take forever and WAL would grow without bound. A checkpoint fixes both: periodically Postgres flushes all dirty data pages to disk and writes a checkpoint record. — PostgreSQL Docs: "Checkpoints are points in the sequence of transactions at which it is guaranteed that the heap and index data files have been updated with all information written before that checkpoint. At checkpoint time, all dirty data pages are flushed to disk…"
Two consequences follow directly. Recovery only needs to replay WAL from the last checkpoint: — PostgreSQL Docs: "the crash recovery procedure looks at the latest checkpoint record to determine the point in the WAL (known as the redo record) from which it should start the REDO operation." And WAL older than that can be thrown away: — PostgreSQL Docs: "after a checkpoint, WAL segments preceding the one containing the redo record are no longer needed and can be recycled or removed."
Frequent checkpoints → short crash recovery and less WAL to keep, but more constant data-page I/O (and I/O spikes as dirty pages are flushed). Infrequent checkpoints → cheaper steady-state, but a longer replay after a crash and more WAL retained. Tuning checkpoint frequency is trading recovery time against runtime write overhead — the same dial you saw on the InnoDB side.
By default, COMMIT waits for the WAL to be flushed to disk before it returns —
full durability.
— PostgreSQL Docs: "The local behavior of all non-off modes is to wait for local flush of WAL to disk."
Turn synchronous_commit = off and commit returns before the flush — much
faster under write load, at a price. And here is the subtle, important part:
PostgreSQL Docs: "setting this parameter to off does not create any risk of database inconsistency: an operating system or database crash might result in some recent allegedly-committed transactions being lost, but the database state will be just the same as if those transactions had been aborted cleanly."
So synchronous_commit=off risks a small window of recent commits
on a crash, but the database stays consistent — unlike turning off fsync,
which really can corrupt. It's the Postgres analogue of InnoDB's
innodb_flush_log_at_trx_commit dial from
MySQL 0007: trade a sliver of
durability for throughput, deliberately.
This is where the whole course clicks together. InnoDB keeps two logs: a redo log (roll forward for durability) and an undo log (roll back a transaction, and serve old row versions for MVCC). Postgres has only the WAL — pure redo. It needs no undo log because, from the very first lesson, old row versions live in the heap (never update in place), and VACUUM — not an undo log — cleans them up.
| Job | InnoDB | PostgreSQL |
|---|---|---|
| Roll forward after crash (durability) | Redo log | WAL (redo) |
| Old row versions for MVCC | Undo log | The heap (dead tuples) |
| Undo an aborted transaction | Undo log | Mark tuples not-visible; heap cleanup |
| Reclaim old versions | Purge threads | VACUUM / autovacuum |
| Flush-at-commit dial | innodb_flush_log_at_trx_commit | synchronous_commit |
So the choice you learned in lesson 0001 — keep versions in the heap — is exactly what lets Postgres drop the undo log. One design decision, echoing all the way down to the durability layer.
From memory. Two items reach back on purpose.
WAL's central rule is:
At commit, the only thing that must be flushed to disk is:
A checkpoint lets crash recovery:
synchronous_commit = off risks:
Postgres needs no undo log (unlike InnoDB) because: (recall 0001)
A lab to run later — peek at the WAL and force a checkpoint:
SHOW wal_level; -- replica (default): enough for crash recovery + streaming
SHOW synchronous_commit; -- on by default (full durability)
SELECT pg_current_wal_lsn(); -- current write position in the WAL
INSERT INTO some_table DEFAULT VALUES; -- generate some WAL
SELECT pg_current_wal_lsn(); -- the LSN advanced
CHECKPOINT; -- force dirty pages to disk now (superuser)Watch the LSN move as you write, and note that CHECKPOINT is what lets old WAL
be recycled. Bring anything surprising to your teacher.
PostgreSQL Docs — 28.4 Write-Ahead Logging for the core idea, then 28.5 WAL Configuration for checkpoints and tuning. For the byte-level picture, Suzuki's "Internals of PostgreSQL," ch. 9 (WAL).