Course Contents — 6 PostgreSQL Courses

Learning tiers included in every course

Core

Foundations and mental models. Six modules per course building practical fluency with the subject's core SQL constructs.

Modules 1 – 6

Advanced

Real-world patterns, performance analysis, and composing complex queries under production constraints.

Module 7

Architect

System-level thinking: data architecture, scalability trade-offs, and integration with the broader PostgreSQL ecosystem.

Module 8

All 6 courses

Master Window Functions

Write analytical SQL without collapsing rows or procedural workarounds.

f1db — Formula One racing

OVER clause, PARTITION BY, frame semantics (ROWS/RANGE/GROUPS)
Ranking functions — ROW_NUMBER, RANK, DENSE_RANK, NTILE
Running totals, moving averages, LEAD & LAG
Sessionization, cohort analysis, gaps & islands
WindowAgg plan nodes, index strategies, materialized views

Full curriculum →

Reliable Aggregation

Avoid correctness traps and build reports you can trust.

f1db — Formula One racing

GROUP BY semantics — evaluation order, row vs group context
COUNT DISTINCT, FILTER clause, WHERE vs HAVING
GROUPING SETS, ROLLUP, CUBE
Ordered-set aggregates — percentile_cont, mode()
Incremental aggregation, ETL/ELT patterns, MERGE

Full curriculum →

Data Modeling for Performance

Schema design from normal forms through indexes and safe evolution.

chinook & f1db — plus JSONB, temporal examples

Normal forms, database anomalies, FK enforcement trade-offs
Natural vs surrogate PKs, UUID, check & exclusion constraints
All index types — B-tree, GiST, GIN, BRIN, partial, covering
Safe schema evolution — NOT VALID, CONCURRENTLY, three-step pattern
JSONB document model, audit trails, SQL/JSON path

Full curriculum →

Advanced JOIN Techniques

SQL as relational algebra — all join types, LATERAL, and recursive CTEs.

f1db, chinook, tweet, geoname

All join types — INNER, LEFT, FULL OUTER, CROSS, NATURAL
WITH RECURSIVE — SEARCH and CYCLE clauses (PostgreSQL 14)
LATERAL — top-N per group, kNN geographic search
Semi-JOIN (EXISTS), anti-JOIN (NOT EXISTS), NOT IN null trap
Hash Join, Nested Loop, Merge Join — plan reading and index impact

Full curriculum →

Query Optimization Fundamentals

Systematic workflow: observe, diagnose, fix, verify.

f1db, chinook, geoname, public.hashtag

PostgreSQL cost model, pg_stats, pg_stat_statements
EXPLAIN ANALYZE — actual timing, row divergence, BUFFERS
Predicate pushdown, CTE fences, anti-patterns
All index types, composite indexes, index-only scans
Partitioning, parallel query, performance budgets, query SLAs

Full curriculum →

Read Query Plans

Fluent EXPLAIN reading — every node type and what to do about it.

f1db, chinook, geoname, public.hashtag

Plan tree anatomy — startup vs total cost, indentation model
Every node type: scans, joins, aggregates, set ops, parallel
Detecting row misestimation, spill to disk, expensive sorts
FORMAT JSON, explain.depesz.com, pev2, cold vs warm cache
pg_stat_activity, VACUUM signals, lock monitoring, CREATE STATISTICS

Full curriculum →

6 Courses — 48 Modules

Master Window Functions

Reliable Aggregation

Data Modeling for Performance

Advanced JOIN Techniques

Query Optimization Fundamentals

Read Query Plans

All 6 courses in one package.