Re: UUID v7

From: "Andrey M(dot) Borodin" <x4mmm(at)yandex-team(dot)ru>
To: Peter Eisentraut <peter(at)eisentraut(dot)org>
Cc: Aleksander Alekseev <aleksander(at)timescale(dot)com>, pgsql-hackers mailing list <pgsql-hackers(at)postgresql(dot)org>, Sergey Prokhorenko <sergeyprokhorenko(at)yahoo(dot)com(dot)au>, Jelte Fennema-Nio <postgres(at)jeltef(dot)nl>, Przemysław Sztoch <przemyslaw(at)sztoch(dot)pl>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, Mat Arye <mat(at)timescaledb(dot)com>, Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com>, Nikolay Samokhvalov <samokhvalov(at)gmail(dot)com>, Junwang Zhao <zhjwpku(at)gmail(dot)com>
Subject: Re: UUID v7
Date: 2024-03-20 18:08:09
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

> On 19 Mar 2024, at 13:55, Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
> On 16.03.24 18:43, Andrey M. Borodin wrote:
>>> On 15 Mar 2024, at 14:47, Aleksander Alekseev <aleksander(at)timescale(dot)com> wrote:
>>> +1 to the idea. I doubt that anyone will miss it.
>> PFA v22.
>> Changes:
>> 1. Squashed all editorialisation by Jelte
>> 2. Fixed my erroneous comments on using Method 2 (we are using method 1 instead)
>> 3. Remove all traces of uuid_extract_variant()
> I have committed a subset of this for now, namely the additions of uuid_extract_timestamp() and uuid_extract_version(). These seemed mature and agreed upon. You can rebase the rest of your patch on top of that.

Great! Thank you! PFA v23 with rebase on HEAD.

> I have started a separate discussion to learn about the precision we can expect from gettimeofday().

Even in presence of real microsecond-enabled and portable timer using microseconds does not seem to me an optimal way of utilising UUID bits.

Timer-based bits contribute to global sortability. But the real timers we have are not even millisecond adjusted. We can hope for ~few ms variation in one datacenter or in presence of atomic clocks.

Time-based bits contribute to global uniqueness, but certainly they are not that effective as counter bits.

Time-based bits do not provide local sortability guarantees: some UUIDs might get same microseconds or be affected by leap backwards.

I think that microseconds are good only for hardware-specific solutions, not for something that runs on variety of platforms, OSes, devices.

Best regards, Andrey Borodin.

Attachment Content-Type Size
v23-0001-Implement-UUID-v7.patch application/octet-stream 17.4 KB

In response to

  • Re: UUID v7 at 2024-03-19 08:55:51 from Peter Eisentraut


  • Re: UUID v7 at 2024-03-21 15:21:15 from Jelte Fennema-Nio
  • Re: UUID v7 at 2024-03-22 14:15:05 from Peter Eisentraut

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2024-03-20 18:13:05 Re: BitmapHeapScan streaming read user and prelim refactoring
Previous Message Pavel Stehule 2024-03-20 17:58:08 Re: Schema variables - new implementation for Postgres 15