Build fluency in the vocabulary of a balanced tree structure powering a general-purpose database index.
0 / 5 completed
1 / 5
At standup, a dev mentions a balanced tree structure used as a database index, keeping data sorted and supporting a logarithmic-time lookup, range scan, insert, and delete. What is this index structure called?
A B-tree index is a balanced tree structure that keeps a database's data sorted, supporting a logarithmic-time lookup, range scan, insert, and delete. A hash index supports only an equality lookup and has no notion of order, so it can't support an efficient range scan at all. This balanced, ordered structure is what makes a B-tree the standard general-purpose index for a wide variety of query patterns.
2 / 5
During a design review, the team wants the tree to automatically split an overfull node during an insert, keeping every path from root to leaf the same length and the whole structure balanced. Which capability supports this?
Node splitting on insert automatically divides an overfull node, keeping every path from root to leaf the same length and the whole B-tree balanced as data is added. Allowing a node to grow arbitrarily large with no splitting mechanism would eventually break the tree's balanced, logarithmic-depth structure. This automatic splitting is what keeps a B-tree's lookup and range-scan performance consistent even as the underlying dataset grows substantially.
3 / 5
In a code review, a dev notices a range query like a BETWEEN clause is served efficiently by walking sequentially through the B-tree's sorted leaf nodes, rather than checking every row individually. What does this represent?
Efficient leaf-node range scans exploit the B-tree's sorted order, letting a range query like a BETWEEN clause walk sequentially through a contiguous span of leaf nodes rather than checking every row in the table individually. Checking every row individually ignores the ordering benefit the B-tree's structure already provides. This efficient range scan is one of the main reasons a B-tree remains the default choice for a general-purpose database index.
4 / 5
An incident report shows a range query slowed down dramatically after a B-tree index on that column was replaced with a hash index to save a small amount of storage space. What practice would prevent this?
Keeping a B-tree index on a column that needs to support an efficient range query is essential, since a hash index can't support an ordered range scan at all, only an equality lookup. Replacing it with a hash index to save storage ignores this fundamental capability gap, exactly as this incident describes. This choice of index type based on the actual query pattern is a foundational database design decision.
5 / 5
During a PR review, a teammate asks why the team uses a B-tree index instead of a hash index for a general-purpose column that might be queried with either an equality filter or a range condition. What is the reasoning?
A B-tree supports both an equality lookup and an efficient range scan, thanks to its sorted, ordered structure, while a hash index supports only an equality lookup and nothing resembling an ordered scan. This makes a hash index unsuitable the moment a range query becomes possible on that column. The tradeoff is that a B-tree's balanced, ordered structure is somewhat more complex to maintain than a simpler hash table.