Why Choose ULIDs over Traditional UUIDs or IDs for Database Identification?
1–10 of 77 posts
Re: Why Choose ULIDs over Traditional UUIDs or IDs for Database Identification?
#2Thanks, chatgpt.
Re: Why Choose ULIDs over Traditional UUIDs or IDs for Database Identification?
#3The GH repo for ULIDs provides better and more relevant reasons for use. https://github.com/ulid/spec
Re: Why Choose ULIDs over Traditional UUIDs or IDs for Database Identification?
#4At this point I think that ideal setup is to use numerical id for primary and foreign keys and maintaining a separate uuid field for everything else.
The reason being that index size matters a lot (for caches and other things) and index size depends on underlying field size, obviously.
Whether to use UUID or ULID is depends on tooling. While it's not hard to write ascending UUID generator and I did it myself few times, using standard APIs might be better approach despite the shortcomings.
Re: Why Choose ULIDs over Traditional UUIDs or IDs for Database Identification?
#5Did the article provide any reason for ULID over UUID?
- it’s slightly more complex than UUID, but not enough to be a problem
- it’s sortable (in time?) which UUID can also be (but usually recommended not to be)
- it can produce slightly more ids per second, but not enough to make a difference.
So, it’s a tie, a tie and a tie. Why would you switch?
Re: Why Choose ULIDs over Traditional UUIDs or IDs for Database Identification?
#6Did the article provide any reason for ULID over UUID? - it’s slightly more complex than UUID, but not enough to be a problem - it’s sortable (in time?) which UUID can also be (but usually recommended not to be) - it can produce slightly more ids per second, but not enough to make a difference. So, it’s a tie, a tie and a tie. Why would you switch?
It jumps straight to “I’m not going to list all the reasons”. Ok, maybe just the biggest one or two reasons? Is lexicographer sorting that helpful?
Re: Why Choose ULIDs over Traditional UUIDs or IDs for Database Identification?
#7Did the article provide any reason for ULID over UUID? - it’s slightly more complex than UUID, but not enough to be a problem - it’s sortable (in time?) which UUID can also be (but usually recommended not to be) - it can produce slightly more ids per second, but not enough to make a difference. So, it’s a tie, a tie and a tie. Why would you switch?
There is a draft for a UUID variant called v7 which is basically the same like ulid
It is absolutely valid
In particular when you want ids to be in mostly ascending order but where you can’t use an auto incrementing int
It’s sortable is a key feature because it can help make searching tables more efficient in situations where the data is naturally tied to the time at which the entry was made and your queries relate to time also
For example, if you append rows with ulid or uuid v7, then you can binary search the records in the file without first sorting or making an extra index of the ids as they are already for the most part in ascending order
Re: Why Choose ULIDs over Traditional UUIDs or IDs for Database Identification?
#8I've been doing something that includes a type character as the first character - followed by 11 random base58 digits. This allows > 2^64 possible ids and a type in ~12 bytes. I occasionally wish they were ordered, but most of my tables usually have a 'created_at' field anyway should ordering matter.
I feel like uuid / ulid are just overkill for most situations - and they're long and kinda ugly imo.
Re: Why Choose ULIDs over Traditional UUIDs or IDs for Database Identification?
#9The main driver behind lexically sortable identifiers is that you generally insert into databases in time order so if your ids are sorted by time then you are appending to the end of the table. If, on the other hand, your ids are random (e.g. the prevalent UUIDv4) then your database is spending all it's time shuffling everything around to insert your new rows in the middle of everything.
Once you have time-based identifiers as primary keys you can do fancy things like using some id you know was generated at time x as a where clause on some other table to dramatically cut down the search space. This kind of dirty trick can really improve performance sometimes when it really matters.
However, the main contender for lexically sorted IDs is ... UUIDs! The new UUID specs have ULID versions of UUIDs.
The new UUID spec includes lexically sortable variants, of which UUIDv7 is gaining widespread support. https://datatracker.ietf.org/doc/draft-ietf-uuidrev-rfc4122b...
That spec lists 16 prior lexically-sortable identifiers, and has good motivation for adding time sorting to the UUID standard.
Two things I particularly like about the UUIDv7 standard is that it is easy to swap into existing codebases that are using UUIDv4, and that it is easy to extract the timestamp part in systems that don't know about it and are treating it as a string e.g. in lots of databases you can do some sql like TIMESTAMP_MILLIS(CAST(CONCAT("0x", LEFT(REPLACE(id, '-', ''), 12)) AS INT64)). This is a massive advantage over systems that use non-hex encodings.
So these days, use UUIDv7 if you want to generate your ID outside of a database and the time the id was created is not a secret; else use UUIDv4. Your database will thank you.
Re: Why Choose ULIDs over Traditional UUIDs or IDs for Database Identification?
#10At this point I think that ideal setup is to use numerical id for primary and foreign keys and maintaining a separate uuid field for everything else. The reason being that index size matters a lot (for caches and other things) and index size depends on underlying field size, obviously. Whether to use UUID or ULID is depends on tooling. While it's not hard to write ascending UUID generator and I did it myself few time…
would you still keep a uniqueness constraint on the uuid/ulid column?