6 ms·
Why Choose ULIDs over Traditional UUIDs or IDs for Database Identification?
- nosefrog 3y agoThanks, chatgpt.
- shaunxcode 3y agoYeah that code example is odd to say the least.
- ramchip 3y ago> https://github.com/f4b6a3 https://github.com/f4b6a3 > f4b6a3: a meaningless random package name ...well, I guess that's one way to name your library!
- KTibow 3y agoThe GH repo for ULIDs provides better and more relevant reasons for use. https://github.com/ulid/spec https://github.com/ulid/spec
- vbezhenar 3y agoAt 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.
- perfmode 3y agowould you still keep a uniqueness constraint on the uuid/ulid column?
- 9dev 3y agoI wouldn’t, it’s redundant for all intents and purposes unless you’re Google, which you are not. UUIDs/ULIDs provide strong uniqueness guarantees on their own.
- dxdm 3y agoYes, 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.
- kreetx 3y agoDepends 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).
- vbezhenar 3y agoOf course. Index for this column is inevitable and with index, uniqueness basically free and protects from programmer errors.
- cbsmith 3y agoOr you can just store the UUIDs as bytes like the almighty intended.
- vbezhenar 3y agoUUID is 128 bits. Even for huge tables 64 bits are enough. And for many tables 32 bits is more than enough.
- cbsmith 3y agoOh, 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.
- itake 3y agoOr just use ints and encrypt them when you want to return them to the user. https://sqids.org/ https://sqids.org/
- perfmode 3y agoNB: may be more accurate to call this hashing, versus encryption
- jerbear4328 3y agoActually, it's not hashing either, it's just encoding. Anyone who knows the alphabet can easily decode them.
- itake 3y agoMy link didn’t link to there library I thought it was. We encrypted ids at Yelp, not hashed them.
- carpenecopinum 3y agoI 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.
- itake 3y agoTouché. The project used to be called hashids, but I guess they renamed it when they weren’t actually hashes
- wodenokoto 3y agoDid 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?
- lazyasciiart 3y agoIt 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?
- codetrotter 3y agoThere 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
- cbsmith 3y agoCassandra seemed to have no problem using type1 UUIDs for this.
- Dylan16807 3y ago> it’s sortable (in time?) which UUID can also be (but usually recommended not to be) It gives you the sortable parts without the "recommended not to" parts.
- erik_seaberg 3y agoIf 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.
- mattbillenstein 3y agoI'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.
- cbsmith 3y agoIsn't that basically a type 4 UUID that you truncated the last 4 bytes from?
- mattbillenstein 3y agoThe 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...
- cbsmith 3y agoYou could encode UUIDs as base58, though I'm with you that that doesn't seem to make a lot of sense.
- willvarfar 3y agoThe 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-rfc4122bis/ 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.
- dagss 3y agoBut if you for instance use MS SQL and the uniqueidentifier type, sorted UUIDv7 is not sorted im the database because MS SQL swapsnthe bytes around. So ULID (stored as binary(16)) is an advantage in that case to avoid making that mistake. I really like that ULID looks differentt from UUIDv4. UUIDv7 is something sorted that on the surface looks like it will be random and is mixed with random IDs etc..
- cbsmith 3y agoWhat 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.
- pants2 3y agoI 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.
- fanf2 3y agoJust use which UUID version?
- w1zard0f0z 3y agoHow about TSIDs? Numerical, half the size, time sorted... https://vladmihalcea.com/uuid-database-primary-key/ https://vladmihalcea.com/uuid-database-primary-key/
- cbsmith 3y agoTSIDs are basically type1 UUID's without the MAC address, no?
- deleted 3y ago[deleted]
- est 3y agoI am using ksuid and it's been great.
- rossant 3y agoNot much contents in this article. I thought I'd learn more about ULIDs.
- moi2388 3y ago"Why Choose ULIDs Over Traditional UUIDs or IDs for Database Identification?" "Is it worth it ? I am not going to cover all the reasons why Ulid is better" Well, this article was definitely worth it /s
- buglungtung 3y agoI'm using KSUID (https://sotergreco.com/why-choose-ulids-over-traditional-uuids-or-ids-for-database-identification https://sotergreco.com/why-choose-ulids-over-traditional-uui...) for a while and totally happen with it One tiny note about using sortable id is you should not expose it to public use without authentication because it can be scan easily
- dang 3y agoRelated. Others? ULIDs and Primary Keys (2022) - https://news.ycombinator.com/item?id=40016413 https://news.ycombinator.com/item?id=40016413 - April 2024 (33 comments) Ulid: Universally Unique Lexicographically Sortable Identifier - https://news.ycombinator.com/item?id=39878319 https://news.ycombinator.com/item?id=39878319 - March 2024 (6 comments) ULID – Sortable Unique Identifier - https://news.ycombinator.com/item?id=34281969 https://news.ycombinator.com/item?id=34281969 - Jan 2023 (23 comments) Using ULIDs at Incident.io - https://news.ycombinator.com/item?id=34230652 https://news.ycombinator.com/item?id=34230652 - Jan 2023 (1 comment) Understanding UUIDs, ULIDs and string representations - https://news.ycombinator.com/item?id=29794186 https://news.ycombinator.com/item?id=29794186 - Jan 2022 (100 comments) Going Deep on UUIDs and ULIDs - https://news.ycombinator.com/item?id=28948815 https://news.ycombinator.com/item?id=28948815 - Oct 2021 (2 comments) Universally Unique Lexicographically Sortable Identifier - https://news.ycombinator.com/item?id=23160641 https://news.ycombinator.com/item?id=23160641 - May 2020 (2 comments) ULID: Universally Unique Lexicographically Sortable Identifier - https://news.ycombinator.com/item?id=18768909 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 https://news.ycombinator.com/item?id=13116308 - Dec 2016 (52 comments) ULID: Universally Unique Lexicographically Sortable Identifier - https://news.ycombinator.com/item?id=12205158 https://news.ycombinator.com/item?id=12205158 - Aug 2016 (3 comments)
- s4i 3y agoThis 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 https://yle.fi/a/74-20030335 [2] https://yle.fi/a/74-20030336 https://yle.fi/a/74-20030336
- ponytech 3y agoMy preference: using traditional incremental numeric IDs and obfuscate them with Hashids (https://hashids.org https://hashids.org) when exposed publicly
- Sammi 3y agoThis 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.