Re: UUID or auto-increment

On Mon, Aug 10, 2020 at 1:45 PM Israel Brewster <ijbrewster(at)alaska(dot)edu>
wrote:

>
>
> > On Aug 10, 2020, at 12:06 PM, Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> 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