Postgres Internals Tour: Processes, Buffer Pool, WAL, and MVCC
Postgres is a small set of mechanisms that compose into everything you recognize. We tour the core pieces: the process model (backend, autovacuum, checkpointer, WAL writer), the shared buffer pool and eviction, the write-ahead log as the durability backbone, and MVCC as the concurrency engine. Each mechanism is traced with real output — pg_stat_activity during a workload, buffer cache hits, checkpoint activity, dead tuples after an update — so the tour reads like a walkthrough, not a slide deck. The goal: when Postgres misbehaves, you know which of the five mechanisms to interrogate.
Topics covered:
- Process architecture: who does what
- Buffer pool, eviction, and cache hits
- WAL: checkpoints, fsync, and durability
- MVCC, dead tuples, and autovacuum
Related articles
Why Your Query Is Slow (And It's Not the Index)
Buffer pool misses, WAL contention, lock waits, and connection pooling — the non-index causes of database slowness.
MVCC and the Multiversion House of Cards
Snapshot isolation, visibility rules, and vacuum — how databases maintain consistency without locking readers.
Write-Ahead Logging and Crash Recovery
WAL, checkpointing, and ARIES recovery — how databases guarantee durability without fsyncing every row.
More in Databases
B-Trees: The Shape of Databases
Why every major database is a tree shaped like a disk page — and how to read your index's health from its shape.
WatchSQL Joins and Execution Plans: Nested Loop, Hash, and Merge
How the database executes joins — nested loop, hash join, and merge join — and how to read execution plans to see which strategy your query gets.
DetailsConnection Pools, Database-Side: What a Connection Really Costs
What actually happens to your database when connections pile up — the connection lifecycle, pool sizing math, and why max_connections is not a tuning knob.
DetailsStorage Engines: LSM-Trees vs. B-Trees
LSM-trees vs. B-trees — how each storage engine writes, compacts, and reads, and what that means for write and read amplification in your workload.
DetailsSharding Strategies: Partition Keys, Distribution, and Rebalancing
How sharding actually works — partition keys, data placement, cross-shard queries, and the operational reality of splitting one database into many.
DetailsReplication Explained: WAL Shipping, Lag, and Failover
How database replication actually works — the transaction log, the lag, and the failure modes of synchronous and asynchronous replication in production.
DetailsTransactions and Isolation Levels: ACID, MVCC, and Anomalies
What transactions actually guarantee — ACID mechanics, MVCC, and the real behavior behind each isolation level, demonstrated with concrete anomalies.
DetailsDatabase Indexes Visualized: B-Trees, Covering Indexes, and Planner Decisions
How database indexes actually work — B-trees, hash indexes, covering indexes, and when the planner will or won't use the index you made.
DetailsQuery Optimizer Internals: From Parse Tree to Execution Plan
What happens inside a query optimizer — parse, rewrite, join ordering, cost models, and how the planner decides the plan your query gets.
DetailsDepth, delivered weekly
One technical dispatch a week — articles and episode notes before they go public.
One technical dispatch per week. No noise.