From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Sequence vs UUID
Date: Sat, 28 Jan 2023 22:02:36 -0600
Message-ID: <c140b8ee-941b-383a-986d-de1b924a0b65@gmail.com> (raw)
In-Reply-To: <CAALojA98Jf6M1NFmKy5qqOyydU6-eCNE8rY2Yg+W+Qp9P0jqDA@mail.gmail.com>
References: <CAB+=1TXUHPFzhn8iZyVBzJM_YPR4RJAqrd-Ek=V1MK=sUJQrHA@mail.gmail.com>
<214765350.744944.1674770109151@office.mailbox.org>
<9864e7fa-27c7-95ac-b9a7-e1ebc4824740@gmail.com>
<1015127885.422245.1674938371999@office.mailbox.org>
<CAD+mzoy0tPKf5+z8KsiFcXsPLE021fTbS7m-POFX8qv-N+M2_Q@mail.gmail.com>
<b7428272-37a0-aa8b-4b84-ebc888e31b0a@gmail.com>
<CAALojA98Jf6M1NFmKy5qqOyydU6-eCNE8rY2Yg+W+Qp9P0jqDA@mail.gmail.com>
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 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
>
>
--
Born in Arizona, moved to Babylonia.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: ronljohnsonjr@gmail.com
Subject: Re: Sequence vs UUID
In-Reply-To: <c140b8ee-941b-383a-986d-de1b924a0b65@gmail.com>
References: <CAB+=1TXUHPFzhn8iZyVBzJM_YPR4RJAqrd-Ek=V1MK=sUJQrHA@mail.gmail.com> <214765350.744944.1674770109151@office.mailbox.org> <9864e7fa-27c7-95ac-b9a7-e1ebc4824740@gmail.com> <1015127885.422245.1674938371999@office.mailbox.org> <CAD+mzoy0tPKf5+z8KsiFcXsPLE021fTbS7m-POFX8qv-N+M2_Q@mail.gmail.com> <b7428272-37a0-aa8b-4b84-ebc888e31b0a@gmail.com> <CAALojA98Jf6M1NFmKy5qqOyydU6-eCNE8rY2Yg+W+Qp9P0jqDA@mail.gmail.com> <c140b8ee-941b-383a-986d-de1b924a0b65@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox