Clojurians Log v2

it seems you can use different _id types in the same table, any problems with that?

if I have a table with UUID and integer ids, then I can't use non-equality comparisons (like > ) where clause, as I get

ERROR:  class java.lang.Long cannot be cast to class java.nio.ByteBuffer (java.lang.Long and java.nio.ByteBuffer are in module java.base of loader 'bootstrap')

makes sense, probably not a good idea to have different _id types in the same table

UUIDv4 is the default recommendation for _id, per https://github.com/xtdb/xtdb/blame/3ca573544b121860977710912f1289b383724948/docs/src/content/docs/intro/data-model.adoc#L13 Integers are really only used in the examples for brevity. You can cast to strings if you need to compare across different _id types The exhaustive list of currently supported _id types hasn't been documented yet but we'll fix that

> are there any restrictions on what can be used? yes, there's a spec in the code somewhere that I'll dig out. we started very restrictive on this and haven't yet released the ropes again 🙂 anything else you'd particularly want?

> it seems you can use different _id types in the same table, any problems with that? nope, should be the same as putting different types in any other col 🙂

> if I have a table with UUID and integer ids, then I can't use non-equality comparisons (like > ) where clause that's true, but I was expecting a different error message there - we usually get 'compare not supported on types integer and uuid'

well UUID makes sense for many tables, some could have a DATE id as well (one entry per day type of things)

but yeah, as @taylor.jeremydavid says, best to use UUIDv4s if you've no other preference - in spite of all of the usual database warnings about "don't use random UUIDs in indices!", "if you must use UUIDs make sure they're the timestamp ones" etc - XT works best with a well distributed id space

but for integers you'd really need a sequence/auto_increment functionality

so random UUIDs are actually the best id type for xtdb?

I was searching for some discussion of _id in the documentation, but couldn't find it. Best practices for those would be valuable in the docs

yep, contrary to all the usual database advice 🙂 it's because the index we keep on _id is a hash trie rather than the usual b-tree

you can also (advanced, optional) use that to your advantage if you curate the first few bits/bytes of the UUID to be a partition key - e.g. if you have a parent/child relationship between entities, giving the child UUID a few bits from the parent's UUID will naturally group all of the children for a given parent in the index; they'll be co-located

definitely one for a best practices doc

hmm... wrangling bits in a UUID might be cumbersome

yeah, agreed, partly why we haven't promoted it as a recommendation as yet

needs some utilities, really

now that we have a database actually working E2E with reasonable performance and stability it's time for us to start dotting these i's and crossing these t's

or even a database supported "type" for it

ooh, I wonder what that would look like...

🤷

but some blessed "best" ID type would be neat

also what I would like, in addition to the SQL time travel, would be a git like "time proof" reference type

I mean, we could also go for supporting composite IDs, getting the user to declare it with lightweight DDL (obv keeping to our schemaless roots)

like, "references table X, row Y at tx Z" in a neatly packed single value

so yeah, in git that's objects with hashes... ok... 💭

o..k..

looks like a github employee. we're investigating.

confirmed, was a github employee. working on getting it reinstated

just what you need on a wet Wednesday! 🤯

wth, github employees can delete your repos at will?

evidently!

the guy was only trying to delete a test security advisory

scary

repo mostly reinstated, few details still to iron out but looks like no loss of data. heart rates slowly descending 😵‍💫

that is... scary; thats an HN/Lobsters front-page bait if i've ever seen one

ha, if you'd asked me a couple of hours ago you could probably have tempted me, but thankfully calm prevailed 🧘‍♂️ there's a nice cold beer in the fridge with my name on it for the end of the day though 🍺