Re: UUID v7

From: Andrey Borodin <x4mmm(at)yandex-team(dot)ru>
To: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
Cc: Daniel Verite <daniel(at)manitou-mail(dot)org>, Peter Eisentraut <peter(at)eisentraut(dot)org>, Jelte Fennema-Nio <postgres(at)jeltef(dot)nl>, Sergey Prokhorenko <sergeyprokhorenko(at)yahoo(dot)com(dot)au>, Przemysław Sztoch <przemyslaw(at)sztoch(dot)pl>, Michael Paquier <michael(at)paquier(dot)xyz>, Aleksander Alekseev <aleksander(at)timescale(dot)com>, Pgsql-Hackers Mailing List <pgsql-hackers(at)postgresql(dot)org>, "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>, Stepan Neretin <sncfmgg(at)gmail(dot)com>
Subject: Re: UUID v7
Date: 2025-01-31 07:08:45
Message-ID: 2168BA61-2294-4812-BBD7-342928B7BD63@yandex-team.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

> On 31 Jan 2025, at 00:54, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> I like this idea. Would you like to write a patch, or shall I?

I propose to separate milliseconds from nanoseconds. Please find attached implementation of this.
With this patch we can generate correct UUIDs in a very distant future.
postgres=# select x, uuid_extract_timestamp(uuidv7((x::text || ' year'::text)::interval)),
(x::text || ' year'::text)::interval
from generate_series(1,9000,1000) x;
x | uuid_extract_timestamp | interval
------+-----------------------------+------------
1 | 2026-01-31 12:00:53.084+05 | 1 year
1001 | 3026-01-31 12:00:53.084+05 | 1001 years
2001 | 4026-01-31 12:00:53.084+05 | 2001 years
3001 | 5026-01-31 12:00:53.084+05 | 3001 years
4001 | 6026-01-31 12:00:53.084+05 | 4001 years
5001 | 7026-01-31 12:00:53.085+05 | 5001 years
6001 | 8026-01-31 12:00:53.085+05 | 6001 years
7001 | 9026-01-31 12:00:53.085+05 | 7001 years
8001 | 10026-01-31 12:00:53.085+05 | 8001 years
(9 rows)

Best regards, Andrey Borodin.

Attachment Content-Type Size
0001-UUDv7-fix-offset-computations-in-dates-after-2262.patch application/octet-stream 3.6 KB

In response to

  • Re: UUID v7 at 2025-01-30 19:54:59 from Masahiko Sawada

Browse pgsql-hackers by date

  From Date Subject
Previous Message Amit Kapila 2025-01-31 06:39:30 Re: Conflict detection for update_deleted in logical replication