On Mon, 26 Oct 2026 14:08:29 -0400, shihao zhong <[email protected]> wrote: > Hi Andres and Greg, > > > I think they're almost unused and don't offer enough advantage over btree > > I largely agree with this. In my experience, B-tree accounts for 95%+ of > indexes, and all other access methods combined make up only a > small fraction. But I do see quite a few hash index users.
While I agree that the vast majority of users rightly default to the BTREE index I don't find that a compelling reason to drop HASH. I see that as a challenge, maybe HASH isn't used because it is not as well maintained as BTREE and needs some attention [0]? > > we should have just ripped them out. > > I have mixed feelings on this. I think this would invite further specialization of code outside nbtree into shapes that only support nbtree reducing the ability to innovate with other table and index AMs. Sure there is GIN, GiST, BRIN, etc. but IMO hash represents a quadrant of access patterns that we should support, and our code there today isn't inspiring many to give it a look even for use cases that fit that pattern well. That's a signal to innovate, not remove code. We have this issue today with HEAP, because we only have one table AM in core things are simpler to maintain but it is also easier for heap-isms to leak outside the table AM boundaries in the name of performance or other goals. IMO this is a long-term philosophical question for Postgres; is it extensible or is it highly opinionated and specialized? > On one hand, hash is probably the least used > "general-purpose" index since its use case overlaps directly with B-tree, > whereas GiST, SP-GiST, and BRIN exist for specialized data types or access > patterns that B-tree can't handle. On the other hand, in practice I still > see hash indexes used at least as often as GiST, BRIN, or SP-GiST. > > I suspect part of the low adoption is historical baggage from being > unlogged prior to old version, which gave hash indexes a bad reputation in > the community, it also miss some features for general OLTP workloads > > I feel there is plenty of room for improvement there. I agree that there is historical baggage, performance, and possibly simple knowledge gaps that push the adoption toward BTREE. > > The advantage hash does have, i.e. large key handling, would have better > > been addressed by adding support to transparently btree indexing a hash > > of the key + recheck. > > That's an interesting point. But I think users can do that today with > expression index right? Expression indexes prevent HOT updates, so maybe you could but at what cost? > Thanks, > Shihao best. -greg
