On Mon, Aug 10, 2020 at 1:45 PM Israel Brewster <ijbrewster@xxxxxxxxxx> wrote:
> On Aug 10, 2020, at 12:06 PM, Peter J. Holzer <hjp-pgsql@xxxxxx> wrote:
>
> On 2020-08-10 09:10:00 -0800, Israel Brewster wrote:
>> I would point out, however, that using a V1 UUID rather than a V4 can
>> help with this as it is sequential, not random (based on MAC address
>> and timestamp + random).
>
> If I read the specs correctly, a V1 UUID will roll over every 429
> seconds. I think that as far as index locality is concerned, this is
> essentially random for most applications.
According to wikipedia, the time value in a V1 UUID is a 60-bit number, and will roll over "around 3400AD”, depending on the algorithm used, or 5236AD if the software treats the timestamp as unsigned. This timestamp is extended by a 13 or 14-bit “uniqifying" clock sequence to handle cases of overlap, and then the 48bit MAC address (constant, so no rollover there) is appended. So perhaps that 13 or 14 bit “uniqifying” sequence will roll over every 429 seconds, however the timestamp *as a whole* won’t roll over for quite a while yet, thereby guaranteeing that the UUIDs will be sequential, not random (since, last I checked, time was sequential).
Except the time portion of a V1 UUID is not written high to low but rather low then middle then high which means that the time portion is not expressed in a sequential format and the left 8 chars of a V1 UUID "rollover" every 429 seconds or so.
For example a V1 UUID right around now looks like
7db3f2ba-db4f-11ea-87d0-0242ac130003
Less than a second later
7db534cc-db4f-11ea-87d0-0242ac130003
So that looks sequential but in roughly 429 seconds it will look like
7db3f2ba-db4f-11ea-87d1-0242ac130003
More importantly in other roughly 300 seconds it would be something like
6ab3f2ba-db4f-11ea-87d2-0242ac130003
Note the move from 87d0 to 87d1 and 87d2 in the middle but the left 8 bytes "rollover".
That's not quite sequential in terms of indexing.
John
