Re: Sequence vs UUID

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(at)ewie(dot)name> wrote:
>
> > On 27/01/2023 01:48 CET Ron <ronljohnsonjr(at)gmail(dot)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.