The perils of UUID primary keys in SQLite
4 hours ago
- #Database Performance
- #SQLite
- #UUID
- UUID4 primary keys cause significant performance degradation in SQLite due to unordered inserts leading to B-tree rebalancing, making operations 10-12 times slower.
- SQLite uses rowid as an implicit clustered index for regular tables, while WITHOUT ROWID tables use the declared primary key as the clustered index.
- Benchmark results show UUID4 inserts slow dramatically as row count increases, unlike integer primary keys which maintain consistent performance.
- Profiling reveals increased time spent on B-tree balancing, reading, and writing with UUID4 due to random ordering.
- UUID7, being time-ordered, eliminates the ordering issue and restores performance close to baseline, though slightly slower due to larger key size.
- The post emphasizes the importance of key ordering in clustered indexes to avoid performance pitfalls, particularly in SQLite and similar databases.