Server-local monotonic function and optional formal parameter timestamp_offset

[sergeyprokhorenko]

@ryanwinchester

I would recommend:

1. Implement a function based on a global lock (for the entire server). This is important for a microservice architecture. There is no need for either a thread-local monotonic function or a non-monotonic function without a counter.
2. Add an optional formal parameter timestamp_offset (positive or negative) in milliseconds.

[ryanwinchester]

1. Implement a function based on a global lock (for the entire server). This is important for a microservice architecture. There is no need for either a thread-local monotonic function or a non-monotonic function without a counter.

Not sure what you mean but the way I have done it should be monotonic increasing per VM

2. Add an optional formal parameter timestamp_offset (positive or negative) in milliseconds.

What would this be for?

[sergeyprokhorenko]

1. If multiple microservices write to the same database table at the same time using their own UUIDv7 generators, then intra-millisecond monotonicity is broken in that table. Such functions are called thread-local. But if multiple microservices use the same UUIDv7 generator, then monotonicity is preserved, because the counter is shared between threads. Here is an example of such a function that preserves monotonicity: https://clickhouse.com/docs/en/sql-reference/functions/uuid-functions#generateUUIDv7 and https://github.com/ClickHouse/ClickHouse/pull/62852

Function generateUUIDv7 guarantees that the counter field within a timestamp increments monotonically across all function invocations in concurrently running threads and queries.

2. Optional parameter timestamp_offset is intended to offset the generated timestamps by the appropriate value, so as not to expose the true time of UUIDv7 generation (for example, the date of the patient's visit, etc.). In many discussions on the internet, the exposure of the record creation date is discussed as a very serious disadvantage of UUIDv7 compared to bigint, along with the twice the length (128 vs. 64 bits). The timestamp offsets do not have to be the same throughout the table. The timestamp offset may change from time to time, similar to FHSS.

In addition, the parameter timestamp_offset solves the problem that it is faster to write simultaneously to several places in one table, so as not to create a write queue (it is enough to use several different timestamp offsets).

This is allowed by the RFC 9562:

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.

[ryanwinchester]

Thanks. Will consider timestamp offsets.