Track D

Databases, under the hood

How Postgres actually executes your query: pages, B-trees, WAL, MVCC.

1. Where your data actually lives: pages, heap, and the buffer pool

   A table is not rows — it's a stack of fixed-size 8KB pages on disk, and Postgres never reads less than one whole page at a time. This lesson opens up a page (header, item pointers, tuples), explains why a row is a "tuple" with a hidden header, follows a value into TOAST when it gets too big, and shows the buffer pool caching those pages in RAM — with an EXPLAIN (ANALYZE, BUFFERS) transcript so you can see cache hits and disk reads for yourself.

   15 min
2. B-tree indexes: how a lookup really works

   An index is not the database magically getting faster — it's a specific tree structure that turns reading every page into walking four pages. This lesson builds a B-tree from the problem it solves, walks a single lookup root-to-leaf-to-heap, and then explains the rules that decide whether your index gets used at all — the leftmost-prefix rule for composite indexes, index-only scans, and the several ways the planner correctly ignores an index you were sure it would use.

   16 min
3. The write path: WAL, fsync, and checkpoints

   When your transaction commits, what does 'committed' physically mean — and how does the database survive a power cut mid-write without corrupting or losing it? The answer is write-ahead logging: append the change to a log, force it to disk with fsync, and only then call it committed. This lesson follows a write through the WAL, explains why sequential appends are the fastest thing a disk does, shows how checkpoints and crash recovery work, and ends on the 2018 bug where fsync itself lied.

   15 min
4. MVCC: what a transaction physically is

   How can one transaction read a row while another writes it, without either waiting for the other? Postgres never overwrites a row in place — an UPDATE writes a whole new version and marks the old one dead, and the xmin/xmax stamps from the pages lesson decide which version each query's snapshot is allowed to see. This lesson makes that concrete with two-session psql transcripts at both the default and Repeatable Read levels, then follows the consequences: bloat, why VACUUM exists, the terrifying transaction-ID-wraparound edge case, and the HOT optimization.

   16 min
5. Isolation levels & the anomalies they permit

   Isolation levels sound like abstract config, but they're defined entirely by which concurrency bugs they let through — so this lesson teaches the bugs first. Dirty read, non-repeatable read, phantom, lost update, and the sneaky write skew, each as a two-session money example. Then the gap between the SQL standard's four levels and what Postgres actually gives you — including the crucial fact that Repeatable Read is snapshot isolation that still permits write skew, and only Serializable closes it, at the cost of retrying aborted transactions.

   17 min