Indexing — B-Trees and Lookup Speed (O(log N))
Understand the universal database index: B-Tree vs B+ Tree internals, fan-out factors, search complexity, page splits, and why B+ Trees power PostgreSQL and MySQL.
01.1. Why Binary Search Trees (BST / Red-Black) Fail on Disk
In memory, balanced Binary Search Trees (AVL trees, Red-Black trees) achieve O(log_2 N) lookup speed. However, they are disastrous for disk-based database engines:
- Low Fan-Out (2 Children Per Node): To search across 1,000,000,000 rows, a binary tree requires
log_2(10^9) ≈ 30levels. Traversing 30 levels on disk means executing 30 random disk reads (~300ms on HDD / 1.5ms on SSD) per single row lookup! - Page Underutilization: Storing 1 single key and 2 pointers per node wastes 99% of a standard 4KB/16KB disk page, causing severe memory fragmentation.
B+ Tree Index Internal Node & Leaf Page Structure 🌲
B+ Tree Index Internal Node & Leaf Page Structure 🌲
B+ Tree internal architecture: High fan-out routing nodes in RAM, data stored exclusively in leaf pages, and bidirectional linked lists enabling ultra-fast range scans.
Unlock Topic #45: Indexing — B-Trees and Lookup Speed (O(log N))
You are viewing a preview. The full in-depth engineering deep dive, interactive simulators, architecture flowcharts, and self-assessment quizzes for this topic are available with Pro or Lifetime Access.
Failure modes, high-throughput bottlenecks, and real FAANG implementation decisions.
Interactive system topology diagrams, live parameter simulators, and downloadable SVG charts.
Staff-level multiple-choice quiz questions with instant feedback and answer explanations.
Firebase Google authentication automatically syncs your completed topics and quiz scores.
How clear and staff-actionable was this system breakdown?