Hacker News Reader

Comment by ComputerGuru | Hacker News Reader

See ulid for similar prior art. We generate Ulids in code and then store them in uuid columns in Postgres to realize the compact (binary) size benefits.

https://github.com/ulid/spec

View on HN

I put in the work to store ULIDs in SQL Server's interesting UUIDv1-based sort order for uniqueidentifier columns. It seems to be providing the clustered index benefits I was hoping for so far (far less clustered index churn behavior than v4 Random UUIDs).

An interesting digression to bring up with respect to this RFC because it is interesting to note that none of the proposed v6, v7, v8 UUIDs match the SQL Server sort order behavior and would still have a lot of clustered index churn unless new sort behaviors were allowed to be opted in. That might be something that this RFC would help to address by also making sort orders a bit more standard, though it does not directly make recommendations on that front.

For what it is worth, it is the SQL Server sort order that is weird: it sorts the last group of the UUID string form first, which in v1 UUIDs corresponded to the MAC Address. Clustering by machine probably made sense by whoever decided on that sort order, at the time, but even with v1 UUIDs they probably would have got better results sorting by timestamp than by machine.

(The ULID timestamp fits exactly into that "MAC address" group and it's just a matter of swapping the front 6 bytes to the back of the UUID when storing to uniqueidentifier.)

Fun reference on UUID sort orders: https://devblogs.microsoft.com/oldnewthing/20190426-00/?p=10...

It’s not just a question of whether you want to cluster by MAC address, it’s more of a fundamental disagreement as to the endianness of the UUID groups themselves, which is probably the biggest difference between a GUID and a UUID in the wild. Microsoft has used GUIDs to brand OS intervals for a long, long time - long enough that there’s a chance they came before UUID was a thing, but I haven’t dug into the history of it.

Unfortunately converting between them without any db extensions is a PITA in the absence of native support. I’ve written about it at length here (SQLite in the example, but the fundamentals apply regardless): https://neosmart.net/blog/2018/converting-a-binary-blob-guid...

That was the question specific to SQL Server storage sort order for clustered indexing and most relevant to ULID to uniqueidentifier "conversion"/storage, but yes there are endianness issues in the sort orders as well, which is why I made sure to include the full Raymond Chen blog post on many of the most common sort orders for GUIDs because it is quite the rabbit hole. (The "update" footnote link at the bottom of the article I posted to the Java order is especially wild if you fall into the rabbit hole.)

(ETA: Also to answer your indirect question, Microsoft was definitely an early adopter of GUID/UUID. Though the inventor was Apollo Computer as a part of their complex network design: https://en.wikipedia.org/wiki/Apollo_Computer)

Thanks for clarifying - perhaps another reader will find that link for in-db conversion useful. And thanks for the interesting information and link to the Apollo Computer; I have some fodder for bedtime reading now!

No problem. Yeah, for what it is worth (getting further aside), I gave up on supporting the ULID conversion directly in-db for that same reason: Base-32 math (which ULID uses for canonical string format) is tough enough in SQL before you add in the endian issues. The endian issues aren't as big of a deal in my storage sort order situation because the MAC address section that I use for the timestamp data most critical to the sort order doesn't suffer from the endian issue and the remainder of a ULID is entirely random and I'm not too concerned if the random section doesn't sort identically to the ULID sort order. (It would be great if it did, but it's not as critical as the more dominant timestamp order.) That said, .NET's GUID ToByteArray() handles the endian order for me and I'm somewhat confident it's correct so long as it is my .NET code doing ULID-to-uniqueidentifier conversions and not the scripts I tried to use directly in-db.

I’m using Postgres so I actually have it a little easier with its native sort order. I switched from NUlid that I was using from years back to this one [0], and the default .ToGuid() gets the desired sort order when used with Npgsql. Likewise though, no db-side generation of uuid - then again, isn’t that the benefit of using globally unique identifiers in the first place, to avoid needing to rely on the db to generate conflict-free identifiers, skipping HiLo or sequences, etc.

Anyway, nice to meet another .NET Ulid user in the wild!

0: https://github.com/Cysharp/Ulid

Yeah, it's a small world.

I actually took the opposite path and explored several of the micro-optimized ULID libraries in C# before settling down with NUlid which I decided had the cleanest API and documentation (and reasonably good response times to GitHub issues). The implementation of Cysharp's lib has a lot of Unsafe code, which I understand why they took that path (given the focus on Unity code), but I'm hesitant about it and I liked a lot better (conceptually) how much more Span<T> code mcb2001's CSharp.Ulid library had and if I were to micro-optimize a library that's much more the path that I would take. (But so far micro-optimizations of that sort haven't been a priority in my real world usage and I'll take the well tested library with the nice API.) I created a couple simple extension methods to do the specific "ToSqlGuid()" and "ToUlidFromSql()" that I desired for the SQL Server sort order. (Which is just shuffling the first six bytes of a ULID byte array to the end of the GUID byte array and vice versa.)

I actually trust that I can at least generate ULIDs in SQL Server, again because the part I care to be sorted/needs to be correct (the timestamp) fits into the non-endian-issue portion of a GUID in SQL Server. I've been using just the generator function from ulid-mssql [1] (I used it mostly just for DB migrations and it was nice to have for that). It was debugging the endian issues and Base-32 encoding issues of the "ULID GUID to string" functions where I gave up and decided I didn't trust doing that in database.

[1] https://github.com/rmalayter/ulid-mssql

What's a bit interesting to me is having read all the negativity/criticism around ULID, and now seeing these proposals by IETF...

Most ulid criticism surrounds the ill-advised (and optional!) part of the spec that tries to achieve sub-ms ordering at the cost of requiring a singleton or cross-thread atomics to try and generate random values for the random portion of the blob from a range that would end up providing additional sort order bits. The reference implementation in JS doesn’t even offer this (although that’s likely because JS doesn’t even support multiple execution threads within the same context).

Apart from that little misadventure (last I checked, there was a request for comments on a proposal to drop that from the Ulid spec altogether) there’s really nothing else that’s not same and straightforward in the spec, and little else to complain about.

They list ULID and 15 other implementations in the document.

Ah, so they do. Thanks.

Reply on news.ycombinator.com