the ubiquitous

Dec 1, 2023

Table indexes are important in databases for fast data retrieval. Instead of scanning an entire table, the system use indexes to locate data more efficiently. For specific searches, like range scans, hash tables fall short because they are unordered. Here, the B+Tree comes into play as a standard for file organization, providing an efficient and ordered structure for data management and index-based searches.

B+trees are efficient in managing sorted data, a key element in modern database and file systems. They extend the binary search tree concept by allowing more than two children per node, reducing the frequency of disk reads and writes, essential in large-scale data storage. Unlike binary trees with a maximum of two children, B+Tree nodes can have multiple children within a specified range, keeping the tree balanced for operations like search, insertion, and deletion to run in logarithmic time.

The structure of a B+Tree hinges on its branching factor, the number of keys and children each node can hold. Nodes contain keys that divide the data range and pointers that link to child nodes, directing the search operation. B+Trees dynamically adjust themselves by splitting or merging nodes as data is inserted or deleted, ensuring efficient organization.

B+Trees are well-suited for disk-based storage systems. Their structure maximizes the data stored in each node, reducing disk access frequency. B+Trees' capability to quickly access a range of sorted records renders them vital in modern databases, where rapid and efficient data handling is crucial.


  • Open Data Structures Morin (2013)

    Chapter 14. External Memory Searching, discusses B-trees as an efficient data structure in the external memory model. B-trees are presented as a generalization of binary trees, particularly efficient for large datasets that cannot be fully loaded into RAM and must be stored in external memory, like hard disks or SSDs.

    A B-tree, for a given integer B ≥ 2, is a tree where all leaves have the same depth. Each non-root internal node in a B-tree has between B and 2B children. This structure allows the B-tree to maintain balance, ensuring efficient operations. The number of children at the root node is more flexible, ranging from 2 to 2B.

    The height of a B-tree (h) is directly proportional to the base-B logarithm of the number of leaves (), i.e., h is approximately logB + 1. This relationship illustrates how B-trees remain shallow even as they store large amounts of data, which is crucial for minimizing the costly disk accesses in external memory.

    Each node in a B-tree holds an array of keys. The number of keys in an internal node with k children is k - 1, and these are stored in a specific order to facilitate efficient searching, insertion, and deletion operations. In a B-tree, every key in a node is greater than all keys in the left subtree and smaller than those in the right subtree, maintaining a structure similar to a binary search tree.

  • B-Tree visualization.

    Andy Pavlo used this in the Lecture on Tree Indexes from the 15-445/645 Database Systems course from Carnegie Mellon University. It's a quite neat and handy visualization of the data structure.

  • Insertion Into a B-Tree from UAlberta

    It details a three-step method: first, finding the appropriate leaf node for the new value using the search procedure, then adding the value to the node, and finally handling potential node overflow. If a node overflows upon insertion, it is split into three parts: left, middle, and right, with appropriate adjustments made up the tree, including the creation of a new root if necessary. This insertion method ensures that B-trees grow from the top rather than the leaves, maintaining their perfect height balance.

  • B-Trees description lecture notes from ITEC 360

  • B-Trees

    The text provides a concise overview of B-trees and B+-trees, data structures optimized for storing and managing large data sets. It begins with the inefficiency of mutating sorted arrays and introduces the concept of dividing data into fixed-size blocks, allowing for more manageable insertions and deletions. The core of the discussion centers on the structure of B-trees, where blocks either store data directly or act as references to other blocks. This hierarchical arrangement efficiently handles large datasets, particularly for operations like splitting full blocks or maintaining tree balance. The text highlights the practicality of B-trees in both on-disk and in-memory storage, emphasizing their superiority over simpler data structures for managing ordered key-value maps.

  • Ubiquitous B-tree. Comer. ACM Computing Surveys (CSUR) 11 (1979)

    "The Ubiquitous B-Tree" is considered a seminal work that played a significant role in promoting the understanding and adoption of B-trees. The paper helped to solidify the B-tree's position as a go-to data structure for database management systems and file systems. It made the concept accessible and laid out the advantages, contributing to B-trees becoming a standard choice for many storage systems.

    The B-tree version described in the paper includes data in the internal nodes. In modern implementations, it's common to only store data in the leaf nodes. This makes B-trees more efficient for range queries and better suited for systems where data is stored on disk. The internal nodes in a B+-tree contain only keys and act as a guide to locate the leaf node containing the desired data.

  • Modern B-tree techniques. Graefe, and others. Foundations and Trends in Databases 3 (2011)

    This paper provides an updated look at B-tree optimizations and techniques that have emerged over the years. It's a must-read for understanding contemporary B-tree practices.

  • Two is Better Than One: The Case for 2-Tree for Skewed Data Sets. Zhou, Yu, Graefe, and Stonebraker. memory 11 (2023) 📺

    This is a discussion on a 2-Tree architecture to improve memory utilization in databases with skewed data sets. B-trees don't handle skewed data well, leading to poor memory utilization. In a 2-Tree, frequently accessed records are stored in one tree, and cold records in another. A migration protocol moves records between the trees as their access frequency changes.

    • It's designed to be an on-disk index that handles larger-than-memory indexes effectively.

    • The record migration protocol adaptable to any B-tree.

    Next step would be to expand the approach for an N-Tree system with multiple storage levels. It's like an LSM of B-tree's except that the levels are mutable and records can move between them.

    The approach aims to significantly improve memory utilization and throughput without compromising range scan performance.

  • Adaptive Hybrid Indexes. Anneser, Kipf, Zhang, Neumann, and Kemper (2022)

    The problem this paper tries to address is the large memory usage of indices in database systems. Traditional compact indexes, while reducing space overhead, compromise on performance due to their static nature. Unable to to adjust to varying workload patterns and to trade-off between memory efficiency and performance is a challenge.

    The objective they try to achieve is is to optimize both space and performance in an index structures by dynamically adjusting encodings at run-time based on workload patterns.

  • Taming B-Trees (ScyllaDB)

    Is an article on how ScyllaDB transitioned from Red-Black to B-trees to enhance its in-memory cache, addressing issues like suboptimal memory consumption and CPU usage during tree searches. While B-trees are often thought of as an on-disk data structure the article goes into how B-trees offer better cache locality and mitigate branch misprediction problems associated with binary trees, optimizing key searches and overall performance.

  • How does B-tree make your queries fast?