5 ms·

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

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.

Lazare 6y ago
> The problem I had with KSUID is that they have a custom format, so not optimised in Postgres As far as I'm aware, Postgres doesn't really have any "optimisations" for UUIDs. The type just provides some help for normalising and converting them to and from their canonical form. The only DB I know of with actual UUID optimisations is MS SQL, and that would be a bit of a trap, because its optimisation is to re-order the bits so the timestamp part of a classic UUID is at the front when it's stored...which means trying to store a ULID as a UUID in MS SQL would actually be slower than storing it as plain byes.
- ComputerGuru 6y ago
  The ordering of bytes within UUID/GUID types is one of the differences between the two, and also causes no end to grief when importing data across systems (or from hex representation to binary). I wrote about some of the pitfalls and how to properly convert MS guid types to hex for interop with, eg, SQLite here: https://neosmart.net/blog/2018/converting-a-binary-blob-guid-to-text-in-sql/ https://neosmart.net/blog/2018/converting-a-binary-blob-guid... The difference in big vs little endian can cause huge problems in practice aside from just not having identical values - as you note, the order of the random part and the timestamp part is switched around, which will actually cause guids to lose their randomness and cluster around not just the time but the inverted time - the last part of which may often even be null.
ComputerGuru 6y ago
The monotonicity component of ULID is - confusingly - an optional part of the spec (which makes the spec weird). There is discussion to drop this component from the official spec altogether.

Lazare 6y ago

> The problem I had with KSUID is that they have a custom format, so not optimised in Postgres As far as I'm aware, Postgres doesn't really have any "optimisations" for UUIDs. The type just provides some help for normalising and converting them to and from their canonical form. The only DB I know of with actual UUID optimisations is MS SQL, and that would be a bit of a trap, because its optimisation is to re-order the bits so the timestamp part of a classic UUID is at the front when it's stored...which means trying to store a ULID as a UUID in MS SQL would actually be slower than storing it as plain byes.

ComputerGuru 6y ago
The ordering of bytes within UUID/GUID types is one of the differences between the two, and also causes no end to grief when importing data across systems (or from hex representation to binary). I wrote about some of the pitfalls and how to properly convert MS guid types to hex for interop with, eg, SQLite here: https://neosmart.net/blog/2018/converting-a-binary-blob-guid-to-text-in-sql/ https://neosmart.net/blog/2018/converting-a-binary-blob-guid... The difference in big vs little endian can cause huge problems in practice aside from just not having identical values - as you note, the order of the random part and the timestamp part is switched around, which will actually cause guids to lose their randomness and cluster around not just the time but the inverted time - the last part of which may often even be null.

ComputerGuru 6y ago

The monotonicity component of ULID is - confusingly - an optional part of the spec (which makes the spec weird). There is discussion to drop this component from the official spec altogether.