On Sat, Jan 28, 2023 at 6:02 PM Ron <ronljohnsonjr@xxxxxxxxx> wrote:
>
> Type 4 UUIDs are sub-optimal for big table because cache hit rates drop through the floor.
>
> This lesson was burned into my psyche waaaay back in the Clinton administration. It was my task to speed up a five hour batch job which read input records from a flat file, did some validations and then inserted them. Simply sorting the input file on the primary key fields -- we used natural keys, not synthetics -- dropped the run time to two hours. (VMS SORT saved the day, because you could tell it the sort order you wanted; thus, I could preserve the header record at the top of the file, and the trailer record at the end of the file without jumping through a bunch of hoops.)
This can be mitigated with judicious use of a sequence at the front of
the uuidv4.
https://www.2ndquadrant.com/en/blog/sequential-uuid-generators/
More effort than just calling the built-in gen_random_uuid() or
equivalent in app code, but a substantial performance gain for your
effort.
https://github.com/tvondra/sequential-uuids
And in a managed environment where you can't install custom
extensions, a fairly simple function with divide on unix epoch seconds
combined with a call to overlay(...) should suffice performance-wise.
At 60 seconds, this will loop every 45 days or so, and you can choose
how much "sequentialness" works for you, from 1 to 4 bytes at the
expense of pseudo-randomness.
-----------------------------
-- Generate time interval UUID
CREATE OR REPLACE FUNCTION gen_interval_uuid(interval_seconds int4 =
60, block_num_bytes int2 = 2)
RETURNS uuid LANGUAGE sql VOLATILE PARALLEL SAFE AS $$
SELECT encode(
overlay(
-- convert the uuid to byte array
uuid_send(gen_random_uuid())
-- use only the bottom bytes
PLACING substring(
int4send((extract(epoch FROM now()) / interval_seconds)::int4)
FROM (5 - block_num_bytes)
)
-- place at the front two bytes of the uuid
FROM 1
)
-- convert the resulting byte array to hex for conversion to uuid
, 'hex')::uuid
WHERE interval_seconds > 0 AND block_num_bytes BETWEEN 1 AND 4
$$;
-----------------------------
Technically affecting the v4 spec. You could always convert to a
UUIDv8, which is the intention behind that new version even though the
standard hasn't been ratified yet.
Cheers,
Miles Elam
