PostgreSQL Mastery Path¶

Mastery Path

BOOTCAMP

VINOD BHANUJA

PostgreSQL is the database people reach for when they want one system to do a great deal: strict transactions, rich types, JSON documents, full-text search, geospatial and vector extensions, partitioning, replication — all open source, all in one server. It rewards understanding. The same database that serves one team effortlessly will bloat, lock up or lose data for another, and the difference is almost never the hardware. It is whether someone on the team knows what an UPDATE really writes, why a forgotten transaction stops VACUUM, which ALTER TABLE takes the whole table offline, and how to get back to the second before someone ran DELETE without a WHERE.

This course teaches that understanding. It is not an SQL course — the SQL Mastery Path covers queries, joins and aggregation, and this course assumes them. Here the subject is PostgreSQL itself: how it stores and versions rows, how it chooses plans, how it locks, logs and replicates, and how to design, tune, secure and operate it.

Level 1 covers the foundations of working with PostgreSQL rather than generic SQL: the server's process model and data directory, psql, the data types that matter, schemas and search_path, roles and pg_hba.conf, constraints that enforce rules under concurrency, isolation levels reproduced with real concurrent sessions, upserts and MERGE, and logical backups — ending with a ticketing database that survives 40 simultaneous buyers fighting for 5 seats. Level 2 opens the engine: MVCC row versions read straight off disk pages, VACUUM and wraparound, TOAST and HOT updates, B-tree, GIN, GiST, BRIN and hash indexes, reading EXPLAIN ANALYZE, planner statistics, locking and deadlocks, and the write-ahead log — ending with a slow help-desk database taken from 29 to over 22,000 transactions per second. Level 3 covers the advanced features: JSONB, full-text search, PL/pgSQL, triggers and audit logs, LATERAL and recursive queries, partitioning, row-level security, extensions including pgvector, and a job queue built on SKIP LOCKED — ending with a multi-tenant SaaS backend. Level 4 is production: configuration, connection pooling with PgBouncer, streaming and logical replication, point-in-time recovery, monitoring, zero-downtime migrations, security hardening, upgrades and high availability — ending with a hardened primary and replica proven by a recovery drill and a failover drill.

Every lesson has a How It Actually Works section — what a snapshot is and how a row decides whether you can see it, why a rolled-back delete leaves its transaction ID behind, why the first change after a checkpoint writes a whole page to the log, why a cast on a partition key disables pruning, how a replication slot can fill a disk, and why pg_upgrade can upgrade a terabyte in minutes.

Versions and what was actually run

Every command, query and script in this course was run on PostgreSQL 18.6 (Homebrew build on macOS), and every output shown is the output it produced — including the errors, which are some of the most useful parts. Supporting tools: psycopg 3.3 for multi-session demonstrations, PgBouncer 1.26, pgvector 0.8.7, and PostgreSQL 17.11 for the upgrade lesson. Timings and throughput numbers come from a laptop and are there to show proportions, not to benchmark hardware; where a lesson compares numbers, both sides were measured the same way on the same machine.

Features new in PostgreSQL 16, 17 and 18 are marked as such. Things that were not run — managed cloud services, Patroni and other failover managers, pgBackRest and similar backup tools, PostGIS, TimescaleDB — are described, never shown with invented output.

How the program is organized¶

Level	Focus	Modules	What you can do after
1 · PostgreSQL Foundations	Server, psql, types, schemas, roles, constraints, isolation, backups	10	Design a schema where the database enforces your rules, and back it up
2 · Internals: MVCC, Storage & Indexes	MVCC, VACUUM, storage, index types, EXPLAIN, statistics, locks, WAL	10	Diagnose and fix slow queries and bloat from first principles
3 · Advanced Features	JSONB, search, PL/pgSQL, triggers, partitioning, RLS, extensions, queues	10	Build rich backends without bolting on extra systems
4 · Operations & Production	Tuning, pooling, replication, PITR, monitoring, migrations, security, upgrades	10	Run PostgreSQL in production and recover when things go wrong

Each level has 9 teaching modules plus a hands-on project (module 10), for 40 lessons total.

How to use this site¶

Run your own server. Level 1 · 01 shows how to create a throwaway cluster in a directory you can delete. Several lessons need superuser access or several servers on different ports — a laptop is ideal; a shared company database is not.
Reproduce the outputs. Your transaction IDs, timings and sizes will differ; the behaviour should not. When something differs in kind, find out why — that is usually where the learning is.
Do the concurrency experiments with two sessions. Isolation, locking, queues and replication only make sense when you watch two things happen at once.
Do the projects. Each one combines its level into something you could adapt for real work, and each was tested end to end, failures included.