UUID Insert Tests in PostgreSQL

uuid_insert_tests.sql

-- READ (in Portuguese): 
-- http://www.savepoint.blog.br/2018/02/17/chaves-artificiais-no-postgresql-desempenho/ 
-- https://gist.github.com/rponte/bf362945a1af948aa04b587f8ff332f8

----------------------------------------------------------------------------
-- PRE-TEST: create tables
----------------------------------------------------------------------------

-- Install UUID-OSSP extension
create extension if not exists "uuid-ossp";

-- Create tables for sequences
create table public.seq_btree (id integer generated by default as identity);
create table public.seq_hash (id integer generated by default as identity);
create index on public.seq_btree using btree (id);
create index on public.seq_hash using hash (id);

-- Create tables for UUID V1 (time-based)
create table public.uuid1_btree (id uuid);
create table public.uuid1_hash (id uuid);
create index on public.uuid1_btree using btree (id);
create index on public.uuid1_hash using hash (id);

-- Create tabels for UUID V4 (random-based)
create table public.uuid4_btree (id uuid);
create table public.uuid4_hash (id uuid);
create index on public.uuid4_btree using btree (id);
create index on public.uuid4_hash using hash (id);

-- Create tabels for UUID V7 (epoch-based)
create table public.uuid7_btree (id uuid);
create table public.uuid7_hash (id uuid);
create index on public.uuid7_btree using btree (id);
create index on public.uuid7_hash using hash (id);

----------------------------------------------------------------------------
-- TEST 1: UUIDs with values generated by UUID_OSSP functions
----------------------------------------------------------------------------

-- Insert in the tables for sequences
explain analyse insert into seq_btree select nextval('seq_btree_id_seq') from generate_series(1::integer, 10000000::integer); -- Execution Time: 28029.854 ms
explain analyse insert into seq_hash select nextval('seq_hash_id_seq') from generate_series(1::integer, 10000000::integer); -- Execution Time: 140375.071 ms

-- Insert in the tables for UUID V1 (time-based)
explain analyse insert into uuid1_btree select uuid_generate_v1() from generate_series(1::integer, 10000000::integer); -- Execution Time: 22991.672 ms
explain analyse insert into uuid1_hash select uuid_generate_v1() from generate_series(1::integer, 10000000::integer); -- Execution Time: 126236.485 ms

-- Insert in the tables for UUID V4 (random-based)
explain analyse insert into uuid4_btree select uuid_generate_v4() from generate_series(1::integer, 10000000::integer); -- Execution Time: 211369.757 ms
explain analyse insert into uuid4_hash select uuid_generate_v4() from generate_series(1::integer, 10000000::integer); -- Execution Time: 138802.738 ms

-- Insert in the tables for UUID V7 (epoch-based)
explain analyse insert into uuid7_btree select uuid_generate_v7() from generate_series(1::integer, 10000000::integer); -- Execution Time: 97811.904 ms
explain analyse insert into uuid7_hash select uuid_generate_v7() from generate_series(1::integer, 10000000::integer); -- Execution Time: 169464.461 ms

-- RESULTS
-- seq_btree       28029.854 ms           28 s 029 ms
-- seq_hash       140375.071 ms    02 min 20 s 375 ms
-- uuid1_btree     22991.672 ms           22 s 991 ms
-- uuid1_hash     126236.485 ms    02 min 06 s 236 ms
-- uuid4_btree    211369.757 ms    03 min 31 s 369 ms
-- uuid4_hash     138802.738 ms    02 min 18 s 802 ms
-- uuid7_btree     97811.904 ms    01 min 37 s 811 ms
-- uuid7_hash     169464.461 ms    02 min 49 s 464 ms

-- NOTES:
--  *  Tests executed on SSD;
--  *  The function implemented for UUIDv7 is not as efficient as the UUID_OSSP functions.

----------------------------------------------------------------------------
-- TEST 2: UUIDs with values generated previously by UUID_OSSP functions
----------------------------------------------------------------------------

-- Create tables for pre-generated values
create table public.seq_pre_generated (id integer generated by default as identity);
create table public.uuid1_pre_generated (id uuid);
create table public.uuid4_pre_generated (id uuid);
create table public.uuid7_pre_generated (id uuid);

-- Insert pre-generated values
insert into seq_pre_generated select nextval('seq_btree_id_seq') from generate_series(1::integer, 10000000::integer);
insert into uuid1_pre_generated select uuid_generate_v1() from generate_series(1::integer, 10000000::integer);
insert into uuid4_pre_generated select uuid_generate_v4() from generate_series(1::integer, 10000000::integer);
insert into uuid7_pre_generated select uuid_generate_v7() from generate_series(1::integer, 10000000::integer);

-- Insert in tables for sequence
truncate table seq_btree;
truncate table seq_hash;
explain analyse insert into seq_btree select id from seq_pre_generated; -- Execution Time: 21516.331 ms
explain analyse insert into seq_hash select id from seq_pre_generated; -- Execution Time: 170375.793 ms

-- Insert in the tables for UUID V1 (time-based)
truncate table uuid1_btree;
truncate table uuid1_hash;
explain analyse insert into uuid1_btree select id from uuid1_pre_generated; -- Execution Time: 38077.444 ms
explain analyse insert into uuid1_hash select id from uuid1_pre_generated; -- Execution Time: 226407.549 ms

-- Insert in the tables for UUID V4 (random-based)
truncate table uuid4_btree;
truncate table uuid4_hash;
explain analyse insert into uuid4_btree select id from uuid4_pre_generated; -- Execution Time: 281123.796 ms
explain analyse insert into uuid4_hash select id from uuid4_pre_generated; -- Execution Time: 235586.245 ms

-- Insert in the tables for UUID V7 (epoch-based)
truncate table uuid7_btree;
truncate table uuid7_hash;
explain analyse insert into uuid7_btree select id from uuid7_pre_generated; -- Execution Time: 33079.732 ms
explain analyse insert into uuid7_hash select id from uuid7_pre_generated; -- Execution Time: 255430.946 ms

-- RESULTS
-- seq_btree       21516.331 ms           21 s 516 ms
-- seq_hash       170375.793 ms    02 min 50 s 375 ms
-- uuid1_btree     38077.444 ms           38 s 077 ms
-- uuid1_hash     226407.549 ms    03 min 46 s 407 ms
-- uuid4_btree    281123.796 ms    04 min 41 s 123 ms
-- uuid4_hash     235586.245 ms    03 min 55 s 586 ms
-- uuid7_btree     33079.732 ms           33 s 079 ms
-- uuid7_hash     255430.946 ms    04 min 15 s 430 ms

------------------------------------
-- Function for generating UUIDv7
-- Source: https://gist.github.com/fabiolimace/515a0440e3e40efeb234e12644a6a346
------------------------------------

create or replace function uuid_generate_v7() returns uuid as $$
declare
	v_time timestamp with time zone:= null;
	v_secs bigint := null;
	v_msec bigint := null;
	v_usec bigint := null;

	v_timestamp bigint := null;
	v_timestamp_hex varchar := null;

	v_random bigint := null;
	v_random_hex varchar := null;

	v_bytes bytea;

	c_variant bit(64):= x'8000000000000000'; -- RFC-4122 variant: b'10xx...'
begin

	-- Get seconds and micros
	v_time := clock_timestamp();
	v_secs := EXTRACT(EPOCH FROM v_time);
	v_msec := mod(EXTRACT(MILLISECONDS FROM v_time)::numeric, 10^3::numeric);
	v_usec := mod(EXTRACT(MICROSECONDS FROM v_time)::numeric, 10^3::numeric);

	-- Generate timestamp hexadecimal (and set version 7)
	v_timestamp := (((v_secs * 10^3) + v_msec)::bigint << 12) | (v_usec << 2);
	v_timestamp_hex := lpad(to_hex(v_timestamp), 16, '0');
	v_timestamp_hex := substr(v_timestamp_hex, 2, 12) || '7' || substr(v_timestamp_hex, 14, 3);

	-- Generate the random hexadecimal (and set variant b'10xx')
	v_random := ((random()::numeric * 2^62::numeric)::bigint::bit(64) | c_variant)::bigint;
	v_random_hex := lpad(to_hex(v_random), 16, '0');

	-- Concat timestemp and random hexadecimal
	v_bytes := decode(v_timestamp_hex || v_random_hex, 'hex');

	return encode(v_bytes, 'hex')::uuid;
	
end $$ language plpgsql;

----------------------------------------------------------------------------------------
-- Python function to format time as '00 min 00 s 000 ms'
----------------------------------------------------------------------------------------
-- def time(ms):
--    return str(int(ms/1000/60)) + ' min ' + str(int(ms/1000%60)) + ' s ' + str(int(ms%1000)) + ' ms'
----------------------------------------------------------------------------------------