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
- What an index really is (and what it costs)
- Index structures: B-tree vs LSM vs hash vs GIN/GiST/BRIN
- Composite indexes and the leftmost-prefix rule
- Covering indexes and index-only scans
- Selectivity, cardinality, statistics, partial and expression indexes
- Reading EXPLAIN ANALYZE like a senior engineer
- Finding the queries worth fixing: pg_stat_statements, slow logs, auto_explain
- N+1 queries in ORMs and GraphQL, and the DataLoader fix
- Pagination: OFFSET vs keyset (seek) pagination
- Write amplification, index hygiene and query timeouts
- Index data structures compared: B-tree vs B+ tree, hash, bitmap and inverted indexes
- 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