Indexing & Query Optimization

B-tree vs LSM vs hash vs GIN/GiST/BRIN, composite indexes and the leftmost-prefix rule, covering and partial indexes, reading EXPLAIN ANALYZE, finding slow queries, N+1 and DataLoader, keyset pagination, write amplification and timeouts.

About 110 minutes · 18 exam questions · 10 interview questions · 2 code labs

What you will learn

  1. What an index really is (and what it costs)
  2. Index structures: B-tree vs LSM vs hash vs GIN/GiST/BRIN
  3. Composite indexes and the leftmost-prefix rule
  4. Covering indexes and index-only scans
  5. Selectivity, cardinality, statistics, partial and expression indexes
  6. Reading EXPLAIN ANALYZE like a senior engineer
  7. Finding the queries worth fixing: pg_stat_statements, slow logs, auto_explain
  8. N+1 queries in ORMs and GraphQL, and the DataLoader fix
  9. Pagination: OFFSET vs keyset (seek) pagination
  10. Write amplification, index hygiene and query timeouts
  11. Index data structures compared: B-tree vs B+ tree, hash, bitmap and inverted indexes
  12. Beyond the B+ tree: hash limits, bitmap indexes vs Postgres bitmap scans, and inverted (GIN) indexes

Previous: SQL vs NoSQL, Data Models, ACID & Isolation · Next: Connection Pooling, Lock Contention & Deadlocks