loading...

re: What would you use as a sortable, globally unique, ID? VIEW POST

TOP OF THREAD FULL DISCUSSION
re: I like the lateral thinking going on in your question :) That's the status quo though, which works decently but it still requires two fields. Wha...
 

Hehe. Then what about the brute force approach, concatenation of timestamp and UUID, using an intermediate character guaranteed not to occur in one of them?

Edit: lexical and chronological sorting of ISO8601 date times is identical. So if you start with that, then the UUID, does that solve the problem?

Then what about the brute force approach, concatenation of timestamp and UUID, using an intermediate character guaranteed not to occur in one of them?

Something like this could work :)

You're really close to the solutions I found searching on the internet, take a look at github.com/segmentio/ksuid for example

You would have to perform more complex comparisons to see if ids match, since you lose the binary representation. v1 and v1mc UUIDs are sort of ordered but not necessarily between multiple nodes, and they cycle fairly rapidly.

Jimmy Nilsson did an interesting experiment back in the day with adding the timestamp inside the UUID itself.

First: it's a little bit funny to read an almost 20 years old article that talks about RAM in order of megabytes :D

I love how hacky his solution was, though it's a little bit weird that the timestamp is at the end, but that's just the order of the two casts.

This part was funny as well:

A DATETIME has the "precision" of 1/300th of a second, and it's no problem for SQL Server to generate five GUIDs in 3ms with this algorithm

5 IDs in 3 ms. Oh, the year 2000 :D

Thanks for linking this, it was such a cool read!

As an aside I wonder how slower the uuid type in PostgreSQL is in comparison to integer primary keys, today.

That’s actually what I meant: concatenation of a timestamp with a UUID to generate a new UUID. Or maybe I just don’t understand your comment :-)

When you said "intermediate character" I took it to mean a string 1567876931|11aca736-c9a9-4266-a2f6-13bd07202c09 or some such.

That’s what I meant. I wasn’t aware of the restrictions placed on UUIDds until now that I looked it up. I just thought it meant “universally unique”, which would be the case with my proposal, I think. So thanks. One more thing I learned.

Code of Conduct Report abuse