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.
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.
How much trouble does SQLite reysing rowid's actually cause?
Regular rowids are definitely the way to go if you can use them.
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.