6 ms·
ULID: Like UUID but Sortable (2019)
- gnabgib 2y agoDiscussions (238 points, 6 years ago, 129 comments) https://news.ycombinator.com/item?id=18768909 https://news.ycombinator.com/item?id=18768909 (213 points, 3 years ago, 100 comments) https://news.ycombinator.com/item?id=29794186 https://news.ycombinator.com/item?id=29794186 (33 points, 2 years ago, 23 comments) https://news.ycombinator.com/item?id=34281969 https://news.ycombinator.com/item?id=34281969 Now that UUIDv7 is lexicographic, does that mean there is no use for ULID? (19 points, 26 days ago, 6 comments) https://news.ycombinator.com/item?id=40712872 https://news.ycombinator.com/item?id=40712872
- cricalix 2y agoWhile looking into a replacement for auto increment primary keys, I also encountered TSIDs / Snowflakes. At least with TSIDs you can store them easily as 64 bit integers; ULIDs were not as compact. Haven't made a decision yet though.
- leetrout 2y agoKeep ints for primary keys and keep all the db magic for your internal references then use ULIDs or TSIDs for your external keys and keep the ability to have well behaved indexes. Win-win with minimal disk impact
- hn_throwaway_99 2y agoThe whole point of ULIDs and newer things like UUIDv7 is precisely so you don't have to do that. I would definitely just use a ULID or UUIDv7 as my sole primary DB key for new projects going forward. You get the benefit of ascending ordered primary keys, but the key is still globally unique so can be used as public keys.
- Arch-TK 2y agoYou don't have to do that if you don't mind leaking metadata about the time when a record was created.
- Phil_Latio 2y agoWhat's the point of ULID again? People say one use case of UUIDs is that clients are able to generate the (random) primary key. Fair enough, I guess... Then they say autoincremental ids are bad because it could give indication about business activity (like how many orders have been done in a time frame). But with ULID or UUIDv7, this "benefit" is gone to some degree because timestamp is included. If client-generation is not needed, then UUID is stupid to begin with, because any incremental id can be transformed ("encrypted") to arbitary different format (which hides the real id). Also UUID is usually much larger: Waste of db/index space, degraded performance. But I guess if people push everything in the cloud, they don't even realize they are paying more than needed... Conclusion: I don't understand what you people are trying to "solve". All those different UUID versions leads me to believe the issue is not the id, but developer confusion.
- jw1224 2y ago> But with ULID or UUIDv7, this "benefit" is gone to some degree because timestamp is included Including the timestamp only tells someone the time the UUID was generated. Unlike incrementing numeric IDs (which can simply be subtracted from one another to measure a change in database records), there’s no way to meaningfully count change over time. > any incremental id can be transformed ("encrypted") to arbitary different format (which hides the real id) Easier said than done… You then have the overhead of encrypting/decrypting every client-facing ID before querying the database. You’ll also need to code workarounds in many frameworks, to bypass conventions where they expect keys in URL paths (for example). > Also UUID is usually much larger: Waste of db/index space, degraded performance With older UUIDs, sure, but sorted ones (like ULIDs) have consistent prefixes, allowing for very efficient indexing and querying with a binary tree search.
- dagss 2y agoYou say win-win but what is the win of having a seperate internal ID specifically? It is possible to do as you say ofc but what is the advantage?
- vbezhenar 2y agoOne advantage is to use more compact data type for performance-critical paths. Table ID datatype will be used not just in the given table, but also in all tables referencing given table. Table ID is always indexed and foreign keys very often are indexed as well, so this datatype "leaks" to the many database indexes. If your index is compact, it allows for better RAM caching and generally faster queries. And, of course, faster writes. Another advantage is that you don't need to "think" about using sortable id, numeric serial id is sortable by default. It also helps with index size and insertion speed for primary key index and often foreign key indices. Using completely random UUID leaks no data about record creation time. It might not look like sensitive information, but generally it's better to be on a cautious side about leaking information. Generating sortable ID is far from generally accepted solution. You need to find obscure libraries or write non-trivial code yourself for all languages you're using. It'll be solved in time, as UUIDv7 became standard, but we're not there yet. UUID v4 is available in any language (and generally trivial to generate).
- deleted 2y ago[deleted]
- deleted 2y ago[deleted]
- drowsspa 2y agoSure, if your only goal with them is hiding meta info and you're generating your IDs into a RDBMS...
- luismedel 2y agoI maintain the Python version[0] of this TSID spec[1]. Using it in several projects and I'm very happy with the result. [0] https://github.com/luismedel/tsid-python https://github.com/luismedel/tsid-python [1] https://github.com/f4b6a3/tsid-creator https://github.com/f4b6a3/tsid-creator
- deleted 2y ago[deleted]
- martinrue 2y agoThere's no mention of the UUID v7 in the README. Based on my basic understanding, v7 uses the first 48 bits to encode the Unix epoch, which makes them more useful for sort/indexing scenarios. Might be useful to compare ULID and v7 in the README to make it clearer the benefits one has over the other.
- sakjur 2y agoDigging into the discussions in the repo’s issues, I found someone linking to this comparison between ULIDs and UUIDs: https://uuid.ramsey.dev/en/stable/rfc4122/version7.html https://uuid.ramsey.dev/en/stable/rfc4122/version7.html In short, it seems UUIDv7 is storage-wise compatible with ULID but not vice versa due to UUID’s versioning bits.
- laserbeam 2y agoThere’s no mention because this is 5-7 years old. I believe that predates UUID 6-8.
- martinrue 2y agoYou know, I didn't even look at the last commit! I assumed it was a new thing, and hence why it was being shared now. Here's an idea for HN: when processing a repo link, auto add (<time since last commit>) to the end of the title.
- dotancohen 2y agoGood idea, but in my opinion that information really only needs to be added if it is significantly long ago, such as when appending a parenthesized year to a title.
- martinrue 2y agoAgreed. If the thing hasn't seen a commit in say 3 years, it'd be useful for idiots like me to know before they assume it's new because it's being shared now. Still, my bad :)
- thih9 2y agoIt says both: > Within the same millisecond, sort order is not guaranteed And: > Monotonic sort order (correctly detects and handles the same millisecond) Which is it? To make it more confusing, the spec later contains a section about monotonic sort order[1]. And looks like not all libraries implement it [2]. [1]: https://github.com/ulid/spec?tab=readme-ov-file#monotonicity https://github.com/ulid/spec?tab=readme-ov-file#monotonicity [2]: https://github.com/suyash/ulid/issues/10 https://github.com/suyash/ulid/issues/10
- GeertJohan 2y agoAfaik it depends on the generate function you use. You can let it keep track of the increment when generating, at a performance cost (atomic operations).
- lukevp 2y agoI think it’s meant to be monotonic within one node but not cross-node (cross-node the sort order is random within the same millisecond). The random portion of the IDs are sequentially incrementing within a single MS on a given node.
- jakjak123 2y agoI think it means all ids from the same node will be monotonic, ie it will never generate a id that will be sorted before an earlier one
- goralph 2y agoAn auto incrementing integer (for internal use) and a string (for external) will be enough for the vast majority of use cases, is simple to understand, and works well.
- sgarland 2y agoYou’re not wrong. This is my preferred method (from a DBRE perspective) if there are concerns about time-based solutions like UUIDv7, and keys must be publicly exposed.
- deleted 2y ago[deleted]
- saagarjha 2y agoHot take: I wish people just used UUIDs as UUIDs, instead of seeing the plethora of bits and going "it's free real estate". Treat it like a random identifier and move on with your life, rather than having all sorts of weird emergent properties that kinda sorta provide something valuable but not if you squint at it too hard.
- robbie-c 2y agoYou’ve maybe not had a use case for sortable uuids, so I can provide one: Columnar databases like Clickhouse don’t have the kind of indices that you’d be used to if you only used OLTP databases like eg Postgres. Instead, you essentially get one index per table, which is the order that things are laid out on disk* The problem is that you might want 2 access patterns, one for aggregating data within a time range, and another for looking up / joining data by its ID. So which do you use as the index? Timestamp or ID? If the ID is a uuidv4 and you index by ID, your time range queries need to scan the entire table, and vice versa. If the ID is time-sortable (eg uuidv7), this stops being a problem, and you don’t need some kind of workaround* * it’s a little more complicated than this but that not in a way that affects the example * eg a projection of your data with a different index
- hakanderyal 2y agoUsing ULID has probably the best ROI among all the architectural decisions I've made. Generating IDs on the server before insert is helpful, and not just for distributed system issues. Issues to think about before you switch: - The ID includes the timestamp of it's creation. If you don't want to leak it, you should use another identifier along with it. (I've never needed this in my use cases) - You won't be able to see the ID in its common representation format if you are using a database GUI. (I'm using Postico 2 for postgresql and the developer was kind enough to add "display as Crockford Base 32" to bytea columns making this a non-issue for me. UUIDv7 will also fix this.)
- blueflow 2y agoDo ULIDs have a defined endianness for their binary representation? Because there was none defined for UUIDs and there ended up to be two versions of it: Big Endian and Microsoft. That mess has created many problems.
- fauigerzigerk 2y agoYes, the spec says big endian.
- carderne 2y agoLast time this discussion came up, someone shared this nice article[0] about all the different ID options. It mentions Stripe IDs, which have pretty prefixes like "prod_foo" and "user_bar". These are great for users of their API (no wondering what an ID refers to) and for internal tooling and debugging, but have to be stored as inefficient varchars, which is not great for indexing and whatnot. So I spent the weekend creating "UPID"[1], which is like ULID but prefixable! Up to four characters for the prefix, 40 bits of time (256ms precision, but could change in a revision) and still 64 bits of randomness. So you can do pretty Stripe-style IDs without losing the benefits of a neat 128-bit UUID column. Have implemented it for Python, Rust and Postgres, maybe worth a kick around for someone! [0] https://brandur.org/nanoglyphs/026-ids https://brandur.org/nanoglyphs/026-ids [1] https://github.com/carderne/upid https://github.com/carderne/upid
- shockleyje 2y agoYou are awesome! I work with a former engineering manager of Stripe and one initiative they had me on was migrating our UUID's to the prefixed strategy you mentioned in Stripe. Most of the effort, honestly, went into the compatibility layer when bridging which version of the ID was accepted on a given API, and ensuring that this major change was implemented between services universally (i.e., is service B going to fail if I send a prefixed ID?). All of this to say, I am hesitant to generally recommend migrating from UUID -> prefixed UUID, but I will be considering starting future projects of my own with UPID
- carderne 2y agoThanks for the comment! Yeah I really love Stripe IDs and if I was building an API-first product would definitely make that a priority, but can imagine a bit of headache if there isn't global buy-in from day one... Would be great to hear about it if you do give it a spin. On projects with simpler domain models I can't imagine a huge benefit, but as soon as you have piles of tables it's quite nice that every row tells you up-front exactly what it is.
- amelius 2y agoBut why would you need to let the library prefix the ID if you can just prefix it yourself right after the UUID call?
- grodes 2y agoKSUID is better in everything
- deleted 2y ago[deleted]
- efilife 2y agohttps://github.com/grantcarthew/awesome-unique-id https://github.com/grantcarthew/awesome-unique-id Here's a great list of many IDs if you haven't got enough dillema on what to use. Everyone is welcome to contribute
- novolunt 2y ago[dead]