The perils of UUID primary keys in SQLite

yepyoukno

Perils of “UUIDv4”. Everyone knows that’s what UUIDv7 was really for, and you should always convert that to binary to optimize everything.

dumbledorf

Wait how is sqlite doing a million inserts a second?

blopker

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. 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.

w10-1

Isn't the solution just to use the rowid (after doing the read-id-after-insert dance)? How much trouble does SQLite reysing rowid's actually cause?

pyuser583

Oh gosh the ints v uuids debate for pks. This is worse than vim v eMacs or brackets v braces.

wood_spirit

If you need (or want the convenience of) a uuid and the time of creation is not secret then use ulids eg uuid v7.