Internals lessons
Learn how PostgreSQL actually works — then watch it run
36 lessons on PostgreSQL internals. 16 cite the postgres/postgres source with verified file and line numbers, and 40 real psql transcripts were captured against a live PostgreSQL 17.10 lab. The first 5 lessons are fully free; the rest are free to read — prose, verified source refs, and the SQL — with their captured lab output unlocked by Pro.
Storage & row versions
MVCC Internals: How PostgreSQL Stores Row Versions
PostgreSQL never lets a reader block a writer or a writer block a reader for ordinary SELECT/INSERT/UPDATE/DELETE traffic. It achieves this with Multi-Version Concurrency Control (MVCC): instead of overwriting a row in place, every modification produces a new physical copy — a tuple version — and the system decides at read time which version each transaction is allowed to see.
Read the lesson →The Page Layout: How PostgreSQL Stores Rows on Disk
PostgreSQL storage is organised 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.
Read the lesson →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.
Read the lesson →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.
Read the lesson →HOT Updates: How PostgreSQL Avoids Index Writes
Because of MVCC, every UPDATE writes a new tuple version. Naively, that new version needs a new entry in every index on the table — even indexes on columns that did not change. On a wide, heavily-indexed, update-heavy table, that index maintenance dominates write cost and generates index bloat. HOT (Heap-Only Tuple) updates exist to avoid it.
Read the lesson →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 buff
Read the lesson →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.
Read the lesson →Buffers & memory
The 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.
Read the lesson →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 behaviour is why a seemingly modest work_mem can still exhaust RAM under load.
Read the lesson →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.
Read the lesson →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.
Read the lesson →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.
Read the lesson →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.
Read the lesson →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
Read the lesson →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/.
Read the lesson →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.
Read the lesson →Indexes
B-Tree Indexes: Structure and When They Win
A B-tree is the default and most versatile PostgreSQL index, implemented in src/backend/access/nbtree/. It supports equality and range operators (=, <, <=, >, >=, BETWEEN, IN), ORDER BY acceleration, and uniqueness enforcement — all from one balanced, ordered structure.
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 specialised index types for each.
Read the lesson →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.
Read the lesson →Concurrency & locking
The Lock Manager: How PostgreSQL Coordinates Concurrent Access
MVCC removes the need to lock for ordinary reads, but PostgreSQL still needs locks to coordinate DDL, conflicting writes, and access to shared memory structures. There are three distinct mechanisms, each for a different purpose:
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.
Read the lesson →Schema changes & migrations
Adding Columns and Constraints Without Downtime
The previous article showed why strong locks held for a long time are dangerous. The art of online schema change is converting an operation that scans or rewrites the table into one that only touches the catalog — or splitting it into a fast lock plus a slow, lock-light scan.
Read the lesson →Expand/Contract: The Pattern Behind Every Safe Migration
In a rolling deployment, old and new application code run simultaneously for a window. If a migration changes the schema in a way only the new code understands, the old code breaks — and if you deploy code first, it references columns that do not yet exist. The expand/contract pattern (also called parallel change) makes every step backward-compatible so old and new code coexist safely.
Read the lesson →Batched Backfills: Updating Millions of Rows Safely
Running UPDATE huge_table SET col = ... in a single statement looks simple but is operationally hostile:
Read the lesson →Blue/Green and Logical Replication Upgrades
PostgreSQL's on-disk format can change between major versions, so you cannot simply start a new binary on the old data directory. The traditional options each have downsides: pg_dump/restore is simple but slow and needs a long outage; pg_upgrade with hard links is fast but still requires stopping the database and is hard to roll back. Logical replication enables a blue/green upgrade with downtime
Read the lesson →Connections & pooling
Connection Overhead: Why Pooling Is Mandatory at Scale
PostgreSQL uses a process-per-connection model: the postmaster fork()s a dedicated backend for every client connection (src/backend/postmaster/postmaster.c). That backend lives until the client disconnects. This design is robust and simple, but it makes connections relatively expensive — unlike thread-per-connection databases, each PostgreSQL connection carries OS-process weight.
Read the lesson →Connection Routing for HA: VIPs, Pooler, and Read Scaling
Replication and automated failover handle the database side of high availability, but they are useless if clients keep connecting to a dead or demoted node. Connection routing is how applications always reach the current primary for writes (and optionally a standby for reads), and how that target switches atomically during failover. Getting this layer wrong reintroduces the split-brain and downtim
Read the lesson →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.
Read the lesson →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
Read the lesson →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 ret
Read the lesson →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 late
Read the lesson →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.
Read the lesson →pg_rewind: Reusing a Diverged Old Primary
When a standby is promoted to primary (after the old primary failed or was isolated), the cluster has a new leader on a new timeline. If the old primary comes back, it has diverged: it may contain transactions that were never replicated, and it is on the old timeline. You cannot simply reconnect it as a standby — its history conflicts with the new primary. Historically the only fix was a full pg_b
Read the lesson →Automatic Failover, Fencing, and Split-Brain
Streaming replication gives you standbys ready to take over, but deciding when to promote one and ensuring only one primary exists is the hard part. Doing it by hand is slow and error-prone. Tools like Patroni, repmgr, and Stolon automate failover — but automation introduces the cluster's most dangerous failure mode: split-brain.
Read the lesson →