PostgreSQL Mastery: Indexes, EXPLAIN, Partitioning & Concurrency
Read EXPLAIN like the planner, size and choose B-tree, GIN and BRIN indexes, tune statistics, vacuum and memory, partition big tables and reason about MVCC, isolation and locks, with every answer checked as you go.
- One-time payment
- Instant access right after payment
- Secure checkout with Stripe
About this course
This advanced PostgreSQL course trains the skills that separate a database professional from a query writer. In 21 hands-on labs you recompute what the planner does: cost and row estimates from pg_stats, most common values and histograms, and you read EXPLAIN ANALYZE with loops and buffers. You decide between B-tree, multicolumn, covering, partial, GIN and BRIN indexes, tune HOT updates, autovacuum and freezing, trace MVCC visibility, isolation levels, lost updates and lock queues, partition large tables, secure rows with roles and row level security, and size work_mem. A final pg_stat_statements tuning project and an exam lead to your certificate.
Lessons
- Reading EXPLAIN: The Planner's Cost ModelFree
- EXPLAIN ANALYZE: Actual Rows, Loops and Misestimates Locked
- EXPLAIN BUFFERS: Pages, Cache Hits and Reads Locked
- pg_stats: Most Common Values and n_distinct Locked
- Histograms: Estimating Range Conditions Locked
- B-Tree Index or Seq Scan? The Break-Even Point Locked
- Multicolumn Indexes and the Leftmost Prefix Locked
- Covering Indexes, Index-Only Scans and the Visibility Map Locked
- Partial and Expression Indexes: Index Only What You Query Locked
- GIN Indexes for Arrays, jsonb and Full Text Locked
- BRIN Indexes for Huge Append-Only Tables Locked
- HOT Updates and Fillfactor Locked
- MVCC: xmin, xmax and Snapshot Visibility Locked
- Autovacuum: Thresholds, Scale Factors and Per-Table Tuning Locked
- Freezing and Transaction ID Wraparound Locked
- Isolation Levels and the Lost Update Locked
- Locks, Lock Queues and Safe Migrations Locked
- Range Partitioning and Partition Pruning Locked
- Roles, Privileges and Row Level Security Locked
- work_mem, Spills and the Memory Budget Locked
- Final Project: Tuning from pg_stat_statements Locked