Re: Sequence vs UUID

Then it's not a Type 4 UUID, which is perfectly fine; just not random.

Also, should now() be replaced by clock_timestamp(), so that it can be 
called multiple times in the same transaction?

On 1/28/23 21:28, Miles Elam wrote:
> On Sat, Jan 28, 2023 at 6:02 PM Ron <ronljohnsonjr@gmail.com> 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
hourbatch job which read input records from a flat file, did some validations and then inserted them.  Simply sorting
theinput file on the primary key fields -- we used natural keys, not synthetics -- dropped the run time to two hours.
(VMSSORT saved the day, because you could tell it the sort order you wanted; thus, I could preserve the header record
atthe 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
>
>

-- 
Born in Arizona, moved to Babylonia.