High Performance PostgreSQL for Rails

Hash Indexes over B-Tree?

Hash indexes are a less-used index type, as they offer only a single capability that’s redundant with the default B-Tree type. Hash indexes support only equality comparisons, also supported by the B-Tree type. Besides the single operation, hash indexes have more limitations. They cannot be used for unique constraint enforcement while B-Tree types can. Given the limited features and the restrictions, when are hash indexes beneficial?

In the post, “PostgreSQL indexes: Hash vs. B-tree,”^[213] the author runs benchmarks on PostgreSQL 15 with data that increases in size, and with increasing amounts of items stored. Both hash and B-Tree indexes are used and compared. The goal was to understand the tipping point where a hash ...