4 ms·

So you rather your DB performance tank because of unsortable and unclusterable (sp?) PKs than expose a tiny bit information that shouldn't even be that importan

So you rather your DB performance tank because of unsortable and unclusterable (sp?) PKs than expose a tiny bit information that shouldn't even be that important? You can use a UUID as a URI link, but you shouldn't use it as a PK on most databases. That means you would have to add another column for the link ID (which I think is a relatively good and clean solution)

eropple 7y 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 7y ago
  Hash 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 7y ago
    Huh, 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. :)
spinningarrow 7y ago
Why shouldn’t you use a uuid as a primary key on most databases?
wvh 7y ago
Use an encoded time prefix and a random part to create your UUID. I've been using such UUIDs as primary keys in Postgresql for years and I can't tell the difference in performance with say 64-bit numbers. If you don't need millions of records, even a 64-bit number with timestamp and random part will do – like "Twitter snowflake" ids.

eropple 7y 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 7y ago
Hash 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 7y ago
  Huh, 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. :)

spinningarrow 7y ago

Why shouldn’t you use a uuid as a primary key on most databases?

wvh 7y ago

Use an encoded time prefix and a random part to create your UUID. I've been using such UUIDs as primary keys in Postgresql for years and I can't tell the difference in performance with say 64-bit numbers. If you don't need millions of records, even a 64-bit number with timestamp and random part will do – like "Twitter snowflake" ids.