Re: primary key hash index

On Tue, Jan 2, 2018 at 3:02 PM, Rick Otten <rottenwindfish(at)gmail(dot)com> wrote:

> After reading this article about keys in relational databases, highlighted
> on hacker news this morning:
> https://begriffs.com/posts/2018-01-01-sql-keys-in-depth.html
>
> I keep pondering the performance chart, regarding uuid insert, shown
> towards the bottom of the article. I believe he was doing that test with
> PostgreSQL.
>
> My understanding is that the performance is degrading because he has a
> btree primary key index. Is it possible to try a hash index or some other
> index type for a uuid primary key that would mitigate the performance issue
> he is recording?
>
> After all, I can't think of any use case where I query for a "range" of
> uuid values. They are always exact matches. So a hash index would
> possibly be a really good fit.
>
> I have many tables, several with more than 1 billion rows, that use uuid's
> as the primary key. Many of those uuid's are generated off system, so I
> can't play around with the uuid generation algorithm like he was doing.
>
> I'm hoping to move to PG 10 any day now, and can migrate the data with
> updated index definitions if it will actually help performance in any way.
> (I'm always looking for ways to tweak the performance for the better any
> chance I get.)
>
>
Hash indexes unfortunately don't support UNIQUE indexes. At least not yet.
So while you can use them for regular indexing, they cannot be used as a
PRIMARY KEY.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>