3 ms·
Serial vs. UUID for primary keys when sharding
ive read a lot on here that serials/bigserials should be used instead of UUIDs for PKs in SQL, but how does one go about this if they're sharding? all of a sudden, there will be duplicate PKs across shards, meaning any reshuffling between physical shards cannot be done
- potamic 2y agoI believe the prevalent advice at this time in fact is to go with UUIDs. They don't leak much information, prevent enumeration attacks and give you flexibility to generate anywhere. UUIDv7 even provides time ordering if index locality matters to you, but of course with the trade off of leaking time information. I would argue there is no good reason to use database sequences any more.
- jonasbrouthers 2y agoIf I use uuidv4 right now, should switching the uuid generation function to uuidv7 some time in the future should give me the full benefit of using uuidv7, from that point in time onward (faster inserts)? As in, the index locality affects writes due to reducing page splits, but for reads, assuming IDs are being queried in random orders, uuidv4 vs v7 won't matter since neither of them would be able to take advantage of any locality?
- potamic 2y agoYes, I think so.