PostgreSQL looks straightforward until you need to reason about MVCC, WAL, locks, and planner behavior. This book is valuable because it walks into that inner layer without turning it into mysticism.
In real work, it connects xmin/xmax, snapshots, buffer cache, index types, and crash recovery to concurrent correctness and operating cost.
In interviews and architecture discussions, this material is strongest when you need to show that you can do more than write SQL and can troubleshoot PostgreSQL at the system level.
Practical value of this chapter
MVCC visibility model
Understanding xmin/xmax and snapshot behavior helps design safe concurrent business operations.
WAL and checkpoint impact
Model write amplification, recovery time, and durability requirements when choosing topology and settings.
Planner and index strategy
Connect statistics, planner behavior, and index types to critical query paths before scale hits.
Interview root-cause reasoning
Demonstrate PostgreSQL troubleshooting at the system-mechanics level, not only at SQL syntax level.
Decision frame and editorial focus
Chapter focus
PostgreSQL MVCC, WAL, planner, and index behavior
Workload profile
Read through mechanism: write path, read path, isolation, recovery, replication, consensus, and concurrent behavior.
Good fit
The deep dive is useful when you need to explain not just which DBMS to choose, but why its latency, reliability, or limits exist.
Boundary and risk
Do not turn the chapter into a book recap: the value is the bridge from internals to system design and operations.
Connect next
Connect takeaways to concrete engine overviews, DDIA, replication/sharding, and performance diagnosis.
Official source
Postgres Pro
Free electronic edition of Egor Rogov's book on PostgreSQL internals.
PostgreSQL 17 from the Inside
Authors: Egor Rogov
Publisher: DMK Press, 2025
Length: 668 pages
Analysis of Egor Rogov's book on PostgreSQL internals: MVCC, WAL, buffer cache, locks, query planning, indexes, and crash recovery.
Why read this book?
You can run PostgreSQL as a black box until it trips. The internals matter at exactly the moment it does:
- Query performance - until you know how the planner reads statistics, speeding up a query stays guesswork rather than a plan
- Root-cause analysis - MVCC and lock behavior explain where stuck transactions and deadlocks come from, which otherwise look like random luck
- Architecture discussions - on a real DBMS, storage, indexing, and recovery trade-offs stop being slogans and get a price in I/O and time
- Index and partitioning choices - the same SQL behaves differently at thousands of rows and at millions of rows, and the internals show when the plan will flip
Related chapter
Database Internals
Book by Alex Petrov about B-Trees, LSM-Trees and general principles of DBMS design.
Book structure: 5 parts
Part 1: Isolation and multi-version concurrency
PostgreSQL is built around MVCC, a mechanism that lets ordinary reads and writes avoid waiting on each other in many common cases.
Key Concepts
- Transaction isolation levels
- Structure of pages and row versions (tuples)
- Consistent snapshots
- Version visibility rules
Cleanup and maintenance
- HOT updates that avoid touching indexes
- Vacuum and Autovacuum processes
- Freezing transaction IDs
- Rebuilding tables and indexes
MVCC: Multi-Versioning in PostgreSQL
Initial state
The row was created by transaction 100. xmin=100 means it is visible to all transactions with ID >= 100.
Heap (table storage)
Transactions (select to check visibility)
Part 2: Buffer cache and log
How PostgreSQL manages memory and makes committed changes durable through Write-Ahead Logging (WAL).
Shared buffer cache
- Shared buffers and their organization
- Clock sweep algorithm
- Buffer pins and reference counters
- Local buffers for temporary tables
WAL (Write-Ahead Log)
- Structure of WAL records
- Checkpoints and recovery
- Synchronization modes (fsync)
- Archiving and replication
Part 3: Locks
MVCC reduces many conflicts, but PostgreSQL still uses several lock families to protect data structures and consistency.
Object locks
- 8 table locking modes
- Compatibility matrix
- Advisory locks
Row locks
- FOR UPDATE / FOR SHARE
- Row-version locks
- Multixact for shared locks
Locks in memory
- Spinlocks
- Lightweight locks (LWLocks)
- Buffer pins
Part 4: Query execution
The path of a query, from SQL parsing to returned rows. At each stage the planner can misjudge an estimate, and then instead of index access you get a full scan of the table.
Processing stages
- Parsing → Rewriting → Planning
- Collection and use of statistics
- Planner cost model
- EXPLAIN ANALYZE in detail
Execution Operations
- Seq Scan vs Index Scan vs Bitmap Scan
- Nested Loop / Hash / Merge Join
- In-memory and disk-based sorting
- Aggregation and grouping
Part 5: Types of Indexes
There is no single best index here: each type wins on its own shape of data and queries, and on the wrong one it just adds write overhead with no read payoff.
Classic
- B-tree - universal, default
- Hash - only equality
Specialized
- GiST — geometry, full text
- SP-GiST - sparse data
- GIN - arrays, JSONB, FTS
For analytics
- BRIN — Block Range Index
- Ideal for time-series data
- Minimum index size
Key takeaways for system design
🔄MVCC and isolation
- Each transaction sees its own “snapshot” of data
- Old versions of rows are stored in the same table
- VACUUM removes “dead” versions
- Trade-off: high concurrency at the cost of table and index bloat
📝WAL and durability
- Changes are first written to the log, then to the data
- After a crash, PostgreSQL recovers from WAL
- Streaming replication is built on WAL flow
- Checkpoints balance recovery time and I/O pressure
🎯Index selection
- B-tree: ranges, sorting, uniqueness
- GIN: full text search, JSONB, arrays
- GiST: geodata, R-tree for coordinates
- BRIN: huge append-only tables (logs, metrics)
⚡Query optimization
- The planner chooses a path based on statistics
- ANALYZE updates table statistics
- Index-only scans avoid heap access when visibility data allows it
- Parallel queries for large tables
Related chapter
Guide to Databases
Tutorial from PostgreSQL - fundamentals of the relational model, SQL and DBMS architecture.
PostgreSQL vs other DBMS
| Aspect | PostgreSQL | MySQL (InnoDB) |
|---|---|---|
| MVCC | Heap versions, requires VACUUM | Undo log, automatic cleaning |
| Indexes | B-tree, Hash, GiST, GIN, BRIN, SP-GiST | B-tree, Full-text, Spatial |
| Replication | Streaming (physical), Logical | Async, Semi-sync, Group Replication |
| Extensibility | Extensions, custom types, operators | Plugins, storage engines |
About the author
Egor Rogov is a PostgreSQL expert at Postgres Professional, an author of PostgreSQL administration and development courses, and an active member of the PostgreSQL community. The book distills years of teaching, consulting, and explaining difficult mechanisms in approachable language.
Related chapters
- PostgreSQL: history and architecture - High-level PostgreSQL overview: evolution, architecture principles, and its role in the modern OLTP landscape.
- Database Internals: A Deep Dive (short summary) - Comparison of PostgreSQL internals with broader storage-engine, transaction, and recovery design principles.
- Replication and sharding - Operational continuation for HA and scale: replication topologies, read/write paths, and failover behavior.
- Database Selection Framework - How MVCC, WAL, and index internals can inform concrete DBMS selection decisions in production systems.
- Designing Data-Intensive Applications, 2nd Edition (short summary) - Theoretical context for transactions, consistency, and replication to better interpret PostgreSQL internals.
- Guide to Databases (short summary) - Foundational SQL and DBMS architecture track that complements this deeper PostgreSQL internals chapter.
Verdict
"PostgreSQL from the inside" is essential reading for engineers who work seriously with PostgreSQL. The book is free, written in Russian, and explains mechanisms that often remain a black box. It is especially useful when you want to discuss storage design through real trade-offs rather than surface-level SQL knowledge.