Track D
Databases, under the hood
How Postgres actually executes your query: pages, B-trees, WAL, MVCC.
- 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 - 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 - 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 - 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 - 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 - The query planner: EXPLAIN, statistics, and join strategies
You write SQL that says what you want; you never say how to get it. Something has to turn 'these rows where this is true, joined to those' into an actual sequence of disk reads and loops — that something is the planner, and it decides by guessing how many rows each step will produce and pricing the alternatives. This lesson reads a real EXPLAIN plan line by line, shows where the row estimates come from (ANALYZE, histograms, most-common-values) and how a stale estimate produces a catastrophically slow plan, walks the three join algorithms and when each wins, and explains the single most common production surprise: why Postgres ignores the index you built.
17 min - Locks & optimistic concurrency
MVCC lets readers and writers pass each other without waiting — but the moment two transactions try to change the same row, someone has to wait, and that waiting is implemented by locks. This lesson is about the concurrency you can't get for free: the read-modify-write race that silently loses updates, the two philosophies for fixing it (lock first vs. check at the end and retry), how deadlocks form and how consistent lock ordering prevents them, advisory locks for serializing work that isn't tied to a single row, and SELECT ... FOR UPDATE SKIP LOCKED — the one-line trick that turns a table into a job queue. Anchored on the exact advisory lock Fable uses to stop two settlements from over-paying a debt.
16 min - LSM-trees & SSTables: the other way to store data
Everything in this track so far assumed a B-tree — update rows in place, keep them sorted, pay for it with random writes. There's an entirely different family that makes the opposite bet: never write randomly at all. Buffer writes in memory, flush them to disk as immutable sorted files, and clean up later in the background. That's the log-structured merge-tree behind RocksDB, Cassandra, and half the databases built in the last fifteen years. This lesson explains how LSM turns every write into a sequential append, why that makes writes fast and reads slower, how tombstones, Bloom filters, and compaction fill the gaps, and the read/write/space trade-off that decides whether you want a B-tree or an LSM in the first place.
15 min - Replication: streaming, logical, and the lag that bites you
One database server is a single point of failure and a single ceiling on read capacity. The fix is to keep copies on other machines — but the moment there's more than one copy, you inherit a hard question: what happens to a write that reached one copy and not the others? This lesson covers how Postgres actually ships changes to replicas (streaming the same WAL from the write-path lesson, vs. logical row-level replication), the synchronous-vs-asynchronous choice that trades write latency against durability, replication lag and the read-your-writes bug it causes in real apps, and what failover and split-brain mean when the primary dies.
16 min - Partitioning & sharding: splitting the data
Replication makes copies of the whole database; that scales reads and survives failure, but every write still funnels through one primary and one machine still has to hold all the data. Partitioning and sharding are the other axis: instead of copying the data, split it. This lesson draws the line people constantly blur — partitioning splits one table inside one server, sharding splits data across many servers — walks the strategies (range, hash, directory) and their hotspot and resharding traps, and confronts the real price of sharding: the cross-shard query and transaction that were free on one node and are agony across many. It closes on the one decision that determines everything, the shard key — and why Fable's data model is already shaped to choose a good one.
16 min