Subject: Re: UUID v7

From: Sergey Prokhorenko <[email-masked]>
Date: Sun, 2 Feb 2025 19:40:17 +0000 (UTC)

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

[email-masked]

---

From: Masahiko Sawada <[email-masked]>
Date: Tue, 4 Feb 2025 14:06:22 -0800

On Sun, Feb 2, 2025 at 11:41 AM Sergey Prokhorenko
<[email-masked]> wrote:
>
> Dear colleagues,
>
> I would like to present for discussion my attached new draft documentation on UUID functions (Section 9.14. UUID Functions), which replaces the previously proposed draft at https://www.postgresql.org/docs/devel/functions-uuid.html. I have preserved and significantly supplemented the text that was there.
>
> I have the following goals:
>
> 1. State that from now on, the function uuidv7(), rather than autoincrement, is the default choice for generating primary keys
>
> 2. Describe the advantages of uuidv7() over autoincrement and uuidv4()
>
> 3. Refute the often-cited imaginary disadvantages of UUIDv7 compared to autoincrement, such as:
>
>    - Lower performance (see the refutation in the article "UUID Benchmark War" https://ardentperf.com/2024/02/03/uuid-benchmark-war/)
>
>    - Disclosure of date and time of record creation in the table (in reality, the timestamp offset parameter distorts this information)
>
> 4. Confirm the fault tolerance of the uuidv7() function in all possible critical situations, namely:
>
>    - System clock failure
>
>    - Receiving an invalid value of the offset argument, which would result in a timestamp overflow or a negative timestamp
>

Thank you for the proposal. Could you share the proposed document as a
.diff or .patch file? That would be easier to review the updates.

Regards,

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

---

From: Sergey Prokhorenko <[email-masked]>
Date: Sat, 8 Feb 2025 21:56:27 +0000 (UTC)

On Wednesday 5 February 2025 at 01:07:02 am GMT+3, Masahiko Sawada <[email-masked]> wrote: 

Regards,

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
_________________________________________________________

Dear colleagues,

I have attached a file changed.diff containing my proposed changes to the documentation on UUID functions (Section 9.14. UUID Functions). The text of the changes in this file is updated, and it is slightly different from the previously submitted text.

Regards,
Sergey Prokhorenko

---

From: Andrew Alsup <[email-masked]>
Date: Sun, 16 Feb 2025 20:21:17 -0500

Sergey,

I took a look at your patch for chapter 9.14 "UUID Functions" docs page.
You've added some really good content here. I think section 9.14.4.
"Deciding Whether and Which UUID to Use" would be better suited for Chapter
8: "Data Types" -- specifically, 8.12. "UUID Type", since the content seems
to deal more with the UUID data type than the UUID functions.

Best regards,
Andy A.

On Sun, Feb 16, 2025 at 8:13 PM Sergey Prokhorenko <
[email-masked]> wrote:

> > I would like to present for discussion my attached new draft
> documentation on UUID functions (Section 9.14. UUID Functions), which
> replaces the previously proposed draft at
> https://www.postgresql.org/docs/devel/functions-uuid.html. I have
> preserved and significantly supplemented the text that was there.
> >
> > 1. State that from now on, the function uuidv7(), rather than
> autoincrement, is the default choice for generating primary keys
> >
> >    - Lower performance (see the refutation in the article "UUID
> Benchmark War" https://ardentperf.com/2024/02/03/uuid-benchmark-war/)
> >
> >    - Disclosure of date and time of record creation in the table (in
> reality, the timestamp offset parameter distorts this information)
> >
> >    - Receiving an invalid value of the offset argument, which would
> result in a timestamp overflow or a negative timestamp
> >
>
> I have attached a file changed.diff containing my proposed changes to the
> documentation on UUID functions (Section 9.14. UUID Functions). The text
> of the changes in this file is updated, and it is slightly different from
> the previously submitted text.
>
>
> Regards,
>
> Sergey Prokhorenko

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 16 Feb 2025 19:14:11 -0700

> to deal more with the UUID data type than the UUID functions.

Any chance we can bring some organization to this work?  The subject line
is too vague to be helpful and thus the thread itself seems to be torn
between fixing stuff in UUID v7 and, separately, making a documentation
enhancement.  I don't see a commitfest entry yet - so how about just
starting two new, well-named, threads, with a summary and current patch
proposal for each topic?  Though I do sense some overlap such that some of
the content probably needs to be written up assuming the fix patch goes in
first, then the documentation patch.  We can always tweak that should the
documentation patch take the lead.

I haven't followed the technical subthread but was asked to comment on the
documentation work off-list (spending enough time editing the DOCX original
and imposing what seems to be the current proposed structure to at least
warrant a mention).  A decent part of my commentary was basically that a
lot of this material seems outside the scope of what we cover in our
documentation generally.  I'd probably be ok with moving it to an appendix
and making sure the relevant places have links to there.  More than just
UUID needs to be altered if you begin comparing UUID to bigint - you need
to add some content to bigint too.

Commenting specifically on the 4 goals:

We don't make these judgements in the documentation, typically.  Happy to
be pointed to exceptions.  Simple enough to avoid saying:

"The uuidv7 function is designed as the preferred method for generating
primary keys" and instead just say "...it can be used as an identifier
generator and see Appendix Z for how it compares to other methods."

This is fine; but probably appendix material.

3. Refute the often-cited imaginary disadvantages of UUIDv7 compared to
autoincrement,

This also seems strictly out-of-place; better suited to a Wiki page than
the documentation.  Though making statements of fact, and possibly the
occasional clarification of a non-fact, would likely fit inline.  Or as
part of the Appendix created to hold the table in goal #2.

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

This fits into the user-facing specification of the generator function in
some manner.

I did have some alternative text for all this which I'll share, ideally
when there is a clear place to put it up for discussion.

David J.

---

From: Sergey Prokhorenko <[email-masked]>
Date: Mon, 17 Feb 2025 19:56:58 +0000 (UTC)

Sergey Prokhorenko [email-masked] 

    On Monday 17 February 2025 at 04:21:31 am GMT+3, Andrew Alsup <[email-masked]> wrote:  
 
 Sergey,
I took a look at your patch for chapter 9.14 "UUID Functions" docs page. You've added some really good content here. I think section 9.14.4. "Deciding Whether and Which UUID to Use" would be better suited for Chapter 8: "Data Types" -- specifically, 8.12. "UUID Type", since the content seems to deal more with the UUID data type than the UUID functions.
Best regards,Andy A.

________________________________________________
Andrew,
I don't think so. The documentation text specifically concerns identifiers generated by these functions, and not UUID version 4 or 7 in general, or the UUID type itself.

Regards,
Sergey Prokhorenko

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 17 Feb 2025 13:03:30 -0700

On Mon, Feb 17, 2025 at 12:57 PM Sergey Prokhorenko <
[email-masked]> wrote:

> I don't think so. The documentation text specifically concerns identifiers
> generated by these functions, and not UUID version 4 or 7 in general, or
> the UUID type itself.
>
>
That likely just means we need to talk about the various UUID versions
within the data type section.  This is a property of the stored data
independent of how those values are generated.  It isn't like this behaves
differently if the user computes it in the application and passes it in
compared to using the built-in function.

David J.

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 18 Feb 2025 00:03:26 -0700

On Monday, February 17, 2025, Sergey Prokhorenko <
[email-masked]> wrote:

> This means exactly the opposite of what you wrote. There is a big
> difference between UUID versions and data types. The properties of
> identifiers strongly depend on the implementation of generating function. Don't
> give advice without studying the subject area.
>

I learn by doing and will make mistakes.  In this case being imprecise and
not making my point with concrete examples.

But can we please stop this thread-jacking and give these two patches
proper homes?  This thread should have died on the 12th with the “both
patches pushed” message, IMHO.

David J.