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.
> 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.
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
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).
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.