News for hackers. Stuff that matters.
Re:ULIDs and Primary Keys by ComputerGuru on Thursday December 29, 2022 @05:24PM (#34178898)
> While all of these formats can be generated by the client before inserting them into the database, for the purpose of simplicity and consistency, having them be generated within the database engine is preferred.

This very much misses the point of UUID/ULID pkeys. The whole idea is that you can generate them client-side without needing a connection to the database and assume that they will not conflict. This speeds up everything considerably allowing you to just write to the database once when generating new records without holding onto a sequence, without needing to worry about synchronizing ids between client & server, and without needing to worry about synhronizing sequences between multiple shards.

We've been using ULIDs with postgres in production for years without the database even being ULID-aware at all. The only thing you might want is support for a ULID type so you can manually draft queries with ULIDs directly (rather than having to use their binary equivalent as a UUID) and to display results of manual db queries with pkeys cast as Crockford base-32 ULIDs rather than UUID format.

Re:ULIDs and Primary Keys by jagged-chisel on Thursday December 29, 2022 @05:29PM (#34178948)
>> ... or the purpose of simplicity

Yeah, it's more simple.

> The whole idea is that you can generate them client-side ...

And this is not precluded in the article. One may, at INSERT, also provide the ID. But if you don't it's not an error - the database chooses a default for you.

Re:ULIDs and Primary Keys by drewcoo on Thursday December 29, 2022 @07:40PM (#34180130)
> generate them client-side without needing a connection to the database

Or in multiple write shards of the db without worrying about whether you've suffered a network partition.

Whether it makes sense for application code or the db itself to generate them . . . depends.

Re:ULIDs and Primary Keys by valzam on Friday December 30, 2022 @02:34AM (#34182804)
One thing to be aware off is that the ULID spec only guarantees ordering up to millisecond precision. Most implementation additionally guarantee ordering of the 80 random bits for absolute ordering but only IN PROCESS. If you have many clients concurrently producing data and generating ULIDs you can run into out-of-order rows. If this matters or not is ofc application specific.

One use case of ULIDs is as event IDs in distributed systems. If the producers are horizontally scaled Microservices you might generate events with reversed ULIDs compared to their logical order.

Ordering in distributed systems is anyway very hard but be aware that ULIDs are also no silver bullet

Re:ULIDs and Primary Keys by lukevp on Friday December 30, 2022 @03:08AM (#34183007)
I think this is a bit of a red herring. ULIDs are not meant as a replacement for a lamport timestamp or something. The accuracy to the millisecond would only be correctly ordered if the clocks were perfectly synchronized anyway. I interpret the purpose of the timestamp based id as allowing denser clustering in b-trees vs random key data, and that’s about it. The timestamp can only be used as a relative ordering when processing multiple events from the same client, and even then not completely (client clock could change).