Lessons
How the engine works
37 sessions, one mechanism each. See it move, then the lab. This is the only engine course, not a second “internals” track.
Lessons = understand the machine. Interview = say it out loud. Pathways = which lesson next (including Deep Internals as an order, not a new catalog).
Start here
5 free end to end, one per major track
Storage & row versions
MVCC Internals: How PostgreSQL Stores Row Versions
Read it free →Vacuum & bloat
Autovacuum Internals: How Dead Tuples Are Reclaimed
Read it free →Query planning & execution
Reading EXPLAIN: How the Planner Describes a Query
Read it free →Indexes
B-Tree Indexes: Ordered Leaves, Right-Links, and Index-Only Scans
Read it free →Concurrency & locking
Lock Manager: How PostgreSQL Coordinates Concurrent Access
Read it free →Every lesson
32 Pro · 33 source-grounded
Storage & row versions
MVCC Internals: How PostgreSQL Stores Row Versions
For ordinary non-locking SELECT/INSERT/UPDATE/DELETE, PostgreSQL readers do not block writers and writers do not block readers. Multi-Version Concurrency Control (MVCC) does that by writing a new physical tuple version on each change and deciding at read time which version each snapshot may see. Row locks, unique conflicts, and DDL still use the lock manager, MVCC is not “never wait for anything.”
Read the lesson →Page Layout: How PostgreSQL Stores Rows on Disk
PostgreSQL storage is organized into fixed-size blocks, 8KB by default (BLCKSZ). A table (the "main fork") is an array of these blocks in one or more 1GB segment files under the database directory. Every block, heap or index, shares the same general layout, defined in src/include/storage/bufpage.h.
See what the 7 sections cover →Snapshots and Visibility: How PostgreSQL Decides What You See
MVCC stores many versions of each row; a snapshot is the rule that selects which versions a statement or transaction may see. Conceptually it captures "which transactions had committed at the instant I started looking." The struct is SnapshotData in src/include/utils/snapshot.h, built by GetSnapshotData() in src/backend/storage/ipc/procarray.c.
See what the 10 sections cover →TOAST: How PostgreSQL Stores Oversized Values
A tuple must fit within a single 8KB page, PostgreSQL does not span a row across blocks. So how does a text column hold a megabyte? The answer is TOAST (The Oversized-Attribute Storage Technique), implemented in src/backend/access/common/toast_internals.c and detoast.c. When a row would be too big, TOAST compresses and/or moves large field values out of the main tuple.
See what the 8 sections cover →HOT Updates: Same-Page Versions Without Index Writes
Because of MVCC, every UPDATE writes a new heap tuple version. A normal update also inserts a new entry in every index, even indexes on columns that did not change. HOT (Heap-Only Tuple) is the optimization that skips those index inserts when the new version fits on the same heap page and no hot-blocking index column changed. Cleanup of dead HOT versions is a separate step: opportunistic page pruning (and VACUUM), which is also where LP_REDIRECT line pointers appear.
See what the 9 sections cover →Write-ahead log & durability
Write-Ahead Logging: How WAL Guarantees Durability
A transaction is durable when, after COMMIT returns, its effects survive a crash. Flushing every changed data page to disk at commit would be ruinously slow and random. PostgreSQL instead uses Write-Ahead Logging (WAL): before any change reaches a data file, a compact, sequential description of that change is written and flushed to the WAL. The rule (the "write-ahead" rule) is enforced in the buffer manager and is the foundation of crash recovery.
See what the 9 sections cover →WAL Archiving and Point-in-Time Recovery
Replication protects against hardware failure, but it faithfully copies mistakes, a DROP TABLE or bad UPDATE replicates instantly to every standby. Point-in-Time Recovery (PITR) protects against logical errors by letting you restore the database to any moment in the past: the instant before the mistake. It combines a base backup with the continuous archive of WAL.
See what the 7 sections cover →Buffers & memory
Buffer Cache: How shared_buffers and Clock-Sweep Work
shared_buffers is a fixed-size array of 8KB page frames allocated in shared memory at startup. Every backend reads and writes data through this cache; a page on disk must be loaded into a buffer before it can be examined or modified. The machinery lives in src/backend/storage/buffer/, principally bufmgr.c and freelist.c.
See what the 10 sections cover →work_mem and Spills: Where Sorts and Hashes Go to Disk
work_mem is the memory budget for a single sort, hash, or similar operation, not per query and not per connection. A complex query can have many such operations running at once, each entitled to its own work_mem. With parallel workers, each worker also gets its own allotment. This multiplicative behavior is why a seemingly modest work_mem can still exhaust RAM under load.
See what the 9 sections cover →Vacuum & bloat
Autovacuum Internals: How Dead Tuples Are Reclaimed
Because MVCC leaves dead tuples behind (see the MVCC article), something must reclaim that space and, just as importantly, advance the cluster's frozen-XID horizon to prevent transaction-ID wraparound. Autovacuum is the background subsystem that does both. Its code spans src/backend/postmaster/autovacuum.c (scheduling) and src/backend/access/heap/vacuumlazy.c (the actual work).
Read the lesson →Online VACUUM, REINDEX, and Table Rewrites
Over time tables and indexes accumulate bloat, dead tuples and empty space left by MVCC. There are two fundamentally different ways to deal with it: reclaiming space for reuse within the object (online, cheap) versus physically shrinking the object and returning space to the OS (expensive, traditionally blocking). Choosing the wrong one causes outages.
See what the 8 sections cover →Query planning & execution
Reading EXPLAIN: How the Planner Describes a Query
The PostgreSQL executor runs a tree of plan nodes, each pulling rows from its children on demand (the Volcano/iterator model, src/backend/executor/). EXPLAIN prints that tree. The node at the top produces the final result; leaves are scans of tables or indexes. Reading a plan means reading this tree from the inside out.
Read the lesson →How the Cost-Based Optimizer Chooses a Plan
For any non-trivial query there are many ways to get the answer, different scan methods, join orders, and join algorithms. The planner/optimizer (src/backend/optimizer/) enumerates candidate paths, estimates the cost of each, and picks the cheapest. It does not try every possibility exhaustively; it prunes aggressively.
See what the 9 sections cover →Statistics and Selectivity: Why the Planner Guesses Wrong
The optimizer chooses plans by estimating how many rows each operation produces. Those estimates come entirely from statistics gathered by ANALYZE into the pg_statistic catalog (human-readable via pg_stats). Bad statistics → bad estimates → bad plans. Almost every "mysteriously slow query" traces back here.
See what the 9 sections cover →Parallel Query: How PostgreSQL Splits Work Across Workers
Historically each PostgreSQL query ran in a single backend process. Parallel query lets the planner split eligible work across multiple worker processes that execute parts of the plan concurrently, then combine results. The machinery lives in src/backend/executor/nodeGather.c, execParallel.c, and the parallel-aware scan nodes.
See what the 9 sections cover →Bitmap Scans: How PostgreSQL Combines Indexes
A plain index scan is great for high selectivity (few rows) and a sequential scan is great for low selectivity (most rows). In between, say 1-15% of a table, both are suboptimal: the index scan jumps around the heap in random order, while the seq scan reads far too much. The bitmap scan fills this gap and is implemented in src/backend/executor/nodeBitmapHeapScan.c and the bitmap index scan nodes.
See what the 8 sections cover →Partitioning: How Declarative Partitioning Prunes Work
A partitioned table is one logical table split into many physical child tables by a partition key. The win is not magic speed on every query, it is the ability to skip entire partitions (pruning), to drop old data instantly (DROP/DETACH a partition instead of a massive DELETE), and to vacuum and index partitions independently. Declarative partitioning lives in src/backend/partitioning/.
See what the 8 sections cover →Caching Plans: Prepared Statements and the Generic Plan
Every SQL statement goes through parse → analyze → rewrite → plan → execute. For repeated queries that differ only in parameter values, redoing parse and plan each time is wasted work. Prepared statements cache the parsed and (sometimes) planned form so subsequent executions skip that overhead. The logic lives in src/backend/utils/cache/plancache.c.
See what the 8 sections cover →Query Executor: From Plan Tree to Tuples
After the planner chooses a Plan tree, the executor builds a parallel PlanState tree, initializes nodes (ExecInitNode), and pulls tuples by calling each node's ExecProcNode method, usually starting at ExecProcNodeFirst. EXPLAIN ANALYZE times that pull loop. This lesson is the missing link between “the plan looks right” and “what the backend actually runs.”
See what the 9 sections cover →Indexes
B-Tree Indexes: Ordered Leaves, Right-Links, and Index-Only Scans
PostgreSQL’s default index is nbtree, a Lehman-Yao B-tree in src/backend/access/nbtree/. Sorted leaf order gives equality, ranges, ORDER BY, and uniqueness. High keys and right-links keep searches correct under concurrent splits. Index-only scans still consult the heap visibility map; without all-visible pages the executor falls back to heap fetches (or a different plan).
Read the lesson →Beyond B-Tree: GIN, GiST, BRIN, and Hash Indexes
B-tree assumes a total ordering of scalar keys. Many workloads do not fit that model, full-text search, JSON containment, geometric overlap, or simply tables too large to index conventionally. PostgreSQL's extensible access-method framework provides specialized index types for each.
See what the 8 sections cover →CREATE INDEX CONCURRENTLY: Building Indexes Online
An ordinary CREATE INDEX takes a SHARE lock on the table for its entire duration. That lock permits reads but blocks all writes, every INSERT, UPDATE, and DELETE waits until the index finishes building, which on a large table can be many minutes. CREATE INDEX CONCURRENTLY (CIC) exists to build the index without blocking writes.
See what the 9 sections cover →Concurrency & locking
Lock Manager: How PostgreSQL Coordinates Concurrent Access
Readers and writers do not block each other, but DDL blocks everyone. The whole art of safe operations comes from one fact in the conflict table: ACCESS EXCLUSIVE (taken by most ALTER TABLE) collides with even a plain SELECT, so a careless schema change can freeze the entire application.
Read the lesson →DDL Locking: Why ALTER TABLE Can Freeze Your App
Most forms of ALTER TABLE take an ACCESS EXCLUSIVE lock, the strongest table lock, conflicting with every other lock mode including the ACCESS SHARE that a plain SELECT takes. While held, no session can even read the table. On a busy table this turns a "quick schema change" into a full outage.
See what the 6 sections cover →Connections & pooling
Replication & HA
Streaming Replication: walsender and walreceiver
Streaming replication keeps a standby byte-for-byte identical to its primary by shipping the primary's WAL stream and replaying it continuously. Because it replays the same physical changes, the standby is an exact copy, same data files, same block contents, which is why it requires the same major version and architecture.
See what the 8 sections cover →Logical Replication and Logical Decoding
Where physical replication ships raw WAL blocks, logical replication ships logical row changes, "insert this row", "update that row", reconstructed from the WAL by logical decoding. Because it operates at the row level via SQL, publisher and subscriber can differ in major version, architecture, and even schema, and you can replicate a subset of tables. This flexibility powers upgrades, selective data distribution, and integration pipelines.
See what the 9 sections cover →Replication Slots: Guaranteeing WAL Retention
A replica (physical standby or logical subscriber) consumes WAL from the primary. If the primary recycles WAL before the replica has read it, the replica falls irrecoverably behind and must be rebuilt. The old wal_keep_size approach, keep a fixed amount of WAL and hope it's enough, is fragile. Replication slots solve this precisely by tracking exactly how far each consumer has progressed and retaining WAL until then.
See what the 6 sections cover →Synchronous Replication and Quorum Commit
By default PostgreSQL replication is asynchronous: a primary commits once its own WAL is durable, without waiting for any standby. If the primary then fails before a standby received the latest WAL, those last transactions are lost. Synchronous replication makes the primary wait for standby confirmation before reporting commit success, eliminating that data-loss window, at the cost of commit latency.
See what the 4 sections cover →Hot Standby and Recovery Conflicts
Hot standby lets a streaming standby serve read-only queries while it continuously replays WAL from the primary. This is how PostgreSQL scales reads: route SELECT traffic to one or more standbys. But it creates a tension that does not exist on the primary, the standby is doing two things at once: replaying changes and answering queries against the data those changes are modifying.
See what the 6 sections cover →pg_rewind: Reusing a Diverged Old Primary
pg_rewind is a frontend utility (src/bin/pg_rewind), not backend postmaster code: it reuses an old primary’s data directory by copying blocks that diverged after the timeline fork, using the old primary’s WAL as the map. Still source-grounded, just not inside the server process.
See what the 7 sections cover →Schema changes & migrations (engine locks + patterns)
Schema changes & migrations (ops pattern)
Expand/Contract: The Pattern Behind Every Safe Migration
Expand/contract is an application deployment pattern (dual-write or dual-read windows), not a single PostgreSQL subsystem. Use it with the lock and rewrite rules from the DDL and column lessons. No backend source chip, the value is the phased change sequence.
See what the 8 sections cover →Batched Backfills: Updating Millions of Rows Safely
Batched backfills keep transactions short so vacuum and HOT can keep up, and so locks and replication lag stay bounded. Pattern-level guidance; pair with MVCC, vacuum, and lock lessons for the engine why.
See what the 6 sections cover →Blue/Green and Logical Replication Upgrades
Blue/green and major-version cutovers often use logical replication as the data plane. Engine grounding is logical decoding and subscriptions; fencing and traffic cutover remain orchestration outside the backend.
See what the 7 sections cover →