3 ms·
Postgres doesn't have issues with UUIDs as primary keys; they don't need to be sorted (hash indexes were dicey before Postgres 10 but are just fine today) and P
by eropple 8y ago
Postgres doesn't have issues with UUIDs as primary keys; they don't need to be sorted (hash indexes were dicey before Postgres 10 but are just fine today) and Postgres doesn't row-cluster by default.
What performance cost there is (and in my experience it is not much) is because a UUID is twice as large as a bigint. And there are situations where that matters. But not that many of them.
- anarazel 8y agoHash indexes can't be used for primary keys: postgres[7878][1]=# SELECT amname, pg_indexam_has_property(oid, 'can_unique') FROM pg_am WHERE amtype = 'i'; ┌────────┬─────────────────────────┐ │ amname │ pg_indexam_has_property │ ├────────┼─────────────────────────┤ │ btree │ t │ │ hash │ f │ │ gist │ f │ │ gin │ f │ │ spgist │ f │ │ brin │ f │ └────────┴─────────────────────────┘ (6 rows) And postgres' btree indexes definitely are affected by the randomness in many UUID generation schemes.
- eropple 8y agoHuh, so it is. And looking at our schema, it looks like the UUID/hash tables we've got don't use pkeys at all. Looks like I have a conversation to have. Thanks. :)