Okay so apparently (this is new info for me) Postgres is fine with having a uuid as the primary key, but databases generally prefer primary keys to be monotonically increasing because it improves performance.
As far as I can tell, if you use UUID as your data type in Postgres, then this satisfies both conditions as long as you use v1 of UUID, which is based on a timestamp (someone correct me if I'm wrong plz).
HOWEVER, although Postgres might interpret UUIDs as sequential, when you serialize the UUID as a string and compare it to another serialized UUID, they can't be sorted alphabetically because the timestamp is encoded in the UUID in such a way that all the bytes get mixed up.
This means that you can't just slap in v1 UUIDs and have something like Tusky or Pinafore be happy with sorting them, because they sort them as a *string* and not as a v1 UUID.
So now I have to find a way to:
1. Keep using UUIDs as the primary key because they're neat and I like them.
and 2. Serialize them through the frontend API in such a way that they can be sorted alphabetically by Tusky AND don't completely mess up url-encoding.
It looks like this library might be worth investigating, but the presence of special characters in the output is a bit of a setback.
https://github.com/team-spectre/go-uuid
Long post
Looks like I might end up writing my own little library purely for serializing v1 UUID's in a sort-friendly way for frontends. Can't be that hard right?
Oh god, but ofc I have to be able to deserialize them as well to respond properly to API requests for the funky not-quite UUIDs.... okay.
Time to sleep.
What I really need is this thing that generates timestamp-first UUIDs, but ported into go.
fuchsiaaaaaaaaaaaaaaaaa
@tobi so it seems that rearranging the UUIDv1 is unsupported but I think it's kind of a wack design to have them aligned in this direction in the first place?
so I would use the principle of UUIDv1 but re-arrange the timestamp to be logical (and thus, sortable). Other applications shouldn't have to interact with them as proper UUIDv1's anyways because the time info (and other status info) is already encoded separately/properly in requests?
https://stackoverflow.com/questions/44356237/sortable-uuid-v1-for-multi-platform-application
@f0x and yes indeed other apps shouldn't have to care whether they're a 'proper' uuid or not, it just needs to be some kind of sortable string. Even a timestamp with a totally random uuid tacked on the end would do the job :')
@f0x yep, that seems like it might be the way to go. Indeed it's kinda crappy design to start with millis in the v1 uuid 😬 not sure what the rationale was there..