The PostgreSQL Handbook

Front Matter

• Preface
• Introduction
• About the Author

Part I — Foundations

1. Why PostgreSQL, and Getting It Running
   • The filing cabinet with a librarian
   • Where PostgreSQL came from, and why it won
   • An honest comparison
   • Setting up: one command, whole environment
   • First contact with psql
   • The meta-commands you'll use daily
   • A tour of the companion repository
2. Designing Lumina Helpdesk: Databases, Schemas, and Tables
   • Lumina has a problem
   • Why not one big spreadsheet
   • Databases, schemas, and tables
   • From entities to tables
   • The ticket and its conversation
   • Tags: when both sides can have many
   • The rest of the cast
   • What we deliberately left out
3. CRUD: Inserting, Reading, Updating, Deleting
   • INSERT: making rows exist
   • Seeding Lumina: the cast takes the stage
   • SELECT: asking questions
   • UPDATE: changing what is already there
   • The statement with no WHERE clause
   • DELETE: making rows not exist
   • RETURNING: the answer rides home with the statement
   • TRUNCATE vs DELETE: emptying a table
4. Filtering, Sorting, and Shaping Results
   • WHERE in earnest
   • A first taste of patterns
   • NULL and the logic of the unknown
   • ORDER BY without surprises
   • LIMIT, OFFSET, and the pagination debt
   • DISTINCT, and PostgreSQL's better idea
   • CASE: computing your own columns
   • The everyday function toolkit
5. Data Types Done Right
   • Numbers: exact, or fast
   • Text: the shortest section, on purpose
   • Time: always timestamptz, and here is the reasoning
   • boolean, briefly
   • uuid: identity that travels
   • Closed value sets: enum, lookup table, or CHECK
   • Arrays and ranges: containers with judgment attached
   • Primary keys: a decision framework, not a dogma

Part II — Relational Thinking

1. Constraints: Making Bad Data Impossible
   • The last line of defense
   • NOT NULL, revisited on live data
   • Foreign keys: the arrows become real
   • UNIQUE: no two rows may share this
   • CHECK: rules in the row's own words
   • Generated columns: facts the database computes
   • Deferrable constraints: for the rare mid-flight contradiction
2. Joins: Reading Across Tables
   • The question that needs two tables
   • LEFT JOIN and the manufactured NULL row
   • Self-joins: the org chart was in there all along
   • The supporting cast: CROSS JOIN, USING, NATURAL
   • Chains: the whole schema in one query
   • How to read a join you didn't write
3. Aggregation: From Rows to Answers
   • Aggregates: many rows in, one answer out
   • GROUP BY: the mental leap
   • Grouping over joins, and the fan-out bill arrives
   • HAVING: a WHERE for the buckets
   • FILTER: several aggregates, several row-sets, one pass
   • Aggregating into text and arrays
   • Buckets made of time
   • Several groupings in one query: SETS, ROLLUP, CUBE
   • The morning dashboard, built honestly
4. Subqueries and CTEs: Composing Queries
   • Scalar subqueries: a value with a query inside
   • Sets to test against: IN and EXISTS
   • The NOT IN betrayal
   • Tables you invent mid-query
   • WITH: composition you can read aloud
   • WITH RECURSIVE: walking the trees you planted
   • LATERAL: the per-row subquery, promoted
5. Window Functions: Analytics Without Leaving SQL
   • The problem aggregates can't solve
   • The rank family: three philosophies of order
   • LAG and LEAD: this row, meeting its neighbors
   • Frames: the window inside the window
   • NTILE, and naming your windows
   • Set operations: the toolkit's closing bracket
   • Capstone: the leaderboard, three ways

Part III — The PostgreSQL Difference

1. JSONB: Relational and Document, Together
   • The false choice
   • Two types, one winner
   • Reading documents: two arrows, one trap
   • A document column of our own
   • Filtering on documents: containment does the heavy lifting
   • Building documents: the API shape, straight from SQL
   • Editing documents without rewriting them
   • Documents back to rows
   • Column or key? The framework
2. Search: Pattern Matching and Full-Text
   • Where LIKE runs out
   • pg_trgm: similarity for the fat-fingered
   • tsvector: text distilled to lexemes
   • tsquery: asking in the same language
   • The searchable column that maintains itself
   • Ranking: from matching to ordering
   • Where PostgreSQL search stops
3. Server-Side Code: Views, Functions, Procedures, Triggers
   • Views: a query with a name
   • Materialized views: the report, precomputed
   • Functions, part one: a named expression
   • Functions, part two: PL/pgSQL, when SQL needs a spine
   • Procedures: transaction control for batch work
   • Triggers: code that fires itself
   • The discipline: facts about data, never decisions about business
4. Transactions and Concurrency: MVCC Without Tears
   • ACID, with Lumina's money on the table
   • The boundary: BEGIN, COMMIT, ROLLBACK
   • MVCC: nobody edits a row, ever
   • Isolation levels: choosing your anomalies
   • FOR UPDATE: when reading means claiming
   • SKIP LOCKED: the job queue that cannot double-send
   • Advisory locks: mutexes on ideas
   • Deadlocks: the mutual ambush
   • Optimistic or pessimistic: who pays for the conflict?

Part IV — Performance & Operations

1. Indexes: The Right Tool for Each Shape of Question
   • Life without an index
   • Inside the default: B-tree mechanics
   • What you have already, and the classic gap
   • Multi-column indexes: order is the design
   • Covering indexes: never touching the table at all
   • Partial indexes: index the 8% that matters
   • Expression indexes: indexing a computation
   • The specialist family
   • The bill: write amplification
   • Hygiene: auditing the bets
2. Reading the Planner's Mind: EXPLAIN and Query Tuning
   • The planner: a cost accountant, not an oracle
   • Reading a plan: bottom-up, one node at a time
   • EXPLAIN ANALYZE: estimates meet reality
   • The scan bestiary
   • Three ways to join, chosen for you
   • When estimates lie
   • The workflow
   • Keyset pagination: the flagship promise
   • Four more slow queries, same loop
3. Security: Roles, Privileges, and Row-Level Security
   • Roles: one concept for users and groups
   • Privileges: deny by default, grant by name
   • The application account: exactly enough
   • Views as the reporting surface
   • Row-Level Security: the WHERE clause nobody can forget
   • SECURITY DEFINER: controlled escalation, one pitfall
   • The front door: pg_hba, SCRAM, TLS
4. Backups, Maintenance, and Monitoring
   • Logical backups: pg_dump and the restore drill
   • Physical backups and the time machine
   • VACUUM: MVCC's bill, collected
   • autovacuum: the daemon you tune, never disable
   • Bloat: finding the invisible weight
   • pg_stat_statements: the first thing you install
   • The Monday morning gauges
5. Partitioning: When One Table Isn't Enough
   • What partitioning solves — and what it doesn't
   • The migration: copy and swap
   • Pruning: the trick Chapter 16's plans couldn't do
   • The retention conveyor
   • Indexes and constraints across the pieces
   • When not to partition
6. Replication and Connection Pooling
   • Why replicate — and the boundary that isn't
   • Streaming replication: the diary, shipped
   • Lag, and reading your own writes
   • Logical replication: rows, not bytes — live
   • Connections are expensive — measurably
   • PgBouncer: many clients, few connections
   • Conversant, not a DBA: the going-deeper map

Part V — PostgreSQL in the Real World

1. Talking to PostgreSQL from Code
   • The driver's job, and the socket it speaks over
   • Connecting: the data source, the string, and the secret
   • First queries: a reader, typed columns, and NULL
   • Parameterized queries: the non-negotiable
   • RETURNING, from the application
   • Transactions, and the retry Chapter 14 promised
   • N+1: the same disease in every language
   • Migrations: schema as version control
2. Gateway: PostgreSQL with EF Core
   • What an ORM buys — and what it costs
   • Mapping Lumina: the database we already have
   • LINQ to SQL: watch it happen
   • The provider that actually likes PostgreSQL
   • EF migrations, meeting ours
   • Where hand-written SQL still wins
   • The concurrency token you already had
   • Onward
3. Gateway: PostgreSQL as an AI Database with pgvector
   • Why vectors, and why in your relational database
   • The vector type: an embedding is just a column
   • Distance operators, and the canonical query shape
   • HNSW: approximate neighbors, honestly traded
   • Semantic search end to end: has anyone solved this before?
   • From C#: Lumina.Search
   • Hybrid search: the Chapter 12 promise, discharged
   • Onward

Appendices

• Appendix A — Installing PostgreSQL 18 Natively
• Appendix B — The psql Survival Guide
• Appendix C — SQL Style Guide
• Appendix D — Worked Solutions
• Appendix E — What Changed: PostgreSQL 16 → 18
• Other Books You’ll Enjoy
• Index