UUIDs are popular, but bad for performance (2019)
1–10 of 246 posts
Re: UUIDs are popular, but bad for performance (2019)
#2Is this specific to MySQL or does it apply to Postgres too?
Re: UUIDs are popular, but bad for performance (2019)
#3Is this specific to MySQL or does it apply to Postgres too?
As far as I can gather from this post and looking at the data type documentation, MySQL does not have a specific UUID type, but Postgres does.[0] I'll assume that Postgres has some internal optimisations to UUID that MySQL thus lacks.
Addendum: I also realise this is anecdotal, but someone on Stackoverflow mentions a significant speed up from changing `text` to `uuid` in Postgres.[1] But this also fits with what I've been told on #postgresql on libera.chat. That being said, integers would still outperform uuid.
[0] https://www.postgresql.org/docs/14/datatype-uuid.html [1] https://stackoverflow.com/questions/29880083/postgresql-uuid...
Re: UUIDs are popular, but bad for performance (2019)
#4Is this specific to MySQL or does it apply to Postgres too?
As far as I can gather from this post and looking at the data type documentation, MySQL does not have a specific UUID type, but Postgres does.[0] I'll assume that Postgres has some internal optimisations to UUID that MySQL thus lacks. Addendum: I also realise this is anecdotal, but someone on Stackoverflow mentions a significant speed up from changing `text` to `uuid` in Postgres.[1] But this also fits with what I've…
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.
Re: UUIDs are popular, but bad for performance (2019)
#5Earlier quoted context omitted.
As far as I can gather from this post and looking at the data type documentation, MySQL does not have a specific UUID type, but Postgres does.[0] I'll assume that Postgres has some internal optimisations to UUID that MySQL thus lacks. Addendum: I also realise this is anecdotal, but someone on Stackoverflow mentions a significant speed up from changing `text` to `uuid` in Postgres.[1] But this also fits with what I've…
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.
Re: UUIDs are popular, but bad for performance (2019)
#6Is this specific to MySQL or does it apply to Postgres too?
As far as I can gather from this post and looking at the data type documentation, MySQL does not have a specific UUID type, but Postgres does.[0] I'll assume that Postgres has some internal optimisations to UUID that MySQL thus lacks. Addendum: I also realise this is anecdotal, but someone on Stackoverflow mentions a significant speed up from changing `text` to `uuid` in Postgres.[1] But this also fits with what I've…
The Postgres UUID type works just like BINARY(16) covered in the article.
Re: UUIDs are popular, but bad for performance (2019)
#7Bad 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.
Re: UUIDs are popular, but bad for performance (2019)
#8> 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.
Re: UUIDs are popular, but bad for performance (2019)
#9Is 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 representing 216 GB.
It assumes that MySQL users store UUIDs as CHAR(36), which is very wasteful, since an UUID actually requires 16 bytes (128 bits).
Now, one can store UUIDs a binary blobs in MySQL, however, they are not human-readable, so one tends to store them as CHAR(36) instead, wasting 20 bytes per entry (in total, requiring 2.25 times the strict necessary).
By supporting UUID as native data type, the storage can use the strict necessary amount of bytes, but still maintain readability, because the RDBMS will convert the data to a human-readable form.
Additionally, MySQL's clustered indexes are subject to write amplification, which makes things worse.
I haven't read the rest of the article though, which likely includes other consideration about the spatiality problems due to randomness. Things gets even more complex, due to the relationship with the data structures (I haven't fully read the article).
Re: UUIDs are popular, but bad for performance (2019)
#10Interestingly, 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.google.com/datastore/docs/best-practices#high_...