Horton

Re: UUID v7

Jelte Fennema-Nio <postgres@jeltef.nl>

View thread View on postgr.es
From: Jelte Fennema-Nio <postgres@jeltef.nl>
To: Aleksander Alekseev <aleksander@timescale.com>
Cc: pgsql-hackers mailing list <pgsql-hackers@postgresql.org>, "Andrey M. Borodin" <x4mmm@yandex-team.ru>, Przemysław Sztoch <przemyslaw@sztoch.pl>, Nikolay Samokhvalov <nik@postgres.ai>, Sergey Prokhorenko <sergeyprokhorenko@yahoo.com.au>, "David G. Johnston" <david.g.johnston@gmail.com>, Nick Babadzhanian <pgnickb@gmail.com>, Mat Arye <mat@timescaledb.com>, Peter Eisentraut <peter.eisentraut@enterprisedb.com>, Tom Lane <tgl@sss.pgh.pa.us>, Daniel Gustafsson <daniel@yesql.se>, Matthias van de Meent <boekewurm+postgres@gmail.com>, Nikolay Samokhvalov <samokhvalov@gmail.com>, "Kyzer Davis (kydavis)" <kydavis@cisco.com>, Andres Freund <andres@anarazel.de>, "brad@peabody.io" <brad@peabody.io>, Kirk Wolak <wolakk@gmail.com>, Alexander Lakhin <exclusion@gmail.com>
Date: 2024-01-29T12:01:36Z
Lists: pgsql-hackers 
On Thu, 25 Jan 2024 at 13:31, Aleksander Alekseev
<aleksander@timescale.com> wrote:
> PFA v14.

+<function>uuidv4</function> () <returnvalue>uuid</returnvalue>
+</synopsis>
+   Both functions return a version 4 (random) UUID. This is the most commonly
+   used type of UUID and is appropriate when random distribution of keys does
+   not affect performance of an application.
+<synopsis>
+<function>uuidv7</function> () <returnvalue>uuid</returnvalue>
+</synopsis>
+   This function returns a version 7 (time-ordered + random) UUID. This UUID
+   version should be used when application prefers locality of identifiers.
+<synopsis>

I think it would be good to explain the tradeoffs between uuidv4 and
uuidv7 a bit better. How about changing the docs to something like
this:

<function>uuidv4</function> () <returnvalue>uuid</returnvalue>
</synopsis>
Both functions return a version 4 (random) UUID. UUIDv4 is one of the
most commonly used types of UUID. It is appropriate when random
distribution of keys does not affect performance of an application or
when exposing the generation time of a UUID has unacceptable security
or business intelligence implications.
<synopsis>
<function>uuidv7</function> () <returnvalue>uuid</returnvalue>
</synopsis>
This function returns a version 7 (time-ordered + random) UUID. It
provides much better data locality than UUIDv4, which can greatly
improve performance when UUID is used in a BTREE index (the default
index type in PostgreSQL). To achieve this data locality, UUIDv7
embeds its own generation time into the UUID. If exposing such a
timestamp has unacceptable security or business intelligence
implications, then uuidv4() should be used instead.
<synopsis>

Commits

  1. Fix timestamp overflow in UUIDv7 implementation.

  2. Add UUID version 7 generation function.

  3. Add some UUID support functions