A Practical Guide to Using, Designing, and Running PostgreSQL

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

PostgreSQL can do far more than most teams ask of it.

It stores relational data with strong guarantees — and also JSON documents, time ranges, full-text search, geographic data, and vector embeddings. But using PostgreSQL well takes more than knowing SQL. Teams that treat it as a generic database miss exclusion constraints, range types, JSONB indexes, and ON CONFLICT — and then hit problems they don't understand: queries that are fast on a laptop and slow in production, tables that keep growing after deletes, migrations that lock the app, and backups nobody has ever restored.

This book is a practical guide to PostgreSQL itself: its tools, its type system, how it works inside, and how to run it in production.

• 456 pages · 28 chapters + 4 appendices
• Install, connect securely, and master psql
• Data types and constraints: timestamptz, UUIDs, JSONB, arrays, range types, and exclusion constraints
• Advanced SQL: CTEs, recursive queries, LATERAL, window functions, ON CONFLICT, MERGE, COPY
• Full-text search, fuzzy matching, functions, procedures, and triggers in PL/pgSQL
• Performance: the planner, EXPLAIN ANALYZE, index types, MVCC, VACUUM, locking, and configuration
• Operations: roles and row-level security, backups and point-in-time recovery, replication, partitioning, extensions
• Applications and production: drivers, connection pooling, zero-downtime migrations, upgrades, and the cloud
• Examples target PostgreSQL 17, with version notes — plus a complete end-to-end case study

Table Of Contents

Part I — Getting Started with PostgreSQL

• Ch 1 · Why PostgreSQL: Features, Architecture, and Where It Fits
• Ch 2 · Installing PostgreSQL and Connecting: Packages, Containers, and Managed Services
• Ch 3 · Working Effectively with psql and GUI Tools
• Ch 4 · Databases, Schemas, and Tables: Building the Nestwork Database
• Ch 5 · SQL Essentials in PostgreSQL: Queries, Joins, and Aggregates

Part II — Data Types and Data Modeling

• Ch 6 · PostgreSQL Data Types: Numbers, Text, Dates and Times, Booleans, UUIDs, Enums, and Domains
• Ch 7 · Constraints and Data Integrity: Keys, CHECK, UNIQUE, Exclusion Constraints, and Generated Columns
• Ch 8 · JSONB, Arrays, and Composite Types
• Ch 9 · Range Types, Time Zones, and Scheduling Data
• Ch 10 · Data Modeling for PostgreSQL: Normalization, Naming, and Schema Design Patterns

Part III — Writing Powerful SQL

• Ch 11 · Advanced Queries: CTEs, Recursive Queries, LATERAL, and Subqueries
• Ch 12 · Window Functions and Analytics
• Ch 13 · Changing Data: RETURNING, ON CONFLICT, MERGE, and Bulk Loading with COPY
• Ch 14 · Full-Text Search and Fuzzy Matching
• Ch 15 · Functions, Procedures, Triggers, and PL/pgSQL

Part IV — Performance and Concurrency

• Ch 16 · How PostgreSQL Executes Queries: The Planner, EXPLAIN, and EXPLAIN ANALYZE
• Ch 17 · Indexing in Depth: B-tree, GIN, GiST, BRIN, Partial, and Expression Indexes
• Ch 18 · MVCC, VACUUM, and Autovacuum
• Ch 19 · Transactions, Isolation Levels, and Locking
• Ch 20 · Configuration, Monitoring, and pg_stat_statements

Part V — Administration and Operations

• Ch 21 · Roles, Privileges, and Row-Level Security
• Ch 22 · Backup and Recovery: pg_dump, pg_basebackup, WAL Archiving, and Point-in-Time Recovery
• Ch 23 · Replication and High Availability: Streaming and Logical Replication
• Ch 24 · Partitioning and Managing Large Tables
• Ch 25 · Extensions and the Ecosystem: pgcrypto, pg_trgm, PostGIS, pgvector, and Foreign Data Wrappers

Part VI — PostgreSQL in Applications and Production

• Ch 26 · PostgreSQL from Application Code: Drivers, Connection Pooling, Migrations, and ORMs
• Ch 27 · Security Hardening, Upgrades, and Running PostgreSQL in the Cloud
• Ch 28 · Case Study: Building and Operating the Nestwork Database End to End

Appendices

• App A · psql and SQL Cheat Sheet
• App B · Configuration and Query Reference
• App C · Production Checklist
• App D · Glossary

WHO IT IS FOR
Application and back-end developers who use PostgreSQL every day, developers moving from another database, and developer-minded DBAs and platform engineers who want a practical, complete reference.

FORMAT
PDF (A4), 456 pages, English, clickable table of contents and PDF bookmarks. 1st Edition, 2026. Published by thenexbooks.

This book is independent and is not affiliated with or endorsed by the PostgreSQL Global Development Group.

Book details