Re: Primary keys and composite unique keys(basic question)

On Fri, Apr 2, 2021 at 3:40 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
>
> On Thu, 2021-04-01 at 21:28 -0500, Merlin Moncure wrote:
> > I would never use UUIDS for keys though.
>
> That makes me curious for your reasons.
>
> I see the following disadvantages:
>
> - A UUID requires twice as much storage space as a bigint.
>
> - B-tree indexes are space optimized for inserting at the
> rightmost leaf page, but UUIDs are random.
>
> - UUIDs are more expensive to generate.
>
> On the other hand, many processes trying to insert into
> the same index page might lead to contention.
>
> Is there anything I have missed?

It's a small thing, but UUIDs are absolutely not memorizable by
humans; they have zero semantic value. Sequential numeric identifiers
are generally easier to transpose and the value gives some clues to
its age (of course, in security contexts this can be a downside).

Performance-wise, UUIDS are absolutely horrible for data at scale as
Tom rightly points out. Everything is randomized, just awful. There
are some alternate implementations of UUID that mitigate this but I've
never seen them used in the wild in actual code.

merlin