The perils of UUID primary keys in SQLite
(andersmurphy.com)
from cm0002@suppo.fi to programming@programming.dev on 06 Jun 15:43
https://suppo.fi/post/12628930
from cm0002@suppo.fi to programming@programming.dev on 06 Jun 15:43
https://suppo.fi/post/12628930
#programming
threaded - newest
awesome!
but seriously who uses UUID as their primary keys? what’s wrong with plain old INT. it’s more predictable you can look at the integer primary key and tell which records created when relative to each other. also you can easily remember them. unlike UUID.
In most cases it’s more useful they’re not predictable, and I’m definitely not remembering private keys myself, so what’s the point. You can have preservation of creation order with UUID v6 and v7.
When you have an application that the client MAY make a record during offline and then upload it after the network connection is re-established.
Lemmy sure loves swallowing my reply. To answer your question shortly is an offline first app where the user can add records and the relation during offline and then sync it later after connection is re-established.
the irony of this comment 😅
Distributed systems where the identity isn’t provided by the DBMS need UUIDs. I don’t really get why you’d use uuids in sql, except in the case mentioned where the client is bringing its own ids
Using integer primary keys often creates race conditions when you have multiple database shards, so UUIDs have become the standard. (2 different webservers can create a record in 2 different database sync that then sync with eachother in the background). Using UUIDs for SQLite is less common though as SQLite mostly used for small or local applications but developers are used to UUIDs now and even consider them the standard for primary keys now.
It’s weird their main reason is performance, but then proceed to benchmark SQLite. Who’s inserting 10s of millions of records per minute on sqlite?
Even in production, client-server DBMSs, I’d wager that there are plenty of other things that dominate performance before you even get near your choice of a primary key, so it probably doesn’t matter until you get a large enough throughput in your database.
I would say their findings would be pretty consistent no matter which DBMS you use.
I’m glad this covers UUIDv7. Unless you need cryptographically-random, unpredictable row IDs, using UUIDv7 helps a lot with the performance by making the keys ordered at least.