Capstone: Database Internals, Everything

The finale. Every lesson in this track - Pages & Heap Files, The Buffer Pool, WAL & Recovery, B-Tree on Disk, MVCC & Vacuum, The Query Planner and Locks, Latches & Transactions - stops being seven separate topics here and becomes one machine.

Before you start. Picture the database as a library: a room of shelves holding the actual books, a front desk that keeps the busiest ones close by instead of walking to the stacks, a ledger that records every checkout the instant it happens, a card catalog that finds one book among millions, spare copies so two readers never fight over the same one, a librarian who decides whether to check the catalog or just walk the aisle, and “reserved” tags that keep two people from grabbing the same book at once. Every one of those seven things is a lesson you already read. This is the one visit where you meet all seven at once, on the same errand.

The idea in 30 seconds

A database has seven parts, and none works alone: pages (fixed-size blocks of rows on disk), the buffer pool (pages kept in memory), the write-ahead log (every change written down before it is made), the B-tree index (a sorted lookup tree pointing at rows), multiversion concurrency control or MVCC (old row versions kept for readers), the query planner (picks how to find rows), and locks (stop two writers colliding). Each relies on a promise the others keep.
The buffer pool can leave a changed page unwritten to disk for a while only because the write-ahead log already made that change durable and replayable.
A write-ahead log entry is small and forced onto disk with fsync (wait until the disk confirms it is saved); it stays fast only because the buffer pool and the page format keep most reads and writes away from disk entirely.
A B-tree index, a sorted tree of keys that finds one row in a few steps, stores the row’s address (page number and slot), not the row itself, so a page can rearrange its own bytes without the index ever needing to change.
Multiversion concurrency control (MVCC) lets a reader and a writer touch the same row at the same moment without either one waiting on the other, by keeping the old version around a little longer.
One ordinary UPDATE, the SQL command that edits an existing row, touches all seven parts in around a millisecond. The slowest step by far is the commit: the one wait for the disk to confirm the change is saved.

Read the story · Skip to the numbers