UUIDv7 timestamp value offset

UUIDv7 is a convenient and secure 128-bit unique identifier that is designed to replace bigint integer surrogate keys as the primary key in high-load databases and distributed systems.

UUIDv7 contains timestampwhich provides quick insertion of new records into the database table, indexed by this identifier. Thanks to timestamping, database performance with UUIDv7 identifiers sameas with bigint identifiers. In addition, the timestamp can be used as a partition key when sectioning (partitioning) at intervals, and can also help in identifying the causes of errors.

However, the timestamp reveals the date and time the record was created, which may violate information security requirements. Fortunately, RFC 9562 allows time stamp offset (timestamp offset), which allows you to distort the true date and time the record was created. In addition, when using a timestamp offset, UUIDv7 values ​​can be distributed fairly evenly, which allows them to be used as a distribution key when segmentation (sharding).

Developers of functions for generating UUIDv7 are already designing using the parameter to set the time stamp offset. Specifically, in PostgreSQL the offset will have type interval. But there is a need to automatically calculate the time stamp offset.

From an information security point of view, it is desirable that the time stamp shift be random (positive or negative), fairly frequent (at least once an hour) and occur at random times. And for high database performance, it is important that the timestamp shift is not too frequent (so that the data pages in memory are as full as possible) – no more than once per minute.

The permissible interval of the time stamp offset is limited only by the fact that the value of the time stamp itself must fit within its permissible interval. Otherwise, no offset should be applied.

When locking conflicts occur, it is advisable to give sequentially created UUIDv7s different sequential offsets from a small looped list of offsets.