WAL, Pages & Buffer Pool

Pages: the unit of disk I/O

Databases read and write in fixed pages (blocks), typically 8 KiB. Slotted page layout stores variable-length rows with an offset array at the page header — inserts fill free space, deletes leave holes until vacuum/compaction reclaims.

Why pages matter: the buffer pool caches pages, not rows. A query touching one column still may load the whole page unless index-only or columnar.

Buffer pool

The buffer pool (shared_buffers in Postgres) holds hot pages in RAM. On read miss, fetch from disk, evict a victim page (clock/LRU variants). On write, mark page dirty — actual disk write may be deferred.

Concept	Meaning
Cache hit ratio	Fraction of reads served from RAM — primary health metric
Dirty page flush	Background writer/checkpoint writes dirty pages
Double buffering	OS page cache + DB buffer pool — memory pressure risk

Tuning shared_buffers to ~25% of RAM is a starting point; measure, don't cargo-cult.

Write-Ahead Log (WAL)

WAL guarantees durability without flushing every data page on every commit:

1. Record intended change in WAL (sequential append — fast)
2. fsync WAL to stable storage
3. Acknowledge commit to client
4. Later, dirty data pages reach disk (checkpoint)

Crash recovery replays WAL from the last checkpoint — same pattern as filesystem journaling (see OS → Journaling & Crash Consistency).

Commit path: WAL append → fsync(log) → return OK (data pages may still be dirty in RAM) Crash: replay WAL → redo committed work, undo uncommitted

Checkpoints and full_page_writes

Checkpoints flush dirty pages and advance the redo start point — bounds recovery time. full_page_writes (Postgres) log entire page images after checkpoint to guard against partial page writes on power loss — trades WAL size for safety.

The fsync latency tax

Every synchronous commit waits for disk. On cloud SSDs with lying flush, pg_test_fsync reveals reality. Batch commits, group commit, and async replicas all exist to amortize this cost.

Senior-level signal

"Database is slow" after hardware migration → check WAL sync latency and checkpoint spikes before blaming queries. iostat, pg_stat_wal, buffer hit ratio tell the storage story.

Where this goes next

Bloom Filters covers a small but load-bearing piece of the read path this topic has referenced twice already: how LSM engines skip a disk read for keys that definitely aren't there.