PostgreSQL Mastery: all 82 lessons

Free PostgreSQL lessons from your first SELECT to production: joins, window functions, JSONB, indexes and EXPLAIN ANALYZE, MVCC and VACUUM, WAL, locking, partitioning, replication, scaling and pgvector. Every lesson runs on a real PostgreSQL 18 in the interactive course.

Open the interactive course All Learning Hub courses

Level 1: Foundations

psql, SELECT, NULL logic, data types, changing data and constraints.

PostgreSQL, psql and the shop database · PostgreSQL and psql · 15 min
PostgreSQL is an open-source relational database server with a strong focus on correctness, extensibility and standards. This lesson maps out how a server is organised and…
psql for DBAs: variables, \gset, \gexec, \if and \watch · PostgreSQL and psql · 25 min
psql is more than a prompt: it has variables, conditionals, result capture and statement generation, which turn it into a scripting tool for maintenance jobs and runbooks. This…
SELECT: filtering, sorting and paging · Querying a table · 20 min
SELECT is evaluated in a logical order that differs from the order you write it. Understanding that order explains which aliases you can use where, and why LIMIT without ORDER BY…
NULL and three-valued logic · Querying a table · 15 min
NULL means unknown, not zero or empty. Any comparison with NULL is itself unknown, and WHERE keeps only rows where the condition is true. That one rule explains most NULL…
Choosing data types · Data types · 20 min
PostgreSQL has a rich type system. The right type documents intent, enforces validity and keeps storage and comparisons fast; the wrong one causes rounding errors, time zone bugs…
Enums, domains and composite types · Data types · 25 min
PostgreSQL lets you define your own types. Enums give a fixed ordered list of labels, domains put a reusable constraint on a base type, and composite types group fields into one…
Collations, encodings and locales · Data types · 30 min
A collation decides how text sorts and compares; an encoding decides how characters are stored as bytes. Both are fixed when a database is created, both affect every text index,…
INSERT, UPDATE, DELETE, upserts and MERGE · Changing data · 25 min
Changing data in PostgreSQL comes with two features other databases lack or added late: RETURNING, which hands back the rows you changed, and transactional everything. Upserts use…
Tables, constraints and safe ALTER TABLE · Tables and constraints · 25 min
Constraints are the database enforcing your business rules for every client, every script and every future developer. PostgreSQL also runs DDL inside transactions, which makes…

Level 2: Querying

Joins, aggregation, subqueries, CTEs, dates and text.

Joins: inner, outer, semi, anti and self · Joins · 25 min
A join combines rows from two relations on a condition. The join type decides what happens to rows without a partner, and that choice is where most wrong reports come from.
GROUP BY, HAVING, FILTER and grouping sets · Aggregation · 25 min
Aggregation collapses many rows into one per group. PostgreSQL adds two very practical extras: FILTER for conditional aggregates, and GROUPING SETS / ROLLUP / CUBE for subtotals…
Subqueries, NOT IN and LATERAL · Subqueries · 25 min
Subqueries let a query ask another query a question. Scalar, IN and EXISTS subqueries cover most needs; LATERAL lets a subquery in FROM refer to earlier tables, which unlocks "top…
CTEs and recursive queries · CTEs · 25 min
WITH gives names to intermediate results, which makes long queries readable. Recursive CTEs walk hierarchies and graphs. Since PostgreSQL 12, simple CTEs are inlined into the main…
Dates, time zones and text · Dates and text · 25 min
timestamptz stores an absolute instant and displays it in the session time zone. Most date bugs come from mixing it with timestamp, or from grouping by days in the wrong zone.…

Level 3: Advanced SQL

Window functions, top-N per group, JSONB, arrays, ranges and search.

Window functions · Window functions · 30 min
A window function computes a value for each row from a set of related rows, without collapsing them the way GROUP BY does. Rankings, running totals, moving averages and…
Top-N per group, DISTINCT ON and gaps and islands · Window functions · 25 min
Three classic problems appear in almost every interview and many production queries: the latest row per key, the top N per group, and runs of consecutive values. PostgreSQL has a…
JSONB: operators, containment and SQL/JSON · JSONB · 30 min
jsonb stores JSON in a decomposed binary form that supports indexing and fast lookups. It is the right home for attributes that vary between rows, and the wrong home for fields…
Arrays, ranges and generate_series · Arrays and ranges · 20 min
Arrays and range types are first-class PostgreSQL types. Arrays suit small, ordered lists such as tags; ranges model intervals with built-in overlap logic and can prevent…
Full-text search and trigram similarity · Search · 25 min
PostgreSQL ships a real full-text search engine: documents become tsvectors of normalised lexemes, queries become tsqueries, and GIN indexes make matching fast. For typos and…
Accent-insensitive and fuzzy matching: unaccent and fuzzystrmatch · Search · 25 min
Users type "Sao Paulo" for São Paulo, "Patell" for Patel and "Smyth" for Smith. unaccent strips diacritics so searches match regardless of accents, and fuzzystrmatch adds edit…

Level 4: Performance

EXPLAIN ANALYZE, join algorithms, index types, statistics and tuning.

Reading EXPLAIN and EXPLAIN ANALYZE · Reading plans · 30 min
EXPLAIN shows the plan the optimizer chose and what it expected; EXPLAIN ANALYZE runs the query and adds what actually happened. Reading the two side by side is the core skill of…
Join algorithms and join order · Reading plans · 25 min
PostgreSQL joins two inputs with one of three algorithms: nested loop, hash join or merge join. Each wins in a different situation, and the choice depends almost entirely on row…
B-tree indexes: composite, covering and ordered · B-tree indexes · 30 min
The B-tree is the default index and covers equality, ranges, sorting and uniqueness. Most index design comes down to which columns, in which order, and whether the index can…
Partial, expression and unique indexes · B-tree indexes · 20 min
An index does not have to cover every row or a plain column. Partial indexes cover only the rows you query, expression indexes index a computed value, and partial unique indexes…
GIN, GiST, BRIN and hash indexes · Other index types · 25 min
B-trees cover ordered scalar data. For documents, arrays, text, geometry, ranges and huge append-only tables, PostgreSQL offers other index types, each built for a specific shape…
Specialised index extensions: btree_gin, bloom and intarray · Other index types · 25 min
Contrib ships index tools for cases the built-in operator classes do not cover well: combining scalar columns with JSONB or arrays in one GIN index, indexing many columns for…
Planner statistics and misestimates · Planner statistics · 25 min
The planner never looks at your data while planning; it reads statistics gathered by ANALYZE. Most bad plans trace back to a row estimate that is wrong because the statistics are…
Finding slow queries with pg_stat_statements · Finding and fixing slow queries · 20 min
Tuning starts with knowing which queries cost the most in total, not which one felt slow. pg_stat_statements aggregates every normalised query with calls, time, rows and buffer…
Rewrite patterns that make queries fast · Finding and fixing slow queries · 25 min
Many slow queries are fixed by rewriting, not by indexing: keeping predicates sargable, paging by key instead of offset, replacing OR across columns, batching instead of looping,…
Parallel query and JIT compilation · Finding and fixing slow queries · 25 min
PostgreSQL can split one query across several worker processes and can compile expressions to machine code with LLVM. Both help large analytical queries and can hurt short OLTP…

Level 5: Internals

Architecture, MVCC, VACUUM, freezing, isolation, locks, WAL and memory.

Processes, memory, pages and TOAST · Architecture · 25 min
PostgreSQL runs one operating-system process per connection, shares a page cache between them, and stores every table as files of 8 kB pages. That design explains why connections…
Looking inside: buffer cache, visibility map, free space and WAL · Architecture · 30 min
Four contrib extensions let you look at what the server is doing internally: which tables occupy shared_buffers, which pages are all-visible or frozen, how much free space each…
MVCC: tuple versions, snapshots and HOT · MVCC · 30 min
PostgreSQL never overwrites a row in place. Every UPDATE writes a new version and every DELETE marks the old one, and each transaction sees the versions that were committed when…
VACUUM, autovacuum and bloat · VACUUM · 30 min
VACUUM removes dead tuples so their space can be reused, updates the visibility map for index-only scans, and freezes old rows. Autovacuum runs it automatically; tuning autovacuum…
Freezing and transaction ID wraparound · VACUUM · 20 min
Transaction IDs are 32-bit and wrap around after about 4 billion. To keep old rows visible forever, VACUUM freezes them. If freezing falls behind for too long, PostgreSQL protects…
Transactions and isolation levels · Transactions · 30 min
PostgreSQL offers READ COMMITTED (the default), REPEATABLE READ and SERIALIZABLE. Each level is defined by which concurrency anomalies it allows. Choosing well, and retrying when…
Locks, deadlocks and safe DDL · Locking · 30 min
PostgreSQL locks at two levels: table-level lock modes taken by every statement, and row-level locks taken by writes and SELECT ... FOR UPDATE. Most lock incidents in production…
WAL, checkpoints and crash recovery · WAL and checkpoints · 25 min
Every change is first written to the write-ahead log. Data pages are flushed later, in the background, and after a crash PostgreSQL replays WAL from the last checkpoint. The same…
Memory and the settings that matter · Memory and configuration · 25 min
A few settings decide most of PostgreSQL's behaviour: shared_buffers, work_mem, maintenance_work_mem, effective_cache_size, the cost settings and max_connections. Understanding…
Running PostgreSQL on Linux: huge pages, OOM, kernel settings and memory contexts · Memory and configuration · 30 min
PostgreSQL relies on the operating system for memory, files and I/O, and several Linux defaults are wrong for a database server. This lesson covers huge pages, memory overcommit…
System catalogs: how PostgreSQL describes itself · System catalogs · 25 min
Every table, column, index, constraint, function and role is a row in a system catalog. psql meta-commands, ORMs, migration tools and monitoring agents all read these tables.…
The DBA catalog toolbox · System catalogs · 25 min
A small set of catalog queries finds most schema problems before they become incidents: tables without primary keys, foreign keys without indexes, duplicate and invalid indexes,…

Level 6: Design and Security

Schema design, partitioning, functions, views, roles and row-level security.

Schema design: keys, types and normalization · Schema design · 30 min
Good schemas make correct data easy and wrong data impossible. In PostgreSQL that means normalising where facts change independently, choosing keys deliberately, using the type…
Table partitioning · Partitioning · 30 min
Declarative partitioning splits one logical table into child tables by range, list or hash. The big wins are operational: dropping a month of data instantly, keeping indexes…
Functions, procedures and triggers · Functions and triggers · 30 min
PostgreSQL runs code inside the database in SQL or PL/pgSQL. Functions return values and can be used in queries, procedures can manage transactions, and triggers run code…
Procedures and transaction control · Functions and triggers · 20 min
Procedures (since PostgreSQL 11) are called with CALL and, unlike functions, may COMMIT and ROLLBACK inside their body. That makes them the right tool for batch jobs that must…
PL/pgSQL in depth: errors, dynamic SQL and set-returning functions · Functions and triggers · 30 min
Beyond simple functions, production PL/pgSQL needs three skills: handling errors precisely, building dynamic SQL without injection holes, and returning sets of rows efficiently.…
Event triggers: reacting to DDL · Functions and triggers · 20 min
Ordinary triggers fire on row changes. Event triggers fire on database-wide events such as CREATE, ALTER and DROP, table rewrites and, from PostgreSQL 17, logins. They are how…
How extensions work · Extensions · 20 min
Extensions package new types, functions, operators, index methods and even background workers into a unit you install with one command. Much of what people love about PostgreSQL…
Useful extensions: pgcrypto, citext, hstore, ltree and btree_gist · Extensions · 30 min
A handful of contrib extensions solve common problems neatly: password hashing and encryption (pgcrypto), case-insensitive text (citext), simple key-value columns (hstore),…
Foreign data wrappers and tablespaces · Extensions · 25 min
Foreign data wrappers let PostgreSQL query files, other PostgreSQL servers and other databases as if they were local tables. Tablespaces decide on which filesystem a table or…
Geospatial data: geometric types, earthdistance and PostGIS · Extensions · 30 min
Location queries such as "stores within 10 km" or "the nearest courier" are common in applications. PostgreSQL has built-in geometric types and GiST indexes, the earthdistance…
Data utilities: crosstab, table sampling and UUIDs · Extensions · 20 min
A few small extensions and built-ins come up constantly: tablefunc pivots rows into columns, TABLESAMPLE and tsm_system_rows read a random sample without scanning the table, and…
The production extension toolkit · Extensions · 30 min
Most production PostgreSQL fleets run the same short list of extensions: statistics and plan capture, auditing, scheduling, partition management, online bloat removal, cache…
Views and materialized views · Views · 20 min
A view is a stored query: always current, no storage, and the planner merges it into the calling query. A materialized view stores the result, which makes reads fast and data…
Less common features: rules, NOT ENFORCED constraints, network and XML types, leakproof functions, pipelining · Views · 30 min
Some features are rarely the right first choice but show up in real schemas, migrations from other databases and interviews: the rule system behind views, informational…
Roles, privileges and least privilege · Security · 25 min
PostgreSQL has one concept for users and groups: roles. Privileges are granted on objects to roles, roles can be members of other roles, and default privileges decide what future…
Row-level security for multi-tenant data · Security · 25 min
Row-level security (RLS) adds a WHERE clause the database enforces on every query against a table. It is the standard way to keep tenants' data apart in a shared schema, and to…
Client authentication and TLS · Security · 30 min
Before a role can run a single query, the server decides whether the connection is allowed and how the client must prove who it is. pg_hba.conf, password storage and TLS settings…

Level 7: Operations and HA

Backups and PITR, replication, failover, pooling, monitoring and upgrades.

Backups, WAL archiving and point-in-time recovery · Backup and recovery · 30 min
PostgreSQL has two kinds of backup. Logical backups (pg_dump) export SQL or an archive of objects and data; physical backups copy the data directory and, combined with archived…
Streaming replication · Replication · 30 min
A physical standby continuously receives the primary's WAL and replays it, staying a byte-identical copy that can serve read-only queries and take over on failure. Replication…
Logical replication and change data capture · Replication · 25 min
Logical replication sends row changes for selected tables as a stream of inserts, updates and deletes. Unlike physical replication it works across major versions and lets the…
Replication in 17 and 18: failover slots, pg_createsubscriber and incremental backup · Replication · 25 min
Recent releases fixed long-standing replication pain points: logical replication slots now survive failover, a physical standby can be converted into a logical subscriber without…
High availability and failover · High availability · 25 min
PostgreSQL provides replication and promotion; it does not decide on its own when to fail over. A cluster manager such as Patroni or a Kubernetes operator watches health, elects a…
PostgreSQL on Kubernetes: CloudNativePG, Crunchy PGO and Zalando · High availability · 30 min
A StatefulSet can run a PostgreSQL pod, but it cannot fail over, take backups or run upgrades. Operators encode those runbooks. Learn how the three main operators differ, and…
Connections and pooling · Connections and pooling · 20 min
Every PostgreSQL connection is a process with its own memory, so thousands of mostly idle connections waste resources and slow everything down. A pooler such as PgBouncer…
Monitoring: activity, waits and key metrics · Monitoring · 30 min
Most incidents are diagnosed from a handful of views: pg_stat_activity for what is running and waiting, pg_locks for blocking, pg_stat_statements for load, and the per-table and…
Server logs, auto_explain and auditing · Monitoring · 30 min
The server log is where PostgreSQL tells you about slow statements, lock waits, checkpoints, autovacuum, failed logins and crashes. Configured well it answers most incident…
Upgrades and routine maintenance · Upgrades and maintenance · 20 min
Minor releases (18.1 to 18.2) are bug and security fixes and should be applied promptly with a restart. Major releases change the storage format and need pg_upgrade, a dump and…
Server command-line tools: pgbench, pg_waldump, pg_checksums, pg_amcheck and friends · Upgrades and maintenance · 25 min
PostgreSQL ships a set of server-side programs for benchmarking, inspecting WAL and control files, verifying data and backups, and bulk maintenance. Each has an SQL equivalent for…

Level 8: Scale and Architecture

Scaling paths, sharding and Citus, distributed transactions, reference architectures and capacity planning.

Scaling PostgreSQL: the order of moves · Scaling PostgreSQL · 25 min
Most PostgreSQL databases that "need sharding" need a better index, a connection pooler or more RAM. There is a well-worn order of scaling moves, from cheap and reversible to…
Read replicas, caching and summary tables · Scaling PostgreSQL · 25 min
Read replicas, materialized views and summary tables all trade freshness for read capacity. The engineering is in deciding how stale each read may be, and in making sure a user…
Sharding: shard keys and routing · Sharding and distributed PostgreSQL · 30 min
Sharding splits rows across several databases by a shard key. Almost every consequence of sharding, good or bad, follows from that key: which queries stay on one shard, which ones…
Distributed PostgreSQL: Citus and co-location · Sharding and distributed PostgreSQL · 25 min
Citus turns PostgreSQL into a sharded cluster: distributed tables are split into shards on worker nodes, small tables are replicated to every node, and queries are routed or…
Transactions across databases · Sharding and distributed PostgreSQL · 30 min
Once data lives in more than one database, a single ACID transaction no longer covers a business operation. Two-phase commit, the transactional outbox, idempotency keys and sagas…
Reference architectures, RPO and RTO · Production architecture · 30 min
A production PostgreSQL design is a set of answers to two numbers: how much data you can lose (RPO) and how long you can be down (RTO). Synchronous replicas, WAL archiving,…
Capacity planning from your own data · Production architecture · 25 min
Capacity planning is arithmetic on numbers PostgreSQL already has: bytes per row, rows per month, connection counts and memory settings. A few queries turn them into a forecast…

Level 9: AI and Vector Search

pgvector, HNSW and IVFFlat indexes, hybrid search and retrieval-augmented generation.

Embeddings and the vector type · Vectors and pgvector · 25 min
An embedding model turns text, images or products into vectors so that similar things end up close together. pgvector adds a vector type and distance operators to PostgreSQL, so…
HNSW and IVFFlat indexes · Vectors and pgvector · 30 min
Exact nearest-neighbour search reads every vector. HNSW and IVFFlat indexes answer approximately and very much faster, trading a little recall for speed. Knowing their parameters,…
Running vector search in production · Vectors and pgvector · 25 min
At scale, vector search is a storage and memory problem: dimensions times rows times bytes per dimension. halfvec, binary quantization with re-ranking, partial indexes and…
Hybrid search: keywords and vectors together · Search and RAG · 30 min
Vector search finds meaning but misses exact terms such as product codes and rare names; keyword search is the reverse. Hybrid search runs both and fuses the rankings, usually…
Retrieval-augmented generation on PostgreSQL · Search and RAG · 30 min
RAG answers questions with a language model grounded in your own documents. PostgreSQL can own every part except the model call: chunks, embeddings, metadata, access control,…