PostgreSQL in Depth
PostgreSQL 18 from the inside: processes, storage, plans, indexes, MVCC, vacuum, locks, replication, backups, security and tuning, with examples that run.
44 lessons: 23 free with Pro answers and 21 with a free core.
Examples run with PostgreSQL 18.6.
Who it is for
Back-end developers and DBAs who run PostgreSQL in production.
What you will be able to do
- Explain how PostgreSQL's processes, shared memory and data files work together, and set up and configure a cluster
- Choose data types, constraints and keys that keep data correct and compact
- Read EXPLAIN ANALYZE output and design B-tree, GIN, GiST, BRIN and other indexes for real queries
- Explain MVCC, VACUUM and freezing, and tune autovacuum before bloat or wraparound becomes an incident
- Run schema migrations without long locks, and choose isolation levels and row locks for concurrent work
- Set up streaming and logical replication, backups and point-in-time recovery, and rehearse a restore
- Secure a server with authentication, roles, privileges and row-level security
- Monitor, tune and pool connections, and plan how a PostgreSQL database grows
Revise and practise
- Practice: every exercise and quiz (2 exercises, 44 quizzes)
- PostgreSQL in Depth flashcards
Modules and lessons
What the labels mean
- Free lesson, Pro answers
- the whole lesson is free; the model answers to its interview questions open with a pass
- Free core, Pro depth
- the core of the lesson is free; its in-depth parts open with a pass
- Pro
- opens with a Pro, Premium or Ultimate pass
Minutes are reading time: “5 of 15 min free” means 5 minutes of the lesson are free.
What a pass opens in this track
58 in-depth parts and 112 interview answers. The same pass opens every other track and every Pro tool.
- Module 1
How PostgreSQL works and how to run it
The processes, memory and files of a server, creating and configuring a cluster, psql for daily work, and the catalogs, pages and TOAST underneath every table.
- PostgreSQL architecture: processes, memory, files Free lesson, Pro answers: 10 minutes
- Creating a cluster: initdb, settings, ALTER SYSTEM Free lesson, Pro answers: 10 minutes
- psql for daily work: meta-commands and scripts Free lesson, Pro answers: 10 minutes
- System catalogs, pages and TOAST Free lesson, Pro answers: 10 minutes
- Module 2
PostgreSQL data types, chosen well
Numbers, identity columns and UUIDs, text and collations, ranges and temporal keys, arrays, enums, domains and composite types, and JSONB at scale.
- Numbers, identity, UUIDv7 and generated columns Free lesson, Pro answers: 20 minutes
- Text, collations and case-insensitive matching Free lesson, Pro answers: 15 minutes
- Ranges, multiranges and WITHOUT OVERLAPS keys Free lesson, Pro answers: 10 minutes
- Arrays, enums, domains and composite types Free lesson, Pro answers: 25 minutes
- JSONB at scale: jsonpath, indexes, update costs Free lesson, Pro answers: 25 minutes
- Module 3
Query plans and indexes
Reading EXPLAIN ANALYZE, B-tree deduplication, INCLUDE and skip scan, index design and its write costs, the other index types, and online index work.
- Reading EXPLAIN ANALYZE in PostgreSQL 18 Free lesson, Pro answers: 10 minutes
- B-tree indexes: dedup, INCLUDE and skip scan Free lesson, Pro answers: 10 minutes
- Index design: partial, expression, write cost Free lesson, Pro answers: 10 minutes
- GIN, GiST, SP-GiST, BRIN, hash and bloom Free lesson, Pro answers: 10 minutes
- Building and checking indexes online Free lesson, Pro answers: 10 minutes
- Module 4
The planner in depth
Statistics and row estimates, join methods with memoize, parallel query and JIT, prepared statements and the plan cache, and rewrites that help the planner.
- Planner statistics and extended statistics Free lesson, Pro answers: 10 minutes
- Joins, Memoize, parallel query and JIT Free core, Pro depth: 5 of 15 minutes free
- Prepared statements and generic plans Free core, Pro depth: 4 of 10 minutes free
- Query rewrites the PostgreSQL planner rewards Free core, Pro depth: 4 of 10 minutes free
- Module 5
MVCC, VACUUM and freezing
Row versions and the xmin horizon, autovacuum tuning, freezing and wraparound, HOT updates, bloat and rebuilding tables.
- MVCC in production: the xmin horizon Free lesson, Pro answers: 15 minutes
- VACUUM and autovacuum: triggers and tuning Free core, Pro depth: 6 of 20 minutes free
- Freezing and transaction ID wraparound Free core, Pro depth: 5 of 15 minutes free
- HOT updates, fillfactor, bloat and rebuilds Free core, Pro depth: 5 of 20 minutes free
- Module 6
Concurrency, locks and safe migrations
Isolation levels in PostgreSQL, row locks, queues and advisory locks, table locks and timeouts, and schema migrations without downtime.
- Isolation in PostgreSQL: RC, RR and SSI Free lesson, Pro answers: 10 minutes
- Row locks, SKIP LOCKED queues, advisory locks Free core, Pro depth: 6 of 20 minutes free
- Table locks, lock queues, deadlocks, timeouts Free core, Pro depth: 5 of 15 minutes free
- Zero-downtime schema migrations Free core, Pro depth: 5 of 15 minutes free
- Module 7
Partitioning large tables
Declarative partitioning and partition pruning, then partition maintenance and data retention.
- Declarative partitioning: keys and pruning Free lesson, Pro answers: 15 minutes
- Partition maintenance and data retention Free core, Pro depth: 5 of 15 minutes free
- Module 8
Server-side code and extensions
Functions, procedures and PL/pgSQL, triggers and event triggers, and extensions with foreign data wrappers.
- Functions, procedures and PL/pgSQL done right Free lesson, Pro answers: 15 minutes
- Triggers and event triggers: what fires when Free core, Pro depth: 6 of 15 minutes free
- Extensions and foreign data wrappers Free core, Pro depth: 7 of 20 minutes free
- Module 9
Search, vectors and maps
Full-text and trigram search, vector similarity search with pgvector, and spatial data with PostGIS.
- Full-text and trigram search in PostgreSQL Free lesson, Pro answers: 15 minutes
- Vector similarity search with pgvector Free core, Pro depth: 5 of 15 minutes free
- Spatial queries with PostGIS Free core, Pro depth: 6 of 15 minutes free
- Module 10
WAL, replication and high availability
WAL, checkpoints and durability, streaming replication and slots, logical replication and change data capture, and failover.
- WAL, checkpoints, checksums and durability Free lesson, Pro answers: 20 minutes
- Streaming replication: standbys, slots and lag Free core, Pro depth: 5 of 15 minutes free
- Logical replication and change data capture Free core, Pro depth: 5 of 20 minutes free
- High availability: failover and Patroni Free core, Pro depth: 5 of 20 minutes free
- Module 13
Monitoring, tuning and connection pooling
Activity, waits and I/O, pg_stat_statements and auto_explain, tuning memory, WAL and asynchronous I/O, and connection pooling with PgBouncer.
- Monitoring: activity, waits, I/O and logs Free lesson, Pro answers: 20 minutes
- Finding slow queries: pg_stat_statements Free core, Pro depth: 6 of 20 minutes free
- Server tuning: memory, WAL and asynchronous I/O Free core, Pro depth: 5 of 20 minutes free
- Connections and pooling with PgBouncer Free core, Pro depth: 5 of 15 minutes free
- Module 14
Applications, scaling and what comes next
Application patterns with COPY, pipelines and RETURNING, scaling PostgreSQL, managed services, and the changes PostgreSQL 19 brings.
- From app code: COPY, pipelining, RETURNING Free lesson, Pro answers: 20 minutes
- Scaling PostgreSQL: replicas, caches, shards Free core, Pro depth: 5 of 15 minutes free
Related tools
Official documentation
- PostgreSQL 18 Documentation (The PostgreSQL Global Development Group)
- PostgreSQL versioning policy (The PostgreSQL Global Development Group)
- PostgreSQL 18 release notes (The PostgreSQL Global Development Group)