Physical Storage: Pages, Buffer Pool & WAL

What commit actually guarantees, and why it is fast

This lesson connects the abstractions above to real latency and durability by following data down to the metal.

Pages

Databases do not read or write individual rows from disk; they move fixed-size pages (Postgres 8KB, InnoDB 16KB). A page holds many rows plus a header and a slot directory. This is why row layout matters: a row-oriented page stores whole rows together, great for "give me this order" (OLTP); a column-oriented layout stores each column contiguously across rows, great for "sum revenue over 10M rows" (OLAP) because you read only the columns you need and they compress extremely well.

Buffer pool

The database keeps hot pages in an in-memory buffer pool (the biggest knob in most databases, e.g. InnoDB innodb_buffer_pool_size). Reads check the buffer pool first; a hit is a memory access, a miss is a disk read that pulls the page in and evicts a cold one (usually via an LRU variant). Writes modify the page in the buffer pool, marking it dirty. Dirty pages are not written to their data-file home immediately; they are flushed later, in batches, at a checkpoint. This is what lets a database absorb many writes to the same hot page as one eventual disk write.

Which raises the durability problem: if a committed change lives only as a dirty page in volatile memory, a crash loses it. The fix is the write-ahead log (WAL).

WAL

Before a change is considered committed, the database appends a small redo record describing the change to the WAL and forces it to stable storage with fsync. The rule is "log before data": the WAL record hits disk before the corresponding data page does. Because the WAL is written sequentially (append-only), this is fast even though a fsync is still the single slowest thing in the commit path. On crash recovery the database replays WAL records after the last checkpoint to reconstruct any dirty pages that were lost, which is why a committed transaction survives a crash even though its data page never reached disk before the failure.

Interview nuance: the number that drives all of this is that sequential I/O is roughly 100x faster than random I/O on spinning disks, and still meaningfully faster on SSDs (which also suffer write amplification from random writes). This one fact explains why the WAL is a sequential append rather than random page writes, why LSM-trees win at writes, and why databases batch and checkpoint. To amortize the fsync cost, databases use group commit: many concurrent transactions' WAL records are batched into one fsync, so 500 commits can cost a handful of fsyncs instead of 500.

Interview nuance: the OS page cache sits underneath the database, so a "disk write" from the DB may only reach the OS buffer, which is exactly why an explicit fsync (not just write()) is required for real durability. Also, compression happens at the page level, and column stores compress far better because adjacent values share a type and range.

INSERT ... COMMIT
  1. change row in a page inside the BUFFER POOL (RAM) -> page now dirty
  2. append redo record to WAL buffer
  3. COMMIT: fsync WAL to disk (sequential, group-committed)  <-- durability point
  4. return success to client
  ...later...
  5. CHECKPOINT: flush dirty data pages to their home (random-ish)
  crash before 5? replay WAL from last checkpoint to rebuild the page.

Recap: writes land in in-memory pages in the buffer pool and are flushed lazily at checkpoints, while a sequentially-written, fsync'd WAL provides the actual durability and crash recovery, all of it shaped by the ~100x gap between sequential and random I/O.