Guided track
PostgreSQL Deep Internals
Engineers preparing for engine-level PostgreSQL interviews and self-driven source study, storage, planner, WAL, replication, and how to reason from the official manual plus postgres/postgres source.
Module 1
How to study here
- ReferenceLessons: the engine course (mechanism end-to-end)
- ReferenceConcepts: single-mechanism out-loud drills (manual → source → proof)
- ReferencePanel questions: checklist + signal notes
- ReferenceThis pathway: one roadmap for deep internals
You know course vs drill vs panel and will not skip the out-loud step.
Module 2
Storage and MVCC
- LessonFreeMVCC internals: how rows are versioned
- ConceptMVCC snapshot visibility, drill
- LessonProThe page layout: how rows sit on disk
- ConceptCTID and line pointers, drill
- LessonProSnapshots, visibility, and isolation levels
- ConceptIsolation anomalies and SSI, drill
- QuestionPanel: REPEATABLE READ vs SERIALIZABLE
- LessonProHOT updates: avoiding index writes
- ConceptHOT updates and page pruning, drill
- LessonProTOAST: oversized values
- ConceptTOAST storage, drill
- ConceptHint bits, drill
- ConceptCLOG commit status, drill
You can explain what UPDATE does on disk, why VACUUM cannot remove a still-visible row, and what a snapshot is.
Module 3
Vacuum, freeze, and bloat
- LessonFreeAutovacuum internals: dead tuples
- ConceptAutovacuum thresholds, drill
- QuestionPanel: how does VACUUM relate to MVCC?
- LessonProOnline VACUUM, REINDEX, and rewrites
- ConceptFree space map, drill
- ConceptIndex LP_DEAD pruning, drill
- ConceptTuple freezing and wraparound, drill
- QuestionPanel: wraparound from the committer seat
- ConceptMultixact shared row locks, drill
- QuestionPanel: a multixact scenario, go deep
Wraparound and multixact from the committer seat, not only autovacuum GUCs.
Module 4
Buffer cache and memory
- LessonProBuffer cache and clock-sweep
- ConceptClock-sweep eviction, drill
- ConceptBufferAccessStrategy rings, drill
- LessonProwork_mem: sorts, hashes, spills
- ConceptTuplesort spill, drill
- ConceptHashAgg spill, drill
You connect shared_buffers eviction and work_mem spills to observed waits and EXPLAIN.
Module 5
Query path: ANALYZE → planner → EXPLAIN → executor
- ConceptANALYZE from source, drill
- LessonProStatistics and selectivity estimation
- ConceptExtended statistics, drill
- QuestionPanel: how ANALYZE builds the stats a plan uses
- QuestionPanel: ANALYZE on a 50TB table
- LessonProCost-based optimizer
- ConceptJoin algorithms, drill
- LessonFreeReading EXPLAIN plans
- ConceptRead EXPLAIN like a pro, drill
- LessonProQuery executor pipeline
- ConceptExecutor PlanState tree, drill
- LessonProBitmap scans and index combination
- ConceptVisibility map and index-only scans, drill
- LessonProPrepared statements and plan caching
- ConceptGeneric vs custom plans, drill
- LessonProParallel query execution
- ConceptParallel query, drill
- LessonProPartitioning and pruning
- ConceptPartition pruning, drill
Full query path: ANALYZE samples into pg_statistic → planner costs a Plan → EXPLAIN prints it → executor PlanState pull runs it. Wrong-plan tree: stats vs cost vs generic plan vs parallel vs partition pruning vs executor shape (loops).
Module 6
Indexes
- LessonFreeB-tree index internals
- ConceptB-tree page anatomy, drill
- ConceptB-tree page split, drill
- ConceptB-tree deduplication, drill
- LessonProGIN, GiST, BRIN, and hash
- LessonProCREATE INDEX CONCURRENTLY
B-tree page life cycle and when other access methods win.
Module 7
Locks and concurrency
- LessonFreeLock manager internals
- ConceptLock manager, drill
- LessonProDDL locking and ALTER TABLE
- ErrorDeadlock detected, SQLSTATE 40P01
- ErrorSerialization failure, SQLSTATE 40001
Heavyweight locks vs row locks vs SSI aborts, and what you capture first.
Module 8
WAL and durability
- LessonProWAL internals and durability
- ConceptWAL records, drill
- ConceptFull-page images, drill
- LessonProWAL archiving and PITR
Durability contract from WAL record to archive/PITR.
Module 9
Replication engine
- LessonProStreaming replication internals
- LessonProReplication slots
- LessonProLogical replication and decoding
- LessonProSynchronous replication and quorum
- LessonProHot standby and recovery conflicts
- LessonPropg_rewind internals
Physical vs logical paths, slots, conflicts, and rewind, engine view. Failover fencing is HA orchestration (separate lesson), not backend source.
Module 10
Panel capstone loop
- QuestionHow does VACUUM relate to MVCC?
- QuestionReset one table's stats, not the whole cluster
- QuestionREPEATABLE READ vs SERIALIZABLE
- QuestionHow ANALYZE builds the stats a plan uses
- QuestionA multixact scenario, go deep
- QuestionReal tuple count beyond live and dead
- QuestionDriving a bug upstream to pgsql-hackers
- QuestionANALYZE on a 50TB table
- QuestionTransaction wraparound from the committer seat
- QuestionJustify a new observability signal
- QuestionOpen with the worst incident you owned
Checklist self-score ≥ 80% cold on every panel question before you call yourself ready.
What you will be able to do afterwards
- • Explain MVCC, vacuum/freeze, buffers, and WAL from first principles with source anchors.
- • Walk the query path ANALYZE → statistics → cost-based planner → EXPLAIN, and debug wrong plans from row estimates.
- • Reason about locks, indexes, and streaming/logical replication internals under interview pressure.
- • Study with a fixed loop: manual → cited source → lab/proof → answer the concept out loud before reveal.