Database Internals — Episode 2

The second episode starts with ACID boundaries, then turns to the page cache and buffer pool. Finite memory requires eviction: FIFO and LRU are simple, clock-sweep tracks recent access, and TinyLFU separates candidates from frequently used pages.

A write-ahead log records changes before data pages. ARIES recovers in three phases: analysis finds dirty pages and transactions, redo replays history, and undo rolls back unfinished work. Recovery progress is also logged in case another crash occurs.

Concurrency control can be optimistic, multiversion, or pessimistic. Serializability equates concurrent and sequential histories; isolation levels permit different anomalies. Snapshot isolation chooses versions, validation checks conflicts before commit, and two-phase locking acquires and then releases locks.

For write-heavy structures, the hosts examine copy-on-write and the LSM tree. Writes enter a sorted memtable; immutable SSTables flush and compact, creating read, write, and space amplification. A tombstone prevents a deleted key from returning, while a Bloom filter skips tables without it.