Why Databases Use B+ Trees for Data Storage

Added:

Log-Structured Merge-Trees (LSM-Trees) for High-Write Workloads

While B+ trees are the traditional gold standard for read-heavy and range-query workloads, they suffer from write-amplification and random disk I/O bottlenecks during heavy write operations. Log-Structured Merge-Trees (LSM-Trees) offer a powerful alternative, optimized specifically for write-intensive database systems like Apache Cassandra, InfluxDB, and RocksDB. Unlike B+ trees, which update data in-place, LSM-trees append write operations sequentially to an in-memory buffer and periodically flush them to immutable on-disk files. A background compaction process later merges and deduplicates these files. This architecture turns random writes into sequential ones, vastly improving ingestion throughput. While LSM-trees introduce some read latency and compaction overhead, they challenge the supremacy of B+ trees in modern, write-heavy big data applications.

Understanding of basic tree data structures, specifically Binary Search Trees (BST) and their search, insertion, and deletion complexities.
The distinction between primary memory (RAM) and secondary storage (HDD/SSD), including the concept of disk page/block reads.
Fundamental concepts of database indexing, specifically why search keys are used to avoid costly sequential table scans.
Familiarity with Big-O notation to evaluate the algorithmic efficiency of data structure operations.
Advanced concurrency control in B+ Trees, including latching protocols (such as latch crabbing) to support multi-threaded database access.
Log-Structured Merge-Trees (LSM-Trees) as an alternative indexing structure optimized for write-heavy workloads in NoSQL databases.
Database query optimization, focusing on how query engines utilize B+ Trees for operations like index-only scans and range scans.
Practical database indexing design, including composite (multi-column) indexes, index selectivity, and how to avoid index fragmentation.
93.3K views2.9Klikes29:43@AsliEngineeringOriginal Release: 2023-03-19

Databases use B+ trees because they provide efficient storage and retrieval of data on disk, solving the limitations of naive sequential file storage where insert, update, find, and delete operations are O(n). B+ trees organize data into nodes (typically 4KB disk blocks) where leaf nodes store actual row data and non-leaf nodes store routing information, enabling O(log n) operations for all database functions including range queries, which is essential for performance in transactional databases.