3 ms·

Is it really true that concerns around UUIDs as primary keys are wholly irrelevant? Maybe I'm working off outdated information but in high scale environments th

by hderms 5y ago

Is it really true that concerns around UUIDs as primary keys are wholly irrelevant? Maybe I'm working off outdated information but in high scale environments there are a lot of downsides primarily related to the random write patterns into B-trees causing page splitting and things like that.

xyzzy_plugh 5y ago
You're right that random unordered writes are worst case for an indexed (ordered) key. ULID and ordered UUIDs (v6+) help solve this. For dimensions, UUIDs are usually fine since writes are infrequent. For facts or timeseries data, ordered IDs are more efficient.
- WorldMaker 5y ago
  Yes, ordered UUIDs help solve this. The unfortunate deep dive rabbit hole here is how UUIDs are sorted in different databases and making sure your UUID generation matches that. One fun for instance I worked directly with: Microsoft's SQL Server made some interesting assumptions based on UUID v1 and sorts the last six bytes first. In UUIDv1 those would have been the MAC addresses and clustering by originating machine first has some sort of sense to it in terms of ordered writes. The ULID timestamp is coincidentally also six bytes (48 bits) so (ignoring the Endian issues of the other "fields" in the UUID) you can get Microsoft's SQL Server to order UUIDs in mostly the same way as their ULID representation by just transposing the first six bytes to be the last six bytes. Unfortunately UUID v6+ won't sort well in Microsoft SQL Server's sort order today. Other databases will vary on what you need to do to get sortable UUIDs. A reference to me on all of this deep rabbit hole was Raymond Chen's blog on the many GUID/UUID sort orders just in Microsoft products: https://devblogs.microsoft.com/oldnewthing/20190426-00/?p=102450 https://devblogs.microsoft.com/oldnewthing/20190426-00/?p=10... (With the fun punchline at the bottom being the link to the Java sort order. My sympathies to anyone trying to sort UUIDs in an Oracle database.)
mdekkers 5y ago
I speak from bitter experience - UUID for PKs is not a good idea out of the box. Both writing as well as reading took very significant penalties. I did not design that particular system, but I had to figure out why relatively simple queries took minutes to return.
- traceroute66 5y ago
  > I did not design that particular system ... why relatively simple queries took minutes to return The road of databases is paved with many such bodies. Whether it is developers treating databases like some blackbox dumping ground, or designing generic "portable" schemas, or people who don't know SQL writing weird long convoluted queries. Many people are quick to blame "the database", but 99% of the time its the fault of those who designed the schema and/or the queries that run on it. I think your statement "UUID for PKs is not a good idea out of the box" is unfair and too broad brush. Without knowing the exact details of every bit of your environment (from database hardware upwards), its not possible to accept such a generic statement to be read as a fact.
traceroute66 5y ago
> Is it really true that concerns around UUIDs as primary keys are wholly irrelevant? I would say yes, with the options we have today with modern compute. We live in a world where compute is powerful enough to enable Let's Encrypt to issue SSL certificates for 235 million websites every 90 days off the back of a single MySQL server[1]. For high scale environments there are also other options such as async queues and Redis middleware. Database technology itself is also evolving, and the degree of measurable downside is less than it might have been 10 years ago. I would still argue that for the vast majority of people, UUIDs are the way to go. I would certainly urge caution against premature optimisation involved with the "but high scale" argument. Sure things MIGHT be noticeable at high scale, but I think its fair to say most people are not doing anywhere enough high scale to do so and should probably just use UUIDs and cross the "high scale" bridge if/when they ever come to it. Finally, its also worth pointing out that all the hyperscalers use UUIDs or other unique identifiers widely in their infrastructure and APIs, all of which must inevitably be tied into a database backend. [1]https://letsencrypt.org/2021/01/21/next-gen-database-servers.html https://letsencrypt.org/2021/01/21/next-gen-database-servers...

by hderms 5y ago

xyzzy_plugh 5y ago

You're right that random unordered writes are worst case for an indexed (ordered) key. ULID and ordered UUIDs (v6+) help solve this. For dimensions, UUIDs are usually fine since writes are infrequent. For facts or timeseries data, ordered IDs are more efficient.

WorldMaker 5y ago
Yes, ordered UUIDs help solve this. The unfortunate deep dive rabbit hole here is how UUIDs are sorted in different databases and making sure your UUID generation matches that. One fun for instance I worked directly with: Microsoft's SQL Server made some interesting assumptions based on UUID v1 and sorts the last six bytes first. In UUIDv1 those would have been the MAC addresses and clustering by originating machine first has some sort of sense to it in terms of ordered writes. The ULID timestamp is coincidentally also six bytes (48 bits) so (ignoring the Endian issues of the other "fields" in the UUID) you can get Microsoft's SQL Server to order UUIDs in mostly the same way as their ULID representation by just transposing the first six bytes to be the last six bytes. Unfortunately UUID v6+ won't sort well in Microsoft SQL Server's sort order today. Other databases will vary on what you need to do to get sortable UUIDs. A reference to me on all of this deep rabbit hole was Raymond Chen's blog on the many GUID/UUID sort orders just in Microsoft products: https://devblogs.microsoft.com/oldnewthing/20190426-00/?p=102450 https://devblogs.microsoft.com/oldnewthing/20190426-00/?p=10... (With the fun punchline at the bottom being the link to the Java sort order. My sympathies to anyone trying to sort UUIDs in an Oracle database.)

mdekkers 5y ago

I speak from bitter experience - UUID for PKs is not a good idea out of the box. Both writing as well as reading took very significant penalties. I did not design that particular system, but I had to figure out why relatively simple queries took minutes to return.

traceroute66 5y ago
> I did not design that particular system ... why relatively simple queries took minutes to return The road of databases is paved with many such bodies. Whether it is developers treating databases like some blackbox dumping ground, or designing generic "portable" schemas, or people who don't know SQL writing weird long convoluted queries. Many people are quick to blame "the database", but 99% of the time its the fault of those who designed the schema and/or the queries that run on it. I think your statement "UUID for PKs is not a good idea out of the box" is unfair and too broad brush. Without knowing the exact details of every bit of your environment (from database hardware upwards), its not possible to accept such a generic statement to be read as a fact.

traceroute66 5y ago

> Is it really true that concerns around UUIDs as primary keys are wholly irrelevant? I would say yes, with the options we have today with modern compute. We live in a world where compute is powerful enough to enable Let's Encrypt to issue SSL certificates for 235 million websites every 90 days off the back of a single MySQL server[1]. For high scale environments there are also other options such as async queues and Redis middleware. Database technology itself is also evolving, and the degree of measurable downside is less than it might have been 10 years ago. I would still argue that for the vast majority of people, UUIDs are the way to go. I would certainly urge caution against premature optimisation involved with the "but high scale" argument. Sure things MIGHT be noticeable at high scale, but I think its fair to say most people are not doing anywhere enough high scale to do so and should probably just use UUIDs and cross the "high scale" bridge if/when they ever come to it. Finally, its also worth pointing out that all the hyperscalers use UUIDs or other unique identifiers widely in their infrastructure and APIs, all of which must inevitably be tied into a database backend. [1]https://letsencrypt.org/2021/01/21/next-gen-database-servers.html https://letsencrypt.org/2021/01/21/next-gen-database-servers...