Special UUIDv7 function for microservices

[sergeyprokhorenko]

Although fault tolerance requires that each microservice writes to its own database tables, in practice this requirement is often violated.

The implementation of UUIDv7 for PostgreSQL had to switch from Method 1 to Method 3 (Increased Clock Precision with 12 bits sub-millisecond timestamp fraction) to synchronize the UUIDv7s generated by different microservices for the same database table. This turned out to be simpler than the autoincrement-like analogue. See the C implementation v27-0001-Implement-UUID-v7.patch of Method 3 at the page as a reference. The entire timestamp acts as a counter in rare case when more than about 4 identifiers per microsecond are generated.

This implementation also added the ability to offset the timestamp by a specified interval to hide the record creation time for information security. If offset would cause the timestamp to be outside the allowed range, it should not be applied.

It would be nice to add such a special UUIDv7 function for microservices.

[fabiolimace]

Hi @sergeyprokhorenko!

I am aware of UUIDv7 for PostgreSQL. I believe it will be a reference for other implementations when it is released.

However, we have to take into account the time resolution available at runtime. Java 8 is limited to millisecond resolution, while this is not an issue with newer versions of Java. So a way to detect the time resolution at runtime is needed.

For now, I think it is better to wait for the release of UUIDv7 for PostgreSQL. It is unlikely that it will be changed, but it's always good to be patient. So I will wait a little longer.

Best regards,

[mkurz]

For now, I think it is better to wait for the release of UUIDv7 for PostgreSQL. It is unlikely that it will be changed, but it's always good to be patient. So I will wait a little longer.

UUIDv7 was committed two month after your conversation here started, in December, and will be Part of PostgreSQL 18:

• https://www.depesz.com/2024/12/31/waiting-for-postgresql-18-add-uuid-version-7-generation-function/
• https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=78c5e141e9c139fc2ff36a220334e4aa25e1b0eb

Move on?

[sergeyprokhorenko]

https://github.com/uuid-rs/uuid/issues/767#issuecomment-2646167569

[fabiolimace]

Hi @sergeyprokhorenko !

Version 6.1.0 is now available! 🎉

About the new release

This release brings enhanced precision to UUIDv7 generation. UUIDs generated with this version will now include microsecond timestamps within the rand_a field, provided your runtime offers sufficient clock resolution.

We've also added, since v5.3.3, the ability to shift the UUIDv7 timestamp by a specified interval. To generate a UUIDv7 with a future timestamp do this:

    // Shift the generation instant 365 ahead of current time
    Instant instant = Instant.now().plus(Duration.ofDays(365));
    UUID uuid = UuidCreator.getTimeOrderedEpoch(instant);

Thank you for your suggestion!

About the uuidv7(interval) function

I've been reviewing the last patch for the new uuidv7() function, and I have a suggestion regarding the INTERVAL parameter. While the current implementation is certainly useful, I believe using a TIMESTAMP parameter would offer greater flexibility and cover a wider range of use cases.

In PostgreSQL 18, the function would be called like this:

select uuidv7(INTERVAL '1 day'); 

This allows for generating UUIDs with a timestamp shifted by a specific interval. However, if the function accepted a TIMESTAMP parameter instead, it would enable two key use cases:

1. Generating UUIDs with a specific timestamp:

select uuidv7('2022-02-22T22:22:22.222Z');

2. Generating UUIDs with a shifted timestamp:

select uuidv7(CURRENT_TIMESTAMP + INTERVAL '1 day');

This approach effectively "kills two birds with one stone" (figuratively speaking, of course!) by providing both specific and shifted timestamp capabilities within a single function. It is also more intuitive in my point of view.

From my experience, choosing function parameters carefully is crucial for long-term usability. I believe this small change could significantly enhance the versatility of the uuidv7() function in PostgreSQL.

I would also apreciate if you could take my suggestion to the patch developers.

Best regards!

[mkurz]

@fabiolimace the git tag is missing, I guess you forgot to push it?

[fabiolimace]

@mkurz I forgot. Thanks!

[sergeyprokhorenko]

1. Generating UUIDs with a specific timestamp:

Hi @fabiolimace, The TIMESTAMP parameter violates RFC 9562. And it was intentionally excluded from the implementation of the UUIDv7 generation function in PostgreSQL: https://www.postgresql.org/message-id/1012137874.340418.1721776188406%40mail.yahoo.com The reason is that this parameter can break the monotonous increase of the generated identifiers. This can not only negatively affect performance, but also increase the likelihood of collisions of the generated identifiers. Of course, the TIMESTAMP parameter can be emulated using the parameter you called INTERVAL (it is preferable to use the term OFFSET): OFFSET = TIMESTAMP - CURRENT_TIMESTAMP But there is hope that the developers will not think of such an abuse.

[fabiolimace]

@sergeyprokhorenko

I disagree with some points, especially those that reveal intentions that cannot be inferred from the text alone. I was there too, but I don't remember those intentions.

However, I also don't have a strong argument for a function that accepts a timestamp instead of an interval. I simply suggested what I thought was an improvement to a feature that leverages a permission in the document for security purposes.

Thanks for clarifying your point of view.