The Map

DATABASE INTERNALS -- THE MAP (00) one UPDATE and one SELECT traced through a real engine (Postgres 16, the one under shop). not 'which database' (that was System Design 14) -- this set opens ONE database and names every gear inside. the lens: every gear exists because DISKS ARE SLOW AND CRASH. each board = one gear, worked to the bolt level. THE HINGE a database is two beautiful lies told to the disk: 1. random WRITES become ONE sequential append (the WAL) 2. random READS become RAM hits (the buffer pool) get these two illusions right and the rest -- MVCC, B-trees, vacuum, checkpoints -- is bookkeeping to keep the lies honest. THE ENGINE, ONE QUERY AT A TIME CLIENT UPDATE users SET plan='pro' WHERE id=42; TCP 5432 ONE BACKEND PROCESS (process-per-connection) postgres forks a process per client -- no shared interpreter state PARSER SQL text -> parse tree ANALYZE / REWRITE names -> tables, views expand, apply rules PLANNER cost-based: pick scan, join order, join algo EXECUTOR pull tuples through the plan tree board 06 boards 01/05/07 reads + writes touch PAGES, never the disk directly SHARED BUFFER POOL (RAM) shared_buffers -- 8KB page slots, shared by ALL backends dirty (red) = changed in RAM, not yet on disk clock-sweep eviction picks victims . pin = in use board 02 THE DURABILITY PLANE 1. BEFORE touching the page: append a WAL record WAL BUFFERS xid=991 heap UPDATE rel=users blk=7 COMMIT = flush + fsync WAL SEGMENT FILES (16MB each) ONE sequential append stream, LSN = byte offset sequential append: ~GB/s . random 8KB writes: ~MB/s that asymmetry is WHY the WAL exists board 03 DISK (data files -- written LAZILY, long after commit) HEAP FILES users -> 1GB segments of 8KB pages B-TREE INDEXES users_pkey pages too WAL DIR pg_wal/ segments (the truth) boards 01 + 04 page in / page out (8KB at a time) THE JANITORS (background processes) CHECKPOINTER every ~5min: flush all dirty pages, truncate old WAL BGWRITER trickle dirty pages out ahead of need AUTOVACUUM reclaim dead tuples (MVCC's garbage) board 05 WAL SENDER streams the same WAL to replicas -- replication IS crash recovery, running forever (DDIA ch.5 callback) PULL THE PLUG AT ANY MOMENT data files: torn, stale, half-written -- FINE. restart replays the WAL from the last checkpoint (REDO), and MVCC + CLOG decide what was committed. the WAL is the database; the files are a cache of it. the SELECT path (green = no WAL, just buffer pool + MVCC visibility) THE DEEP DIVES (each gear, one board) 01 PAGES & HEAP FILES the 8KB page: headers, line pointers, tuples, TOAST -- how a row really sits 02 THE BUFFER POOL clock-sweep, pin counts, dirty pages, why not just mmap? 03 WAL & RECOVERY LSN, fsync, group commit, ARIES redo/undo, checkpoints, torn pages 04 B-TREE ON DISK page splits, Lehman-Yao right-links, index-only scans, HOT 05 MVCC & VACUUM xmin/xmax, snapshots, dead tuples, bloat, xid wraparound 06 THE PLANNER statistics, cost model, scan + join choices, reading EXPLAIN 07 LOCKS & TXNS locks vs latches, deadlock detection, isolation levels as implemented 08 EVERYTHING the whole engine on one canvas + the DECISION LOG (PG vs InnoDB) JARGON, DECODED page = the 8KB unit of ALL I/O tuple = one row version WAL = write-ahead log, the append-only truth LSN = a byte position in the WAL buffer pool = page cache in RAM dirty = changed in RAM only checkpoint = 'all dirty pages <= this LSN are on disk' MVCC = readers get a snapshot, never block vacuum = collect dead row versions latch = lock on a data structure (microseconds) lock = lock on data (whole txns) fsync = 'disk, PROMISE me it is durable' THE ONE THING commit does NOT write your data to the data files. it writes a description of the change to one append-only stream and calls fsync once. the heap pages catch up later, maybe minutes later. every database you respect is an event log with a cache in front of it (DDIA ch.11 said this too).