Second Edition, Updated (2026)

PostgreSQL 12–18 Coverage

New features introduced across PostgreSQL versions 12 through 18 are covered where they apply to the book's topics:

• GENERATED ALWAYS AS (expr) STORED — stored generated columnsPG 12
• MATERIALIZED / NOT MATERIALIZED CTE hintsPG 12
• Incremental sort — planner optimisation for partially-sorted inputPG 13
• SEARCH and CYCLE clauses in recursive CTEsPG 14
• EXCLUDE clause in window frame specificationsPG 14
• JSONB subscript syntax ['key']PG 14
• Multi-ranges and range_agg()PG 14
• NULLS NOT DISTINCT in unique constraints and indexesPG 15
• MERGE statement; WHEN NOT MATCHED BY SOURCEPG 15/17
• SQL/JSON constructors and JSON_TABLEPG 16/17
• Built-in C.UTF-8 collationPG 17
• MERGE RETURNING merge_action()PG 17
• Partition SPLIT PARTITION / MERGE PARTITIONSPG 17
• COPY … ON ERROR IGNORE, LOG_VERBOSITY, REJECT_LIMITPG 17/18
• GENERATED ALWAYS AS (expr) VIRTUAL — virtual generated columnsPG 18
• uuidv7() — time-ordered UUID generationPG 18
• RETURNING OLD / RETURNING NEW — before/after values in DMLPG 18

The book also expands coverage of existing features across all PostgreSQL versions: B-tree, GiST, SP-GiST, GIN, BRIN, and Bloom index internals; fillfactor and HOT updates; GROUPING() function; Row Level Security; and RANGE / GROUPS window frame modes.

New Extensions Coverage — pgvector and Citus

Part VIII (Extensions) gains two major additions:

• Vector search with pgvector — vector type, HNSW and IVFFlat index types, distance operators (<-> / <#> / <=>), and hybrid search combining vector similarity with relational filters
• Citus distributed architecture — coordinator, workers, shards, and reference tables
• pg_trgm — index rebuild requirement after upgrading to PG 18

56 New Figures

All 56 figures are new TikZ diagrams, generated from SQL or authored for this edition — not drawn by hand. They span every part of the book:

• Part II — psql session anatomy; B-tree, GiST, SP-GiST, GIN index page layouts
• Part III — select pipeline; river hop visualisations; neighbour reach map
• Part IV — numeric types; array layout; JSONB operators; multi-range timeline
• Part V — normalisation anomalies; materialized view refresh; validity ranges; partition timeline
• Part VI — three-valued logic truth tables; SQL isolation matrix; trigger timing; listen/notify flow
• Part VIII — IP range search; trigram similarity; Citus architecture; PostGIS map of Châteaux within 100 km of Paris

Fully Integrated Companion Lab

The lab is now a first-class part of the book. One command starts a complete PostgreSQL environment with all datasets pre-seeded:

docker compose up -d

Datasets include F1db, MoMA, Geonames, Last.fm, Wikidata castle ruins, London OSM, and more — every dataset used in the book. Open localhost:8042 to reach an interactive query pane organised by book chapter — every SQL query from the book is there, ready to run, edit, and experiment with. No account required beyond Docker. Learn more about the lab →

Prose and Presentation Overhaul

• AI-assisted typo and grammar pass across the full manuscript
• Six interview chapters reformatted: questions promoted to headings, answers in styled pull-quote blocks
• New appendix chapter: PostGIS SQL rendering pipeline for the Loire river basin, orthographic globe, and London pub k-NN map — TikZ and SVG variants shown side by side