A self-taught course in database internals

A Field Guide to the Database Engine

Sixteen weeks, one machine. This is the map. Every box below is a layer a query passes through on its way from text to bytes and back. Click any layer to open the lesson that takes it apart, or read the parts in order. The point of this page is the thing that is hardest to get from sixteen separate lectures: how it all fits together.

Week 1 · Architecture: the whole pipeline, end to end (click) SQL query text SELECT name FROM t WHERE id = 42 tokens Parser and analyzer Week 8 · lex, parse, bind, logical plan logical plan Optimizer Week 11 · cost, selectivity, join order physical plan Executor · Volcano operators Weeks 9 to 10 · scan, filter, join, sort, aggregate key lookups Access methods · B+tree and hash Weeks 6 to 7 · find the right pages fast page requests Buffer pool Weeks 4 to 5 · cache pages, decide what to evict read / write page Storage · slotted pages on disk Weeks 2 to 3 · heap files, records, free space Transactions and concurrency Weeks 12 to 13 ACID, isolation, 2PL, deadlock, MVCC decides which interleavings are legal Logging and recovery (WAL) Week 14 · ARIES every change is logged before it hits the page so a crash can always be undone Or a different machine entirely Weeks 15 to 16 · LSM trees, columnar, DuckDB, distributed, CAP same problems, different trade-offs
query processing indexes buffer pool storage transactions recovery modern and distributed
The one story

A SELECT enters as text at the top and walks down: the parser turns it into a tree, the optimizer picks a plan, the executor pulls tuples one at a time through its operators, the access methods find the right pages, the buffer pool serves them from memory or fetches them from disk, and the storage layer hands back raw bytes. An UPDATE adds two more characters to the cast: the transaction manager on the left decides whether your change is allowed to interleave with everyone else's, and the log on the right writes down what you did before the change touches the page, so a crash can never lose a committed write. Hold this picture. Every lesson is a zoom into one box.

I

The whole machine

Week 01
DBMS architecture
The five components of a database and the path a query takes through them. A tour of how PostgreSQL and SQLite are actually put together.
Play: click a query through the live pipeline
II

Storage: where the bytes live

Week 02
Pages and slotted records
Why databases read fixed-size pages, not bytes. The slotted page layout that lets variable-length records move without breaking their addresses.
Play: a byte-level slotted page editor Week 03
Heap files and free space
How pages are strung into a file, how the engine finds room for a new record, and what really happens when you delete a row.
Play: insert and delete into a heap file
III

The buffer pool: memory is the real disk

Week 04
Buffer pool design
The page table, pin counts, and dirty flags that let the database pretend memory is infinite, and the rules for evicting a page safely.
Play: pin, dirty, and evict frames by hand Week 05
Replacement: LRU-K and friends
Why plain LRU collapses under a big sequential scan, and how LRU-K, CLOCK, and prefetching fix it.
Play: race LRU vs CLOCK vs LRU-K on a workload
IV

Indexes: finding the needle

Week 06
B+trees: search, insert, split
Why a fat, shallow, balanced tree beats a binary tree on disk. Watch a node fill, split, and push a separator up to the root.
Play: insert keys and watch the tree split Week 07
Deletion, bulk load, hashing
Merging and redistributing on delete, loading a tree bottom-up, composite keys, and when a hash index beats a B+tree.
Play: delete keys and trigger merges
V

Query processing: from text to tuples

Week 08
Parsing and logical plans
How SQL text becomes a syntax tree, gets checked against the catalog, and turns into a relational algebra plan.
Play: tokenize and parse SQL live Week 09
The Volcano model
One interface, open-next-close, that lets you snap operators together like Lego. Scans, filters, projection, and the nested loop join.
Play: pull tuples through an operator tree Week 10
Joins, sorting, aggregation
Sort-merge and hash joins, external merge sort when data does not fit in memory, and hash aggregation.
Play: compare join algorithms side by side Week 11
Query optimization
Why the same query has a thousand plans, how the optimizer estimates cost and selectivity, and how it orders joins.
Play: reorder joins and watch the cost
VI

Transactions and recovery: never lose a write

Week 12
ACID and isolation levels
The anomalies that appear when transactions interleave, the isolation levels defined to forbid them, and why two-phase locking works.
Play: trigger each anomaly in a schedule Week 13
Concurrency: 2PL and MVCC
Strict 2PL, detecting deadlocks with a waits-for graph, and how MVCC lets readers and writers stop blocking each other.
Play: watch versions and a deadlock cycle Week 14
WAL and the ARIES algorithm
Write-ahead logging, the three guarantees it buys, and the Analysis, Redo, and Undo passes that bring a crashed database back.
Play: crash the database and replay the log
VII

Modern and distributed

Week 15
LSM trees and columnar
The write-optimized LSM design behind RocksDB and Bigtable, the read-versus-write trade-off of compaction, and why analytics went columnar.
Play: flush memtables and run compaction Week 16
Distributed databases
Partitioning and replication, the blocking problem in two-phase commit, and what CAP actually forces you to choose.
Play: partition a network and test CAP
·

Reference shelf and exam prep

Cheat sheets are built to print. The exam bank is MCQ-only practice across every topic; the viva bank is the anticipated-question set for the four papers.

MCQ exam bankpractice across all 16 weeks Viva prep: the four papersanticipated questions and answers Slotted page cheat sheetbyte layout at a glance B+tree operationssearch, split, merge rules Join algorithmsNLJ, SMJ, HJ costs Isolation levels matrixanomalies by level ARIES on one pagethe three passes LSM vs B-treeread/write amplification Glossarycanonical terms
This is a course, not a pile of files

Each lesson ends with a prompt to ask your teacher. That teacher is the agent that built this. Ask it to go deeper on any box in the map above, redraw a diagram, or quiz you harder before the exam.

Built with the teach and humanizer skills. The research that grounds every lesson, the course spine, and the build methodology are in research/.