Why Choose ULIDs over Traditional UUIDs or IDs for Database Identification? • Hacker News Board

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

#51

Post by willvarfar » Mon, May 06, 2024, 11:07 AM UTC

zarzavat wrote: post #49

Kwpolska wrote: post #25

Earlier quoted context omitted.

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.

there's no reason to not use hex, though.

In my post this is replying to, I mentioned "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."

I've had struggles with binary and base62 encodings and things and just want everyone to use hex.

Typical databases compress rows or columns anyway these days; or if they don't, then the storage does. So often the question is entropy not character count. For example storage I work with these days takes as an identical number of bytes to store a hex string as to store the same id as a byte array.

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

#52

Post by s4i » Mon, May 06, 2024, 12:17 PM UTC

j-pb wrote: post #27

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).

Depending on your read and write patterns, overflowing even every few days might be appropriate, and also obfuscate the creation time [1] more.

[1] https://news.ycombinator.com/item?id=40272446

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

#53

Post by kreetx » Mon, May 06, 2024, 12:21 PM UTC

erik_seaberg wrote: post #36

willvarfar wrote: post #30

Earlier quoted context omitted.

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.…

You should definitely bench your storage to confirm that it likes hotspots (some don't!)

You should, but B-tree is the most common data structure used for database indexes, and sequential order performs better than random order for index keys.

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

#54

Post by kreetx » Mon, May 06, 2024, 12:26 PM UTC

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?

Depends on how many new U(U/L)IDs you insert, but when it's part of an index anyway then I personally would, as the performance penalty would be small (much smaller than anything else you do).

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

#55

Post by j-pb » Mon, May 06, 2024, 1:32 PM UTC

s4i wrote: post #52

j-pb wrote: post #27

Earlier quoted context omitted.

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).

Depending on your read and write patterns, overflowing even every few days might be appropriate, and also obfuscate the creation time [1] more. [1] https://news.ycombinator.com/item?id=40272446

Great point, using a u16 is probably the best choice then. As it give locality within 65.53600 seconds (roughly a minute) without exposing sensitive timing information, and should cover most real world write scenarios.

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

#56

Post by fanf2 » Mon, May 06, 2024, 2:11 PM UTC

pants2 wrote: post #12

I worked on a huge backend system that used ULIDs for everything. I liked them slightly better than UUIDs in general, but nobody coming in or using our system knew what ULIDs were, so we constantly had to explain what they were. If we used UUIDs there would have been far less confusion. In conclusion: Just use UUIDs.

Just use which UUID version?

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

#57

Post by [deleted] » Mon, May 06, 2024, 2:15 PM UTC

cbsmith wrote: post #19

w1zard0f0z wrote: post #13

How about TSIDs? Numerical, half the size, time sorted... https://vladmihalcea.com/uuid-database-primary-key/

TSIDs are basically type1 UUID's without the MAC address, no?

[deleted]

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

#58

Post by [deleted] » Mon, May 06, 2024, 2:38 PM UTC

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…

[deleted]

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

#59

Post by j-pb » Mon, May 06, 2024, 3:59 PM UTC

j-pb wrote: post #55

s4i wrote: post #52

Earlier quoted context omitted.

Depending on your read and write patterns, overflowing even every few days might be appropriate, and also obfuscate the creation time [1] more. [1] https://news.ycombinator.com/item?id=40272446

Great point, using a u16 is probably the best choice then. As it give locality within 65.53600 seconds (roughly a minute) without exposing sensitive timing information, and should cover most real world write scenarios.

A small additional improvement might be to also create a random 16bit salt at startup and then xor the timestamp with that, to leak even less information. That way entries will still have locality without leaking ms timestamp info.

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

#60

Post by erik_seaberg » Mon, May 06, 2024, 5:23 PM UTC

kreetx wrote: post #53

erik_seaberg wrote: post #36

Earlier quoted context omitted.

You should definitely bench your storage to confirm that it likes hotspots (some don't!)

You should, but B-tree is the most common data structure used for database indexes, and sequential order performs better than random order for index keys.

You often can't bottleneck the entire workload on the capacity of one cluster node and one storage device.