4 ms·
> 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 "optimisat
by 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 agoThe 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.