Since RFC-9562 was published last year, there has been a growing interest in replacing random-only UUIDv4 keys by UUID-v7, which are made from a creation timestamp plus random numbers. As the sort order of UUID-v7 is inherently chronological, index access tends to be much more efficient than with UUID-v4.

Quoting the RFC:

UUID versions that are not time ordered, such as UUIDv4 (described in Section 5.4), have poor database-index locality. This means that new values created in succession are not close to each other in the index; thus, they require inserts to be performed at random locations. The resulting negative performance effects on the common structures used for this (B-tree and its variants) can be dramatic.

UUID-v7 solves that problem. There is a drawback however, in having these values holding creation dates.

Quoting the RFC again:

Timestamps embedded in the UUID do pose a very small attack surface. The timestamp in conjunction with an embedded counter does signal the order of creation for a given UUID and its corresponding data but does not define anything about the data itself or the application as a whole. If UUIDs are required for use with any security operation within an application context in any shape or form, then UUIDv4 (Section 5.4) SHOULD be utilized.

And even though it “does not define anything about the data itself”, the timestamp may be used to facilite de-anonymization, because knowing the creation date of a record facilitates pairing with data obtained elsewhere.

Ideally we’d like to use UUID-v7 in the database, but without exposing the timestamp when exporting them to untrusted third parties. We can do that by encrypting the timestamp, transforming the UUID-v7 into an equivalent UUID-v4. That UUID-v4 does not need to be stored, because the permutation between UUID-v7 and UUID-v4 can be done anytime knowing the encryption key. On the other hand, a third party without that key has no way to guess the corresponding UUID-v7.

The rest of this post describes an implementation of this transformation. The code is available in the uuidv7-sql extension as two functions written in plpgsql (no compilation or superuser permissions required):

• uuidv7_encrypt(input uuid, crypt_key bytea, [uuid_ver int]) → uuid
• uuidv7_decrypt(input uuid, crypt_key bytea) → uuid

Transforming bit fields

There are probably many encryption schemes that would be suitable for this. I’ve chosen XTEA, which is a Feistel cipher for 64-bit blocks, using a 128-bit key. The C code is simple enough to be ported to plpgsql.

The timestamp in the UUID-v7 is represented by 48 bits (unix_t_ms field), and optionally 12 more bits (rand_a field) for sub-millisecond precision. They must be encrypted. The rest of the UUID consists of:

• ver and var fields that should not be encrypted.
• the rand_b field that does not need encryption. But since XTEA needs 64-bit inputs and 48+12 = 60, we need to add 4 arbitrary bits from rand_b for inclusion in the input.

The complete transformation is shown in the following diagram:

The UUID-v4 is structurally quite similar to UUID-v7. Essentially after encryption:

• the 48 bits from unix_t_ms go encrypted into random_a.
• the 4 ver bits become 0100 (=4) instead of 0111 (=7).
• the 12 bits from rand_a go encrypted into random_b.
• the 2 var bits are left unchanged.
• 4 encrypted bits from rand_b go at the same place into random_c.

After recombining these into a value of uuid datatype, it is not reasonably distinguishable from something produced by a UUID-v4 generator like gen_random_uuid().

Naturally the decryption does the same in reverse, to go back to the initial UUID-v7 value.

Example:

\set u '0199de0c-25fa-79cc-b512-2bc33f293d01'
\set key 'SeCrEt #Key# !!!'

=> select uuidv7_encrypt(:'u', :'key'::bytea);
uuidv7_encrypt
--------------------------------------
f0c10151-22a1-4186-b512-2bc93f293d01
(1 row)

=> select uuidv7_decrypt('f0c10151-22a1-4186-b512-2bc93f293d01', :'key'::bytea);
uuidv7_decrypt
--------------------------------------
0199de0c-25fa-79cc-b512-2bc33f293d01
(1 row)

Transforming into UUID-v8 instead of UUID-v4

In some cases, we might want to encrypt UUID-v7 keys but still be able to distinguish values produced by a UUID-v4 generator from values obtained by encrypting UUID-v7.

For instance if we switch our stored keys from UUID-v4 to UUID-v7 and keep the streams of exported keys to UUID-v4, when a third party refers to an ID from these exports, how can we know whether it’s a native UUID-v4 or an encrypted UUID-v7?

One simple solution is to encrypt into UUID-v8 instead.

The specification says:

UUIDv8 provides a format for experimental or vendor-specific use cases. The only requirement is that the variant and version bits MUST be set as defined in Sections 4.1 and 4.2.

So we can use the convention that our encrypted UUID-v7 become UUID-v8, with exactly the same values at the same place as UUID-v4 except for the ver field. This is why the uuidv7_encrypt() function accepts an additional argument, where 4 or 8 can be passed, setting the UUID version.

Then when considering a UUID handed back to us, if the ver field if set to 8, it will mean that the value should be passed to uuidv7_decrypt() to obtain the original value. Since Postgres 18, the function uuid_extract_version() can be used to query the version field from a UUID.

SQL tips to manipulate bits and bytes

The plpgsql implementation uses a few tricks to handle bits and bytes with concise SQL code, on top of the functions described in the documentation in Bit String Functions and Operators and Binary String Functions and Operators .

Here’s a quick list:

• transform a UUID into a binary string (bytea): uuid_send($1)
• transform a binary string into a vector of bits: right($1::text, -1)::bit(N) with bytea_output=hex
• transform a binary string into an int8: right($1::text, -1)::bit(64)::int8 with bytea_output=hex
• emulate the C unsigned 32-bit addition with int8 SQL operands: ($1 + $2)&4294967295
• assemble two 64 bit numbers (int8) into a UUID: encode(int8send($1) || int8send($2), 'hex')::uuid

References

RFC-9562 Universally Unique IDentifiers (UUIDs)

Implementing UUIDs v7 in pure SQL (a previous post on this blog, July 2024)

uuidv7-sql extension on GitHub

XTEA page on Wikipedia.

About performance: UUID Benchmark War (on Jeremy Schneider’s blog, February 2024)

Exploring PostgreSQL 18’s new UUIDv7 support (blog post by Aiven, September 2025)

Hacker News discussion on the previous blog post (essentially about the timestamp-leaking issue)