The perils of UUID primary keys in SQLite
The article examines the performance implications of using UUIDs as primary keys in SQLite databases. Unlike auto-incrementing integer keys, UUIDs are randomly distributed, which disrupts the natural ordering of B-tree indexes. This causes excessive page splits as new rows are inserted into random positions, leading to index fragmentation and wasted space. The author benchmarks a large table, finding that UUID primary keys result in query times significantly slower than integer keys for range scans and point lookups. Additionally, the larger key size increases memory and storage overhead, and the lack of locality in UUIDs degrades cache performance. The article suggests alternatives such as using sequential UUIDs (e.g., UUID v7) or maintaining a separate integer rowid with a UUID column as a secondary index. It also notes that SQLite's lack of clustered indexes exacerbates the issue compared to other databases.
Using UUIDs as primary keys in SQLite can silently cripple performance at scale.