UUIDv7 in PostgreSQL 18 (Still in dev phase as time of this post)

 

Check these uuid functions are available

mytest=# select version();
                                                       version
---------------------------------------------------------------------------------------------------------------------
 PostgreSQL 18devel on x86_64-apple-darwin24.2.0, compiled by Apple clang version 16.0.0 (clang-1600.0.26.6), 64-bit
(1 row)

mytest=# select uuidv4(),uuidv7() from generate_series(1,5);;
                uuidv4                |                uuidv7
--------------------------------------+--------------------------------------
 6cd7e3c8-06f3-4459-9e43-02d83983e337 | 0194a08d-88d0-778a-ba8b-66c65ea88774
 cd9e75fe-6864-4af4-94ef-3a0329246c2e | 0194a08d-88d0-79f4-b239-2d755157f618
 d50cbff5-cfae-4df5-b045-389e37adb978 | 0194a08d-88d0-7bc1-a09d-0461661379f7
 eeb39221-9c79-4f9b-99c5-b451dc38b644 | 0194a08d-88d0-7d97-b11c-c59d1dbdd9fe
 8cfaf63b-14f1-48a1-86bd-feffb61e1b2f | 0194a08d-88d1-71d0-9286-342eca29aa6c
(5 rows)

Create table

mytest=# create table t_uuidv4 (id uuid primary key, c text);
CREATE TABLE

mytest=# create table t_uuidv7 (id uuid primary key, c text);
CREATE TABLE

Populate data for uuidv4 and uuidv7 tables

insert into t_uuidv4
select uuidv4(), rpad('x',100,'x') from generate_series(1,1000000);

mytest=# insert into t_uuidv4
mytest-# select uuidv4(), rpad('x',100,'x') from generate_series(1,1000000);
INSERT 0 1000000
Time: 35709.216 ms (00:35.709)

insert into t_uuidv7
select uuidv7(), rpad('x',100,'x') from generate_series(1,1000000);

-- The time difference of 2 seconds is test margin of error,
--  dones't mean uuidv7 is faster based on this test.
mytest=# insert into t_uuidv7
select uuidv7(), rpad('x',100,'x') from generate_series(1,1000000);
INSERT 0 1000000
Time: 33109.727 ms (00:33.110)

Check the storage size

mytest=# \dt+
                                     List of relations
 Schema |   Name   | Type  |  Owner   | Persistence | Access method |  Size  | Description
--------+----------+-------+----------+-------------+---------------+--------+-------------
 public | t_uuidv4 | table | donghual | permanent   | heap          | 142 MB |
 public | t_uuidv7 | table | donghual | permanent   | heap          | 142 MB |
(2 rows)

mytest=# \di+
                                            List of relations
 Schema |     Name      | Type  |  Owner   |  Table   | Persistence | Access method | Size  | Description
--------+---------------+-------+----------+----------+-------------+---------------+-------+-------------
 public | t_uuidv4_pkey | index | donghual | t_uuidv4 | permanent   | btree         | 38 MB |
 public | t_uuidv7_pkey | index | donghual | t_uuidv7 | permanent   | btree         | 30 MB |
(2 rows)

t_uuidv7_pkey is 21% smaller than t_uuidv4_pkey, further more, it would have smaller memory footprint if working set is hot data based on time.