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.e...@productops.com> wrote: > On Wed, Feb 1, 2023 at 10:48 AM Kirk Wolak <wol...@gmail.com> wrote: > >> >> >> On Wed, Feb 1, 2023 at 1:34 PM veem v <veema0...@gmail.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 > > >
