Your country

Tools that support it use your country for local currency, number formats, units and paper size. Your choice is saved only in this browser.

Type a name or a two-letter code. Use the up and down arrow keys to move through the countries, Enter to choose one and Escape to close.

Data and databases

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 to read 12 modules

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

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.

  1. 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.

    1. PostgreSQL architecture: processes, memory, files Beginner Free lesson, Pro answers: 10 minutes
    2. Creating a cluster: initdb, settings, ALTER SYSTEM Beginner Free lesson, Pro answers: 10 minutes
    3. psql for daily work: meta-commands and scripts Beginner Free lesson, Pro answers: 10 minutes
    4. System catalogs, pages and TOAST Intermediate Free lesson, Pro answers: 10 minutes
  2. 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.

    1. Numbers, identity, UUIDv7 and generated columns Beginner Free lesson, Pro answers: 20 minutes
    2. Text, collations and case-insensitive matching Intermediate Free lesson, Pro answers: 15 minutes
    3. Ranges, multiranges and WITHOUT OVERLAPS keys Intermediate Free lesson, Pro answers: 10 minutes
    4. Arrays, enums, domains and composite types Intermediate Free lesson, Pro answers: 25 minutes
    5. JSONB at scale: jsonpath, indexes, update costs Intermediate Free lesson, Pro answers: 25 minutes
  3. 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.

    1. Reading EXPLAIN ANALYZE in PostgreSQL 18 Intermediate Free lesson, Pro answers: 10 minutes
    2. B-tree indexes: dedup, INCLUDE and skip scan Intermediate Free lesson, Pro answers: 10 minutes
    3. Index design: partial, expression, write cost Intermediate Free lesson, Pro answers: 10 minutes
    4. GIN, GiST, SP-GiST, BRIN, hash and bloom Advanced Free lesson, Pro answers: 10 minutes
    5. Building and checking indexes online Advanced Free lesson, Pro answers: 10 minutes
  4. 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.

    1. Planner statistics and extended statistics Advanced Free lesson, Pro answers: 10 minutes
    2. Joins, Memoize, parallel query and JIT Advanced Free core, Pro depth: 5 of 15 minutes free
    3. Prepared statements and generic plans Advanced Free core, Pro depth: 4 of 10 minutes free
    4. Query rewrites the PostgreSQL planner rewards Advanced Free core, Pro depth: 4 of 10 minutes free
  5. Module 5

    MVCC, VACUUM and freezing

    Row versions and the xmin horizon, autovacuum tuning, freezing and wraparound, HOT updates, bloat and rebuilding tables.

    1. MVCC in production: the xmin horizon Intermediate Free lesson, Pro answers: 15 minutes
    2. VACUUM and autovacuum: triggers and tuning Advanced Free core, Pro depth: 6 of 20 minutes free
    3. Freezing and transaction ID wraparound Advanced Free core, Pro depth: 5 of 15 minutes free
    4. HOT updates, fillfactor, bloat and rebuilds Advanced Free core, Pro depth: 5 of 20 minutes free
  6. 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.

    1. Isolation in PostgreSQL: RC, RR and SSI Intermediate Free lesson, Pro answers: 10 minutes
    2. Row locks, SKIP LOCKED queues, advisory locks Advanced Free core, Pro depth: 6 of 20 minutes free
    3. Table locks, lock queues, deadlocks, timeouts Advanced Free core, Pro depth: 5 of 15 minutes free
    4. Zero-downtime schema migrations Advanced Free core, Pro depth: 5 of 15 minutes free
  7. Module 7

    Partitioning large tables

    Declarative partitioning and partition pruning, then partition maintenance and data retention.

    1. Declarative partitioning: keys and pruning Intermediate Free lesson, Pro answers: 15 minutes
    2. Partition maintenance and data retention Advanced Free core, Pro depth: 5 of 15 minutes free
  8. Module 8

    Server-side code and extensions

    Functions, procedures and PL/pgSQL, triggers and event triggers, and extensions with foreign data wrappers.

    1. Functions, procedures and PL/pgSQL done right Intermediate Free lesson, Pro answers: 15 minutes
    2. Triggers and event triggers: what fires when Intermediate Free core, Pro depth: 6 of 15 minutes free
    3. Extensions and foreign data wrappers Intermediate Free core, Pro depth: 7 of 20 minutes free
  9. Module 9

    Search, vectors and maps

    Full-text and trigram search, vector similarity search with pgvector, and spatial data with PostGIS.

    1. Full-text and trigram search in PostgreSQL Intermediate Free lesson, Pro answers: 15 minutes
    2. Vector similarity search with pgvector Intermediate Free core, Pro depth: 5 of 15 minutes free
    3. Spatial queries with PostGIS Intermediate Free core, Pro depth: 6 of 15 minutes free
  10. Module 10

    WAL, replication and high availability

    WAL, checkpoints and durability, streaming replication and slots, logical replication and change data capture, and failover.

    1. WAL, checkpoints, checksums and durability Intermediate Free lesson, Pro answers: 20 minutes
    2. Streaming replication: standbys, slots and lag Advanced Free core, Pro depth: 5 of 15 minutes free
    3. Logical replication and change data capture Advanced Free core, Pro depth: 5 of 20 minutes free
    4. High availability: failover and Patroni Advanced Free core, Pro depth: 5 of 20 minutes free
  11. 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.

    1. Monitoring: activity, waits, I/O and logs Intermediate Free lesson, Pro answers: 20 minutes
    2. Finding slow queries: pg_stat_statements Intermediate Free core, Pro depth: 6 of 20 minutes free
    3. Server tuning: memory, WAL and asynchronous I/O Advanced Free core, Pro depth: 5 of 20 minutes free
    4. Connections and pooling with PgBouncer Intermediate Free core, Pro depth: 5 of 15 minutes free
  12. 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.

    1. From app code: COPY, pipelining, RETURNING Intermediate Free lesson, Pro answers: 20 minutes
    2. Scaling PostgreSQL: replicas, caches, shards Advanced Free core, Pro depth: 5 of 15 minutes free

Related tools

Official documentation

More in Data and databases

Quick answers and tool search

Type to search tools or to get a quick answer, for example 18% of 2500. Use the up and down arrow keys to move through the results, Enter to choose, and Escape to close.