Is the hash index in PostgreSQL worth using compared to B-tree?
A hash index supports only equality lookups. Since PostgreSQL 10 it is crash-safe and can be slightly smaller than a B-tree for long keys, but B-tree also handles equality plus ranges and sorting, so B-tree stays the default.
Explain it simply
A hash index runs the key through a function that points straight to a bucket, like a cloakroom token that tells you exactly which hook your bag is on. That is fast for "give me bag 417", but useless for "give me all bags between 400 and 450", because tokens have no order.
B-tree keeps keys sorted, so it can do equality, ranges, ORDER BY and prefix searches.
Example
CREATE INDEX sessions_token_hash ON sessions USING hash (token);
SELECT * FROM sessions WHERE token = 'a81f...'; -- can use the hash index
SELECT * FROM sessions WHERE token > 'a0'; -- cannot
SELECT * FROM sessions ORDER BY token; -- cannot
-- Hash indexes cannot enforce uniqueness
CREATE UNIQUE INDEX ON sessions USING hash (token);
-- ERROR: access method "hash" does not support unique indexesBefore PostgreSQL 10, hash indexes were not written to the WAL, so they were not crash-safe and not replicated. That old warning is why many people still avoid them.
What to say in the interview
"A hash index only serves equality, cannot be unique and cannot sort. It can be a bit smaller for long keys and is safe since version 10, but a B-tree does everything it does plus ranges and ordering, so I default to B-tree and measure before choosing hash."
Common mistakes
- Expecting a hash index to enforce a unique constraint.