UUIDs are way over used. There is almost always a better key to use, usually a bigint for databases. If you're making some kind of leaderless distributed data store, then maybe, but even then there are other ID sharding strategies I'd go for first depending on the constraints.<p>For a single database, bigints are smaller and faster, with less footguns.<p>UUIDs can be nice for an opaque public ID, however I'd still prefer something like a Sqid for space and usability.
> bigints are smaller and faster, with less footguns<p>But be careful!! Javascript WILL interpret your bigints as Number() and round them down because they are too big without telling you!!!<p>Famously seen by every snowflake user that has interacted with Javascript, quite an annoying problem.
Fortunately we're seeing more JS DB libraries offering to read large numbers as the BigInt type.
!!<p>Node.js drivers will correctly read int64 as <i>string</i> or <i>bigint,</i> not number.<p>E.g. pg for PostgreSQL<p>Maybe there’s a buggy driver but I don’t know it.
Yes this matters even more if you are doing a lot of joins. Naive string UUIDs are 32 bytes (though I use binary uuid in the post which is 16) compared to 8 bytes for a 64-bit int. This matters even more with sqlite as it uses varint encoding. The upshot of all this is your indexes take up a lot less space in memory.
UUIDs also have a nice benefit of it being impossible to query the wrong table with one if you mixup what an FK goes to
I am finding UUIDs help a lot if your primary schema consumer is an LLM.<p>Inappropriate aliasing of integer keys allows for silent errors in queries because it will actually return <i>some</i> 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.
No one ever got fired for using UUIDs
Is this relevant for other databases? For postgres for example, which supports concurrent writers, wouldn't sequential keys lead to contention on the page at the frontier?
This is actually a draft. I Wanted to add more details about how this changes with row size etc. I might get time to update it later today.
Oh gosh the ints v uuids debate for pks. This is worse than vim v eMacs or brackets v braces.
Isn't the solution just to use the rowid (after doing the read-id-after-insert dance)?<p>How much trouble does SQLite reysing rowid's actually cause?
If you need (or want the convenience of) a uuid and the time of creation is not secret then use ulids eg uuid v7.
Perils of “UUIDv4”. Everyone knows that’s what UUIDv7 was really for, and you should always convert that to binary to optimize everything.
Small nit: uuid7 is 128 bits (16 bytes) by definition. So there’s no need to convert it to binary. It already is. Unless you’re working with a stringified version of the uuid7.
> and you should always convert that to binary to optimize everything<p>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.<p>You give up a lot of convenience for this optimization. You should be absolutely sure your design requires it before using it.
Wait how is sqlite doing a million inserts a second?
In batches
sqlite is <i>really fast</i>. I'm surprised it's only a million.
':memory:'<p><a href="https://sqlite.org/inmemorydb.html" rel="nofollow">https://sqlite.org/inmemorydb.html</a>