Re: primary key hash index

On Tue, Jan 2, 2018 at 6:02 AM, 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?
>

Hash indexes do not yet support primary keys, but you could always test it
with just an plain index, since you already know the keys are unique via
the way they are constructed. But I wouldn't expect any real improvement.
Hash indexes still trigger FPW and still dirty massive numbers of pages in
a random fashion (even worse than btree does as far as randomness goes but
since the hash is more compact maybe more of the pages will be re-dirtied
and so save on FPW or separate writes). I was surprised that turning off
FPW was so effective for him, that suggests that maybe his checkpoints are
too close together, which I guess means max_wal_size is too low.

Cheers,

Jeff