4 ms·

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 in

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.

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.