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