Re: Sequence vs UUID

On Mon, Feb 6, 2023 at 1:22 PM Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:

> On 2023-02-06 20:04:39 +0100, Julian Backes wrote:
> > I don't really understand what you mean by 'performance'. To me it is not
> > surprising that incrementing (I know it is not just incrementing) a
> > 64bit integer is faster than generating 128 bit data with a good amount
> of
> > random data even if it seems to be too slow.
>
> But UUIDs are random and that plays havoc with locality. For example
> consider one table with invoices and another with invoice items. If you
> want to get all the invoices including the items of a single day, the
> data is probably nicely clustered together in the tables. But the join
> needs to look up random ids in the index, which will be spread all over
> the index. In a simple benchmark for this scenario the UUIDs were about
> 4.5 times slower than sequential ids. (In other benchmarks the
> difference was only a few percent)

This is really key.

While many of the people posting here may understand this, all of the
databases I've seen that are written with the UUID pattern appear to be
written by developers oblivious to this fact. The UUID pattern seems to be
popular with developers who see abstract away the database underneath the
code and might use an ORM and be weaker in terms of database facing
constraint checking. My direct observation is that these databases scale
poorly and the developers spend a lot of time building tools that fix
broken data stemming from application bugs.

I'm certain this is not the experience of everyone here. I do however find
the counter sequence arguments to be somewhat silly; partition safe
sequence generation is simple to solve using simple methods. "ID guessing"
is not insecure along similar lines; if your application relies on id
obfuscation to be secure you might have much bigger issues to contend with
IMO.

merlin