Horton

Re: UUID v7

Sergey Prokhorenko <sergeyprokhorenko@yahoo.com.au>

View thread View on postgr.es
From: Sergey Prokhorenko <sergeyprokhorenko@yahoo.com.au>
To: Masahiko Sawada <sawada.mshk@gmail.com>, Andrey Borodin <x4mmm@yandex-team.ru>
Cc: Daniel Verite <daniel@manitou-mail.org>, Peter Eisentraut <peter@eisentraut.org>, Jelte Fennema-Nio <postgres@jeltef.nl>, Przemysław Sztoch <przemyslaw@sztoch.pl>, Michael Paquier <michael@paquier.xyz>, Aleksander Alekseev <aleksander@timescale.com>, Pgsql-Hackers Mailing List <pgsql-hackers@postgresql.org>, "David G. Johnston" <david.g.johnston@gmail.com>, Mat Arye <mat@timescaledb.com>, Matthias van de Meent <boekewurm+postgres@gmail.com>, Nikolay Samokhvalov <samokhvalov@gmail.com>, Junwang Zhao <zhjwpku@gmail.com>, Stepan Neretin <sncfmgg@gmail.com>
Date: 2025-02-02T19:40:17Z
Lists: pgsql-hackers

Attachments

Dearcolleagues,

I wouldlike to present for discussion my attached new draft documentation on UUIDfunctions (Section 9.14. UUID Functions), which replaces the previouslyproposed draft at https://www.postgresql.org/docs/devel/functions-uuid.html.I have preserved and significantly supplemented the text that was there.

I have thefollowing goals:

1. Statethat from now on, the function uuidv7(), rather than autoincrement, is thedefault choice for generating primary keys

2. Describethe advantages of uuidv7() over autoincrement and uuidv4()

3. Refutethe often-cited imaginary disadvantages of UUIDv7 compared to autoincrement,such as:

   - Lower performance (see the refutation inthe article "UUID Benchmark War" https://ardentperf.com/2024/02/03/uuid-benchmark-war/)

   - Disclosure of date and time of recordcreation in the table (in reality, the timestamp offset parameter distorts thisinformation)

4. Confirm thefault tolerance of the uuidv7() function in all possible critical situations,namely:

   - System clock failure

   - Receiving an invalid value of the offsetargument, which would result in a timestamp overflow or a negative timestamp

 

Regards,

SergeyProkhorenko

sergeyprokhorenko@yahoo.com.au

Commits

  1. Fix timestamp overflow in UUIDv7 implementation.

  2. Add UUID version 7 generation function.

  3. Add some UUID support functions