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

21–30 of 77 posts

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

#21

Post by russell_h »

erik_seaberg wrote: post #15
wodenokoto wrote: post #5

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

If your team hasn't engineered in monotonically increasing distributed clocks, the results of sorting are only partly correct. I'd rather not promote an option we can't rely on.

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.

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

#23

Post by itake »

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…

Or just use ints and encrypt them when you want to return them to the user.

https://sqids.org/

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

#24

Post by Kwpolska »

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…

> your database is spending all it's time shuffling everything around to insert your new rows in the middle of everything

Is it? The order of data on disk tends to be the insertion order. What might get shuffled around is the index for the primary key, but indexes can handle that quite well.

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

#25

Post by Kwpolska »

cbsmith wrote: post #16
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…

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.

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

#26

Post by MarceColl »

russell_h wrote: post #21
erik_seaberg wrote: post #15

Earlier quoted context omitted.

If your team hasn't engineered in monotonically increasing distributed clocks, the results of sorting are only partly correct. I'd rather not promote an option we can't rely on.

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.

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

#27

Post by j-pb »

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…

You don't need a total order for the locality properties. E.g. a 32bit ms timestamp that overflows every ~50 days + 96bit of entropy will give you the same locality with a much lower collision probability.

That being said, a lot of index types don't care about this kind of locality and might even benefit from higher entropy (randomized algorithms).

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

#28

Post by perfmode »

itake wrote: post #23
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…

Or just use ints and encrypt them when you want to return them to the user. https://sqids.org/

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

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

#29

Post by carpenecopinum »

itake wrote: post #23
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…

Or just use ints and encrypt them when you want to return them to the user. https://sqids.org/

I like the general idea, but the page you linked explicitly states that it isn't meant to be used to hide (user) IDs, or actually encrypt data.

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

#30

Post by willvarfar »

Kwpolska wrote: post #24
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…

> your database is spending all it's time shuffling everything around to insert your new rows in the middle of everything Is it? The order of data on disk tends to be the insertion order. What might get shuffled around is the index for the primary key, but indexes can handle that quite well.

And the answer is "depends on the database tech".

Here are some nice benchmarks: https://www.toomanyafterthoughts.com/uuids-are-bad-for-datab...

I learned the lexically-sortable-id trick from wizened DBAs in the 90s. It's an old trick. I've applied it myself to speed things up and have used both the ULID types described in the article and UUIDv7 etc.

I even advocate using UUID7 on BigQuery where there are no indexes. Having sortable keys is nice even if it doesn't add speed.

Post reply on HN