Are there any plans for allowing hash indexes in uniqueness constraints such as the ones created for primary keys? It seems like a good fit for an index that is specialized for equality checks.
Wasn't sure what a hash index was vs. btree<p>Short version - hash indexes are faster in PG11, but they only apply to "where = foobar" queries, giving a 0(1) time. Btree indexes have O(logn)<p>But hash indexes can't be applied to range clauses, like "where < 50". You can still use a btree index however.<p>SO post:<p><a href="https://stackoverflow.com/a/398921" rel="nofollow">https://stackoverflow.com/a/398921</a>
I remember someone commenting a while back that with hash indexes allowing you to navigate relationships in O(1} time, relational DBs can approximate the perf characteristics of a graph DB. When I did a quick and dirty test a couple years ago(before they would have been usable anyway due to durability and replication) I found that hash indexes performed noticeably worse than btree for navigating a few test tables with 10-100m rows each. Curious whether this is a major enough improvement for hash indexes that btree will not still be faster for many common equality lookups.
We have been talking about this for a while. Consider the coming of more distributed and nvme based storage and a lot of these things eventually make sense.
> A report from a tester who goes by "AP" in July tipped us off to the need for a few further tweaks. AP found that trying to insert 2 billion rows into a newly-created hash index was causing an error.<p>This is what I call trying things "at Indian scale" :D