Engineering / advanced
Indexes and access path tradeoffs
Derive lookup and update behavior from index layout, concurrency, compression, and workload selectivity rather than treating every index as beneficial.
Your route through engineering
Follow the path or begin with a question. Every lesson is open.
Your place, at your pace. Progress is optional and saved only in this browser when available.
Read / explore / reason
Inside this module
Module 01 advanced
Indexes and access path tradeoffs
Derive lookup and update behavior from index layout, concurrency, compression, and workload selectivity rather than treating every index as beneficial.
- 01
B-tree pages: fanout is a layout calculation
Derive leaf capacity and internal fanout from a specified page format, then connect tree height to logical and physical access costs.
23 min - 02
Page splits and rebalancing: preserve search boundaries
Trace leaf splits, separator propagation, root growth, and deletion policies while separating logical changes from durable structural updates.
23 min - 03
Index concurrency: protect structure separately from transactions
Distinguish page latches, transaction locks, lock coupling, and B-link recovery from stale routing information.
24 min - 04
Covering indexes: payload availability still needs visibility
Explain included columns, MVCC visibility checks, and the leaf-width tradeoff behind PostgreSQL index-only scans.
23 min - 05
Composite keys: match the ordered prefix to the question
Trace lexicographic ranges, conditional selectivity, and workload tradeoffs without relying on a universal most-selective-first rule.
24 min - 06
Bitmap access: combine candidate sets, then recheck what was lost
Distinguish persistent bitmap structures from transient bitmap scans, trace AND/OR combinations, and account for lossy page-level candidates.
24 min - 07
Hash indexes: equality still needs collision handling
Separate key equality, hash equality, bucket mapping, overflow behavior, and workload assumptions behind expected lookup costs.
23 min - 08
Adaptive radix trees: compress shared paths, adapt the branches
Explain bytewise trie navigation, path compression, adaptive node layouts, and the encoding needed to preserve database ordering.
24 min - 09
Bloom filters: rule out membership under an intact contract
Derive the approximate false-positive model, choose a hash count, and preserve the conditions behind no false negatives.
24 min - 10
Index maintenance: count the work behind one logical update
Define write-amplification boundaries, account for MVCC and HOT behavior, and compare index benefits with sustained maintenance cost.
24 min