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

61–70 of 77 posts

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

#61

Post by cbsmith »

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.

Stuff gets "converted" between you and the database anyway. If you really want to see it as though they were strings all the time, you can always make a view.

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

#62

Post by cbsmith »

willvarfar wrote: post #51
zarzavat wrote: post #49

Earlier quoted context omitted.

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

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

Storage tends to be cheap, so it's less about that, and more about memory consumption and index efficiency. The cost of transforming back to and from binary is also pretty trivial on modern systems.

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

#63

Post by vbezhenar »

cbsmith wrote: post #18
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 you can just store the UUIDs as bytes like the almighty intended.

UUID is 128 bits.

Even for huge tables 64 bits are enough. And for many tables 32 bits is more than enough.

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

#64

Post by cbsmith »

mattbillenstein wrote: post #44
cbsmith wrote: post #20

Earlier quoted context omitted.

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

You could encode UUIDs as base58, though I'm with you that that doesn't seem to make a lot of sense.

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

#65

Post by vbezhenar »

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?

Of course. Index for this column is inevitable and with index, uniqueness basically free and protects from programmer errors.

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

#66

Post by cbsmith »

vbezhenar wrote: post #63
cbsmith wrote: post #18

Earlier quoted context omitted.

Or you can just store the UUIDs as bytes like the almighty intended.

UUID is 128 bits. Even for huge tables 64 bits are enough. And for many tables 32 bits is more than enough.

Oh, 32-bits is enough for a lot of tables assuming you have a transactional ID allocator. The whole reason to have a UUID is so you don't have to serialize all your ID allocation. Agreed though that for some lower volume systems, 64-bits is likely enough to make collisions statistically unlikely.

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

#67

Post by vbezhenar »

cbsmith wrote: post #66
vbezhenar wrote: post #63

Earlier quoted context omitted.

UUID is 128 bits. Even for huge tables 64 bits are enough. And for many tables 32 bits is more than enough.

Oh, 32-bits is enough for a lot of tables assuming you have a transactional ID allocator. The whole reason to have a UUID is so you don't have to serialize all your ID allocation. Agreed though that for some lower volume systems, 64-bits is likely enough to make collisions statistically unlikely.

May be I'm spoiled by postgres, but transactional ID allocator sounds like a problem solved in every database. Is there issues somewhere?

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

#68

Post by perfmode »

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.

thank you for clarifying

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

#69

Post by itake »

carpenecopinum wrote: post #29
itake wrote: post #23

Earlier quoted context omitted.

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.

Touché. The project used to be called hashids, but I guess they renamed it when they weren’t actually hashes

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

#70

Post by itake »

perfmode wrote: post #28
itake wrote: post #23

Earlier quoted context omitted.

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

My link didn’t link to there library I thought it was. We encrypted ids at Yelp, not hashed them.
Post reply on HN