Design, Query, and Ship Data-Driven Applications with PostgreSQL 18

Read before you buy: the PDF here is a free sample — the first 20 pages of the 611-page book (payments, sales and reviews on this demo site are sample data). Get the full e-book at thenexbooks on Gumroad.

The PostgreSQL book for people who build applications — every technique shown with real SQL, real output, and the application code around it.

Most applications are a careful conversation with a database. Every sign-up, purchase, search box, dashboard, and background email depends on queries that must be correct when two users click at the same moment, fast when tables hold millions of rows, and safe to change while the app keeps running.

Many developers treat PostgreSQL as a place to store rows behind an ORM, and miss most of what it can do. This book shows you how to let PostgreSQL enforce your rules, return API-ready JSON, search with ranking and typo tolerance, run a reliable job queue, notify your app of changes, isolate tenants, and explain exactly why a query is slow. Each chapter starts from a real feature or a real bug, shows it happening, and then fixes it.

What's inside

• Schemas for applications: keys (identity vs UUID, including uuidv7), constraints, naming, and the data types developers get wrong — money, time zones, text, enums
• Talking to PostgreSQL from code with psycopg 3: parameters, transactions, row factories, COPY, and SQL-injection-proof dynamic queries
• CRUD done right: RETURNING (with OLD/NEW in PostgreSQL 18), upserts with ON CONFLICT, and MERGE
• Dashboards without double counting, keyset pagination that never skips rows, CTEs, LATERAL, and window functions
• JSONB for flexible data, full-text search with ranking and highlighting, and fuzzy search with pg_trgm
• Arrays, ranges, exclusion constraints, and temporal keys for scheduling
• Job queues with FOR UPDATE SKIP LOCKED, LISTEN/NOTIFY, triggers, and the transactional outbox
• Transactions and isolation levels from the app's point of view; lost updates, optimistic locking, advisory locks, deadlocks, and retry loops
• Constraints as a safety net and turning SQLSTATE errors into clean API responses
• Zero-downtime migrations (lock queues, CREATE INDEX CONCURRENTLY, expand/contract) and testing against real PostgreSQL
• Reading EXPLAIN, choosing indexes for real queries, fixing N+1 queries in SQLAlchemy, pooling with psycopg_pool and PgBouncer, and timeouts
• Multi-tenancy with row-level security, least-privilege roles, and shipping to managed PostgreSQL

One running example, start to finish. You build the data layer of Larkhaven Learning, a fictional online course platform where schools publish courses and learners enroll, follow lessons, and leave reviews. A small fixed data set runs through every chapter so you can check each result yourself, and the book ends with the complete data layer, search, background jobs, and notifications working together.

Real output. The SQL examples were run on PostgreSQL 18 while writing, and the plans, results, and error messages you see are real. PostgreSQL 18 features are labeled, and there are no invented benchmarks. Application code uses Python with psycopg 3, and Appendix B maps it to Node.js, Java, and Go. Every chapter includes Tips, Notes, Warnings, practice exercises with selected solutions, and Key Takeaways.

Format: PDF, 611 pages (A4), linked table of contents and bookmarks. English. First Edition, 2026. Published by thenexbooks. This book is independent and is not affiliated with or endorsed by the PostgreSQL Global Development Group. PostgreSQL is a registered trademark of the PostgreSQL Community Association.

Table of Contents

Part I — Foundations for App Developers

• Ch 1 · Why PostgreSQL for Applications: What You Get Beyond "Just a Database"
• Ch 2 · A Developer Setup: PostgreSQL 18, psql, Docker, and a Python Project
• Ch 3 · Designing the Schema: Tables, Keys, Constraints, and Naming for Applications
• Ch 4 · Data Types Developers Get Wrong: Money, Time Zones, Text, UUIDs, and Enums
• Ch 5 · Connecting from Code: psycopg 3, Parameters, Transactions, and Row Factories

Part II — Querying for Features

• Ch 6 · CRUD Done Right: RETURNING, Upserts with ON CONFLICT, and MERGE
• Ch 7 · Joins, Aggregates, and Reporting Queries for Dashboards
• Ch 8 · Pagination, Sorting, and Filtering: OFFSET vs Keyset and Dynamic Filters
• Ch 9 · CTEs, LATERAL, and Window Functions for Real Features
• Ch 10 · JSONB for Flexible Data: When to Use It, Querying, and Indexing

Part III — Search, Events, and Background Work

• Ch 11 · Full-Text Search and Fuzzy Matching: tsvector, Ranking, Highlighting, and pg_trgm
• Ch 12 · Arrays, Ranges, and Exclusion Constraints: Tags and Scheduling
• Ch 13 · Job Queues in PostgreSQL with FOR UPDATE SKIP LOCKED
• Ch 14 · LISTEN/NOTIFY, Triggers, and the Transactional Outbox
• Ch 15 · Views, Functions, and Generated Columns: When Logic Belongs in the Database

Part IV — Correctness and Concurrency

• Ch 16 · Transactions and Isolation Levels from the Application's Point of View
• Ch 17 · Concurrency Bugs and Their Fixes: Lost Updates, Optimistic Locking, and Advisory Locks
• Ch 18 · Constraints as a Safety Net: Integrity Rules, Deferrable Constraints, and Handling Errors in Code
• Ch 19 · Schema Migrations Without Downtime: Tools, Patterns, and Expand/Contract
• Ch 20 · Testing Database Code: Fixtures, Rollback Isolation, Testcontainers, and pgTAP

Part V — Performance and Scale

• Ch 21 · Reading EXPLAIN as a Developer
• Ch 22 · Indexes for Application Queries: Composite, Partial, Expression, Covering, and GIN
• Ch 23 · ORMs, N+1 Queries, and Query Patterns That Scale (SQLAlchemy 2.0)
• Ch 24 · Connection Pooling, Timeouts, and Resilience: psycopg_pool and PgBouncer
• Ch 25 · Multi-Tenancy and Security: Roles, Row-Level Security, and Secrets

Part VI — Projects and Production

• Ch 26 · Project: The Larkhaven Data Layer End to End
• Ch 27 · Project: Search, Background Jobs, and Notifications for Larkhaven
• Ch 28 · Shipping to Production: Managed PostgreSQL, Backups, Observability, Upgrades, and Next Steps

Appendices
A · SQL and psql Cheat Sheet for App Developers
B · psycopg 3 Reference with Node.js, Java, and Go Equivalents
C · Common PostgreSQL Errors (SQLSTATE) and What to Do in Code
D · Glossary

Want the administration side — backups, replication, and operations in depth? The companion book PostgreSQL Essentials covers it.

Book details