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 (PR: Add lesson 16: DDL and schemas #61)
• 17-normalization — keys, relationships, normal forms (PR: Add lesson 17: Normalization #59)

Module 5 — Intermediate querying · intermediate

• 18-conditional-expressions — CASE, COALESCE, NULLIF (PR: Add lesson 18: Conditional expressions #58)
• 19-cte — WITH, readability, chaining (PR: Add lesson 19: CTEs #57)
• 20-window-functions — OVER/PARTITION BY, ROW_NUMBER, RANK (PR: Add lesson 20: Window functions #60)
• 21-window-functions-advanced — running totals, LAG/LEAD, frames (PR: Add lesson 21: Advanced window functions #80)
• 22-recursive-cte — hierarchies, graph walks (PR: Add lesson 22: Recursive CTEs #81)

Module 6 — Postgres power types · intermediate → advanced

• 23-json-and-jsonb — operators, ->/->>/@>, path queries (PR: Add lesson 23: JSON and JSONB #82)
• 24-arrays — array ops, unnest, ANY/ALL (PR: Add lesson 24: Arrays #83)
• 25-full-text-search — tsvector/tsquery, ranking (PR: Add lesson 25: Full-text search #84)
• 26-ranges-and-generated-columns — range types, exclusion constraints, generated cols (PR: Add lesson 26: Ranges and generated columns #85)
• 27-uuid-and-domains — UUIDs, enums, domains (PR: Add lesson 27: UUIDs and domains #86)

Module 7 — Performance & indexing · advanced

• 28-indexes-basics — B-tree, when/why, selectivity (PR: Add lesson 28: Indexes basics #87)
• 29-explain — reading EXPLAIN / EXPLAIN ANALYZE (PR: Add lesson 29: Reading EXPLAIN #88)
• 30-index-types — GIN, GiST, BRIN, partial, expression, covering (PR: Add lesson 30: Index types beyond B-tree #89)
• 31-query-optimization — statistics, ANALYZE, common anti-patterns (PR: Add lesson 31: Query optimization #91)
• 32-partitioning — range/list partitioning, pruning (PR: Add lesson 32: Partitioning #92)

Module 8 — Programmability · advanced

• 33-views — views & materialized views, refresh (PR: Add lesson 33: Views and materialized views #93)
• 34-functions — SQL & PL/pgSQL functions (PR: Add lesson 34: Functions #94)
• 35-triggers — BEFORE/AFTER, trigger functions (PR: Add lesson 35: Triggers #95)
• 36-procedures — stored procedures, transaction control (PR: Add lesson 36: Procedures #96)

Module 9 — Concurrency · advanced → expert

• 37-mvcc-and-isolation — isolation levels, snapshots (PR: Add lesson 37: MVCC and isolation #97)
• 38-locking — row/table locks, SELECT FOR UPDATE (PR: Add lesson 38: Locking #98)
• 39-deadlocks — detection, avoidance, advisory locks (PR: Add lesson 39: Deadlocks #99)

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

• 40-roles-and-privileges — roles, GRANT, RLS (row-level security) (PR: Add lesson 40: Roles and privileges #100)
• 41-vacuum-and-bloat — MVCC cleanup, autovacuum, bloat (PR: Add lesson 41: Vacuum and bloat #101)
• 42-extensions — pg_trgm, citext, intro to PostGIS/others (PR: Add lesson 42: Extensions #102)
• 43-capstone-troubleshooting — diagnose & fix a slow real-world query (PR: Add lesson 43: Capstone: troubleshooting a slow query #103)