technotes · topic

PostgreSQL

First-principles depth, taught by contrast with InnoDB — the heap & MVCC, VACUUM & bloat, the planner, snapshot isolation, the WAL & replication.

GlossaryContext: PostgreSQL current (16/17+)

Lessons

1. 0001 The heap & MVCC

   Postgres never updates in place — so dead tuples, VACUUM, bloat, and "every index is secondary" all derive from one keystone fact.

   done
2. 0002 Dead tuples, VACUUM & HOT updates

   Following the dead tuples: what plain VACUUM vs VACUUM FULL do, when autovacuum fires (~20% dead), and how HOT lets an update skip every index.

   done
3. 0003 Secondary indexes & the visibility map

   Why 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.

   done
4. 0004 Transaction IDs, freezing & wraparound

   The 32-bit XID clock that InnoDB's undo log hides — why old rows must be frozen, and the wraparound that VACUUM exists to prevent.

   done
5. 0005 Snapshot isolation: Read Committed → Serializable

   A snapshot is just a captured set of XIDs — so an isolation level is a timing choice. RC vs RR vs Serializable (SSI), the Postgres way.

   planned

Reference