pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.





view thread (46+ messages)  latest in thread

Message-ID: <c140b8ee-941b-383a-986d-de1b924a0b65@gmail.com>
Permalink:  ../c140b8ee-941b-383a-986d-de1b924a0b65@gmail.com/
Also on:    postgresql.org/message-id/c140b8ee-941b-383a-986d-de1b924a0b65@gmail.com

 · 

reply

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