pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: Sequence vs UUID
Date: Sat, 28 Jan 2023 20:01:50 -0600
Message-ID: <b7428272-37a0-aa8b-4b84-ebc888e31b0a@gmail.com> (raw)
In-Reply-To: <CAD+mzoy0tPKf5+z8KsiFcXsPLE021fTbS7m-POFX8qv-N+M2_Q@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>

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.)

On 1/28/23 19:44, Benedict Holland wrote:
> Why is it a terrible idea? I have been using them for years without a 
> single problem. I don't rely on them for create order. Terrible seem a bit 
> extreme.
>
> Thanks,
> Ben
>
> On Sat, Jan 28, 2023, 3:39 PM Erik Wienhold <ewie@ewie.name> wrote:
>
>     > On 27/01/2023 01:48 CET Ron <ronljohnsonjr@gmail.com> wrote:
>     >
>     > On 1/26/23 15:55, Erik Wienhold wrote:
>     > >
>     > > There are arguments against sequential PK, e.g. they give away too
>     much info and
>     > > allow attacks such as forced browsing[2].  The first I can
>     understand: you may
>     > > not want to reveal the number of users or customers.  But access
>     control should
>     > > prevent forced browsing.
>     >
>     > Shouldn't your application layer isolate the users from the
>     database?  UUIDs
>     > are all over the DBs I manage, but the PKs are all sequences.
>
>     Yes, I meant the application layer, not Postgres' access control.
>
>     --
>     Erik
>
>

-- 
Born in Arizona, moved to Babylonia.

view thread (46+ messages)  latest in thread

Message-ID: <b7428272-37a0-aa8b-4b84-ebc888e31b0a@gmail.com>
Permalink:  ../b7428272-37a0-aa8b-4b84-ebc888e31b0a@gmail.com/
Also on:    postgresql.org/message-id/b7428272-37a0-aa8b-4b84-ebc888e31b0a@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: <b7428272-37a0-aa8b-4b84-ebc888e31b0a@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>

* 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