3 ms·

This is a really bad idea as explained eloquently in this blog: https://tomharrisonjr.com/uuid-or-guid-as-primary-keys-be-careful-7b2aa3dcb439 https://tomharri

This is a really bad idea as explained eloquently in this blog: https://tomharrisonjr.com/uuid-or-guid-as-primary-keys-be-careful-7b2aa3dcb439 https://tomharrisonjr.com/uuid-or-guid-as-primary-keys-be-ca...

tgb 6y ago
Maybe I just don't know enough about this field, but I didn't actually understand the objections in that post. They were either orthogonal to UUID use (don't store them as string, don't share them publicly) or seemed to not actually come to a conclusion. The "UUIDs are a pain" point makes sense as does the "Primary keys get around" section. But the section "Planning for real scale" seems to stop short of saying anything against UUIDs (it only talks about ints versus bigints?). The author even concludes (confusingly) with: > So, in reality, the right solution is probably: use UUIDs for keys, and don’t ever expose them. Can someone shed light on this?
- zerkten 6y ago
  >> So, in reality, the right solution is probably: use UUIDs for keys, and don’t ever expose them. > Can someone shed light on this? Some of the answers over at https://softwareengineering.stackexchange.com/questions/218306/why-not-expose-a-primary-key https://softwareengineering.stackexchange.com/questions/2183... cover the reasoning that I had been taught in the mid-nineties: it's an implementation detail and there are better choices to expose outside the database. When it comes to UUIDs, these often have a usability penalty when exposed to users. There may be something better to expose to users depending on the context, but not always. It's never an absolute. Since few people now start by building their database first with relatively rigorous modeling, it's hard to avoid the outside exposure. You should consider the situation though. In the early-2000s, there was a raft of web apps built in the .NET space that exposed GUIDs just because they were cool (I've literally heard them described as that), or devs didn't think about the implications. As with many ASP.NET abstractions at the time, these were problematic in the contexts used: Web Forms relied on POSTing everything and now your URLs weren't "hackable". Retrofitting UUIDs is possible if you are using other keys. If you are merging databases you can introduce these to get the data merged, as long as you are OK with some level of fix-ups to the original (integer?) primary key that you've exposed to the application. Ultimately, it becomes an application problem if you have collisions in the original IDs.
  - tgb 6y ago
    Sorry, but my question is about whether UUIDs are or are not recommended for use as primary keys. The article seems to be saying both No and Yes to that question.
anamexis 6y ago
It seems like the biggest arguments against UUIDs in that post don't apply here, and the other arguments are explicitly acknowledged in the "caveats" section of OP. Also half the things in that post seem like they have nothing to do specifically with UUIDs at all (scaling, foreign keys, exposing primary keys)
lights0123 6y ago
I've always disagreed with that post, and I've seen others with the same thoughts when this was mentioned on HN before. Specifically: "Don’t be naive": that's just talking about storing the UUID wrong. You could design your schema badly and store your integer primary key as a string type too, but the post doesn't mention that. "UUIDs are a pain": so just don't memorize primary keys? "Planning for real scaling": ok, each record uses 16 bytes instead of 8. But their proposed solution, "Best of Both", uses both 8 and 16 byte records. "Primary keys get around in normalized databases": finally a real problem. "It’s really hard to sort random numbers": also potentially a problem—I'm not familiar enough with clustering so I can't offer any thoughts. "My keys will never change (until they do)": they never explain why they needed to change PKs when migrating DBs
- mikepurvis 6y ago
  > they never explain why they needed to change PKs when migrating DBs Not a migration scenario, but thinking of other kinds of consolidations, like folding together separate Employee tables after a company merger.
  - ldng 6y ago
    Wouldn't you have a lot more issues than just ids ? It very improbable that folding 1:1 would be even possible. You'll much more likely have to do a migration merging relevant information into a new table. So the question holds, when do you change a PK ?
    - oxfordmale 6y ago
      Database migrations
IgorPartola 6y ago
I can buy some of those arguments for databases where your indexes are so huge that making them 4x the size would make things a lot more expensive. But outside of that I think you would be hard pressed to find a real world difference. One nice thing about using random IDs is that in a multi master setup you are significantly less likely to run into issues with two nodes generating the same ID for two different rows. Yeah you can set up different offsets and increments but that’s fragile. Also, under what circumstances would you want to change the values of your primary keys?
istjohn 6y ago
The gist of it: > But there’s a far more compelling reason not to use any kind of PK in a public context: if you ever need to change keys, all your external references are broken. Think “404 Page Not Found”. > When would you need to change keys? As it happens, we’re doing a data migration this week, because who knew in 2003 when the company started that we would now have 13 massive SQL Server databases and growing fast?
- agustif 6y ago
  Amazon just launched Babelfish in case you're moving to postgres
ldng 6y ago
RTFM ? Drawbacks are clearly stated, feels like you have not read the project page. Plus, most of the objections from your article are IMO invalid.

tgb 6y ago

Maybe I just don't know enough about this field, but I didn't actually understand the objections in that post. They were either orthogonal to UUID use (don't store them as string, don't share them publicly) or seemed to not actually come to a conclusion. The "UUIDs are a pain" point makes sense as does the "Primary keys get around" section. But the section "Planning for real scale" seems to stop short of saying anything against UUIDs (it only talks about ints versus bigints?). The author even concludes (confusingly) with: > So, in reality, the right solution is probably: use UUIDs for keys, and don’t ever expose them. Can someone shed light on this?

zerkten 6y ago
>> So, in reality, the right solution is probably: use UUIDs for keys, and don’t ever expose them. > Can someone shed light on this? Some of the answers over at https://softwareengineering.stackexchange.com/questions/218306/why-not-expose-a-primary-key https://softwareengineering.stackexchange.com/questions/2183... cover the reasoning that I had been taught in the mid-nineties: it's an implementation detail and there are better choices to expose outside the database. When it comes to UUIDs, these often have a usability penalty when exposed to users. There may be something better to expose to users depending on the context, but not always. It's never an absolute. Since few people now start by building their database first with relatively rigorous modeling, it's hard to avoid the outside exposure. You should consider the situation though. In the early-2000s, there was a raft of web apps built in the .NET space that exposed GUIDs just because they were cool (I've literally heard them described as that), or devs didn't think about the implications. As with many ASP.NET abstractions at the time, these were problematic in the contexts used: Web Forms relied on POSTing everything and now your URLs weren't "hackable". Retrofitting UUIDs is possible if you are using other keys. If you are merging databases you can introduce these to get the data merged, as long as you are OK with some level of fix-ups to the original (integer?) primary key that you've exposed to the application. Ultimately, it becomes an application problem if you have collisions in the original IDs.
- tgb 6y ago
  Sorry, but my question is about whether UUIDs are or are not recommended for use as primary keys. The article seems to be saying both No and Yes to that question.

anamexis 6y ago

It seems like the biggest arguments against UUIDs in that post don't apply here, and the other arguments are explicitly acknowledged in the "caveats" section of OP. Also half the things in that post seem like they have nothing to do specifically with UUIDs at all (scaling, foreign keys, exposing primary keys)

lights0123 6y ago

I've always disagreed with that post, and I've seen others with the same thoughts when this was mentioned on HN before. Specifically: "Don’t be naive": that's just talking about storing the UUID wrong. You could design your schema badly and store your integer primary key as a string type too, but the post doesn't mention that. "UUIDs are a pain": so just don't memorize primary keys? "Planning for real scaling": ok, each record uses 16 bytes instead of 8. But their proposed solution, "Best of Both", uses both 8 and 16 byte records. "Primary keys get around in normalized databases": finally a real problem. "It’s really hard to sort random numbers": also potentially a problem—I'm not familiar enough with clustering so I can't offer any thoughts. "My keys will never change (until they do)": they never explain why they needed to change PKs when migrating DBs

mikepurvis 6y ago
> they never explain why they needed to change PKs when migrating DBs Not a migration scenario, but thinking of other kinds of consolidations, like folding together separate Employee tables after a company merger.
- ldng 6y ago
  Wouldn't you have a lot more issues than just ids ? It very improbable that folding 1:1 would be even possible. You'll much more likely have to do a migration merging relevant information into a new table. So the question holds, when do you change a PK ?
  - oxfordmale 6y ago
    Database migrations

IgorPartola 6y ago

I can buy some of those arguments for databases where your indexes are so huge that making them 4x the size would make things a lot more expensive. But outside of that I think you would be hard pressed to find a real world difference. One nice thing about using random IDs is that in a multi master setup you are significantly less likely to run into issues with two nodes generating the same ID for two different rows. Yeah you can set up different offsets and increments but that’s fragile. Also, under what circumstances would you want to change the values of your primary keys?

istjohn 6y ago

The gist of it: > But there’s a far more compelling reason not to use any kind of PK in a public context: if you ever need to change keys, all your external references are broken. Think “404 Page Not Found”. > When would you need to change keys? As it happens, we’re doing a data migration this week, because who knew in 2003 when the company started that we would now have 13 massive SQL Server databases and growing fast?

agustif 6y ago
Amazon just launched Babelfish in case you're moving to postgres

ldng 6y ago

RTFM ? Drawbacks are clearly stated, feels like you have not read the project page. Plus, most of the objections from your article are IMO invalid.