Reference · Cheat sheet
Lesson 0009 distilled — log before data, checkpoints, the durability dial, and why Postgres needs one log where InnoDB needs two. Built to print.
Log before data: a change's WAL record is flushed to disk before its data page. At commit, flush only the WAL (one sequential file), not the scattered dirty pages.
Recovery = REDO / roll forward: replay WAL to re-apply committed changes not yet in the data files.
Periodically flush all dirty data pages + write a checkpoint record.
→ Recovery replays WAL only from the last checkpoint; older WAL can be recycled.
Trade-off: frequent = fast recovery, more I/O spikes; infrequent = cheaper runtime, longer replay + more WAL.
on (default): commit waits for the WAL flush → full durability.
off: commit returns before flush → faster, but a crash can lose a small window of recent commits — no corruption, DB stays consistent.
≠ fsync=off, which does risk corruption.
Postgres WAL = redo only. No undo log — old versions live in the heap (never update in place, 0001) and VACUUM reclaims them. That design choice is what removes the need for undo.
| Job | InnoDB | Postgres |
|---|---|---|
| Durability (redo) | redo log | WAL |
| MVCC old rows | undo log | heap |
| Reclaim | purge | VACUUM |
| Flush dial | flush_log_at_trx_commit | synchronous_commit |
SHOW wal_level; · SHOW synchronous_commit;
SELECT pg_current_wal_lsn(); — current WAL write position (advances as you write).
CHECKPOINT; — force dirty pages to disk (superuser).