Course curriculum: beginner → expert lesson roadmap

[exekias]

Full lesson roadmap for the PostgreSQL course, ordered beginner → expert. Checked items already exist under lessons/.

Notes:

• The difficulty enum is beginner | intermediate | advanced, so "expert" tier maps to advanced (or we extend the enum).
• Everything runs in a learner's own branch except infra-level ops (replication, physical backup, WAL), intentionally left out as they don't fit the sandbox model.
• Modules 7 and 9 (indexes/EXPLAIN/partitioning/locking) need large seeds — good fit for the deploy-time template seeding + "generator → committed .sql" approach.

Module 1 — Query fundamentals · beginner

• 01-select-basics — projection, filtering, ordering
• 02-where-conditions — comparisons, AND/OR, IS NULL, LIKE, IN, BETWEEN
• 03-sorting-and-pagination — ORDER BY, DISTINCT, LIMIT/OFFSET, keyset pagination
• 04-aggregations — COUNT/SUM/AVG, GROUP BY, HAVING

Module 2 — Changing data · beginner

• 05-insert-and-update — INSERT, UPDATE, RETURNING
• 06-delete-and-lifecycle — DELETE, TRUNCATE, soft deletes
• 07-upsert — INSERT … ON CONFLICT (DO UPDATE/NOTHING)
• 08-transactions-basics — BEGIN/COMMIT/ROLLBACK, savepoints

Module 3 — Combining tables · beginner → intermediate

• 09-joins — INNER/LEFT, ON vs USING
• 10-advanced-joins — RIGHT/FULL/CROSS, self-joins, multi-table
• 11-set-operations — UNION, INTERSECT, EXCEPT
• 12-subqueries — scalar, IN/EXISTS, correlated, LATERAL

Module 4 — Schema & modeling · intermediate

• 13-data-types — numeric, text, boolean, enum, identity/serial
• 14-dates-and-times — timestamptz, intervals, time zones
• 15-constraints — PK, FK, UNIQUE, CHECK, NOT NULL
• 16-ddl-and-schemas — CREATE/ALTER TABLE, schemas, namespaces
• 17-normalization — keys, relationships, normal forms

Module 5 — Intermediate querying · intermediate

• 18-conditional-expressions — CASE, COALESCE, NULLIF
• 19-cte — WITH, readability, chaining
• 20-window-functions — OVER/PARTITION BY, ROW_NUMBER, RANK
• 21-window-functions-advanced — running totals, LAG/LEAD, frames
• 22-recursive-cte — hierarchies, graph walks

Module 6 — Postgres power types · intermediate → advanced

• 23-json-and-jsonb — operators, ->/->>/@>, path queries
• 24-arrays — array ops, unnest, ANY/ALL
• 25-full-text-search — tsvector/tsquery, ranking
• 26-ranges-and-generated-columns — range types, exclusion constraints, generated cols
• 27-uuid-and-domains — UUIDs, enums, domains

Module 7 — Performance & indexing · advanced

• 28-indexes-basics — B-tree, when/why, selectivity
• 29-explain — reading EXPLAIN / EXPLAIN ANALYZE
• 30-index-types — GIN, GiST, BRIN, partial, expression, covering
• 31-query-optimization — statistics, ANALYZE, common anti-patterns
• 32-partitioning — range/list partitioning, pruning

Module 8 — Programmability · advanced

• 33-views — views & materialized views, refresh
• 34-functions — SQL & PL/pgSQL functions
• 35-triggers — BEFORE/AFTER, trigger functions
• 36-procedures — stored procedures, transaction control

Module 9 — Concurrency · advanced → expert

• 37-mvcc-and-isolation — isolation levels, snapshots
• 38-locking — row/table locks, SELECT FOR UPDATE
• 39-deadlocks — detection, avoidance, advisory locks

Module 10 — Expert & operations · expert (= advanced)

• 40-roles-and-privileges — roles, GRANT, RLS (row-level security)
• 41-vacuum-and-bloat — MVCC cleanup, autovacuum, bloat
• 42-extensions — pg_trgm, citext, intro to PostGIS/others
• 43-capstone-troubleshooting — diagnose & fix a slow real-world query