Re: Sequence vs UUID

On Wed, Feb 1, 2023 at 10:48 AM Kirk Wolak <wolakk(at)gmail(dot)com> wrote:

>
>
> On Wed, Feb 1, 2023 at 1:34 PM veem v <veema0000(at)gmail(dot)com> wrote:
>
>>
>> 1) sequence generation vs UUID generation, execution time increased from
>> ~291ms to 5655ms.
>> 2) Insert performance of "sequence" vs "UUID" execution time increased
>> from ~2031ms to 10599ms.
>> 3) Index performance for sequence vs UUID, execution time increased from
>> ~.3ms to .5ms.
>>
>>
> Yes, assuming that UUIDs would be efficient as keys when they are randomly
> generated, versus sequences (which tend to expand in one direction, and
> have been relatively optimized for years).
>
> This article explains in detail what is going on. If I were doing this, I
> would strongly consider a ULID because of getting the best of both worlds.
> https://blog.daveallie.com/ulid-primary-keys
>
> Of course, YMMV... And since ULIDs are not native to PG, there is
> overhead, but it is far more performant, IMO...
>

Biased comparison. ULIDs have a timestamp component. The closest UUID
equivalent in Postgres is UUIDv1 from the uuid-ossp extension, not v4.
Another difference not mentioned in the blog article is that UUID is
versioned, meaning you can figure out what kind of data is in the UUID,
whereas ULIDs are a "one size fits all" solution.

There is an implementation of sequential UUIDs for Postgres I posted
earlier in this thread. In addition, here is an implementation of UUIDv7
for Postgres:

https://gist.github.com/kjmph/5bd772b2c2df145aa645b837da7eca74

I would suggest running your tests against v1, v7, and sequential UUID
before jumping on ULID, which has no native type/indexing in Postgres.

It should also be noted that apps cannot provide a bigint ID due to
collisions, but an app can generate UUIDs and ULIDs without fear,
essentially shifting the generation time metric in UUID/ULID's favor over a
bigserial.

- Miles