So wanted to know from experts here, is there really exists any scenario in which UUID really cant be avoided?
Funny you are asking about this. My recent experience is that UUIDs really get crushed on performance in medium (> 5 million rows) tables.
I found an article by Dave Allie on ULID, and I modified his implementation to create a timestamp(6) (microsecond level) sequenced version.
Doing an article on this soon. But WITHOUT calling the "gen_random_bytes" I can generate 2 timestamps at the same microsecond level.
Once that call is included in the function, I've never been close to returning 2 timestamps at the same microsecond level. Although I did not
run this on multiple threads. This fit our needs for an efficient UUID formatted key...
9 Bytes (18 Hex Digits) of Randomness at the far right.
Oh, and some time after the year 10,000 you will get some wrap around... But I expect 256 bit UUIDs will take over before then.
CREATE FUNCTION generate_ulid() RETURNS uuid LANGUAGE sql RETURN ((lpad(to_hex((floor((EXTRACT(epoch FROM clock_timestamp()) * (1000000)::numeric)))::bigint), 14, '0'::text)