From: | John W Higgins <wishdev(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: UUID or auto-increment |
Date: | 2020-08-10 21:30:48 |
Message-ID: | CAPhAwGw-AUn24vsqpwLPn=PWH0NdvAb=gKJTD463Og_YKT4S9Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Phillips | 2020-08-10 22:19:52 | serial + db key, or guid? |
Previous Message | Rob Sargent | 2020-08-10 20:56:37 | Re: UUID or auto-increment |