Learning Pathway
05 — The Engine Room: How PostgreSQL Actually Executes
12 lessons- 01 ANALYZE Internals: How PostgreSQL Samples Your Data to Guess Plans How PostgreSQL's ANALYZE works at the source level: two-stage reservoir sampling, the 300x rule, MCV lists, equi-depth histograms, n_distinct, correlation, and how it differs…
- 02 Heap Pruning and the xmin Horizon: How a Plain SELECT Cleans Up How PostgreSQL heap pruning works at the source level: pruneheap.c, line pointers, HOT chains, pd_prune_xid, and the xmin horizon that decides what can be…
- 03 From SQL Text to Result: The Parse, Plan, Execute Pipeline How PostgreSQL turns a SQL string into rows: raw parsing, parse analysis, rewriting, planning, and execution, traced through postgres.c, the optimizer, and the executor.
- 04 Paths vs Plans: How the Optimizer Chooses, Then Commits How the PostgreSQL optimizer uses lightweight Paths and add_path to compare strategies on cost, startup cost, and sort order, then elaborates the winner into…
- 05 One Tuple at a Time: How the Volcano Executor Runs Your Plan How the PostgreSQL executor runs a plan as a tree of demand-pull iterators: ExecProcNode, TupleTableSlots, and the pipelined-vs-blocking node split behind startup cost.
- 06 How a B-tree Splits Without Stopping the World: Lehman-Yao in PostgreSQL How PostgreSQL's B-tree stays concurrent during page splits: high keys, right-links, _bt_split, suffix truncation and deduplication, derived from the nbtree source.
- 07 How PostgreSQL Remembers Who Committed: clog and the SLRU Caches How PostgreSQL records transaction fate in two-bit commit-log entries (pg_xact) cached through the SLRU machine, plus hint bits, subtransaction overflow and pg_stat_slru.
- 08 Anatomy of a WAL Record: What PostgreSQL Writes Before It Touches Your Data The structure of a PostgreSQL WAL record: the XLogRecord header, resource managers, XLogInsert, the LSN flush rule, and the full-page writes behind post-checkpoint WAL…
- 09 Tuple Deforming: Why Column Order Quietly Changes Your Row Size How PostgreSQL deforms a heap tuple into column values: attlen, attalign padding, the NULL bitmap, attcacheoff, and why column ordering changes on-disk row width.
- 10 The Lock Fast-Path: How PostgreSQL Avoids a Traffic Jam at the Lock Table How PostgreSQL's fast-path locking records weak relation locks in per-backend slots: LOCALLOCK, the 16 fast-path slots, strong-lock transfers, and reading pg_locks.fastpath.
- 11 Memory Contexts: Why PostgreSQL Almost Never Calls free() How PostgreSQL's memory contexts work: palloc and CurrentMemoryContext, the aset.c block/chunk allocator, the context tree, per-tuple reset, and pg_backend_memory_contexts.
- 12 The Background Processes: Who Actually Runs a PostgreSQL Server How PostgreSQL's background processes work: the postmaster supervisor, checkpointer, background writer, WAL writer, autovacuum launcher/workers, archiver, and backend_type.