I have a question, As i understand here, usage wise there are multiple benefits of UUID over sequences like, in case of distributed app where we may not be able to rely on one point generator like sequences, in case of multi master architecture, sharding.
If we just look in terms of performance wise, the key advantage of sequence is that for read queries, because of the storage size it will be smaller and thus it will cache more index rows and so will be beneficial during read queries and should also be beneficial even on joins because of its smaller size. Also fetching a value from sequence is cheaper than calculating the UUIDS. But the downside is during write operation, it can be a point of contention in case of concurrent data load as every incoming request will try to modify same table/index page/block. But as its mentioned in this blog (https://www.2ndquadrant.com/en/blog/sequential-uuid-generators/), state this UUID can be made sequential so even this can be sequential using prefix-timestamp etc. However isn't it that making the UUID sequential will again actually be a disadvantage and can be contention point for this unique index as each incoming write will now fight for same block/page while doing concurrent data load and will contend for the same table block or say one side of the index branch/leaf block etc, whereas in case of random UUIDs the write was spreading across multiple blocks so there was no contention on any specific blocks? Please correct if my understanding is wrong?
On Sat, Jan 28, 2023 at 8:02 PM Ron <ronljohnsonjr@gmail.com> wrote: > > Then it's not a Type 4 UUID, which is perfectly fine; just not random.
Yep, which is why it really should be re-versioned to UUIDv8 to be pedantic. In everyday use though, almost certainly doesn't matter.
> Also, should now() be replaced by clock_timestamp(), so that it can be > called multiple times in the same transaction?
Not necessary. Instead of 122 bits of entropy, you get 106 bits of entropy and a new incremented prefix every minute. now() vs clock_timestamp() wouldn't make a substantive difference. Should still be reasonably safe against the birthday paradox for more than a century when creating more than a million UUIDs per second.