6 ms·

I really like UUIDs for all of the reasons described in the article. But can there can be significant performance related drawbacks: UUIDs are double the size

I really like UUIDs for all of the reasons described in the article. But can there can be significant performance related drawbacks: UUIDs are double the size of 64 bit integers. Which could mean fewer rows on data and index pages, leading to memory pressure, leading to I/O pressure. UUIDs are also random. I'm not an expert when it comes to PostgreSQL internals. But for MSSQL it's common and recommended to use your PK as a clustered index. The randomness can wreck havoc on the clustered index, causing severe fragmentation due to page splitting. There are some techniques to mitigate the effects of this (decreasing page fill factor, using incrementing UUIDs, using a surrogate IDENTITY clustered key), but each has significant tradeoffs

cm2187 9y ago
The article doesn't mention them but there are incremental only UUIDs (NEWSEQUENTIALID in TSQL) that solve that problem. Or you can prefix it with a timestamp.
- detaro 9y ago
  Don't they have the same problems the article dislikes about auto increment numbers?
  - cm2187 9y ago
    I don't think so. They don't disclose information (other than the timestamp if you use one, but each ID is still random). You will not get collisions between tables either. And unless you operate on a massive scale, you are unlikely to get collisions between machines (and you can always xor the ID with the hash of the server name to further reduce the collision risk).
- dhd415 9y ago
  The sequencing of those UUIDs resets every time Windows restarts. Given the frequency of OS and SQL Server updates that require reboots, that wasn't good enough for any of my workloads.
  - cm2187 9y ago
    Agree. And if you have two clusters, they will start at different IDs. Which is why I would probably prefix them with a timestamp (if it is not a sensitive information). Say the number of seconds since 1-1-2000. Fits on 32 bits, that still leaves you with 96 bit of entropy for a UUID, which is plenty to guarantee uniqueness unless you are working at massive scale.
- borplk 9y ago
  For anyone interested checkout UUID v1 which includes a timestamp.
vkjv 9y ago
I still like auto-increment IDs. They are small and give you a monotonically increasing value for optimistic locking. If you need multi-DC uniqueness. I prefer to add a 2nd DC column. The combination of the two is unique across DC. If you need to hide the count from customers--often the case--add a 2nd surrogate unique key that is a UUID. This keeps your joins small, but gives you a random key to give back to the customer.
lmm 9y ago
Random distribution seems like it would be better for write performance - it means that inserting multiple new values and rebuilding the index can happen in parallel rather than having to coordinate the auto increments. It also naturally has the right properties for sharding as and when you get to the point of needing that. On the read side it shouldn't make any difference, as there's no reason the rows you want to access at any given point should have any correlation to when those rows were created.
- dhd415 9y ago
  >>Random distribution seems like it would be better for write performance - it means that inserting multiple new values and rebuilding the index can happen in parallel rather than having to coordinate the auto increments. It also naturally has the right properties for sharding as and when you get to the point of needing that.<< I'm not sure what you mean by "inserting new values and rebuilding the index can happen in parallel." In most storage engines using clustered indexes, writes that append to a b-tree are faster than writes that insert somewhere in the middle of a b-tree so there really isn't any rebuilding of the index required in the former case. >>On the read side it shouldn't make any difference, as there's no reason the rows you want to access at any given point should have any correlation to when those rows were created.<< That depends entirely on the nature of your workload. There are plenty of common workloads such as sensor data or log entries for which the common read patterns are very much correlated with the order in which the rows were written.
ryebit 9y ago
While the article doesn't mention it, I think the recommended uuid function for postgres is uuid_generate_v1mc() from https://www.postgresql.org/docs/10/static/uuid-ossp.html https://www.postgresql.org/docs/10/static/uuid-ossp.html This generates a v1 uuid, so the first few bytes are time dependant, which reduces fragmentation compared to v4 uuids. At the same time, it generates random values for the "mac" section, so it isn't as host-specific and low-entropy as a normal v1 uuid.
muthdra 9y ago
Firebase creates lexicographically ordered random-looking UUIDs so you can always check relative entry order: https://firebase.googleblog.com/2015/02/the-2120-ways-to-ensure-unique_68.html https://firebase.googleblog.com/2015/02/the-2120-ways-to-ens...

cm2187 9y ago

The article doesn't mention them but there are incremental only UUIDs (NEWSEQUENTIALID in TSQL) that solve that problem. Or you can prefix it with a timestamp.

detaro 9y ago
Don't they have the same problems the article dislikes about auto increment numbers?
- cm2187 9y ago
  I don't think so. They don't disclose information (other than the timestamp if you use one, but each ID is still random). You will not get collisions between tables either. And unless you operate on a massive scale, you are unlikely to get collisions between machines (and you can always xor the ID with the hash of the server name to further reduce the collision risk).
dhd415 9y ago
The sequencing of those UUIDs resets every time Windows restarts. Given the frequency of OS and SQL Server updates that require reboots, that wasn't good enough for any of my workloads.
- cm2187 9y ago
  Agree. And if you have two clusters, they will start at different IDs. Which is why I would probably prefix them with a timestamp (if it is not a sensitive information). Say the number of seconds since 1-1-2000. Fits on 32 bits, that still leaves you with 96 bit of entropy for a UUID, which is plenty to guarantee uniqueness unless you are working at massive scale.
borplk 9y ago
For anyone interested checkout UUID v1 which includes a timestamp.

vkjv 9y ago

I still like auto-increment IDs. They are small and give you a monotonically increasing value for optimistic locking. If you need multi-DC uniqueness. I prefer to add a 2nd DC column. The combination of the two is unique across DC. If you need to hide the count from customers--often the case--add a 2nd surrogate unique key that is a UUID. This keeps your joins small, but gives you a random key to give back to the customer.

lmm 9y ago

Random distribution seems like it would be better for write performance - it means that inserting multiple new values and rebuilding the index can happen in parallel rather than having to coordinate the auto increments. It also naturally has the right properties for sharding as and when you get to the point of needing that. On the read side it shouldn't make any difference, as there's no reason the rows you want to access at any given point should have any correlation to when those rows were created.

dhd415 9y ago
>>Random distribution seems like it would be better for write performance - it means that inserting multiple new values and rebuilding the index can happen in parallel rather than having to coordinate the auto increments. It also naturally has the right properties for sharding as and when you get to the point of needing that.<< I'm not sure what you mean by "inserting new values and rebuilding the index can happen in parallel." In most storage engines using clustered indexes, writes that append to a b-tree are faster than writes that insert somewhere in the middle of a b-tree so there really isn't any rebuilding of the index required in the former case. >>On the read side it shouldn't make any difference, as there's no reason the rows you want to access at any given point should have any correlation to when those rows were created.<< That depends entirely on the nature of your workload. There are plenty of common workloads such as sensor data or log entries for which the common read patterns are very much correlated with the order in which the rows were written.

ryebit 9y ago

While the article doesn't mention it, I think the recommended uuid function for postgres is uuid_generate_v1mc() from https://www.postgresql.org/docs/10/static/uuid-ossp.html https://www.postgresql.org/docs/10/static/uuid-ossp.html This generates a v1 uuid, so the first few bytes are time dependant, which reduces fragmentation compared to v4 uuids. At the same time, it generates random values for the "mac" section, so it isn't as host-specific and low-entropy as a normal v1 uuid.

muthdra 9y ago

Firebase creates lexicographically ordered random-looking UUIDs so you can always check relative entry order: https://firebase.googleblog.com/2015/02/the-2120-ways-to-ensure-unique_68.html https://firebase.googleblog.com/2015/02/the-2120-ways-to-ens...