UUID v7 so far seems like the best solution if you want UUID benefits and ordering.
The Integer id is used for joins and looks ups and such but that's it. If I need to send anything to the frontend or outside of the app/DB then that's the UUID.
How much trouble does SQLite reysing rowid's actually cause?
Regular rowids are definitely the way to go if you can use them.
Contention and coordination are real killers, concurrent writes (that require coordination like postgres) often underdeliver.
I disagree. I tried this once. Now you need a client access layer to touch the DB in any context. All your console tools no longer work well or at all. If they show up in URLs you need to deoptimize them for transport.
You give up a lot of convenience for this optimization. You should be absolutely sure your design requires it before using it.
(defonce db
(d/init-db! "db/db.db"
{:pool-size 4 :pragma {:synchronous "FULL"}}))
There's only one index so there's no real write amplification. The numbers will go down as you add more data and indexes.
For a single database, bigints are smaller and faster, with less footguns.
UUIDs can be nice for an opaque public ID, however I'd still prefer something like a Sqid for space and usability.
But be careful!! Javascript WILL interpret your bigints as Number() and round them down because they are too big without telling you!!!
Famously seen by every snowflake user that has interacted with Javascript, quite an annoying problem.
Then it will always be a string and you will be free to change the format/type of the key in the future to UUID or whatever you like.
In JS - BigInt is 64bit integer.
In anything else - BigInt is a arbitrarily large integer.
Node.js drivers will correctly read int64 as string or bigint, not number.
E.g. pg for PostgreSQL
Maybe there’s a buggy driver but I don’t know it.
It does not actually make it impossible to query the wrong table it just tells you quickly when you’ve done so.
Inappropriate aliasing of integer keys allows for silent errors in queries because it will actually return some result a lot of the time. A UUID is immune to this problem. The model recognizes its mistake a lot more reliably when previously non-empty tables start showing up empty after attempting a join.