Published on Aug 9, 2026

Notes from The Art of PostgreSQL

15 min read·
BooksPostgreSQLSQL

Dimitri Fontaine's The Art of PostgreSQL makes the case that SQL is the most underused tool in a developer's toolkit. The book is aimed at application developers who use PostgreSQL through an ORM and argues you're missing much of what SQL offers if you only reach for it when the ORM falls short. These are some notes I took.

Part I - Preface

The book's premise is that most developers only use a fraction of what SQL/PostgreSQL can do, and don't know how to integrate raw SQL into a modern workflow (versioning, testing, review, deployment).

Central argument, in Dijkstra's terms: count "lines of code" as lines spent, not produced - writing more in SQL means writing (and maintaining) less overall.

Part II - Introduction

Ch 1 - Structured Query Language

SQL is declarative: you describe the desired result set, not the steps to get there; the model must be statically typed before the query runs.

SQL injection is really fixed by keeping query text and parameters separate at the database level, not by escaping strings yourself — PREPARE/EXECUTE and asyncpg's $1-style parameters show how.

generate_series() + LEFT JOIN + coalesce() can fill in missing rows (e.g. gaps in a date range) in one query, replacing what would otherwise be a manual loop in application code.

Ch 2 - Software Architecture

Think of PostgreSQL as a concurrent data-access service rather than just a storage layer, with SQL as its API.

ACID recap:

Why PostgreSQL:

Part III - Writing SQL Queries

Ch 4 - Business Logic

Every query already embeds business logic - the real question is how much logic belongs in SQL vs. app code.

Multi-query app-side logic risks reading across different snapshots (read-committed default) - concurrent writes between queries can silently corrupt results; a single SQL query is immune (one snapshot).

Network round-trips dominate cost as queries get cheap - 5 round trips vs. 1 well-written query matters more than server CPU time.

Prefer plain-SQL stored procedures (language sql) over PL/pgSQL; only reach for procedural code when set-based SQL genuinely can't express the logic.

Ch 5 - A Small Application

Keep queries in version-controlled .sql files instead of embedded strings - directly runnable in psql, diffable, reviewable like any other code.

Ch 6 - The SQL REPL (psql)

Recommends a tuned ~/.psqlrc (\x auto, ON_ERROR_ROLLBACK interactive, unicode borders, prompt showing transaction state) - but disable it for scripts/reports (--no-psqlrc).

psql doubles as a reporting tool (--tuples-only, -P format=html, -f script.sql); \set ECHO_HIDDEN true reveals the SQL behind backslash commands.

Ch 7 - SQL is Code

SQL deserves the same discipline as any code: consistent style, meaningful aliases, comments explaining why, version control, tests.

Avoid order by <column number>, pre-ANSI comma-joins, and natural join (silently changes semantics on schema drift).

Recommends pgTAP (pg_prove) for unit tests and RegreSQL for regression testing of .sql files.

Set application_name per module to trace production queries back to source.

Ch 8 - Indexing Strategy

Two reasons to index: (1) required to back UNIQUE/PRIMARY KEY/EXCLUDE constraints, (2) query performance - everything else is a deliberate trade-off (indexes cost write-side maintenance).

Access method cheat sheet:

Workflow: pg_stat_statements to find costly queries -> EXPLAIN (analyze, verbose, buffers) to spot seq scans and estimate/actual mismatches.

Ch 9 - Interview: Yohann Gabory

The database, not the framework, should enforce consistency; use the ORM for simple queries but always be able to inspect/override its generated SQL.

Rejects database-agnosticism as impractical - developing on SQLite and deploying on PostgreSQL means you never get to use PostgreSQL's real capabilities.

Part IV - SQL Toolbox

Ch 10-12 - Data, SQL, Statement Categories

SQL statements fall into four categories: changing data (INSERT/UPDATE/DELETE), changing schema (CREATE/ALTER/DROP), managing transactions (BEGIN/COMMIT/ROLLBACK), and managing permissions (GRANT/REVOKE). Notably, PostgreSQL treats schema changes as transactional, so they can be wrapped in a transaction and rolled back alongside data changes - something many other databases don't support.

Ch 13 - Select, From, Where

SELECT * is discouraged - hides intent, breaks silently on schema changes, wastes I/O (incl. TOAST decompression).

Join-condition placement matters: a filter in ON vs WHERE changes whether a LEFT JOIN stays outer or collapses to inner.

NOT IN + a NULL in the list means the predicate can never be true - use NOT EXISTS for anti-joins instead.

Ch 14 - Order By, Limit, No Offset

kNN (k-nearest neighbors) search answers questions like "what are the 5 closest stores to this address?" Normally that requires calculating the distance from the target to every row, then sorting and taking the top results - slow on a large table. PostgreSQL can instead write this as ORDER BY point <-> point LIMIT k (<-> is the distance operator), and if the column has a GiST index, the database walks the index outward from the target point, stopping as soon as it has k matches. No full scan, no separate sort step.

OFFSET is actively discouraged (must scan+discard all prior rows); proper pagination uses keyset/seek pagination: WHERE row(col1,col2) > (last1,last2) ORDER BY ... FETCH FIRST n ROWS ONLY.

Ch 15 - Group By, Having, With, Union All

FILTER (WHERE ...) restricts what an aggregate counts without a subquery.

GROUPING SETS/ROLLUP/CUBE compute multiple aggregation levels in one pass.

You can't nest aggregates directly - e.g. MAX(SUM(amount)) to find the customer with the highest total spend is invalid SQL, since SUM produces one row per customer and MAX needs those rows to already exist. The fix is to chain CTEs: one CTE computes SUM(amount) grouped by customer, then a second query runs MAX() over that result.

DISTINCT ON (expr) (PostgreSQL-only) picks first row per group per ORDER BY.

Prefer UNION ALL over UNION when duplicates are impossible; EXCEPT is a handy tool for regression-testing query equivalence.

Ch 16 - Understanding Nulls

SQL is three-valued logic (true/false/unknown) - NULL = NULL is NULL, not true.

Use IS DISTINCT FROM when NULL should compare like a normal value.

Ch 17 - Window Functions

Frames (default UNBOUNDED PRECEDING .. CURRENT ROW), PARTITION BY for subgroup scope, named WINDOW clauses for reuse.

Evaluated after WHERE filtering - can't see rows already excluded.

Ch 18 - Relations and Joins

A relation is a bag (duplicates allowed) of same-typed rows; join types: cross/inner/outer(left/right)/full outer/lateral; join conditions aren't limited to equality (non-equi joins are valid).

Ch 19 - Interview: Markus Winand

SQL feels hard because it's declarative - stop thinking "how would I loop this," describe the result instead.

Prefer standard SQL first (portability is as much about people/readability as about engines); DDL is the exception - don't try to make schema definitions portable.

Part V - Data Types

Ch 20-21 - Serialization & Relational Theory

A database's value is transactional consistency, not marshaling - storage alone isn't a hard problem.

PostgreSQL enforces real-world data-domain rules (date '2010-02-29' errors - 2010 isn't a leap year; no year zero exists).

Operators/functions dispatch polymorphically by argument type via catalogs (pg_operator, pg_opclass, pg_am).

Ch 22 - PostgreSQL Data Types

Boolean: three-valued; use IS TRUE/FALSE/NULL, not =.

Text: text and varchar(n) are internally identical (varchar is just text + a length check); never use SQL_ASCII encoding - use validated UTF8.

Numbers: no unsigned integer types by design (avoids operator-combination explosion); real/double precision can't exactly represent decimals like 0.1 - never use for money, use numeric or integer cents.

Sequences/serial: serial is a pseudo-type (int column + owned sequence); sequences are the one non-transactional object; use bigserial on high-volume tables to avoid the underlying int4 column overflowing before the sequence does.

UUID: always use the native uuid type (16 bytes) not text (37 bytes).

Date/Time: always use timestamptz, never bare timestamp - both are 8 bytes on disk, so there's no storage cost to the "safe" choice; timestamptz is stored as UTC and only converts on input/output based on session TimeZone. now() is frozen per-transaction; use clock_timestamp() to see real time advance.

Intervals: calendar-aware arithmetic against a real date (correctly handles month-length differences).

Ranges: e.g. daterange + GiST exclusion constraints (EXCLUDE USING gist (currency WITH =, validity WITH &&)) prevent overlapping validity periods - demonstrated with an exchange-rate table.

Ch 23 - Denormalized Data Types

Arrays: good for "whole unit" data like tags; if you're constantly querying into the array, that's a sign you need a real table; unnest() + GIN @> for efficient contains-search.

JSON vs JSONB: json is just validated text (preserves whitespace/duplicate keys, near zero operator support); jsonb is binary, normalized, and fully indexable - almost always the right choice. Best practice: typed columns for known fields + one jsonb column for the unpredictable rest.

Enum: mainly useful for MySQL migrations; PostgreSQL enums are named/shared types (unlike MySQL's anonymous per-column ones); a lookup table is often just as good.

Ch 24-25 - Extensions & Interview: Grégoire Hubert

Deploy postgresql-contrib from day one in dev and prod.

POMM's design keeps model entities "database-ignorant" and shaped by the SQL projection, deliberately avoiding Active Record; JSON/XML in the DB is valuable for genuinely extensible fields that don't need relational consistency enforcement.

Part VI - Data Modeling

Ch 26 - ORM

Application object models (user workflows) and the database model (consistent view of the whole world) serve different purposes - sharing one model between them tends to produce monoliths and technical debt.

Ch 27 - Modeling Tooling

Use begin;/rollback; in psql to iterate schema drafts without committing half-finished changes; random-data generators and Lorem Ipsum tables validate designs at realistic volume.

Nested lateral joins solve Top-N-per-category queries; adding the right index on top can meaningfully cut runtime further.

Ch 28 - Normalization

Normal forms:

Three classic anomalies from under-normalizing:

Worked "address field" example: normalization needs depend on the use case (a text field is fine for invoicing; a logistics business needs a full geographic hierarchy).

Surrogate keys (e.g. bigserial) don't prevent business-level duplicates on their own - pair with a unique() constraint on the natural key too.

Ch 29 - Geonames Case Study

Compound codes can be split into reference tables and comma-separated fields split into association tables via regexp_split_to_table; a GiST point index keeps nearest-neighbor lookups fast even at tens of millions of rows.

Ch 30 - Modelization Anti-Patterns

EAV (entity-attribute-value) is called out as one of the worst patterns possible: loses type safety, allows silent typo'd "phantom" parameters, forces app-side pivoting.

Multiple values per column (delimited text) breaks 1NF, kills indexed search and per-value stats.

UUID as a silver bullet: still just a surrogate key - doesn't solve the "what's the natural business key" problem any better than a sequence does.

Ch 31 - Denormalization

Normalize first, measure real query timings (avg/median/p95/p99), and only denormalize with hard evidence - otherwise it's premature optimization.

Materialized views as caches (build a normal view first, then materialize on top; refresh ... concurrently avoids blocking readers).

History/audit tables, or a generic JSONB-snapshot archive table via row_to_json.

Native table partitioning (PG10+) has real costs: no cross-partition PK/unique/exclusion constraints, no FKs referencing partitioned tables, no ON CONFLICT - effectively loses 1NF guarantees at the whole-table level.

Ch 32 - Not Only SQL

PostgreSQL's "schemaless" JSONB isn't truly schemaless - the shape just moves from the catalog into application code.

synchronous_commit can be tuned per-transaction to trade write latency against durability guarantees.

No native scale-out in core PostgreSQL - requires extensions (Citus, BDR) or PG10 logical replication.

Ch 33 - Interview: Álvaro Hernández Tortosa (ToroDB)

Rejects "schemaless" - prefers "dynamic schema"; even MongoDB data ends up having real relations.

Benchmarks showed relational storage beating NoSQL on analytics by 1-2 orders of magnitude, since unstructured stores must infer structure at query time.

Recommended sweet spot: normalized core schema + JSONB for evolving fields, promoting fields to real columns once their shape stabilizes.

Part VII - Data Manipulation and Concurrency Control

Ch 35 - Insert, Update, Delete

RETURNING (PostgreSQL extension) avoids a round-trip to learn generated/default values.

UPDATE internally does insert-new + mark-old-invisible under MVCC (xmin/xmax); row locks are per-tuple.

DELETE ... RETURNING wrapped in a CTE is the recommended default pattern for interactive deletes (gives a summary of what was removed).

TRUNCATE is technically DDL - bypasses per-tuple MVCC but is still transactional/rollback-safe.

Ch 36 - Isolation and Locking

PostgreSQL doesn't implement read-uncommitted; defaults to read-committed; disallows phantom reads at repeatable-read (stricter than the SQL standard requires).

Serializable Snapshot Isolation (SSI) guarantees the same effect as if transactions ran one at a time.

Key concurrency-modeling lesson: replace shared-counter UPDATE columns with an append-only activity/event table - updating one popular row serializes all concurrent writers, while inserting into a fresh table never contends. Benchmark shows the insert-based design clearly outperforming the update-based one under load.

Ch 37 - Computing and Caching in SQL

Views: computed at query time, no caching, no invalidation problem, but no performance win either.

Materialized views: persisted snapshots (refresh ... concurrently needs a unique index) - the real "invalidation policy" is just your refresh schedule.

Ch 38 - Triggers

Must be written in a procedural language (PL/pgSQL etc.) - plain SQL can't implement a trigger function.

The classic "counter trigger" anti-pattern (check-then-update-or-insert) reintroduces exactly the single-row contention problem Ch.36 solved, and has a race condition on first-insert-of-the-day; fix correctness with INSERT ... ON CONFLICT DO UPDATE, but the underlying scalability bottleneck (still hammering one row) remains.

Ch 39 - Listen and Notify

Async pub/sub over the wire protocol (pg_notify), useful for cache-refresh/invalidation daemons - but fire-and-forget with no durable queueing, so it can't be used as a guaranteed-delivery queue.

Ch 40 - Batch Update (MoMA)

Standard pattern:

  1. Stage the new snapshot in a temp table
  2. UPDATE ... FROM staging on the natural key (skip no-op rows via row-value <> comparison)
  3. INSERT ... SELECT WHERE NOT EXISTS for new rows

Concurrent re-runs risk duplicate-key errors; fix with LOCK TABLE serialization or INSERT ... ON CONFLICT DO NOTHING.

Ch 41 - Interview: Kris Jenkins (YeSQL)

"Performance is the tail, correctness is the dog" - get transaction boundaries right first, tune for real observed hotspots later, not speculatively.

Part VIII - PostgreSQL Extensions

Ch 42 - What's an Extension?

Extensions add catalog objects (functions, types, operators, index access methods) via create extension, no restart needed; notable ones: Bloom, earthdistance, hstore, ltree, pg_trgm (contrib), PostGIS, ip4r, Citus, pg_partman, postgres-hll, MADlib (external).

Ch 43 - Auditing with hstore

hstore's - (diff) operator between hstore(old) and hstore(new) in an after update trigger gives a clean before/after audit trail with no native JSONB equivalent for this specific diff capability.

Ch 45 - Trigrams for Typos (pg_trgm)

similarity()/% operator for fuzzy/typo-tolerant matching (case-insensitive, unlike regex); word_similarity()/%> for matching a word against multi-word titles; GiST trigram index turns both regex and similarity search into single-digit-millisecond queries.

Ch 46 - Tags with intarray

query_int + @@ lets you evaluate boolean tag queries (3&739, i.e. AND/OR/NOT) against a GIN-indexed integer array of tag IDs - avoids the awkward multi-way joins a junction-table design would need for combined tag filters.

Ch 47-49 - Geolocation (pubs, earthdistance, ip4r)

kNN pattern ORDER BY pos <-> point(...) LIMIT n with a GiST index turns a 20ms sequential scan into a sub-1ms indexed nearest-neighbor search.

earthdistance's <@> gives real Earth-surface miles between points; ip4r + GiST gives sub-millisecond "which network contains this IP" lookups.

Ch 50 - HyperLogLog

hll type gives approximate distinct counts in a small fixed size (~1280 bytes for tens of billions of values); hll_union_agg correctly deduplicates when rolling up daily counts into weekly/monthly ones (naive summing of daily uniques would double-count).

Ch 51 - Interview: Craig Kerstiens (Citus Data)

Considers PostgreSQL's extension ecosystem the biggest advancement of the last decade - it turned a relational database into a broader data platform while letting core PostgreSQL move cautiously.

Main risk: managed/cloud PostgreSQL providers only support a limited extension allowlist.

Part IX - Closing Thoughts

The book closes by circling back to its opening argument: SQL is a full programming language in its own right, and should be treated that way. That means writing one query per result set instead of assembling data in the app, and running SQL through the same lifecycle as any other code - spec it, test it, review it, and rewrite it as requirements change - rather than writing it once and never touching it again.

LinkedIn