How a B-Tree Index Speeds Up Your Query
Most engineers know indexes make queries faster. Few know why — or when they silently stop working.
In this video, we go inside a Postgres B+Tree: how data is laid out in 8KB pages, why a 4-level tree covers a billion rows with exactly 4 page reads, how range scans use a leaf-level linked list, and what breaks in production when an index degrades without throwing a single error.
Topics covered:
B-Tree vs B+Tree — what Postgres actually implements (nbtree)
Fanout math: why height stays at 3–4 for any real table
Point lookup: every step from SQL to disk and back
Range scan: the leaf linked list walk
Page splits: when they happen and when they hurt
Concurrent B-Tree access (Lehman-Yao protocol)
Failure modes: index bloat, monotonic key hot spots, visibility map staleness
Index-only scans and why they silently degrade
Production Go code for monitoring index health
Staff+ interview angle: compound index prefix rule, B-Tree vs LSM, schema design
Source code referenced:
src/backend/access/nbtree/ — Postgres nbtree implementation
pageinspect extension — live B-Tree introspection
Prerequisites: Basic SQL, concept of disk I/O being slow.