Database internals:
under the hood
The field guide told you which database to pick. This course opens the hood: pages and buffer pools, B-trees splitting under load, LSM compaction debt, the write-ahead log that makes COMMIT mean something, MVCC's hidden row versions, the anomalies each isolation level permits: and finally, the query planner's actual reasoning, so "why is this slow?" stops being a mystery and becomes a diagnosis.
Pages & the buffer pool: the unit of all cost
Databases don't read rows: they read pages. Fetch one 100-byte row and pay for 8KB; climb the latency ladder from RAM to disk; then operate a tiny buffer pool and watch a scan evict everything your queries loved.
B-tree mechanics: splits, fill factor, and the UUID tax
Insert keys into a live B-tree and watch pages split and the tree grow. Then run the classic experiment: sequential vs random primary keys: and see exactly why UUID inserts shred your cache and bloat your index.
LSM trees: compaction and the amplification triangle
Read, write, and space amplification: you can lower any two by raising the third. Choose compaction strategies and watch the triangle move; then build a bloom filter bit-by-bit and catch its false positive in the act.
The write-ahead log: what COMMIT actually promises
Step through a real write: page dirtied in memory, WAL record appended, fsync, acknowledgment. Crash the server at every point in the sequence and run recovery: then discover why group commit exists.
MVCC: the rows you can't see
UPDATE never updates: it writes a new version and hides the old one. Run two transactions against version chains, watch each read its own snapshot without blocking, then update one row five times and meet the bloat VACUUM exists to fight.
Isolation levels: the anomaly theater
Run the same two concurrent transactions under each isolation level and watch which anomalies appear: dirty reads, non-repeatable reads, phantoms: and write skew, the subtle one that empties the on-call schedule under everything short of serializable.
Why your query is slow: thinking like the planner
The planner is a cost estimator, not an oracle. Watch it choose between scans as selectivity changes, then run the autopsy on four classic slow queries: leading wildcards, functions on columns, stale statistics, and the OR that broke the index.
This course zooms into a single node. It pairs with the Databases field guide (which model to choose), and with the Replication and Distributed Systems courses (what happens when there's more than one node). Lesson 3 deepens the LSM introduction from the field guide's wide-column lesson; Lesson 4's WAL is the same log that replication ships between nodes.