Skip to content

Level 2 · Internals: MVCC, Storage & Indexes

Level 1 taught you to use PostgreSQL well. Level 2 is about understanding why it behaves the way it does — because every performance problem you will ever debug is explained by one of the mechanisms here. Why did the table double in size after one UPDATE? Why did a one-millisecond ALTER TABLE take the site down? Why is the query fast in psql and slow from the app? Why does the first change after a checkpoint write 60 times more log than the second?

Each lesson opens up one part of the engine with the tools PostgreSQL gives you for looking inside: hidden system columns, pageinspect and pgstattuple, pg_stats, pg_locks, pg_waldump, and above all EXPLAIN (ANALYZE, BUFFERS). Every output shown was produced on PostgreSQL 18.6; the project at the end applies all of it to take a realistic workload from 29 to over 22,000 transactions per second.

Prerequisites: Level 1, especially transactions and isolation (lesson 7). You will want a throwaway cluster where you are superuser, because several inspection extensions require it.

Modules

  1. MVCC: How Rows Become Visible — xmin, xmax, ctid, raw page contents and snapshots; why an UPDATE is an insert plus a delete.
  2. VACUUM, Autovacuum & Bloat — reading VACUUM VERBOSE, autovacuum thresholds, what blocks cleanup, and transaction-ID wraparound.
  3. Pages, TOAST, Fillfactor & HOT Updates — the 8 kB page, how big values are compressed and moved, and updates that skip the indexes.
  4. B-tree Indexes in Depth — scan types, column order, skip scan, expression, partial and covering indexes, and LIKE prefixes.
  5. GIN, GiST, BRIN & Hash Indexes — arrays, trigram search, nearest-neighbour, and tiny indexes for huge time-ordered tables.
  6. Reading EXPLAIN ANALYZE — estimates versus actuals, loops, buffers, join methods and disk spills.
  7. The Planner & Statistics — pg_stats, correlated columns and extended statistics, stale stats and generic plans.
  8. Locks, Deadlocks & SKIP LOCKED — the ALTER TABLE lock queue, deadlock detection, job queues and advisory locks.
  9. WAL, Checkpoints & Durability — LSNs, full-page images, checkpoint tuning, synchronous_commit and what fsync really guarantees.
  10. Project — Diagnose a Slow Database — pg_stat_statements, pgbench and index design applied to a 1 GB help-desk database.

After Level 2

Level 3 turns to PostgreSQL's advanced features — JSONB, full-text search, PL/pgSQL, triggers, partitioning and row-level security — with the internals knowledge you now have to judge their costs.