Every senior DBA I know carries the same reflex: never use hash indexes. The reflex has a pedigree. Before PostgreSQL 10, hash index writes skipped the write-ahead log entirely, which meant two horrifying things at once — a crash could leave the index silently corrupt, and a streaming replica never received the index at all. In 2017 I failed over onto a replica and watched session lookups return empty result sets for tokens that absolutely existed, because the hash index those queries used was a ghost on the standby. We dropped every hash index that week, and I did not touch one again for years.
Here is the uncomfortable part: the reason for the rule disappeared in PostgreSQL 10, eight major versions ago, and the rule fossilized anyway. Hash indexes have been durable, crash-safe, and replicated for years. They are still the wrong choice most of the time — but for reasons worth articulating precisely, because the remaining edge cases where they win are real.
What did PostgreSQL 10 actually fix about hash indexes?
Durability, end to end. The PG10 work made hash index changes WAL-logged: bucket splits, overflow-page allocations, tuple inserts, metapage updates — all of it now flows through the write-ahead log like any other index. That single change bought crash safety (recovery replays hash index changes instead of abandoning them), point-in-time recovery correctness, and replication (standbys apply the same WAL and carry a real, queryable hash index). The pre-10 hash index was effectively an unlogged object with an index's name; the post-10 one is a boring, first-class citizen.
I emphasize this because the consequences of the old behavior were worse than "index unavailable". A planner that believes an index exists will use it, and an empty-but-present hash index does not error — it returns wrong answers, fast. Wrong answers are the worst failure mode a database has. That is the scar the folklore remembers, and it is also why you should verify the folklore against your version before repeating it in a design review.
When does a hash index beat a btree?
On single-column equality at scale over long keys — session tokens, API keys, URLs, content hashes — where the win is size, not raw lookup speed. The mechanism: a hash index stores a four-byte hash code plus a tuple pointer per entry, never the key itself, while a btree must store the full key bytes. On our sessions table — one hundred million rows, 48-character tokens — the btree weighed 4.1 GB and the hash index 2.2 GB. Cached equality lookups were within single-digit microseconds of each other; the durable advantage was buffer cache economics, because nearly twice as much of the hot index fits in shared buffers at once.
The limits are equally concrete, and each one is absolute. No range scans and no ORDER BY — a hash code destroys ordering by design. No unique constraint backing. No multicolumn indexes. No INCLUDE columns, so no covering indexes. And no index-only scans, ever: the index does not contain the key value, so PostgreSQL must visit the heap to confirm the match and to return anything at all — which also means the visibility-map tricks from the index-only scan notes simply do not apply to this access method. The practical test I apply: if the query might ever grow a second predicate, a sort, or a range, the hash index is future dead weight. It does one thing, and it will never learn another.
CREATE INDEX sessions_token_hash_idx ON sessions USING hash (token);
CREATE INDEX sessions_token_btree_idx ON sessions (token);
SELECT pg_size_pretty(pg_relation_size('sessions_token_hash_idx')) AS hash_size,
pg_size_pretty(pg_relation_size('sessions_token_btree_idx')) AS btree_size;
EXPLAIN (COSTS OFF) SELECT id FROM sessions WHERE token = '9f2c7ad1e4b34f09a81c6d5203f7e1b84aa05512c39d6e47';Run the EXPLAIN and you will always see Index Scan with a heap fetch, never Index Only Scan — that one line in the plan output is the whole covering-index story.
How do fillfactor and growth behave differently from btree?
A hash index is a bucket table, not a tree, and its maintenance knobs follow from that. It starts with a couple of buckets and grows by linear hashing: when growth pushes the index past its fillfactor target, nhash splits one bucket at a time in split-pointer order — not necessarily the bucket that just overflowed — and rehashes that bucket's entries onto the new one, while a bucket page that fills up between splits simply gains an overflow page. The default fillfactor is 75 rather than btree's 90, and it applies at build time — headroom inside bucket pages so early splits stay cheap. Deletes leave dead entries that vacuum reclaims within the bucket structure, but the index as a whole does not shrink; like every PostgreSQL index, compaction means REINDEX. Operationally that gives three rules: build hash indexes after bulk loads rather than before (growing through splits during COPY is pure churn), expect the index to plateau at its high-water mark, and keep REINDEX CONCURRENTLY in the maintenance toolbox for tables with heavy delete churn.
One more sizing note that surprises people: because entries are four-byte codes, hash indexes are not automatically smaller than btree on short keys. On an integer or bigint column the btree entry is already tiny, ordering comes free, and hash buys you nothing at all. The size win is specifically a long-key phenomenon, which is why tokens and URLs are the canonical use case and customer_id is not.
Why did hash indexes stay niche after they became safe?
Because btree is never wrong, and "never wrong" is an enormous feature. Btree handles equality nearly as fast, plus ranges, ordering, uniqueness, multicolumn, covering, and partial indexing; the only thing it concedes is those extra key bytes on long values. An index type that wins one scenario by a modest margin and loses every other scenario by disqualification will always be a specialist tool, and specialists get reached for rarely. Add the pre-10 trauma, the silence around the fix, and the fact that most ORMs cannot even emit CREATE INDEX ... USING hash without a raw migration, and the obscurity explains itself.
My own rule after ten years: reach for hash when a single hot, pure-equality lookup on long keys is bound by buffer-cache capacity and the table churns enough that two gigabytes of index is a different operational life than four. I have made that call exactly twice. Both times it was right, both times I benchmarked btree first, and both times the hash index is still running today, unremarkable and fully replicated — which, given where this index type started, is the highest compliment I can pay it.
How do you watch index payloads with MonPG?
The bet behind a hash index is a size bet, so the monitoring that matters is per-index size, buffer churn, and scan counts — is the index actually being used, and is the smaller footprint showing up as steadier cache behavior? MonPG graphs per-index size over time next to idx_scan from pg_stat_user_indexes, which answers both halves: a hash index whose scans drift toward zero is dead weight you are replicating for nothing, whatever its type. The same dashboards carry the btree side of the comparison, and the bloat math there is covered in the btree bloat and REINDEX CONCURRENTLY notes. If you are sizing index trade-offs in production, MonPG's PostgreSQL monitoring gives you the size-versus-usage evidence instead of a benchmark you ran once on a laptop.