The Best Books to Learn PostgreSQL, In Order
This curriculum starts at the intermediate level, assuming basic familiarity with relational databases, and builds systematically from solid SQL and PostgreSQL fundamentals through advanced database design and into expert-level performance tuning and internals. Each stage sharpens a distinct layer of mastery — language, design, and optimization — so that every book builds directly on the vocabulary and mental models established by the ones before it.
PostgreSQL Foundations
BeginnerGet fluent with PostgreSQL's core SQL dialect, data types, indexing basics, and day-to-day administration so you have a solid working baseline before going deeper.
▸ Study plan for this stage
Pace: 8–10 weeks, ~40–50 pages/day. Start with "Learning PostgreSQL 10" (weeks 1–5, ~6 chapters covering installation, SQL fundamentals, and data types), then transition to "PostgreSQL: Up and Running" (weeks 6–10, focusing on indexing, administration, and operational tasks).
- PostgreSQL installation, configuration, and initial setup across different operating systems
- SQL fundamentals in PostgreSQL: SELECT, INSERT, UPDATE, DELETE, JOIN operations, and query structure
- PostgreSQL data types (numeric, text, date/time, JSON, arrays, ranges) and when to use each
- Table design, constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK), and normalization principles
- Indexing strategies (B-tree, Hash, GiST, GIN) and how to create and optimize indexes for query performance
- Basic administration: user roles, permissions, authentication, and database/schema management
- Backup, restore, and basic disaster recovery procedures
- Query optimization and EXPLAIN ANALYZE for understanding query execution plans
- How do you install PostgreSQL on your operating system and verify the installation is working correctly?
- What are the key differences between PostgreSQL's data types (e.g., VARCHAR vs TEXT, INTEGER vs BIGINT, TIMESTAMP vs DATE), and when would you choose each?
- How do you design a normalized table schema with appropriate constraints, and what problems do PRIMARY KEY and FOREIGN KEY constraints prevent?
- What are the main index types in PostgreSQL (B-tree, Hash, GiST, GIN), and which would you use for different query patterns?
- How do you create users, assign roles, and configure basic permissions in PostgreSQL?
- What does EXPLAIN ANALYZE output tell you, and how would you use it to identify and fix a slow query?
- How do you perform a full database backup and restore it, and what are the trade-offs between different backup methods?
- Install PostgreSQL locally and connect via psql; create a test database and verify connectivity from a client application.
- Design and create a normalized schema for a real-world scenario (e.g., an e-commerce store, blog, or library system) with at least 4–5 tables, appropriate data types, and all necessary constraints.
- Write and execute 15–20 SQL queries covering SELECT with WHERE/GROUP BY/ORDER BY, INNER/LEFT/RIGHT JOINs, INSERT/UPDATE/DELETE, and aggregate functions on your schema.
- Create indexes on your schema's tables (at least 3–5 indexes on different columns), run EXPLAIN ANALYZE on slow queries, and measure the performance improvement.
- Create at least 3 database users with different role hierarchies (e.g., admin, read-only, application user) and test that permissions are correctly enforced.
- Perform a full database backup using pg_dump, intentionally corrupt or delete data, and successfully restore from the backup.
Next up: This foundation in PostgreSQL's core SQL, data types, indexing, and administration gives you the operational confidence and mental models needed to tackle advanced topics like replication, performance tuning, and specialized features (JSON/JSONB, full-text search, extensions) in the next stage.
A practical, structured introduction to PostgreSQL covering installation, SQL, data types, and basic administration — ideal for building the shared vocabulary needed for every later book.
A concise, opinionated tour of PostgreSQL's most important features (including extensions and PostGIS basics) that rounds out foundational knowledge and bridges toward intermediate topics.
SQL Mastery & Relational Thinking
IntermediateDevelop deep, database-agnostic SQL fluency — window functions, CTEs, set-based thinking — that makes advanced PostgreSQL-specific features immediately accessible.
▸ Study plan for this stage
Pace: 8–10 weeks, ~25–30 pages/day, with 2–3 days per week dedicated to hands-on exercises and query practice
- Recognizing and refactoring common SQL antipatterns (ambiguous groups, implicit conversions, N+1 queries, etc.) to write correct, maintainable code
- Set-based thinking: replacing procedural/iterative logic with declarative SQL operations that work on entire result sets at once
- Advanced query composition using CTEs (Common Table Expressions) and subqueries to break complex problems into readable, testable steps
- Window functions (PARTITION BY, ORDER BY, ranking, aggregation over frames) for analytical queries without self-joins or correlated subqueries
- Query optimization fundamentals: understanding execution plans, index usage, and how to write queries that the database engine can execute efficiently
- Relational algebra foundations: understanding joins, unions, intersections, and how to think in terms of relations rather than row-by-row processing
- Performance trade-offs: when to denormalize, when to normalize, and how schema design decisions impact query complexity and speed
- Defensive SQL practices: handling NULLs correctly, avoiding type coercion pitfalls, and writing queries that fail safely rather than silently
- What are the five most common SQL antipatterns discussed in Karwin's book, and how would you refactor a query exhibiting each one?
- Explain the difference between set-based and procedural thinking in SQL. Give an example of a procedural approach and show how to rewrite it set-based.
- How do window functions eliminate the need for self-joins or correlated subqueries? Provide a concrete example using PARTITION BY and ORDER BY.
- What is a CTE (Common Table Expression), and how does using CTEs improve query readability and maintainability compared to deeply nested subqueries?
- Describe the relationship between query performance and how you structure your SQL. How do execution plans inform your query design decisions?
- How should you handle NULL values in SQL to avoid silent logical errors? What are the pitfalls of NULL comparisons and implicit type conversions?
- Work through 5–10 antipattern examples from Karwin's book: identify the antipattern in a given query, explain why it's problematic, and rewrite it correctly.
- Convert 3–4 procedural/iterative queries (e.g., using cursors or application-level loops) into pure set-based SQL using CTEs and window functions.
- Write 5 analytical queries using window functions: row numbering, running totals, rank/dense_rank comparisons, and frame-based aggregations (ROWS BETWEEN).
- Refactor 3 complex nested subqueries into readable CTEs; compare the execution plans and query readability before and after.
- Analyze 4–5 real or synthetic slow queries: generate execution plans, identify missing indexes or inefficient joins, and optimize them.
- Design a small schema (5–8 tables) and write 10–15 queries of increasing complexity that exercise joins, aggregations, window functions, and CTEs together.
- Create a "query audit" document: collect 5 queries from your own work or open-source projects, identify antipatterns, and propose refactored versions with explanations.
Next up: By mastering database-agnostic SQL fundamentals and set-based thinking through Karwin and Faroult, you'll have the mental models and query-writing discipline needed to leverage PostgreSQL-specific extensions (JSON, arrays, full-text search, advanced indexing) and advanced features (partitioning, materialized views, custom types) effectively in the next stage.
Teaches correct relational design by dissecting common mistakes; reading this first sharpens the intuition needed to appreciate good schema design in the books that follow.
Elevates SQL from a query language to a craft, emphasizing set-based thinking and query strategy — essential mental models before tackling performance tuning.
Database Design & Advanced PostgreSQL
IntermediateMaster relational and PostgreSQL-specific database design — normalization, partitioning, advanced data types, transactions, and concurrency — so you can architect production-grade schemas.
▸ Study plan for this stage
Pace: 8–10 weeks, ~40–50 pages/day (alternating between both books; start with Hernandez for conceptual foundation, then layer in Fontaine for PostgreSQL-specific implementation)
- Normalization theory (1NF through 3NF/BCNF) and when to denormalize for performance
- Entity-Relationship (ER) modeling and translating designs into relational schemas
- PostgreSQL data types (arrays, JSON/JSONB, ranges, custom types, enums) and when to use them
- Table partitioning strategies (range, list, hash) and constraint exclusion for query optimization
- ACID transactions, isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE), and deadlock prevention
- Concurrency control: row-level locking, MVCC (Multi-Version Concurrency Control), and lock modes (SHARE, EXCLUSIVE, etc.)
- Schema design for scalability: inheritance, foreign keys, check constraints, and triggers
- Index strategies (B-tree, Hash, GiST, GIN, BRIN) and query planning to avoid full table scans
- What are the differences between 2NF and 3NF, and why would you denormalize a schema despite violating normalization rules?
- How do you design a PostgreSQL schema using JSONB columns, and what are the trade-offs versus strict relational design?
- Explain the four SQL isolation levels in PostgreSQL and describe a real-world scenario where SERIALIZABLE isolation is necessary.
- Design a partitioning strategy for a time-series table with millions of daily records, and explain how constraint exclusion improves query performance.
- What is MVCC, and how does it allow PostgreSQL to handle concurrent reads and writes without blocking?
- Given a slow query, how would you use EXPLAIN ANALYZE to identify missing indexes or poor join strategies?
- Work through Hernandez's case studies (Chapters 8–10): normalize a poorly designed schema from a real business domain, document assumptions, and explain trade-offs.
- Build a multi-table schema for a blog platform (posts, comments, users, tags) following 3NF; then intentionally denormalize one table and measure query performance differences.
- Design and implement a partitioned table in PostgreSQL for a 1-year dataset (e.g., sales transactions); write queries that benefit from constraint exclusion and verify with EXPLAIN.
- Create a schema using PostgreSQL-specific types: define an enum for status values, a custom composite type for addresses, and a JSONB column for metadata; query each using appropriate operators.
- Write a multi-statement transaction with explicit locking (SELECT FOR UPDATE) to prevent race conditions; then simulate concurrent clients and observe lock behavior.
- Analyze a provided slow query using EXPLAIN ANALYZE; identify the bottleneck (sequential scan, bad join order, missing index); add an appropriate index and re-run to confirm improvement.
Next up: This stage equips you with the architectural and concurrency knowledge needed to move into application-level concerns—connection pooling, query optimization patterns, and ORM design—where you'll apply these schemas under real-world load.
The definitive practical guide to relational schema design; reading it here cements normalization and entity-relationship modeling before diving into PostgreSQL-specific architecture.
Focuses on using PostgreSQL's advanced SQL features (window functions, custom types, stored procedures) from an application developer's perspective, bridging design and real-world usage.
Performance Tuning & Query Optimization
ExpertDiagnose and resolve performance bottlenecks — understand the query planner, index strategies, VACUUM, autovacuum, and configuration tuning for high-throughput production systems.
▸ Study plan for this stage
Pace: 6–8 weeks, ~40–50 pages/day (with hands-on lab time)
- Query planner fundamentals: how PostgreSQL generates and chooses execution plans using cost-based optimization
- EXPLAIN and EXPLAIN ANALYZE: reading and interpreting query plans to identify sequential scans, index usage, and join strategies
- Index strategies: B-tree, hash, GiST, GIN indexes and when to apply each for different query patterns and data types
- Query optimization techniques: rewriting queries, statistics management, and leveraging PostgreSQL-specific features (CTEs, window functions, lateral joins)
- VACUUM, AUTOVACUUM, and table maintenance: preventing bloat, managing dead tuples, and tuning autovacuum parameters for workload patterns
- Configuration tuning: work_mem, shared_buffers, effective_cache_size, random_page_cost, and other parameters that influence planner decisions and performance
- Monitoring and profiling: using pg_stat_statements, slow query logs, and system metrics to identify bottlenecks in production systems
- Transaction isolation and locking: understanding how isolation levels and lock contention affect query performance under concurrent load
- How does PostgreSQL's cost-based query planner decide between different execution plans, and what role do statistics play in that decision?
- What information does EXPLAIN ANALYZE provide, and how do you use it to diagnose why a query is slow?
- When should you use B-tree, hash, GiST, or GIN indexes, and how do you determine if an index is actually being used by the planner?
- How does VACUUM work, why is it necessary, and what are the trade-offs between aggressive and conservative autovacuum settings?
- What are the most impactful PostgreSQL configuration parameters for query performance, and how do you tune them for your workload?
- How do you identify slow queries in a production system, and what tools does PostgreSQL provide for monitoring and profiling?
- Set up a test database with sample data (1M+ rows) and run EXPLAIN ANALYZE on 5–10 queries of varying complexity; document the plans and identify sequential scans vs. index scans
- Create indexes on different columns and data types (B-tree, GIN for full-text search, GiST for geometric data); measure query performance before and after with timing
- Rewrite 3–5 slow queries using techniques from the book (query restructuring, CTE optimization, window functions); compare execution plans and runtimes
- Configure autovacuum parameters for a test table; simulate write-heavy workload and monitor table bloat using pg_stat_user_tables; adjust settings and observe impact
- Profile a multi-query workload using pg_stat_statements; identify the top 5 most expensive queries by total time and optimize them
- Tune 4–6 key PostgreSQL configuration parameters (work_mem, shared_buffers, effective_cache_size, random_page_cost) on your test instance; measure query performance before and after
- Run a concurrent workload (multiple clients executing transactions) and observe lock contention using pg_locks; experiment with different isolation levels and measure throughput
- Document a complete optimization case study: identify a slow query, analyze its plan, apply 2–3 optimization techniques, and quantify the improvement
Next up: This stage equips you with the diagnostic and tuning skills to sustain high-performance production systems; the next stage will likely deepen specialized topics such as replication, high-availability architectures, or advanced scaling strategies that depend on a solid foundation in query optimization.
A dedicated, PostgreSQL-specific guide to query planning and optimization; it builds directly on the SQL fluency developed earlier and introduces the EXPLAIN/ANALYZE workflow systematically.
Expert Operations & Scaling
ExpertOperate PostgreSQL at scale: replication, high availability, backup strategies, connection pooling, and monitoring — the skills that separate a DBA from a true PostgreSQL expert.
▸ Study plan for this stage
Pace: 8–10 weeks, ~40–50 pages/day with hands-on lab work
- Streaming replication and logical replication architectures for distributing data across multiple PostgreSQL instances
- High availability patterns: failover mechanisms, automated recovery, and split-brain prevention using tools like Patroni and etcd
- Backup and recovery strategies: WAL archiving, point-in-time recovery (PITR), and designing backup retention policies for production systems
- Connection pooling with PgBouncer and pgpool-II to manage thousands of concurrent connections without overwhelming the database
- Monitoring and alerting infrastructure: tracking replication lag, identifying bottlenecks, and proactive performance tuning at scale
- Cascading replication and multi-tier topologies for distributing read load across geographically distributed replicas
- Handling operational challenges: managing replica promotion, handling network partitions, and coordinating maintenance windows in HA clusters
- What are the key differences between streaming replication and logical replication, and when would you choose one over the other in a production environment?
- How would you design a high-availability PostgreSQL cluster that automatically fails over to a replica without manual intervention, and what are the trade-offs?
- Describe a complete backup and recovery strategy for a large PostgreSQL database, including WAL archiving, retention policies, and how you would perform a point-in-time recovery.
- How does connection pooling improve database scalability, and what are the differences between transaction-level and session-level pooling in PgBouncer?
- What metrics would you monitor in a production PostgreSQL cluster to detect replication lag, identify performance degradation, and predict capacity issues?
- How would you set up cascading replication across multiple data centers, and what operational challenges would you need to address?
- Set up streaming replication between a primary and replica PostgreSQL instance; verify replication lag and test failover by promoting the replica
- Configure Patroni with etcd as a DCS to create an automated HA cluster; simulate primary failure and verify automatic failover without data loss
- Implement a complete backup strategy using pg_basebackup and WAL archiving to S3 or local storage; practice point-in-time recovery to a specific timestamp
- Deploy PgBouncer in front of a PostgreSQL instance; configure transaction and session pooling modes, then load-test to measure connection overhead reduction
- Set up logical replication between two PostgreSQL instances with selective table replication; test adding new tables and handling schema changes
- Build a monitoring stack (Prometheus + Grafana or similar) to track replication lag, transaction rates, cache hit ratios, and slow queries; create alerts for anomalies
- Design and implement a cascading replication topology (primary → replica1 → replica2); test failover at each level and verify data consistency
- Simulate network partitions and split-brain scenarios in a HA cluster; document recovery procedures and measure RTO/RPO
Next up: This stage equips you with the operational mastery to run PostgreSQL reliably at scale; the next stage will deepen your expertise in advanced optimization, security hardening, and architectural patterns for mission-critical systems.
Covers streaming replication, failover, load balancing, and connection pooling with concrete recipes — a natural capstone that applies everything learned about internals and performance to production reliability.
Discussion
Keep reading
Paths that share books, cover the same subject, or open a related topic.
