Just a month ago we migrated many of the columns from integers to UUIDs (encoded as binary(16)) in several critical tables which are pretty large (Percona server, too), and so far I haven't heard about any serious performance degradation after the release.
UUIDs are popular, but bad for performance (2019)
41–50 of 246 posts
Re: UUIDs are popular, but bad for performance (2019)
#42Just a month ago we migrated many of the columns from integers to UUIDs (encoded as binary(16)) in several critical tables which are pretty large (Percona server, too), and so far I haven't heard about any serious performance degradation after the release.
what's pretty large for you though ? There are fields where 100k entries is a pretty large dataset and others where "large" starts at petabyte
Re: UUIDs are popular, but bad for performance (2019)
#43A lot of things that we do for security or privacy are bad for performance, but I think they are still good tradeoffs.
Agreed. Using UUID-s for keys is useful to exclude entire classes of security issues. Most notable are many kinds of enumeration attacks.
Re: UUIDs are popular, but bad for performance (2019)
#44Earlier quoted context omitted.
That's why the pattern is eventual consistency. You receive a message "{entity}Created" and it contains the Id of the full object.
It's like, _maybe_ eventual consistency. Hopefully the client doesn't try to do anything important with the ID/new object
If you get a "Created" back, it is possible for it to be guaranteed that it'll be created eventually.
Re: UUIDs are popular, but bad for performance (2019)
#45Earlier quoted context omitted.
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.
Some years back (maybe 5-10?) I remember doing a test with UUIDs on Postgres, and found no speed difference between UUIDs and integer PKs. I don't remember the parameters of the test, however.
I've experienced some performance differences with UUIDs vs integers, but it has been minor enough to never cause any issues, even in tables with billions of records.
Re: UUIDs are popular, but bad for performance (2019)
#46Earlier quoted context omitted.
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.
> SQL Server has a sequential uuid type which avoids exactly this problem. you refer to the uuid generated by sql server?
Yes. When generating you can use NewSequentialId() which generates a sequential guid to avoid needing to reorganise indexes.
Re: UUIDs are popular, but bad for performance (2019)
#47Earlier quoted context omitted.
It's like, _maybe_ eventual consistency. Hopefully the client doesn't try to do anything important with the ID/new object
If you get a "Created" back, it is possible for it to be guaranteed that it'll be created eventually.
I don't think you would ever get a 201 Created in an eventual consistency scenario. You would get a 202 Accepted. Unless the API is lying to the clients.
Re: UUIDs are popular, but bad for performance (2019)
#48Isn't this easily solved by supporting 128 bit keys and using UUIDs as intended, i.e. as integers and not in their string serialization? This is as nonsensical as storing IPv4 as strings instead of 32 bit integers.
Re: UUIDs are popular, but bad for performance (2019)
#49Earlier quoted context omitted.
If you get a "Created" back, it is possible for it to be guaranteed that it'll be created eventually.
I don't think you would ever get a 201 Created in an eventual consistency scenario. You would get a 202 Accepted. Unless the API is lying to the clients.
Fair enough, though what I said remains true for an "Accepted" response.
Re: UUIDs are popular, but bad for performance (2019)
#50Isn't this easily solved by supporting 128 bit keys and using UUIDs as intended, i.e. as integers and not in their string serialization? This is as nonsensical as storing IPv4 as strings instead of 32 bit integers.
It should be noted that some database providers already provide a UUID data type which is a 128bit integer.