14 ms·

"Then add a column populated with a UUID (perhaps as a trigger on insert). Within the scope of the database itself, relationships can be managed using the real

"Then add a column populated with a UUID (perhaps as a trigger on insert). Within the scope of the database itself, relationships can be managed using the real PKs and FKs." That would mean doing lookups by UUID, which is /really/ bad for performance. UUIDs are evenly distributed, so index caches are rendered nearly useless. With sequential keys, and access patterns that touch mostly new data, all you need to find the row is likely to already be in RAM, no matter how many rows you have. With UUIDs, you'd end up doing random I/O. Might not sound like that big deal to some, but we got a 3x overall throughput increase in one of our apps by switching from UUIDs to sequential ints.

firebird84 9y ago
I've found many have made the reverse argument, I assume for write-heavy loads. With incoming new UUIDs likely being on different pages, any locking within the DB is likely to be uncontended with a UUID key. Most people's RDBMS use cases are read heavy anyway, though, so it's usually better to have sequential (clustered) keys.
- dhd415 9y ago
  I've heard that case made for certain write-heavy workloads, too, but I would expect that to be highly dependent on the details of the db engine's implementation and your particular workload. While UUIDs, as you said, would generally be written to different pages on insert, you'd also experience a higher page split rate when UUIDs arrive that need to be written to pages that are already full which could be expensive. Without knowing the details of your db engine and/or workload really well, I'd expect you'd have to do some testing of your workload to see which one would come out ahead. In a series of tests that I did on application with a 95% write, 5% read workload, integer IDs were substantially higher performing than GUID IDs.
Rapzid 9y ago
Not all "UUID's" are distributed evenly. This is mentioned in the article.
- mreftel 9y ago
  This was on Oracle, which lacks MS SQL's special handling of UUIDs (which speeds up indexes on UUID columns by using the bytes for the time stamp in UUIDv1/v2 as the high-order bytes - clever hack!). But sure, if you need UUIDs and your database has support for speeding them up, by all means, use that. I'd still avoid them given a choice, though.

firebird84 9y ago

I've found many have made the reverse argument, I assume for write-heavy loads. With incoming new UUIDs likely being on different pages, any locking within the DB is likely to be uncontended with a UUID key. Most people's RDBMS use cases are read heavy anyway, though, so it's usually better to have sequential (clustered) keys.

dhd415 9y ago
I've heard that case made for certain write-heavy workloads, too, but I would expect that to be highly dependent on the details of the db engine's implementation and your particular workload. While UUIDs, as you said, would generally be written to different pages on insert, you'd also experience a higher page split rate when UUIDs arrive that need to be written to pages that are already full which could be expensive. Without knowing the details of your db engine and/or workload really well, I'd expect you'd have to do some testing of your workload to see which one would come out ahead. In a series of tests that I did on application with a 95% write, 5% read workload, integer IDs were substantially higher performing than GUID IDs.

Rapzid 9y ago

Not all "UUID's" are distributed evenly. This is mentioned in the article.

mreftel 9y ago
This was on Oracle, which lacks MS SQL's special handling of UUIDs (which speeds up indexes on UUID columns by using the bytes for the time stamp in UUIDv1/v2 as the high-order bytes - clever hack!). But sure, if you need UUIDs and your database has support for speeding them up, by all means, use that. I'd still avoid them given a choice, though.