1

I realize a similar question has been asked multiple times but all the ones I can find are from 2014/2015 and many versions of postgres ago.

I'm on postgres 11. I'm introducing a new primary key for a table of type uuid. I see that since postgres 10, hash index types are in pretty good shape ( e.g. http://rhaas.blogspot.com/2017/09/postgresqls-hash-indexes-are-now-cool.html ), and I'm wondering if it's the better choice for a uuid key in postgres these days?


Update I realize I'm missing part of the use case which impacts this question.

My primary use case is that I previously had a composite primary key and am replacing it with a UUID for easy joins and to not have to propagate the primary key's data to other tables.

In this sense, I'm likely to want create the primary key using INCLUDE (composite, fields, here) which is only supported for btree index types. So in that sense, I have my answer.

I am still curious about a 'generic' use case of simply wanting rapid access to looking up a value by its primary key, however.

2

My primary use case is that I previously had a composite primary key and am replacing it with a UUID for easy joins and to not have to propagate the primary key's data to other tables.

A hash index wouldn't work for that: it can't enforce uniqueness, therefore it can't be used to support a primary key. As mentioned in the v11 documentation:

Currently, only B-tree indexes can be declared unique.

|improve this answer|||||
  • That's an excellent point, thank you. – drobert Jun 10 '19 at 21:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.