13 parts · 32 chapters
PostgreSQL Internals
PostgreSQL makes a different bet from MySQL at almost every layer: a heap of tuple versions instead of a clustered index, a process per connection instead of a thread, and extensibility as a first principle. This course opens each layer the same way the MySQL course does, so the two can be compared chapter by chapter.
Thirteen parts: history and philosophy; process architecture and pooling; storage layout down to the byte; MVCC, vacuum and wraparound; every index type; WAL, checkpoints and recovery; the planner; types and extensions; replication and CDC; partitioning; operating Postgres; a capstone that builds an extension, an FDW and a decoding plugin; and expert mode.
storage8 KB pages, line pointers, tuple headers, TOAST, the FSM and VM, HOT updates.
MVCCxmin and xmax, snapshots, bloat, vacuum, autovacuum and transaction ID wraparound.
indexesB-tree, hash, GIN, GiST, SP-GiST, BRIN, bloom, partial, expression and covering.
WALRecords, LSNs, full page writes, checkpoints, synchronous_commit, PITR, pg_rewind.
plannerStatistics, costs, scans, joins, parallel query, JIT and EXPLAIN (ANALYZE, BUFFERS).
replicationStreaming, slots, synchronous quorum, logical replication, CDC and failover.
00
History and Philosophy
Ingres to PostgreSQL, and the extensibility thesis
1 ch · ~8 min01Process Architecture
Postmaster, backends and background workers · Connection pooling as a requirement
2 ch · ~12 min02Storage Layout
Files, forks and the 8 KB page · Tuple headers, TOAST, FSM and VM · Fillfactor and HOT updates
3 ch · ~18 min03MVCC, Vacuum and the Postgres Bargain
Snapshots, visibility and the cost of UPDATE · Bloat, VACUUM and autovacuum · Wraparound, and what blocks vacuum
3 ch · ~18 min04Indexes
Access methods, one per kind of data · Index-only scans, partial, expression and covering indexes · Index bloat and rebuilding online
3 ch · ~18 min05WAL, Checkpoints and Recovery
WAL records, LSNs and the commit path · Checkpoints and synchronous_commit · Crash recovery, PITR and pg_rewind
3 ch · ~18 min06The Planner
Statistics and costs · Scan and join nodes · Reading EXPLAIN, and plan instability
3 ch · ~18 min07Types, Extensions and Extensibility
Types, domains, ranges and JSONB · Writing an extension, FDWs and decoding plugins · The extensions worth knowing, and PostGIS
3 ch · ~18 min08Replication
Streaming replication, slots and synchronous commit · Logical replication and CDC · Failover: Patroni, repmgr and consensus
3 ch · ~18 min09Partitioning and Large Data
Declarative partitioning and pruning
1 ch · ~8 min10Operating Postgres
Configuration and the stat views · Locks and safe migrations · Backups and upgrades
3 ch · ~18 min11Build: Extension, FDW and Decoding Plugin
A custom type with operators and B-tree support · An FDW and a decoding plugin
2 ch · ~12 min12Expert Mode
Postgres vs MySQL, both directions · War stories, and where Postgres is going
2 ch · ~12 minBuilt on MySQL and SQLThis course mirrors MySQL Internals part by part and assumes SQL, The Language. Formal Database Theory and Scaling Databases build on it.