DB Indexing in System Design Interviews - B-tree, Geospatial, Inverted Index, and more!

In summary, the most valuable insight is that mastering database indexing for system design interviews hinges less on memorizing complex implementations and more on recognizing query patterns to efficiently select between B-trees, geospatial structures, inverted indexes, or hash maps based on data type and access needs.

• Indexes eliminate slow sequential scans by acting as disk maps that point directly to relevant data pages.
• B-trees are the default choice for most workloads, efficiently handling exact matches, range queries, and sorting.
• Hash indexes provide O(1) lookups but lack range/sort capabilities, making them ideal mostly for in-memory caches like Redis.
• Two-dimensional geospatial data requires specialized structures like geohashing, quadtrees, or R-trees, as B-trees cannot efficiently process latitude/longitude ranges.
• Inverted indexes map text tokens to documents, enabling fast full-text search without costly table scans.
• Index selection should be driven by query patterns: text data needs inverted indexes, location data needs geospatial indexes, and general lookups default to B-trees.
• System design interviews prioritize recognizing query inefficiencies and matching them to appropriate indexing strategies over memorizing low-level implementations.