5 ms·

I don't get this, as far as I understand there is no reason at all to use a string column for a UUID. Your primary keys are important for performance and extrem

I don't get this, as far as I understand there is no reason at all to use a string column for a UUID. Your primary keys are important for performance and extremely important for integrity. Making them slower and less robust by using a string column just doesn't make any sense, why would you ever do that? UUIDs are binary data, they should be stored as such.

nhoughto 5y ago
Yeah it is essentially storing bytes in a encoded form.. it is never the most efficient way. uuid, hex, base64 whatever.. when it hits the db it can be decoded into bytes and stored more efficiently which is generally a good idea.
Aeolun 5y ago
I’m fairly certain I heard someone say that it was inconvenient for the devs, or something like that. A lot of technical problems are born of laziness.
yardstick 5y ago
From the article: > If you use an embedded type like PostgreSQL UUID everywhere, then this is not an issue for you. So only applies where your language/system doesn’t support strong types.
mercantile 5y ago
Real UUIDs in the DB are definitely the right answer. I couldn't help but think the article has the right idea but reaches the wrong conclusion — it is possible to use user-specified IDs, but make sure you're taking them as UUID and storing them as such too. That said, I understand how they got there. Although using real UUIDs in the backend is obviously the right path, it's amazing how rare their use is in industry. Developers either (1) don't know about the UUID type, (2) don't understand the advantages of such and therefore use a string instead because it's more familiar, or (3) in hubris, cast off the use of UUID because they know better and are doing their own thing. We're using real UUIDs where I work now, but after a full ten years of industry experience, it's the first job where we're doing it right, despite previous jobs being at top name Silicon Valley companies who you'd think would know what they're doing.
- dspillett 5y ago
  There is a fourth reason: momentum. We are just (finally) retiring a product with a large less-organised-than-we-care-to-about codebase that has been around in various states longer than our younger staff have been alive, that stored UUIDs wrong due to early misunderstanding by a long-gone expert. Several times during its life I made the case for converting it's use of UUIDS in strings to proper UUID types, even demonstrating significant performance improvements under load in a PoC, and storage savings. Everyone agreed it should be done that way, but there was considerably more work involved than just switching types (changing procs or type conversion could kill good query plans, dealing with the fact that at some point in history a smart person had used other strings as special values in one or two places, finding ad-hoc query preparation everywhere and making sure that declared the right type, ..., and of course the dreaded full regression test) so it never got done. Especially in the last decade and a bit, because it would be retired in a year or two (ahem) so the time & effort would be better put to use elsewhere.

nhoughto 5y ago

Yeah it is essentially storing bytes in a encoded form.. it is never the most efficient way. uuid, hex, base64 whatever.. when it hits the db it can be decoded into bytes and stored more efficiently which is generally a good idea.

Aeolun 5y ago

I’m fairly certain I heard someone say that it was inconvenient for the devs, or something like that. A lot of technical problems are born of laziness.

yardstick 5y ago

From the article: > If you use an embedded type like PostgreSQL UUID everywhere, then this is not an issue for you. So only applies where your language/system doesn’t support strong types.

mercantile 5y ago

Real UUIDs in the DB are definitely the right answer. I couldn't help but think the article has the right idea but reaches the wrong conclusion — it is possible to use user-specified IDs, but make sure you're taking them as UUID and storing them as such too. That said, I understand how they got there. Although using real UUIDs in the backend is obviously the right path, it's amazing how rare their use is in industry. Developers either (1) don't know about the UUID type, (2) don't understand the advantages of such and therefore use a string instead because it's more familiar, or (3) in hubris, cast off the use of UUID because they know better and are doing their own thing. We're using real UUIDs where I work now, but after a full ten years of industry experience, it's the first job where we're doing it right, despite previous jobs being at top name Silicon Valley companies who you'd think would know what they're doing.

dspillett 5y ago
There is a fourth reason: momentum. We are just (finally) retiring a product with a large less-organised-than-we-care-to-about codebase that has been around in various states longer than our younger staff have been alive, that stored UUIDs wrong due to early misunderstanding by a long-gone expert. Several times during its life I made the case for converting it's use of UUIDS in strings to proper UUID types, even demonstrating significant performance improvements under load in a PoC, and storage savings. Everyone agreed it should be done that way, but there was considerably more work involved than just switching types (changing procs or type conversion could kill good query plans, dealing with the fact that at some point in history a smart person had used other strings as special values in one or two places, finding ad-hoc query preparation everywhere and making sure that declared the right type, ..., and of course the dreaded full regression test) so it never got done. Especially in the last decade and a bit, because it would be retired in a year or two (ahem) so the time & effort would be better put to use elsewhere.