The sequence thing is definitely interesting, I stopped using them a while ago, using mostly sequential uuid (or uuid v7), or use something like HiLo
https://en.wikipedia.org/wiki/Hi/Lo_algorithm
-- IETF Draft Spec: https://www.ietf.org/archive/id/draft-peabody-dispatch-new-uuid-format-01.html
CREATE SEQUENCE uuidv7_seq MAXVALUE 4095; -- A 12-bit sequence
CREATE OR REPLACE FUNCTION generate_uuidv7()
RETURNS uuid AS $$
DECLARE
unixts bigint;
msec bigint;
seq bigint;
rand bigint;
uuid_hex varchar;
BEGIN
-- Get current UNIX epoch in milliseconds
unixts := (EXTRACT(EPOCH FROM clock_timestamp()) * 1000)::bigint;
-- Extract milliseconds
msec := unixts % 1000; -- Milliseconds
-- Get next value from the sequence for the "motonic clock sequence counter" value
seq := NEXTVAL('uuidv7_seq');
-- Generate a random 62-bit number
rand := (RANDOM() * 4611686018427387903)::bigint; -- 62-bit random number
-- Construct the UUID
uuid_hex := LPAD(TO_HEX(((unixts << 28) + (msec << 16) + (7 << 12) + seq)), 16, '0') ||
LPAD(TO_HEX((2 << 62) + rand), 16, '0');
-- Return the UUID
RETURN uuid_hex::uuid;
END;
$$ LANGUAGE plpgsql VOLATILE;
SELECT generate_uuidv7();I'm not sure how difficult it would be to tell postgres to only generate the identifier if it's not part of the inserted data already... but that might be a nice compromise