Bad for performance as primary keys . But, still provide strong value as a unique identifier which is what makes them popular. I’ve used integers as primary keys, with UUIDs as alternate keys for external-to-the-data-store queries.
Done the same :-)
UUIDs are popular, but bad for performance (2019)
11–20 of 246 posts
Re: UUIDs are popular, but bad for performance (2019)
#12Earlier quoted context omitted.
A lot of the problems listed in the post are physical issues with larger data types that are somewhat random - eg the size, how clustered indexes work, and you will have the same problems with them in SQL Server.
If you order the data based on the uuid and your uuid is randomly distributed, then you will almost always be writing the data in the middle of your table, physically. You can cut the impact somewhat by using spare tables (leaving lots of empty space) but eventually you'll be re-writing the data. SQL Server has a sequential uuid type which avoids exactly this problem.
If that's the underlying issue, sequentiallity... Just use uuid uuid 6 or 7. They are time based and approximately sortable (unlike uuid1).
https://datatracker.ietf.org/doc/html/draft-peabody-dispatch...
Disclaimer, I have no data to back up this solves the performance problems described. It's just likely to solve that "writing in the middle of the table" part.
Re: UUIDs are popular, but bad for performance (2019)
#13> Let’s begin by the base64 notation. The cardinality of each byte is 64 so it takes 3 bytes in base64 to represent 2 bytes of actual value. Wait, what? I thought it takes 4 base-64 digits to represent 3 bytes of data. Not 3 base-64 digits to represent 2 bytes of data.
Maybe they're using a 9-bit byte? :)
Re: UUIDs are popular, but bad for performance (2019)
#14Bad for performance as primary keys . But, still provide strong value as a unique identifier which is what makes them popular. I’ve used integers as primary keys, with UUIDs as alternate keys for external-to-the-data-store queries.
Rather: bad for performance (as primary keys) when read/write in sequential order.
Great for (primary keys or anything) in a distributed/sharded database (e.g. CockroachDB), when data access is mostly by keys.
Re: UUIDs are popular, but bad for performance (2019)
#15Is this specific to MySQL or does it apply to Postgres too?
Postgres is somewhat different mainly because it doesn't use clustered index primary keys. So the row's position on disk is not related to the primary key index entry's position on disk.
Additionally using the less cryptographically secure uuid v1 can be a performance optimization since it has implicit time based sorting.
Re: UUIDs are popular, but bad for performance (2019)
#16I think the author is missing an overall picture, eg. Event driven scenario's.
Where you don't have to check collisions with a db. He mentioned generating the pk's on remote client, but that doesn't capture the interesting bits.
You generate the newly created object with the guid.
You send it to the API/Microservices and it's generated, fire-and-forget style. And the remote client has an Id of the newly created object to do something with.
Re: UUIDs are popular, but bad for performance (2019)
#17Interestingly, for other systems you sometimes want the exact opposite: for your key space to be distributed across indexes to balance the load (vs wanting them all to hit the same “hot” index for MySQL). For example, Google’s Cloud Firestore is bottlenecked to 500 writes/second if your key is monotonically increasing (like a timestamp or these timestamp-based UUIDs) causing you to “hotspot” the index: https://cloud.…
Yeah S3 has similar performance issues where accessing objects with the same prefixes has lower throughput because they get sharded onto the same server. It's very counterintuitive when you're used to how performance works on single computers where you want to optimize for cache-locality.
Re: UUIDs are popular, but bad for performance (2019)
#18Is this specific to MySQL or does it apply to Postgres too?
Postgres is somewhat different mainly because it doesn't use clustered index primary keys. So the row's position on disk is not related to the primary key index entry's position on disk. Additionally using the less cryptographically secure uuid v1 can be a performance optimization since it has implicit time based sorting.
> Additionally using the less cryptographically secure uuid v1 can be a performance optimization since it has implicit time based sorting.
Except the way the fields are laid out basically defeats the point: UUIDv1 lays a 60 bits timestamp starting from the lower 32 bits, so it only sorts within a 7 minutes (2*32 * 100ns) bucket.
Hence the proposal for UUIDv6, which lays the exact same timestamp in reverse order (starting from the "top" 32b), making it naturally sortable.
Re: UUIDs are popular, but bad for performance (2019)
#19I think the author is missing an overall picture, eg. Event driven scenario's. Where you don't have to check collisions with a db. He mentioned generating the pk's on remote client, but that doesn't capture the interesting bits. You generate the newly created object with the guid. You send it to the API/Microservices and it's generated, fire-and-forget style. And the remote client has an Id of the newly created objec…
But now the remote client has an ID of something that may or may not exist the next time they try to use it depending on whether or not it actually made its way into the database.
I've seen this kind of architecture before. It sounds nice but is loaded with consistency problems.
Re: UUIDs are popular, but bad for performance (2019)
#20Is this specific to MySQL or does it apply to Postgres too?
There are several problems, one of which is also ergonomy. I've only read it partially (it's a very interesting read nonetheless), however, this is a key point (italic mine): > Let’s assume a table of 1B rows having UUID values as primary key and five secondary indexes. If you read the previous paragraph, you know the primary key values are stored six times for each row. That means a total of 6B char(36) values repre…
> they are not human-readable, so one tends to store them as CHAR(36) instead
one is an idiot