Re: UUID v7

Dear Colleagues,

Although the uuidv7(timestamp) function clearly contradicts RFC 9562, but the uuidv7(timestamp_offset) function is fully compliant with RFC 9562 and is absolutely necessary.

"Altering, Fuzzing, or Smearing:

Implementations MAY alter the actual timestamp. Some examples include security considerations around providing a real-clock value within a UUID to 1) correct inaccurate clocks, 2) handle leap seconds, or 3) obtain a millisecond value by dividing by 1024 (or some other value) for performance reasons (instead of dividing a number of microseconds by 1000). This specification makes no requirement or guarantee about how close the clock value needs to be to the actual time. "

It’s written clumsily, of course, but the intention of the authors of RFC 9562 is completely clear: the current timestamp can be changed by any amount and for any reason, including security or performance reasons. The wording provides only a few examples, the list of which is certainly not exhaustive.

The motives of the authors of RFC 9562 are also clear. The timestamp is needed only to generate monotonically increasing UUIDv7. The timestamp should not be used as a source of data about the time the record was created (this is explicitly stated in section 6.12. Opacity). Therefore, the actual timestamp can and should be changed if necessary.

Why then does RFC 9562 contain wording about the need to use "Unix Epoch timestamp"? First, the authors of RFC 9562 wanted to get away from using the Gregorian calendar, which required a timestamp that was too long. Second, the RFC 9562 prohibits inserting into UUIDv7 a completely arbitrary date and time value that does not increase with the passage of real time. And this is correct, since in this case the generated UUIDv7 would not be monotonically increasing. Thirdly, on almost all computing platforms there is a convenient source of "Unix Epoch timestamp".

Why does the uuidv7() function need the optional formal parameter timestamp_offset? This question is best answered by a quote from https://lu.sagebl.eu/notes/maybe-we-dont-need-uuidv7 :

"Leaking information

UUIDv4 does not leak information assuming a proper implementation. But, UUIDv7 in fact does: the timestamp of the server is embeded into the ID. From a business point of view it discloses information about resource creation time. It may not be a problem depending on the context. Current RFC draft allows implementation to tweak timestamps a little to enforce a strict increasing order between two generations and to alleviate some security concerns."

There is a lot of hate on the internet about "UUIDv7 should not be used because it discloses the date and time the record was created." If there was a ban on changing the actual timestamp, this would prevent the use of UUIDv7 in mission-critical databases, and would generally lead to a decrease in the popularity of UUIDv7.

The implementation details of timestamp_offset are, of course, up to the developer. But I would suggest two features:

1. If the result of applying timestamp_offset the timestamp goes beyond the permissible interval, the timestamp_offset value must be reset to zero

I really hope that timestamp_offset will be used in the uuidv7() function for PostgreSQL.