When would you deliberately create a hash index instead of a B+tree index on the same column, and why do most teams still default to the B+tree even for pure equality lookups?

## Framing the decision Every index is a bet: you pay write amplification, storage, and maintenance in exchange for making some query shapes cheap. Choosing hash over B+tree narrows which shapes you buy. So the comparison is not purely about lookup latency. ## What hash genuinely offers - **Constant-cost probes.** One hash computation plus one bucket read, independent of table size, versus a logarithmic descent. - **Small, fixed-width entries** when only the hash is stored. A B+tree on a 200-byte URL stores the 200 bytes in every leaf entry — and in separators up the tree — so the index gets large, fanout drops, and the tree gets taller. A hash entry may be 8 bytes regardless. On long-key columns the size difference can be several times over. - **Fewer key comparisons.** A tree descent performs a comparison per level on possibly long keys; a hash probe does arithmetic plus a comparison at the end. ## What hash costs you - **Only full-key equality.** No ranges, no anchored prefixes, no ordered output, no merge join input, no top-N early stop, no min/max shortcut, no leading-column use on a multi-column key. - **Fragility to skew.** Duplicated or low-cardinality keys pile into a few buckets, and splitting cannot separate identical hashes. - **Growth behaviour.** Bucket splits and overflow chains mean performance depends on load factor and rebuild history in a way B+trees, which self-balance, do not. - **Maturity and tooling.** In most engines the B+tree is the path everything else is tuned for; hash variants receive less attention, and historically some implementations lagged on crash-recovery guarantees or replication support. Verify per engine before committing. ## Why the speed advantage under-delivers The usual mental model — "O(1) beats O(log n)" — ignores the buffer cache. For a table with hundreds of millions of rows, a B+tree is typically four levels deep. The root and the level below it are hot and effectively always memory-resident; often the level under that is too. So the *physical* reads for an equality probe are frequently one, the same as a hash probe, and the difference collapses to a few in-memory comparisons. Under a benchmark that fits in cache, both structures look flat. The advantage becomes real when key comparisons are expensive (long strings), when the index is so large that fewer levels are cacheable, or in memory-resident engines where CPU, not I/O, is the budget. ## The optionality argument This is the argument that usually decides it. Suppose you index a token column with a hash index because today's only query is an exact-match lookup. Six months later someone adds pagination ordered by that column, or a range filter, or a query that supplies a prefix. With a B+tree those queries get an index for free. With a hash index they get a sequential scan, and — worse — nothing errors; the planner just quietly stops using the index while you keep paying its write cost. Because a B+tree serves the hash index's entire use case plus many more at a small constant-factor penalty, the B+tree is the strictly safer default. You need a specific, measured reason to give that up. ## Where hash wins in practice, outside index DDL Worth mentioning because interviewers probe it: hashing dominates *inside* the executor even when hash indexes are rare on disk. A hash join builds a transient in-memory hash table on the smaller input and probes it per outer row — the same equality-only structure, but built for one query and discarded, so none of the persistence and maintenance drawbacks apply. Hash aggregation groups rows the same way. In-memory and key-value engines also lean on hash indexes because their cost model is CPU-bound. A related pattern in row stores: rather than a hash index, teams sometimes store a hash of a long value in its own column and put an ordinary B+tree index on that column, then filter on both the hash and the original value. That keeps the small-entry benefit while remaining on the well-trodden access method. ## How to answer Name the narrow win conditions, name the cache argument that shrinks the benefit, name the optionality loss, and finish with the decision rule: default to B+tree; reach for hash only with a measured equality-only workload, a high-cardinality key, and preferably a long one — and re-check the engine's guarantees for that index type before shipping it.