I suggest testing the same workload with your existing hash indexes replaced with equivalent B-Trees.
[1] https://github.com/postgres/postgres/blob/master/src/backend...
I suggest testing the same workload with your existing hash indexes replaced with equivalent B-Trees.
[1] https://github.com/postgres/postgres/blob/master/src/backend...
Later that got fixed, but I haven't tried again since, just been using btree because it seemed like Postgres favored that and it has theoretical advantages too.
You've probably already read the Postgres docs on hash indexes, but just in case, it says "hash indexes may not be suitable for tables with rapidly increasing number of rows." I agree with the other commenter that it's worth at least trying without them if you haven't already, even though you're already VACUUMing.
On some data, for certain ways of using and querying that data, it makes a lot more sense than a b-tree. When we use hash indices for some of the columns in our test just to see how Postgres will perform, and run our test towards a quarter of a billion rows where Postgres' 32-bit hash indices have a relatively high chance of colliding, querying these hash indices and returning the single requested row is still instantaneous.