The conversation

bruce511HNSource author
Lessons learnedExperience excerpt · English

We started by running a database on prem

… We started by running a database on prem. Each branch or store got their own db. 15 years later always-on networking happened. 15 years after that, all businesses have fibre. So now all the branches use a giant shared online database. With merged data. Uuid based this task would be trivial. Bigint based, yeah, it's not. Along the same timeline data started escaping from our database. It would go to a phone, travel around a bit, change, get new records, then come home. Think lots of sales folk, in places without reception, doing stuff. So you're right in the context of a single database (cluster) which encompasses all the data all the time. But in the context where data lives beyond the database, using uuids solves a lot of problems. There are other places as well where uuids shine. So as with most advice when it comes to SQL, I'd add "context matters".

Original discussionThe UX of UUIDs206 source comments · at import
2

What was this responding to?

Parent context · original excerpt

HO
hot_grilHN

UUIDs should not be used as database primary keys unless the DBMS recommends it or you have a well-studied special reason for it. Postgres and MySQL are meant to use bigserial by default, even Citus. Some special sharded DBMSes like Spanner need non-sequential pkeys, but even Spanner explicitly tells you to use uuid4 because k-sortable keys cause hotspotting: https://cloud.google.com/spanner/docs/schema-design#uuid_pri...

Full original

Community replies 0

No community replies yet. Add your perspective.

Replies from the source 2

Direct replies available in this import. Original-language excerpts.

PE
perfectspiralHN

How do you know it would have worked out better with UUIDs? Did you load test it? What's the size of your dataset?

Full original
HO
hot_grilHN

When data lives beyond the database, you need a uuid, but it doesn't need to be your pkey. Even your typical backend-frontend app with a single DB will often send uuids over the API. If you're copying a DB, mutating, then merging back in, you just have to reset the bigint pkeys. I can see how in some contexts that might be less convenient (or if merges are very frequent and reads are not, less performant), but that's a special case and not something to assume from the start. For example I've done merges like this before pretty easily with bigints, and I've also been in places where they start out with uuids pkeys then never benefit.

Full original
Continue on Hacker News

Respect different perspectives. Share concrete experiences.

Sign in to post, reply, repost, and quote.