Month 1 PostgreSQL Storage Engine, MVCC Mechanics & Deep Indexing Strategies Weeks 1–4 • 8 Live Sessions
  • Session 1-2: Storage anatomy: Page layout (8KB blocks), Tuples, Heap files, TOAST tables, Free Space Maps (FSM), and Visibility Maps (VM).
  • Session 3-4: Multi-Version Concurrency Control (MVCC): xmin/xmax tuple headers, write amplification, table bloat, and tuning autovacuum aggressive thresholds.
  • Session 5-6: Index engineering deep dive: B-Tree internal node traversal, GIN for full-text/JSONB, BRIN for massive time-series logs, partial indexes, and index-only scans.
  • Session 7-8 (Live Hand-Holding): Index Remediation & Bloat Reclamation Lab — Diagnosing a bloated 25-million row table, fixing unindexed foreign-key locks, and defragmenting indexes online using REINDEX CONCURRENTLY with zero table locks.
Month 2 Query Planner Mechanics, EXPLAIN ANALYZE Mastery & Execution Tuning Weeks 5–8 • 8 Live Sessions
  • Session 9-10: Cost-based query optimizer (CBO): Sequential scans vs Bitmap index scans, Join algorithms (Nested Loop, Hash Join, Merge Join), and statistical histograms (pg_stats).
  • Session 11-12: Mastering EXPLAIN (ANALYZE, BUFFERS, VERBOSE): Reading flame graphs, identifying costly JIT compilation overhead, and hunting disk spilling in Sort / Hash nodes.
  • Session 13-14: Memory sizing & kernel tuning: Sizing shared_buffers, work_mem, maintenance_work_mem, and mitigating OS kernel dirty-page flush freezes under write bursts.
  • Session 15-16 (Live Hand-Holding): Slow-Query Optimization Lab — Taking complex, unoptimized production queries running at 4,500ms and refactoring them with CTE optimizations, covering indexes, and subquery flattening down to <15ms.
Month 3 Connection Scaling with PgBouncer, Partitioning & High Availability Weeks 9–12 • 8 Live Sessions
  • Session 17-18: Connection pool exhaustion: Forked process memory overhead, pooling modes (Session vs Transaction vs Statement), and architecting PgBouncer on Kubernetes / bare metal.
  • Session 19-20: Declarative Table Partitioning (Range, List, Hash): Partition pruning, partition-wise joins, and routing multi-tenant databases with pg_partman.
  • Session 21-22: Write-Ahead Logging (WAL) & Replication: Physical streaming replication, synchronous vs asynchronous commit levels, and logical replication for CDC (Change Data Capture).
  • Session 23-24 (Final Capstone & Architecture Defense): 20,000 Concurrent Writes Stress Drill — Configuring a high-availability primary/read-replica cluster behind PgBouncer, running k6 write stress tests, and executing a zero-data-loss failover drill.