On Wed, Feb 1, 2023 at 10:48 AM Kirk Wolak <wolakk@xxxxxxxxx> wrote:
On Wed, Feb 1, 2023 at 1:34 PM veem v <veema0000@xxxxxxxxx> 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.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:
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
