patternsqlMinor
best index type for uuid in postgres 11
Viewed 0 times
postgrestypeforuuidindexbest
Problem
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
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
I am still curious about a 'generic' use case of simply wanting rapid access to looking up a value by its primary key, however.
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.
Solution
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.
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.
Context
StackExchange Database Administrators Q#240001, answer score: 5
Revisions (0)
No revisions yet.