The perils of UUID primary keys in SQLite
andersmurphy.com
The perils of UUID primary keys in SQLite
1–10 of 117 posts
Re: The perils of UUID primary keys in SQLite
#2Perils of “UUIDv4”. Everyone knows that’s what UUIDv7 was really for, and you should always convert that to binary to optimize everything.
Re: The perils of UUID primary keys in SQLite
#3Wait how is sqlite doing a million inserts a second?
Re: The perils of UUID primary keys in SQLite
#4Wait how is sqlite doing a million inserts a second?
sqlite is really fast. I'm surprised it's only a million.
Re: The perils of UUID primary keys in SQLite
#5UUIDs 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.
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.
Re: The perils of UUID primary keys in SQLite
#6Wait how is sqlite doing a million inserts a second?
':memory:'
Re: The perils of UUID primary keys in SQLite
#7Wait how is sqlite doing a million inserts a second?
In batches
Re: The perils of UUID primary keys in SQLite
#8Perils 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.
Re: The perils of UUID primary keys in SQLite
#9Perils 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.
Oh yes, I meant don’t store as an ID in its string format!
Re: The perils of UUID primary keys in SQLite
#10UUIDs 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. 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…
[deleted]