I’m revisiting an old question from 16 years ago about PostgreSQL HASH indexes vs B-TREEs. At the time, it seemed like HASH indexes were far less efficient than B-TREEs in terms of both space and time and the consensus was that HASH indexes offered no significant performance advantage in many use cases.
I’m curious if the situation has changed in 2024?
Comment to this answer (and the linked doc) suggests that there have been some optimizations to HASH indexes in PostgreSQL that make them viable. Are they significant?
Main question:
What are the current best practices regarding when to use a HASH index over a B-TREE in Postgres?
Additional considerations:
Are there specific pitfalls to be aware of with HASH indexes, particularly on:
- BOOL columns?
- Columns with very few unique values (e.g., HASH indexes where only 3 distinct values exist)?
One of the comments in the original thread stated, "In fact, using the index for something that’d match 99% of rows would be massively inefficient, much slower than seqscan." Does this still hold true today, or has the landscape shifted with modern optimizations?
Would love to hear about your experiences and advice regarding HASH indexes in PostgreSQL in 2024!
You need to sign in to view this answers
Leave feedback about this