Performance validation for UUIDv7 implemented using SQL in PostgreSQL v16

Source code for UUIDv7 for PostgreSQL: https://gist.github.com/fabiolimace/515a0440e3e40efeb234e12644a6a346

create or replace function uuid7() returns uuid as $$
declare
begin
    return uuid7(clock_timestamp());
end $$ language plpgsql;

create or replace function uuid7(p_timestamp timestamp with time zone) returns uuid as $$
declare

    v_time double precision := null;

    v_unix_t bigint := null;
    v_rand_a bigint := null;
    v_rand_b bigint := null;

    v_unix_t_hex varchar := null;
    v_rand_a_hex varchar := null;
    v_rand_b_hex varchar := null;

    c_milli double precision := 10^3;  -- 1 000
    c_micro double precision := 10^6;  -- 1 000 000
    c_scale double precision := 4.096; -- 4.0 * (1024 / 1000)
    
    c_version bigint := x'0000000000007000'::bigint; -- RFC-9562 version: b'0111...'
    c_variant bigint := x'8000000000000000'::bigint; -- RFC-9562 variant: b'10xx...'

begin

    v_time := extract(epoch from p_timestamp);

    v_unix_t := trunc(v_time * c_milli);
    v_rand_a := trunc((v_time * c_micro - v_unix_t * c_milli) * c_scale);
    v_rand_b := trunc(random() * 2^30)::bigint << 32 | trunc(random() * 2^32)::bigint;

    v_unix_t_hex := lpad(to_hex(v_unix_t), 12, '0');
    v_rand_a_hex := lpad(to_hex((v_rand_a | c_version)::bigint), 4, '0');
    v_rand_b_hex := lpad(to_hex((v_rand_b | c_variant)::bigint), 16, '0');

    return (v_unix_t_hex || v_rand_a_hex || v_rand_b_hex)::uuid;
    
end $$ language plpgsql;

Performance Testing

Testing for UUID generation

Setup

do
$$
declare 
u uuid;
begin
    for i in 1..10000000
    loop
        select gen_random_uuid() into u;
--        raise notice '%', u;
    end loop;
end;
$$;

do
$$
declare 
u uuid;
begin
    for i in 1..10000000
    loop
        select uuid7() into u;
--        raise notice '%', u;
    end loop;
end;
$$;

Test

mytest=# do
mytest-# $$
mytest$# declare
mytest$# u uuid;
mytest$# begin
mytest$#     for i in 1..10000000
mytest$#     loop
mytest$#         select gen_random_uuid() into u;
mytest$# --        raise notice '%', u;
mytest$#     end loop;
mytest$# end;
mytest$# $$;
DO
Time: 31743.056 ms (00:31.743)
-- each execution 0.031743056ms

mytest=# do
mytest-# $$
mytest$# declare
mytest$# u uuid;
mytest$# begin
mytest$#     for i in 1..10000000
mytest$#     loop
mytest$#         select uuid7() into u;
mytest$# --        raise notice '%', u;
mytest$#     end loop;
mytest$# end;
mytest$# $$;
DO
Time: 54132.676 ms (00:54.133)
-- each execution 0.054132676ms

Testing for UUID insertion

Setup

create table tbl_uuidv4 
(u uuid primary key, c text);
insert into tbl_uuidv4
select gen_random_uuid(), 'additional data' from generate_series(1,10000000);

create table tbl_uuidv7 
(u uuid primary key, c text);
insert into tbl_uuidv7
select uuid7(), 'additional data' from generate_series(1,10000000);

Test

mytest=# insert into tbl_uuidv4
mytest-# select gen_random_uuid(), 'additional data' from generate_series(1,10000000);
INSERT 0 10000000
Time: 60405.272 ms (01:00.405)

mytest=# insert into tbl_uuidv7
mytest-# select uuid7(), 'additional data' from generate_series(1,10000000);

INSERT 0 10000000
Time: 56902.619 ms (00:56.903)

mytest=# \dt+ tbl_uuid*
                                      List of relations
 Schema |    Name    | Type  |  Owner   | Persistence | Access method |  Size  | Description
--------+------------+-------+----------+-------------+---------------+--------+-------------
 public | tbl_uuidv4 | table | donghua  | permanent   | heap          | 575 MB |
 public | tbl_uuidv7 | table | donghua  | permanent   | heap          | 575 MB |
(2 rows)

mytest=# \di+ tbl_uuid*
                                               List of relations
 Schema |      Name       | Type  |  Owner   |   Table    | Persistence | Access method |  Size  | Description
--------+-----------------+-------+----------+------------+-------------+---------------+--------+-------------
 public | tbl_uuidv4_pkey | index | donghua  | tbl_uuidv4 | permanent   | btree         | 392 MB |
 public | tbl_uuidv7_pkey | index | donghua  | tbl_uuidv7 | permanent   | btree         | 301 MB |
(2 rows)

Testing how many buffers dirtied for UUID insertion

Setup

explain (analyze,buffers)
insert into tbl_uuidv4
select gen_random_uuid(), 'additional data' from generate_series(1,100);

explain (analyze,buffers)
insert into tbl_uuidv7
select uuid7(), 'additional data' from generate_series(1,100);

Test

mytest=# explain (analyze,buffers)
mytest-# insert into tbl_uuidv4
mytest-# select gen_random_uuid(), 'additional data' from generate_series(1,100);
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Insert on tbl_uuidv4  (cost=0.00..2.25 rows=0 width=0) (actual time=40.337..40.338 rows=0 loops=1)
   Buffers: shared hit=231 read=186 dirtied=106 written=1
   ->  Subquery Scan on "*SELECT*"  (cost=0.00..2.25 rows=100 width=48) (actual time=0.038..1.379 rows=100 loops=1)
         ->  Function Scan on generate_series  (cost=0.00..1.25 rows=100 width=48) (actual time=0.037..1.306 rows=100 loops=1)
 Planning Time: 0.048 ms
 Execution Time: 40.365 ms
(6 rows)

Time: 41.754 ms

mytest=# explain (analyze,buffers)
mytest-# insert into tbl_uuidv7
mytest-# select uuid7(), 'additional data' from generate_series(1,100);
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Insert on tbl_uuidv7  (cost=0.00..27.00 rows=0 width=0) (actual time=1.204..1.205 rows=0 loops=1)
   Buffers: shared hit=219 dirtied=1 written=1
   ->  Subquery Scan on "*SELECT*"  (cost=0.00..27.00 rows=100 width=48) (actual time=0.077..0.614 rows=100 loops=1)
         ->  Function Scan on generate_series  (cost=0.00..26.00 rows=100 width=48) (actual time=0.076..0.594 rows=100 loops=1)
 Planning Time: 0.036 ms
 Execution Time: 1.227 ms
(6 rows)

Time: 1.904 ms