How are Data Structures used in Database Indexing? (B+ Trees, Hash Indexes)

0-2 years experience

1. 1If you need to add a new column that will be frequently searched by equality, how would you choose between a B+ tree index and a hash index?
2. 2Suppose a query on a table with a B+ tree primary key is returning rows in descending order. How does the B+ tree help avoid extra sorting?

2-5 years experience

1. 1We observed a sudden slowdown on range queries after adding a new hash index. Walk me through how you would diagnose the issue.
2. 2When implementing a composite index on (user_id, timestamp) using a B+ tree, what trade‑offs do you consider for query patterns that filter only on timestamp?
3. 3If a B+ tree index becomes fragmented after many deletes, what steps would you take to rebuild or maintain it, and why?

5-8 years experience

1. 1Design the indexing strategy for a time‑series table that stores billions of rows per day, supporting both point lookups and range scans. Explain why you would combine B+ trees and hash indexes, and how you’d handle hot‑spot partitions.
2. 2Our service experiences occasional index corruption after a crash. How would you redesign the index write path to improve durability while keeping write latency low?
3. 3Explain the performance impact of using a B+ tree with a high fan‑out versus a lower fan‑out in a distributed database shard, especially under skewed key distribution.

8+ years experience

1. 1We need to migrate a legacy system that uses only hash indexes to a new platform that prefers B+ tree indexes for range queries. Outline a migration plan that minimizes downtime and data inconsistency.
2. 2Across multiple teams, some services rely on hash indexes for security‑sensitive lookups, while others need ordered scans. How would you establish a company‑wide indexing guideline that balances performance, maintainability, and future extensibility?