PostgreSQL Deep Dive
by Sergey Okinchuk
Ch. 1

PostgreSQL Deep Dive

Welcome

Your application talks to Postgres all day, and nearly everything the server does with those statements happens where you cannot see it. This book is about that side of the connection: how PostgreSQL physically stores a row, why an UPDATE never overwrites one, what the planner is really doing when it ignores your index, and what it takes to run the thing in production — configuration, WAL and point-in-time recovery, replication, partitioning, roles, upgrades. One system, Cartwheel, carries every example from the first page to the last.

14 chapters 77 topics covered Knowledge check on every topic ≈19.5 hours to complete

About This Course

A row in Postgres is a version, not a slot. An UPDATE does not edit the row you are updating; it writes a brand-new version of it, marks the old one as expired at the current transaction id, and leaves that old version sitting on the page for every transaction that started earlier. One decision, and almost the whole engine falls out of it: snapshots and isolation levels, dead tuples and vacuum, why indexes must be maintained on updates that changed no indexed column, why disk usage grows while row counts do not, why replication ships a physical change log. This book states that idea on page one and then keeps cashing it in.

The second thing it returns to constantly is the cost model. The planner does not follow rules. It prices plans: estimated rows multiplied by per-page and per-tuple cost constants, and the cheapest one wins. So a "wrong plan" is almost always a wrong estimate rather than a stubborn planner, and the repair is to fix what the planner believes rather than to switch off the node it chose. By Chapter 9 you read a plan the way you would read an argument: which number was wrong, where it came from, and what the statistics would have to say for the plan to change.

Every example comes from one running system. Cartwheel is an online grocery-delivery service on a single Postgres box: eight tables, 40 million orders, 4 million delivery events a day, and a Saturday-morning peak of 3,000 orders a minute. Nadia owns that database for all fourteen chapters, as a developer in the first half and as the person carrying the pager in the second. Two failures open in Chapter 1 and are left deliberately unexplained: on the first Saturday of the month checkout goes from 40 ms to 6 seconds, and one morning two customers were both sold the last box of strawberries. The strawberries are settled in Chapter 6 and the Saturday in Chapter 9, each by the machinery the book has built by then rather than by a guess that happened to work.

Who This Is For

Engineers whose product runs on Postgres and who now need the engine underneath it. The bar is deliberately concrete: you can read a SELECT with a JOIN in it, and you are comfortable on a Linux shell, because everything here is driven from psql against self-managed Postgres on Debian. If most of your SQL is generated by an ORM rather than typed by you, the book is more use to you rather than less — dead tuples, lock queues and plan flips do not ask who wrote the statement, and the query the framework built is the one you will be reading in EXPLAIN. No internals knowledge is assumed: page layout, MVCC, vacuum, indexes and the planner are all built from zero.

It is not a first database course. If a JOIN is not yet something you can read, or if "primary key" is a term you would have to look up, read Databases for Beginners in this catalogue first and come back; that book covers tables, relationships and SQL with no engine named and nothing installed, which is exactly the ground this one refuses to re-cover. It is also not a survey of database engines: MySQL, Oracle, SQL Server and the distributed systems that speak the Postgres wire protocol appear only where the contrast explains why Postgres chose differently.

What You Should Already Know

  • Working SQL — SELECT, JOIN, GROUP BY, and what a transaction is for
  • A Linux shell — editing a config file, reading a log, running a command against a server you can break
  • What an index is for; the book starts at what a B-tree entry physically costs on every update
  • Nothing about Postgres internals — storage, MVCC, vacuum, the planner and WAL are built from the ground up

How the Course Is Built

The fourteen chapters run in three movements. The developer half (Chapters 1–4) is what an application engineer must know to use Postgres well: the process model and the catalogue, a type system where the column type is a contract rather than a label, constraints and migrations that ship without taking the site down, and the SQL that replaces three round trips with one query. The internals half (Chapters 5–9) is the core of the book: the 8 KB page opened up, MVCC and isolation, vacuum and bloat and freezing, the index types and what each one costs, and the planner. The operations half (Chapters 10–14) is running it for real: configuration and pooling, partitioning a 1.2-billion-row table, WAL and point-in-time recovery, replication and failover, roles and row-level security, upgrades, and a last honest look at managed Postgres.

The version canon is PostgreSQL 18, and a version number appears only where the version is the lesson — a default that changed, a feature that landed, behaviour you will hit on 17 and earlier. Every topic has the same shape: an opening that says what the thing is and why it exists, the mechanics with real psql sessions and real plans, the specific mistakes that cause real outages, the practices that prevent them, and a short knowledge check. Every mechanism gets a number, because the numbers are the difference between predicting Postgres and guessing at it.

A row is a version, not a slot
MVCC is introduced in Chapter 1 and never dropped. Snapshots, isolation levels, dead tuples, autovacuum, HOT updates, bloat, index maintenance and physical replication are all the same decision seen from different angles — which is why they stop needing to be memorized separately.
Estimates, not rules
The planner prices plans from statistics. Chapter 9 teaches the diagnostic order — find the lowest node where estimated and actual rows diverge, fix that estimate, and leave the plan to the planner. Hints are treated as what they are: a way to stop the bleeding, with a removal date.
One system, all the way through
Cartwheel's schema is fixed in Chapter 2, constrained in Chapter 3, explained in Chapters 5–9, tuned in Chapter 10, partitioned in Chapter 11, backed up in Chapter 12 and failed over in Chapter 13. The examples compound instead of resetting, and the two open failures get real answers.
Numbers over adjectives
8 KB pages. A 24-byte tuple header. TOAST at about 2 KB. Wraparound at roughly 2.1 billion transactions. An autovacuum scale factor of 0.2, which on 40 million rows means 8 million dead tuples before anything starts. Specific figures are what let you predict behaviour you have not seen yet.

Chapter Map

Chapter 1
The Postgres Model
Row versions, one process per connection, the three levels of namespace, and psql as a thin shell over the system catalogue — then Cartwheel, handed over with two failures nobody can explain yet.
Chapter 2
Types That Carry Meaning
The column type as a contract: int against bigint against numeric, collation and the sort order that moves under you, timestamptz, jsonb and when not to reach for it, arrays and ranges, identity columns and UUIDs.
Chapter 3
Schema Design and Evolution
Constraints that make a bad row impossible, views and materialized views, functions and triggers with their real costs — and widening orders.id from integer to bigint on a live 40-million-row table without downtime.
Chapter 4
SQL Beyond the Basics
Window functions, CTEs and recursion, INSERT … ON CONFLICT, LATERAL and DISTINCT ON and GROUPING SETS, RETURNING and data-modifying CTEs — each with what it costs when the planner meets it.
Chapter 5
How a Row Is Stored
The 8 KB page opened up: line pointers, the 24-byte tuple header, TOAST for values past about 2 KB, the free space and visibility maps, and which files on disk a table actually is.
Chapter 6
MVCC and Isolation
xmin, xmax and a row's lifetime; snapshots and what a transaction can see; the three isolation levels priced honestly; locks and deadlocks; and why an idle transaction is expensive. The strawberries are settled here.
Chapter 7
Vacuum, Bloat, and Freezing
Why dead tuples exist, what autovacuum does with the shipped defaults and when it silently cannot keep up, HOT updates and fillfactor, measuring bloat instead of guessing at it, and transaction ID wraparound.
Chapter 8
Indexes
B-tree and what an entry costs on every update, multicolumn and covering indexes, partial and expression indexes, GIN and GiST and BRIN, full-text and trigram search, and building indexes on a live table.
Chapter 9
The Planner and EXPLAIN
Reading a plan as an argument built from estimates: EXPLAIN with ANALYZE and BUFFERS, the scan nodes, the three join strategies, where statistics come from — and the Saturday slowdown, solved.
Chapter 10
Configuration and Performance
shared_buffers, work_mem and the OS cache; why 400 connections need a pooler in front of 200; pg_stat_statements and auto_explain; the statistics views worth watching; pgbench used honestly; a baseline that is not cargo cult.
Chapter 11
Big Tables and Partitioning
When a table is genuinely too big, declarative partitioning of 1.2 billion delivery events by month, how pruning decides what gets read, retention as a DETACH that takes milliseconds, and where one server stops being the answer.
Chapter 12
WAL, Backup, and Recovery
The write-ahead log, checkpoints and the cost of durability, what "committed" actually promises, pg_dump against a physical base backup, point-in-time recovery to the minute before the bad migration, and a restore you have tested.
Chapter 13
Replication and High Availability
Streaming replication and the replica that serves the dashboard, lag measured rather than assumed, synchronous replication as a durability dial, logical replication for what physical cannot do, and a planned failover with its numbers.
Chapter 14
Running Postgres in Production
Roles and privileges, authentication and encryption, row-level security instead of a shared password, the upgrade from 17 to 18, the extensions worth knowing, and an honest account of what managed Postgres gives you and takes away.

Disclaimer

This course is an independent educational project created and maintained by Sergey Okinchuk. It is provided for learning and reference purposes only.

No affiliation. This course is not affiliated with, sponsored by, endorsed by, or officially connected to any company, product, or project mentioned — including the PostgreSQL Global Development Group, the PostgreSQL Community Association of Canada, EDB, Amazon Web Services, Google, Microsoft, Oracle, Supabase, or Neon. All opinions, interpretations, and recommendations expressed are those of the author.

Trademarks. Product and project names referenced — including "PostgreSQL", "Postgres", "MySQL", "Oracle", "SQL Server", "Amazon RDS", "Cloud SQL", "PgBouncer", "Patroni", "TimescaleDB", and "PostGIS" — are the property of their respective owners. Use of these names is for identification and educational purposes only and does not imply any endorsement.

Not operational advice. This material teaches how PostgreSQL works and the practices that follow from it, not turnkey instructions for any specific environment. Configuration values, snippets, and recovery procedures are simplified for learning and sized for one fictional workload. Always consult the official documentation and test on a system you can afford to lose before changing a production database.

Accuracy and currency. PostgreSQL ships a major release every year and changes defaults with it. Facts in this course are written against PostgreSQL 18 and reflect the author's understanding at the time of writing; version-dependent behaviour drifts. Always verify against the official documentation for the version you actually run.

No warranty. This material is provided "as is" without warranty of any kind. The author accepts no liability for any loss or damage arising from reliance on the content.