Reading EXPLAIN ANALYZE, indexes (B-tree, partial, covering, GIN/GiST), join algorithms and the planner, MVCC and isolation with real anomaly demos, locking and deadlocks, VACUUM, and partitioning — measured against PostgreSQL 18.
EXPLAIN prints the plan the database will run — a tree of scans, joins, and sorts read from the bottom up, each node tagged with an estimated cost. What the numbers mean, and why a full-table scan is sometimes the right answer. Run against PostgreSQL 18.
EXPLAIN ANALYZE actually runs the query and prints what happened — real timings, real row counts next to the estimates, loop counts, and the buffer hits and reads that tell cache from disk. How to read the gap between guess and truth. Run against PostgreSQL 18.
The B-tree index, shown before and after: a selective lookup as a full-table scan, then the same query as an index scan once the index exists, with the real timing drop. When an index earns its keep, when the planner ignores it, and what it costs you. Run against PostgreSQL 18.
One B-tree over several columns and the leftmost-prefix rule that decides which queries it serves; INCLUDE columns that turn a lookup into an index-only scan; and partial indexes that cover just the rows you query. Each shown as a real plan. Run against PostgreSQL 18.
When a value has many keys inside it — the tags in a JSONB document, the words in a text field — a B-tree can't help. GIN indexes those; GiST indexes ranges and geometry; BRIN indexes huge ordered tables for almost nothing. Each shown turning a scan into an index scan. Run against PostgreSQL 18.
Nested loop, hash, and merge — the three algorithms a database has for joining two tables, why the planner picks one over another, and how to watch it switch. Run against PostgreSQL 18.
Where a query planner gets its row-count estimates — ANALYZE, pg_stats, histograms and most-common-values — how stale statistics wreck a plan, and how CREATE STATISTICS fixes the correlated-column blind spot. Run against PostgreSQL 18.
How Postgres lets a long-running reader and a live writer touch the same row without ever waiting on each other — an UPDATE writes a new version, xmin and xmax track visibility, and the price is dead tuples. Run against PostgreSQL 18.
Read committed, repeatable read, and serializable — the three isolation levels, demonstrated with real two-session runs that produce a non-repeatable read, a phantom, and write skew, plus the serialization failure that stops it. Run against PostgreSQL 18.
How row locks make concurrent writes safe, why a second UPDATE waits instead of corrupting data, the deadlock you cause by accident, and the one-line habit that prevents it. Run against PostgreSQL 18.
Why an UPDATE doesn't overwrite, how dead tuples pile up and grow your tables, reading n_dead_tup, and what VACUUM versus VACUUM FULL actually reclaim. Run against PostgreSQL 18.
Splitting a big table into physical partitions by range, how the planner prunes the pieces it doesn't need, and why dropping a month of data becomes instant. Run against PostgreSQL 18.
Precomputing an expensive aggregate with a materialized view, refreshing it without downtime, and the everyday query mistakes that silently disable your indexes. Run against PostgreSQL 18.