Day 90: WAL, buffer pool, checkpoints, vacuum
The machinery under every write
Postgres doesn't write every change directly to the actual table files on disk immediately — that would mean tiny, random, slow disk writes for every transaction. Instead:
- A change is first appended to the WAL (Write-Ahead Log) — a sequential, append-only log, which is fast because sequential writes are cheap (Phase 0, Day 1's hierarchy)
- The change is applied to pages held in the buffer pool (an in-memory cache of disk pages) — not yet written back to the actual table file
- A background checkpoint periodically flushes dirty (modified) buffer pool pages to disk, and marks WAL up to that point as no longer needed for crash recovery
Why this is journaling (Phase 0, Day 6), again
This is exactly the journaling filesystem pattern: log the intent first (WAL), apply it, and only later flush the actual data — if the system crashes between steps, WAL is replayed on restart to recover, the same as a journaling filesystem replaying its journal.
Vacuum: cleaning up after MVCC
Postgres never overwrites a row in place on UPDATE/DELETE — it marks the old version dead and writes a new one (tomorrow's MVCC topic). Vacuum reclaims the space used by these dead row versions. Without regular vacuuming, tables and indexes accumulate bloat — dead space that's never reclaimed, slowing scans and wasting disk.
VACUUM ANALYZE orders;
VACUUM FULL orders; -- reclaims space fully, but takes an exclusive lock — rarely safe in productionKey terms
- WAL (Write-Ahead Log)
- A sequential, append-only log recording every change before it's applied to actual data pages.
- Buffer pool
- An in-memory cache of disk pages, modified in place before being flushed by a checkpoint.
- Checkpoint
- Periodically flushes dirty buffer pool pages to disk and trims WAL no longer needed for recovery.
- Vacuum
- Reclaims space from dead row versions left behind by UPDATE/DELETE under MVCC.
Why does Postgres write to the WAL before modifying the actual data pages?
