Official source
Postgres Pro
Free electronic version of the book on the Postgres Professional website.
PostgreSQL 17 from the Inside
Authors: Egor Rogov
Publisher: DMK Press, 2025
Length: 668 pages
Analysis of Egor Rogov's book: MVCC, buffer cache, WAL, locks, query planner and PostgreSQL index types.
OriginalWhy read this book?
Understanding the internals of PostgreSQL is critical to:
- Performance optimizations - knowing how the query planner works helps you write effective SQL
- Troubleshooting - understanding MVCC and blocking allows you to diagnose concurrency problems
- System Design interview — deep knowledge of a real DBMS distinguishes the candidate from those who know only theory
- Architectural solutions — choosing the right index type or partitioning strategy
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-versioning
The foundation of PostgreSQL is the MVCC (Multi-Version Concurrency Control) mechanism, which allows readers and writers not to block each other.
Key Concepts
- Transaction isolation levels
- Structure of pages and row versions (tuples)
- Data snapshots
- Version visibility (visibility rules)
Data cleaning
- HOT updates (Heap-Only Tuples)
- Vacuum and Autovacuum processes
- Freezing transactions
- 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 provides durability through Write-Ahead Logging (WAL).
Buffer cache
- Shared buffers and their organization
- Clock sweep algorithm
- Buffer pinning and reference counting
- Local buffers for temporary tables
WAL (Write-Ahead Log)
- Structure of WAL records
- Checkpoint and recovery
- Synchronization modes (fsync)
- Archiving and replication
Part 3: Locks
Despite MVCC, PostgreSQL uses different types of locks to ensure consistency.
Object locks
- 8 table locking modes
- Compatibility Matrix
- Advisory locks
Row locks
- FOR UPDATE / FOR SHARE
- Tuple-level locks
- Multixact for shared locks
Locks in memory
- Spinlocks
- Lightweight locks (LWLocks)
- Buffer pins
Part 4: Running Queries
From SQL parsing to returning the result - the full cycle of query processing.
Processing stages
- Parsing → Rewriting → Planning
- Collection and use of statistics
- Cost model
- EXPLAIN ANALYZE in detail
Execution Operations
- Seq Scan vs Index Scan vs Bitmap Scan
- Nested Loop / Hash / Merge Join
- Sorting: quicksort vs external sort
- Aggregation and grouping
Part 5: Types of Indexes
PostgreSQL supports many types of indexes for different use cases.
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 Concepts for System Design
🔄MVCC and insulation
- 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 competition vs accumulation of bloat
📝WAL and durability
- Changes are first written to the log, then to the data
- In case of failure - recovery from WAL (crash recovery)
- Basis for replication (streaming replication)
- Checkpoint balances between recovery time and I/O
🎯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 scan avoids heap access
- 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 working at Postgres Professional. Author of courses on PostgreSQL administration and development, active participant in the PostgreSQL community. The book is based on many years of teaching and consulting experience.
Where to find the book
Verdict
"PostgreSQL from the inside" is a must-read for everyone who seriously works with PostgreSQL. The book is free, written in Russian and provides a deep understanding of mechanisms that usually remain a “black box”. It is especially valuable for those who are preparing for a System Design interview and want to demonstrate not superficial knowledge, but an understanding of real trade-offs in the design of data storage systems.