On 11st of December 2024, Masahiko Sawada committed patch:
Add UUID version 7 generation function. This commit introduces the uuidv7() SQL function, which generates UUID version 7 as specified in RFC 9652. UUIDv7 combines a Unix timestamp in milliseconds and random bits, offering both uniqueness and sortability. In our implementation, the 12-bit sub-millisecond timestamp fraction is stored immediately after the timestamp, in the space referred to as "rand_a" in the RFC. This ensures additional monotonicity within a millisecond. The rand_a bits also function as a counter. We select a sub-millisecond timestamp so that it monotonically increases for generated UUIDs within the same backend, even when the system clock goes backward or when generating UUIDs at very high frequency. Therefore, the monotonicity of generated UUIDs is ensured within the same backend. This commit also expands the uuid_extract_timestamp() function to support UUID version 7. Additionally, an alias uuidv4() is added for the existing gen_random_uuid() SQL function to maintain consistency. Bump catalog version. Author: Andrey Borodin Reviewed-by: Sergey Prokhorenko, Przemysław Sztoch, Nikolay Samokhvalov Reviewed-by: Peter Eisentraut, Jelte Fennema-Nio, Aleksander Alekseev Reviewed-by: Masahiko Sawada, Lukas Fittl, Michael Paquier, Japin Li Reviewed-by: Marcos Pegoraro, Junwang Zhao, Stepan Neretin Reviewed-by: Daniel Vérité Discussion: https://postgr.es/m/CAAhFRxitJv%3DyoGnXUgeLB_O%2BM7J2BJAmb5jqAT9gZ3bij3uLDA%40mail.gmail.com
One of my issues with UUIDs is that they aren't sortable. Sure, it might make sense in some cases, but generally, I'd like to be able somehow to tell, given two uuids, which was made earlier, and which later. At least more or less.
Luckily, since 2022, there is actually an UUID format/version that makes that possible. UUID version 7.
First, let's see how that looks. Before Pg 18, we could use gen_random_uuid() function to get an uuid (version 4), which is totally random:
=$ SELECT gen_random_uuid() FROM generate_series(1,10); gen_random_uuid -------------------------------------- ba604c90-b70a-4e34-990c-19f6279a7078 a9196baa-2326-4fa2-9e57-5232ae1fee04 ee7076f8-0177-4e36-a7eb-ae7d56fef3e2 1cd2e852-7642-4936-9b45-4a4f821a00ff feadb02a-2746-4e8e-83a1-31d9c3203049 1a72dc31-c95e-4ce7-b848-ca29a9c614ea f57ac276-d22b-4d32-8f38-79614a126a7e f92f98dd-e359-490f-9008-bdf38c86cbdc 39c51f13-9035-43a8-b6aa-c54de897ba08 1c8c0929-65f9-4b9d-abab-63117a028d0b (10 ROWS)
(well, aside from 4 at the beginning of 3rd “word", but this is just version marker).
Now, in pg18, I can generate uuid v7 values, that, as you can see below, clearly have some parts that don't change that much (time dependent):
=$ SELECT uuidv7() FROM generate_series(1,10); uuidv7 ────────────────────────────────────── 01941c04-4185-7ea3-ab00-82c8a75adf41 01941c04-4185-7efe-b171-12949bdf8bd8 01941c04-4185-7f05-89b4-0409545aefc2 01941c04-4185-7f09-9952-b4b515d8b0c8 01941c04-4185-7f0d-a009-252decd3c9ea 01941c04-4185-7f10-89d1-651c6b36fdbb 01941c04-4185-7f14-a77e-edccd6f145bf 01941c04-4185-7f18-b109-c02d263b7457 01941c04-4185-7f1b-8185-24c7c304b7e2 01941c04-4185-7f1f-a950-dec8a092fbe5 (10 ROWS)
With these values you can now also use uuid_extract_timestamp() function to get timestamp of when the uuid was created:
=$ SELECT uuid_extract_timestamp( '01941c04-4185-7efe-b171-12949bdf8bd8'::uuid ); uuid_extract_timestamp ---------------------------- 2024-12-31 10:20:28.549+01 (1 ROW)
Per specification timestamp is with millisecond precision. Which should be good enough for huge majority of usecases.
There is also interesting functionality that allows one to generate uuids with some time shift.
For example, if I'd run uuid_extract_timestamp on newly made uuid v7, I will get more or less the same as now():
=$ SELECT uuid_extract_timestamp(uuidv7()), now(); uuid_extract_timestamp │ now ────────────────────────────┼─────────────────────────────── 2024-12-31 10:27:41.122+01 │ 2024-12-31 10:27:41.122049+01 (1 ROW)
But, I can also do this:
=$ SELECT uuid_extract_timestamp( uuidv7( '-21 years' ) ); uuid_extract_timestamp ──────────────────────────── 2003-12-31 10:28:11.544+01 (1 ROW)
Definitely looks useful/helpful. Thanks to all involved.
This one is an exciting feature:
support for LIKE with nondeterministic collations added (commit 85b7efa1)
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=85b7efa1cdd63c2fe2b70b725b8285743ee5787f
@James:
thanks, added to queue. The holiday period makes it hard to find time to read mail backlog 🙂