Re: Sequence vs UUID

Well... until two processes generate an identical UUID. That happened to me
several times. It's rare but when that happens, oh boy that is a mess to
figure out.

Thanks,
Ben

On Thu, Feb 2, 2023, 10:17 AM Miles Elam <miles(dot)elam(at)productops(dot)com> wrote:

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