5 ms·

After a lot of time spent investigating the different kind of (U)UIDs, I've come to the conclusion that ULIDs[0] are the best kind of (U)UIDs you can use for yo

After a lot of time spent investigating the different kind of (U)UIDs, I've come to the conclusion that ULIDs[0] are the best kind of (U)UIDs you can use for your application today: * they are sortable so pagination is fast and easy. * they can be represented as UUID in database (at least in Postgres). * they are kind of serial, so insertion into indexes is rather good, as opposed to completely random UUIDs. * 48 bits timestamp gives enough space for the next 9000 years. [0] https://github.com/ulid/spec https://github.com/ulid/spec

minitoar 6y ago
Not good if you’re using them in a situation where they are visible to the public (eg urls) and you don’t want to leak the time component.
- andy_ppp 6y ago
  If you really need this why not add a public ID...
  - minitoar 6y ago
    A benefit of ULIDs is the better index performance. Adding a second id column kinda undermines that, since you’ll need an index there as well.
    - Dowwie 6y ago
      What column type?
- mnahkies 6y ago
  True, but for many entities the time they were created is basically public information anyway - time of a tweet, date hacker news account was created, time blog post was published. It might leak more granular information than would be publicly available otherwise, but for many use cases it's not a security concern as far as I can tell
  - minitoar 6y ago
    Yes, this only matters if you don’t want to leak the time component, eg to thwart people from estimating how many total active users you have.
j-pb 6y ago
The last time ULID was mentioned in a thread it got some flack for its "weird" generator local monotonicity property. But it's actually pretty useful, when you're building database indices, because it reduces the amount of entropy that your index has, and is therefore a big help for succinct data structures, which occupy space proportional to the information theoretic lower bound up to a log factor. This can make the difference between a multi mb and multi gb index. Obscure feature, big gain.
fatsdomino001 6y ago
How does ULID compare to Instagram's bigint id_generator for postgres?[0] ULID is 128 bits vs 64 bits for instagram id, so ULID is stored in a uuidfield but instagram id can be stored in bigint field. There is some advantages of using the application code to generate the ID though (avoiding roundtrip to DB if need to use the ID afterwards), but I'm seduced by just offloading this work to the DB and being able to use bigint field to store the id. As someone who is just about to commit to using ULID or an instagram-like bigint id generator, would you have any thoughts you could share on which may be better? [0] https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c https://instagram-engineering.com/sharding-ids-at-instagram-...
- sylvain_kerkour 6y ago
  Is there any reasons to prefer BIGINT instead of UUID (other than the very few % of performance, which may never be a problem)? I just find instagram's ID complex (custom epoch, mandatory use of RETURNING) while Ulid is elegant (easy to generate and use). For me the biggest selling point was the interoperability with other kinds of UIDs, if later your requirements change. Also, note that (at least in Rust) Ulid are implemented as u128, so are pretty fast.
  - fatsdomino001 6y ago
    I'll admit that I'm not actually sure on the performance difference between BIGINT and UUID in postgres. So in that sense you're right that the question is a tradeoff of x% of performance (which may never be a problem) but with added complexity vs ULID which is simple/elegant and signifcantly less cognitive overhead (easier to maintain) but will cause bigger indexes vs bigint. It's a tradeoff.. and your post has me thinking again that realistically the maintainability and agility that ULID provides may actually be better than a potentially error-prone but x% more performant instagram-like bigint generator. I just don't know enough to properly compare the two, so I suspect opting for the easier cognitive load / maintainability / agility is best.
Lazare 6y ago
I also spent some time evaluating them, and came to the conclusion that KSUIDs (https://github.com/segmentio/ksuid https://github.com/segmentio/ksuid) were strictly better than ULIDs. The differences aren't huge in practice, but the ULID spec has a couple of highly questionable choices. Specifically, according to the spec: 1. The first time you generate a ULID in a given millisecond on a node, you should fill in the random component randomly. 2. But if you generate more than one ULID per milliscond, subsequent ULIDs should use the first ULIDs random component and just increment the least significant bit. 3. If that would overflow, then you just can't generate any more ULIDs that millisecond. The goal is that ULIDs will be strictly sortable even when you're generating multiple per millisecond...except for that to work, you'd have to only generate them in a single thread (or at any rate on a single node). If you're just incrementing a numbers on a single node, then uh, why not just use an autoincrementing integer column in a DB? Of course, if you're generating multiple IDs per millisecond, you're probably using multiple nodes, and if you are, then wall clock skew means the IDs will never be strictly sortable. Except actually it's even worse than that; even if you ARE only using a single node, your code probably can't assume that every ULID it will ever encounter was generated in that way, so you still can't rely on the sorting guarantee. Meanwhile, a compliant ULID generator has to have state and, if it's going to be called in a multithreaded way, to deal with some complex race conditions, which makes it a lot harder to run. Meanwhile, you could get 100% of the benefits and none of the drawbacks by just...using random bits for the random component every time. Anyhow, the idea of slapping a timestamp and a random string of bits together is a good one. I just think the KSUID spec is a bit better thought out.
- sylvain_kerkour 6y ago
  The problem I had with KSUID is that they have a custom format, so not optimised in Postgres, while ULIDs are fully interoperable with other kinds of UUIDs. So if your requirements change later, it's just a change to the function generating your UIDs. Also I don't use monotonic generators, because in pratice, in my applications, it never make business sense to sort entities with a greater granularity than a millisecond, so even if I generate 10 entities' IDs in a millisecond, I will let the random part randomly sort the entities generated during this millisecond. Monotonic generators, are in my opinion only useful when you require absolute ordering, like with events, and thus rarely are a concern.
Dowwie 6y ago
I'm skeptical but can be swayed. A snowflake id can be represented as a BIGINT in postgres. I'm curious how a BIGINT contrasts with a UUID column in terms of storage and performance. If anyone has used a snowflake id in a production environment and ran into a unique id limit, please shout about it.
giancarlostoro 6y ago
I like MongoDBs ObjectIDs because they secretly maintain a timestamp, therefore... if you sort it sorts by that value. MongoDB isnt for every use case but when I do use it I try to take advantage of everything it has.

minitoar 6y ago

Not good if you’re using them in a situation where they are visible to the public (eg urls) and you don’t want to leak the time component.

andy_ppp 6y ago
If you really need this why not add a public ID...
- minitoar 6y ago
  A benefit of ULIDs is the better index performance. Adding a second id column kinda undermines that, since you’ll need an index there as well.
  - Dowwie 6y ago
    What column type?
mnahkies 6y ago
True, but for many entities the time they were created is basically public information anyway - time of a tweet, date hacker news account was created, time blog post was published. It might leak more granular information than would be publicly available otherwise, but for many use cases it's not a security concern as far as I can tell
- minitoar 6y ago
  Yes, this only matters if you don’t want to leak the time component, eg to thwart people from estimating how many total active users you have.

j-pb 6y ago

The last time ULID was mentioned in a thread it got some flack for its "weird" generator local monotonicity property. But it's actually pretty useful, when you're building database indices, because it reduces the amount of entropy that your index has, and is therefore a big help for succinct data structures, which occupy space proportional to the information theoretic lower bound up to a log factor. This can make the difference between a multi mb and multi gb index. Obscure feature, big gain.

fatsdomino001 6y ago

How does ULID compare to Instagram's bigint id_generator for postgres?[0] ULID is 128 bits vs 64 bits for instagram id, so ULID is stored in a uuidfield but instagram id can be stored in bigint field. There is some advantages of using the application code to generate the ID though (avoiding roundtrip to DB if need to use the ID afterwards), but I'm seduced by just offloading this work to the DB and being able to use bigint field to store the id. As someone who is just about to commit to using ULID or an instagram-like bigint id generator, would you have any thoughts you could share on which may be better? [0] https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c https://instagram-engineering.com/sharding-ids-at-instagram-...

sylvain_kerkour 6y ago
Is there any reasons to prefer BIGINT instead of UUID (other than the very few % of performance, which may never be a problem)? I just find instagram's ID complex (custom epoch, mandatory use of RETURNING) while Ulid is elegant (easy to generate and use). For me the biggest selling point was the interoperability with other kinds of UIDs, if later your requirements change. Also, note that (at least in Rust) Ulid are implemented as u128, so are pretty fast.
- fatsdomino001 6y ago
  I'll admit that I'm not actually sure on the performance difference between BIGINT and UUID in postgres. So in that sense you're right that the question is a tradeoff of x% of performance (which may never be a problem) but with added complexity vs ULID which is simple/elegant and signifcantly less cognitive overhead (easier to maintain) but will cause bigger indexes vs bigint. It's a tradeoff.. and your post has me thinking again that realistically the maintainability and agility that ULID provides may actually be better than a potentially error-prone but x% more performant instagram-like bigint generator. I just don't know enough to properly compare the two, so I suspect opting for the easier cognitive load / maintainability / agility is best.

Lazare 6y ago

I also spent some time evaluating them, and came to the conclusion that KSUIDs (https://github.com/segmentio/ksuid https://github.com/segmentio/ksuid) were strictly better than ULIDs. The differences aren't huge in practice, but the ULID spec has a couple of highly questionable choices. Specifically, according to the spec: 1. The first time you generate a ULID in a given millisecond on a node, you should fill in the random component randomly. 2. But if you generate more than one ULID per milliscond, subsequent ULIDs should use the first ULIDs random component and just increment the least significant bit. 3. If that would overflow, then you just can't generate any more ULIDs that millisecond. The goal is that ULIDs will be strictly sortable even when you're generating multiple per millisecond...except for that to work, you'd have to only generate them in a single thread (or at any rate on a single node). If you're just incrementing a numbers on a single node, then uh, why not just use an autoincrementing integer column in a DB? Of course, if you're generating multiple IDs per millisecond, you're probably using multiple nodes, and if you are, then wall clock skew means the IDs will never be strictly sortable. Except actually it's even worse than that; even if you ARE only using a single node, your code probably can't assume that every ULID it will ever encounter was generated in that way, so you still can't rely on the sorting guarantee. Meanwhile, a compliant ULID generator has to have state and, if it's going to be called in a multithreaded way, to deal with some complex race conditions, which makes it a lot harder to run. Meanwhile, you could get 100% of the benefits and none of the drawbacks by just...using random bits for the random component every time. Anyhow, the idea of slapping a timestamp and a random string of bits together is a good one. I just think the KSUID spec is a bit better thought out.

sylvain_kerkour 6y ago
The problem I had with KSUID is that they have a custom format, so not optimised in Postgres, while ULIDs are fully interoperable with other kinds of UUIDs. So if your requirements change later, it's just a change to the function generating your UIDs. Also I don't use monotonic generators, because in pratice, in my applications, it never make business sense to sort entities with a greater granularity than a millisecond, so even if I generate 10 entities' IDs in a millisecond, I will let the random part randomly sort the entities generated during this millisecond. Monotonic generators, are in my opinion only useful when you require absolute ordering, like with events, and thus rarely are a concern.

Dowwie 6y ago

I'm skeptical but can be swayed. A snowflake id can be represented as a BIGINT in postgres. I'm curious how a BIGINT contrasts with a UUID column in terms of storage and performance. If anyone has used a snowflake id in a production environment and ran into a unique id limit, please shout about it.

giancarlostoro 6y ago

I like MongoDBs ObjectIDs because they secretly maintain a timestamp, therefore... if you sort it sorts by that value. MongoDB isnt for every use case but when I do use it I try to take advantage of everything it has.