Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why Choose ULIDs over Traditional UUIDs or IDs for Database Identification?

sotergreco.com

Post reply on HN
Print view View original

41–50 of 77 posts

Re: Why Choose ULIDs over Traditional UUIDs or IDs for Database Identification?

#41

Post by dxdm »

perfmode wrote: post #10
vbezhenar wrote: post #4

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

Yes, I'd keep a uniqueness constraint, because it prevents bugs from inserting the same value more than once. From concurrency issues to broken id generation, I've seen it happen before. So unless there are other considerations, having the database enforce uniqueness is a good thing in my book.

Re: Why Choose ULIDs over Traditional UUIDs or IDs for Database Identification?

#42

Post by dang »

Related. Others?

ULIDs and Primary Keys (2022) - https://news.ycombinator.com/item?id=40016413 - April 2024 (33 comments)

Ulid: Universally Unique Lexicographically Sortable Identifier - https://news.ycombinator.com/item?id=39878319 - March 2024 (6 comments)

ULID – Sortable Unique Identifier - https://news.ycombinator.com/item?id=34281969 - Jan 2023 (23 comments)

Using ULIDs at Incident.io - https://news.ycombinator.com/item?id=34230652 - Jan 2023 (1 comment)

Understanding UUIDs, ULIDs and string representations - https://news.ycombinator.com/item?id=29794186 - Jan 2022 (100 comments)

Going Deep on UUIDs and ULIDs - https://news.ycombinator.com/item?id=28948815 - Oct 2021 (2 comments)

Universally Unique Lexicographically Sortable Identifier - https://news.ycombinator.com/item?id=23160641 - May 2020 (2 comments)

ULID: Universally Unique Lexicographically Sortable Identifier - https://news.ycombinator.com/item?id=18768909 - Dec 2018 (129 comments)

Universally Unique Lexicographically Sortable Identifier in Go - https://news.ycombinator.com/item?id=13116308 - Dec 2016 (52 comments)

ULID: Universally Unique Lexicographically Sortable Identifier - https://news.ycombinator.com/item?id=12205158 - Aug 2016 (3 comments)

Re: Why Choose ULIDs over Traditional UUIDs or IDs for Database Identification?

#44

Post by mattbillenstein »

cbsmith wrote: post #20
mattbillenstein wrote: post #8

I'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.

Isn't that basically a type 4 UUID that you truncated the last 4 bytes from?

The encoding is completely different? And given that encoding I don't go to any great means to store them as a special type in the db - just text. uuid being hexidecimal, databases often want to store them as binary since that's half the bytes...

Re: Why Choose ULIDs over Traditional UUIDs or IDs for Database Identification?

#45

Post by evgpbfhnr »

MarceColl wrote: post #26
russell_h wrote: post #21

Earlier quoted context omitted.

They’re only partially correct no matter what. The timestamp in a ULID has only has millisecond resolution, so two IDs generated in the same millisecond, even within the same process, will be randomly ordered. In practice though there are a lot of advantages to having approximately-time-ordered IDs, and I’ve found the pitfalls easy enough to avoid.

The ULID spec has the weird "first generate a random number and all successive calls within the same millisecond just increment the previous number by 1", which tries to solve this somewhat at the expense of now needing to lock, making ULID generation sequential within the same process.

That probably isn't something good for everyone, it makes trying to guess ids on a busy system so much easier than 60ish bits of random data (just get an id, try to add or remove 1, and if someone else happened to generate another id the same millisecond you've just found it?)

Whether the id is secret or not is a debatable choice, but there's no reason to make it easily guessable; at least that increment should be random with a somewhat large stride...

But in general anyway in a distributed system network latency etc will mean things are just "mostly sorted" which is good enough, keeping the last few inserts in order is much easier than reshuffling the whole db/index all the time

Re: Why Choose ULIDs over Traditional UUIDs or IDs for Database Identification?

#46

Post by s4i »

dang wrote: post #42

Related. Others? ULIDs and Primary Keys (2022) - https://news.ycombinator.com/item?id=40016413 - April 2024 (33 comments) Ulid: Universally Unique Lexicographically Sortable Identifier - https://news.ycombinator.com/item?id=39878319 - March 2024 (6 comments) ULID – Sortable Unique Identifier - https://news.ycombinator.com/item?id=34281969 - Jan 2023 (23 comments) Using ULIDs at Incident.io - https://news.ycombinator.…

This topic resurfaces quite frequently, I've noticed. One thing I find quite frustrating is that most of the time this is discussed, people don't remember that exposing the creation time of a database row to the public is not acceptable in many systems. This makes v7 UUIDs, ULIDs, KSUIDs, etc. not appropriate for the task.

In any application where the user is able to first create a draft of something (a video upload, a news article, a blog post, ...) and then release it some day in the future, we probably don't want the public to be aware of when the author created the initial draft. For instance, news websites often start drafting articles about people that are speculated to pass away soon.

The problem also affects numeric/auto-incremented IDs. The "Finnish BBC" YLE uses numeric IDs in their news article URLs, making it possible to ballpark when the article was first started. Here's [1] an article about the death of former president Martti Ahtisaari from 2023/10/16 and here's [2] the next integer ID dated 2023/5/5 (5 months before).

[1] https://yle.fi/a/74-20030335

[2] https://yle.fi/a/74-20030336

Re: Why Choose ULIDs over Traditional UUIDs or IDs for Database Identification?

#47

Post by jitl »

willvarfar wrote: post #9

The 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 iden…

It depends per storage system. I think we evaluated this on Postgres b-tree and weren’t super jazzed about it. Some storage systems don’t like hotspots. They can write two far-apart keys in parallel, but adjacent keys need to be serialized. Sometimes this happens with sharding systems.

Re: Why Choose ULIDs over Traditional UUIDs or IDs for Database Identification?

#48

Post by cryptos »

jerbear4328 wrote: post #31
perfmode wrote: post #28

Earlier quoted context omitted.

NB: may be more accurate to call this hashing, versus encryption

Actually, it's not hashing either, it's just encoding. Anyone who knows the alphabet can easily decode them.

To make this secure, some secret salt value needs to be added before hashing.

Re: Why Choose ULIDs over Traditional UUIDs or IDs for Database Identification?

#49

Post by zarzavat »

Kwpolska wrote: post #25
cbsmith wrote: post #16

Earlier quoted context omitted.

What drives me nuts is people storing uuids in databases as hex strings. They're bytes, and if you do it right, even the type 1's are lexigraphically sortable.

Hex strings may require more storage, but they remove the need to convert back and forth and make it easier to manually query the database when debugging. The best type is your database's uuid type if it has one.

There’s no reason to use hex though. Base32/36/62/64 should be considered.

Re: Why Choose ULIDs over Traditional UUIDs or IDs for Database Identification?

#50

Post by Sammi »

ponytech wrote: post #43

My preference: using traditional incremental numeric IDs and obfuscate them with Hashids ( https://hashids.org ) when exposed publicly

This looks amazing.

I'm wondering about the security of using a random alphabet with this instead of the default one. In my mind this amounts to a form of cryptography, but I have no idea how to analyse how much security it gives.

EDIT: Reading the faq I see that they insist that sqids cannot provide any encryption. This does not fit with my understanding of the word. Using unique random alphabet is probably the oldest form of encryption. Whether or not it is secure enough depends on your threat model. What I want and what I need is a way to calculate the security provided by a random alphabet.

Post reply on HN