technotes · topic
First-principles depth, taught by contrast with InnoDB — the heap & MVCC, VACUUM & bloat, the planner, snapshot isolation, the WAL & replication.
Postgres never updates in place — so dead tuples, VACUUM, bloat, and "every index is secondary" all derive from one keystone fact.
doneFollowing the dead tuples: what plain VACUUM vs VACUUM FULL do, when autovacuum fires (~20% dead), and how HOT lets an update skip every index.
doneWhy every index scan is a two-step heap fetch, how the visibility map (set by VACUUM) earns an index-only scan, and INCLUDE covering indexes.
doneThe 32-bit XID clock that InnoDB's undo log hides — why old rows must be frozen, and the wraparound that VACUUM exists to prevent.
doneA snapshot is just a captured set of XIDs — so an isolation level is a timing choice. RC vs RR vs Serializable (SSI), the optimistic Postgres way.
doneThe plan as a tree of nodes: cost, estimated vs actual rows, BUFFERS for cache vs disk, and the scan nodes from the storage arc.
doneStorage, transactions & the planner mixed on purpose — cross-pillar quiz, diagnose-it scenarios, a synthesis prompt, and community pointers.
doneSelectivity, random_page_cost, and the cases where a Seq Scan is genuinely the right call — the Postgres cousin of "when indexes are ignored."
doneWrite-ahead logging — log before data, checkpoints, the synchronous_commit dial, and why Postgres needs one log where InnoDB needs two.
doneShip the WAL to a replica — physical (streaming) vs logical replication, and the sync-vs-async availability/consistency trade-off.
doneEXPLAIN, a bloat mystery, or a
Postgres-vs-MySQL contrast that feels off, and we'll dissect it together.