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