primary key hash index

From: Rick Otten <rottenwindfish(at)gmail(dot)com>
To: "pgsql-performa(dot)" <pgsql-performance(at)postgresql(dot)org>
Subject: primary key hash index
Date: 2018-01-02 14:02:50
Message-ID: CAMAYy4Kkyzng+LH95jHXJy_ARP2KvKkkRANMSHkuU+z=WYumGg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.)

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Magnus Hagander 2018-01-02 14:09:50 Re: primary key hash index
Previous Message Vasilis Ventirozos 2018-01-02 13:30:17 Re: Restoring a table is ten times slower on Ubuntu 14.04 than on Ubuntu 16.04