hits
0
Lesson 01 · The currency of everything
Pages & the buffer pool: the unit of all cost
Every mystery in this course dissolves once you accept one fact: a database never reads or writes a row. It reads and writes pages: fixed-size blocks (8KB in PostgreSQL, 16KB in InnoDB) that are the atomic unit of I/O, caching, locking granularity decisions, and recovery. Your 100-byte row lives inside a page with ~80 neighbors; touching it means fetching all of them. Everything a storage engine does: B-trees, WAL, MVCC, the planner's cost mathematics: is ultimately an attempt to touch fewer pages, or to touch them sequentially instead of randomly. First, feel why the stakes are so high:
The latency ladder
Where is the page right now?
The same 8KB page, fetched from each level of the hierarchy. Bars are on a scale where RAM = one pixel-width of pain. Press each button.
Translate to human scale: if the buffer-pool hit is 1 second, the SSD read is a day and a half, and the spinning-disk seek is four months. This is why the entire discipline of database performance is, at bottom, cache management: and why the question "is the working set in RAM?" predicts more about your latency than any query rewrite. The cache in question:
Operate the buffer pool
Four frames, your workload
A toy buffer pool: 4 frames, LRU-ish eviction. The hot pages your app loves are P1 and P2. Warm them up, then run different access patterns: including the big scan: and watch the hit rate.
misses (disk reads)
0
hit rate
:
The scan disaster you just caused has a name: cache pollution: and real engines defend against it: PostgreSQL gives large sequential scans a tiny private ring buffer (256KB) instead of the main pool; InnoDB inserts scanned pages at the middle of its LRU list, not the head, so one pass can't evict the establishment. The general lesson generalizes far beyond databases: a cache is a bet on repetition, and a scan is the workload that never repeats. When an analyst's ad-hoc query suddenly makes the whole app slow, this lab is usually the mechanism: the OLTP working set got evicted and the next thousand queries paid the SSD tax to rebuild it.
Inside a page
┌──────────── 8192 bytes ─────────────┐
│ header (24B): LSN★, checksum, flags │
│ item pointers → ↓ (grow down) │
│ ...free space... │
│ tuples ← (grow up from the end) │
│ [row v2][row v1 (dead★)][row ...] │
└─────────────────────────────────────┘
★ LSN: the WAL position that last touched this page (Lesson 4)
★ dead row versions: MVCC's leftovers (Lesson 5)
Two details planted here on purpose: every page carries the LSN of the log record that last modified it: the hook crash recovery hangs on in Lesson 4: and pages hold multiple versions of rows, most invisible to you, which is Lesson 5's entire subject. Also note the indirection: item pointers mean a row can move within its page without any index noticing: and when an updated row no longer fits its page, the engine must either find it a new home (leaving a forwarding stub) or split structures: the mechanics that Lesson 2 makes you perform by hand.
The dirty-page contract: writes work the same way: the page is modified in the buffer pool and merely marked dirty; disk hears about it later (background writer, checkpoints). Which raises the obvious terrifying question: what if we crash while memory holds changes disk has never seen? Holding that question open: precisely, uncomfortably: until Lesson 4 is the plot of this course.
Check yourself
1. A query fetches one 64-byte counter value. Roughly what does the storage engine actually read, and why does that ratio matter?
The 128:1 amplification is the tax; locality is the rebate. It's why clustering related rows (Lesson 2), storing aggregates together (the field guide's document lesson), and sorting by access pattern (wide-column clustering keys) all pay off: the page you were forced to read is full of things you were about to ask for anyway.
2. Your database's buffer pool hit rate drops from 99% to 92% and p99 latency triples. Why is a 7-point drop so catastrophic?
Cache math is about the miss rate, not the hit rate: avg_cost ≈ hit_cost + miss_rate × miss_penalty, and with a 1000× penalty the second term is the whole story. This is why "the working set stopped fitting in RAM" is a cliff, not a slope: and why DBAs watch hit rates in tenths of a percent.
3. A nightly analytics query scans a 200GB table on the production OLTP database. The app is slow for an hour after the query finishes. What happened?
The lab's scan, at production scale. Locks die with the transaction; the cache damage outlives it. Mitigations in order of goodness: run analytics on a replica (the replication course's read-scaling, now with a second justification), rely on scan-resistant eviction policies, or schedule for the trough and accept the rebuild. Diagnosing this pattern: "slow AFTER the heavy thing": is a small superpower.