PostgreSQL's Hash Indexes Are Now Cool

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&#x27;t sure what a hash index was vs. btree<p>Short version - hash indexes are faster in PG11, but they only apply to &quot;where = foobar&quot; queries, giving a 0(1) time. Btree indexes have O(logn)<p>But hash indexes can&#x27;t be applied to range clauses, like &quot;where &lt; 50&quot;. You can still use a btree index however.<p>SO post:<p><a href="https:&#x2F;&#x2F;stackoverflow.com&#x2F;a&#x2F;398921" rel="nofollow">https:&#x2F;&#x2F;stackoverflow.com&#x2F;a&#x2F;398921</a>

I hope Postgres keeps on getting Cooler. Reading all the Change Logs makes me feel warm and fuzzy inside .

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.

Any good articles that go into detail on when to use hash indexes in PG 10?

Is it just me, or has PostgreSQL kept on getting better and better, to the point of being #1 DB in the open source market?

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.

&gt; A report from a tester who goes by &quot;AP&quot; 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 &quot;at Indian scale&quot; :D