explain.md

psuedo order with prefix

The first 2 characters of the UUID value comes from the MD5 hash of a supplied UUID. The next 3 characters are the MD5 hash of the concatenation of the current year and week number -- this value is, of course, static over a week. The remaining of the UUID value comes from the MD5 of a random value and the current time at a precision of 1us. The third field is prefixed with a “4” to indicate it is a version 4 UUID type.

pseudo-order-w-prefix-md5.sql

DROP FUNCTION IF EXISTS f_pseudo_order_with_prefix_uuid();
CREATE FUNCTION f_pseudo_order_with_prefix_uuid ()
    RETURNS uuid
AS $$
DECLARE
    new_uuid char(36);
    md5_str char(32);
BEGIN
    md5_str := md5(concat(random(), now()));
    new_uuid := concat(
        LEFT (md5('c44dceee-ee67-4bfc-35c1-3a3d66a1d66e'), 2),
        LEFT (md5(concat(extract(year FROM now()), extract(week FROM now()))), 3),
        LEFT (md5_str, 3), '-', substring(md5_str, 5, 4), '-4', substring(md5_str, 9, 3), '-', substring(md5_str, 13, 4), '-', substring(md5_str, 17, 12)
    );
    RETURN new_uuid;
END;
$$
LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS f_pseudo_order_with_prefix_uuids();
CREATE FUNCTION f_pseudo_order_with_prefix_uuids ()
    RETURNS SETOF uuid
AS $$
BEGIN
    RETURN QUERY SELECT f_pseudo_order_with_prefix_uuid() FROM generate_series(1,10,1);
END;
$$
LANGUAGE plpgsql;


SELECT
    *
FROM
    f_pseudo_order_with_prefix_uuids ();

psuedo-order-w-prefix-uuid.sql

DROP FUNCTION IF EXISTS f_pseudo_order_with_prefix_uuid() cascade;
CREATE FUNCTION f_pseudo_order_with_prefix_uuid ()
    RETURNS uuid
AS $$
DECLARE
    new_uuid char(36);
    md5_str char(32);
    uid text;
BEGIN
  	uid = uuid_generate_v4();	
    new_uuid := concat(
        LEFT (md5('c44dceee-ee67-4bfc-35c1-3a3d66a1d66e'), 2),
        LEFT (md5(concat(extract(year FROM now()), extract(week FROM now()))), 2),
        substring(uid, 1, 4),
        '-',
		 substring(uid, 10, 4),
		'-',
		 substring(uid, 15, 4),
		'-',
		 substring(uid, 20, 4),
		'-',
		 substring(uid, 25, 12)
    );
    RETURN new_uuid;
END;
$$
LANGUAGE plpgsql;

psuedo-order-with-prefix-md5-more-random.sql

-- was able to generate 1000000 with no dups! and this was 2X faster than using another UUID for randomness. So was better to use 2 md5 calls...
CREATE FUNCTION f_pseudo_order_prefixed_uuid ()
    RETURNS uuid
AS $$
DECLARE
    new_uuid char(36);
    md5_str char(32);
    md5_str2 char(32);
    uid text;
BEGIN
    md5_str := md5(concat(random(), now()));
    md5_str2 := md5(concat(random(), now()));
    
    new_uuid := concat(
        LEFT (md5('c44dceee-ee67-4bfc-35c1-3a3d66a1d66e'), 2),
        LEFT (md5(concat(extract(year FROM now()), extract(week FROM now()))), 2),
        substring(md5_str, 1, 4),
        '-',
        substring(md5_str, 5, 4),
        '-4',
        substring(md5_str2, 9, 3),
        '-',
        substring(md5_str, 13, 4),
        '-', 
        substring(md5_str2, 17, 12)
    );
    RETURN new_uuid;
END;
$$
LANGUAGE plpgsql;

testing

create table insertids ( id uuid not null primary key default f_pseudo_order_with_prefix_uuid() );

After running this a bunch, I did get a duplicate

DROP FUNCTION IF EXISTS f_pseudo_order_with_prefix_uuids2();
CREATE FUNCTION f_pseudo_order_with_prefix_uuids2 ()
    RETURNS void
AS $$
DECLARE
  c int = 0;
BEGIN
WHILE c < 10000
LOOP
  INSERT INTO insertids DEFAULT VALUES;
  c = c + 1;
END LOOP;
END;
$$
LANGUAGE plpgsql;

select f_pseudo_order_with_prefix_uuids2();

Command completed successfully. (Line 5)
Command completed successfully. (Line 19)
ERROR:  duplicate key value violates unique constraint "insertids_pkey"
DETAIL:  Key (id)=(58c35f4b-e58d-4c1c-ca8d-e19870187601) already exists.
CONTEXT:  SQL statement "INSERT INTO insertids DEFAULT VALUES"
PL/pgSQL function f_pseudo_order_with_prefix_uuids2() line 7 at SQL statement. (Line 21)

testing uuid version

drop table if exists insertids;   

DROP FUNCTION IF EXISTS f_pseudo_order_with_prefix_uuid() cascade;
CREATE FUNCTION f_pseudo_order_with_prefix_uuid ()
    RETURNS uuid
AS $$
DECLARE
    new_uuid char(36);
    md5_str char(32);
    uid text;
BEGIN
	uid = uuid_generate_v4();
	
    -- md5_str := md5(concat(random(), now()));
    new_uuid := concat(
        LEFT (md5('c44dceee-ee67-4bfc-35c1-3a3d66a1d66e'), 2),
        LEFT (md5(concat(extract(year FROM now()), extract(week FROM now()))), 2),
        substring(uid, 1, 4),
        --'-', substring(md5_str, 5, 4), '-4', substring(md5_str, 9, 3), '-', substring(md5_str, 13, 4), '-', substring(md5_str, 17, 12)
        '-',
		 substring(uid, 10, 4),
		'-',
		 substring(uid, 15, 4),
		'-',
		 substring(uid, 20, 4),
		'-',
		 substring(uid, 25, 12)
    );
    RETURN new_uuid;
END;
$$
LANGUAGE plpgsql;

create table insertids ( id uuid not null primary key default uuid_generate_v4() );
 
DROP FUNCTION IF EXISTS gen();
CREATE FUNCTION gen ()
    RETURNS void
AS $$
DECLARE
  c int = 0;
BEGIN
WHILE c < 100000
LOOP
  INSERT INTO insertids DEFAULT VALUES;
  c = c + 1;
END LOOP;
END;
$$
LANGUAGE plpgsql;

select gen();

results in zero duplicates

with regular uuid, generating 100,000 rows takes 5.5 seconds.

with these new uuids, generating 100,000 rows takes 6.8 seconds.