The Perils of UUID Primary Keys in SQLite
Using random UUIDs as primary keys in databases with clustered indexes—specifically SQLite WITHOUT ROWID tables—can lead to severe performance degradation. The primary cause is the unordered nature of UUIDv4, which forces the database to insert rows randomly into the B-tree, triggering constant re-balancing and excessive paging.
The Impact of Random UUIDs on SQLite Performance
Random UUIDs (UUIDv4) cause significant write latency in SQLite when used as the clustered index. In a benchmark inserting 100 million rows in batches of 1 million, the performance difference between sequential integers and random UUIDs is stark:
- Integer Primary Key (Baseline): Approximately 1 million inserts per second.
- UUIDv4 (WITHOUT ROWID): Inserts are 14-16x slower than the integer baseline.
Profiling via diffgraphs reveals that the performance drop is not due to the UUID generation itself, but rather the time spent balancing the B-tree, reading, and writing. Because UUIDv4 values are not sequential, SQLite cannot simply append data to the end of the table; it must find the correct physical location for each random key, leading to high I/O overhead.
Comparing UUID Strategies in SQLite
Different primary key strategies offer varying trade-offs between write performance, storage efficiency, and architectural flexibility.
UUIDv7: The Time-Ordered Alternative
UUIDv7 solves the ordering problem by incorporating a timestamp, making the IDs sequential. Benchmarks show that UUIDv7 WITHOUT ROWID tables return performance to a reasonable level, though they remain slightly slower than integer primary keys. This is largely because UUID blobs are 16 bytes, whereas integers are 8 bytes, increasing the overall data footprint.
UUIDv4 WITH ROWID
Using UUIDv4 in a standard SQLite table (with the implicit 64-bit rowid) keeps the clustered index sequential. However, this introduces write amplification because the database must maintain two indexes: the sequential rowid and the non-clustered index for the UUID primary key. This approach is slower than UUIDv7 WITHOUT ROWID because the database still has to perform random insertions into the non-clustered index.
Summary of Benchmark Results
| Strategy | Performance Relative to Baseline | Primary Cause of Latency |
|---|---|---|
| Integer PK | Baseline (Fastest) | N/A |
| UUIDv7 (WITHOUT ROWID) | Slightly slower than baseline | Larger key size (16 bytes vs 8 bytes) |
| UUIDv4 (WITH ROWID) | Significantly slower | Write amplification from maintaining two indexes |
| UUIDv4 (WITHOUT ROWID) | 14-16x slower | Constant B-tree re-balancing and paging |
Architectural Considerations and Trade-offs
While sequential integers are the most performant, they are not always suitable for all use cases. Community discussion highlights several key considerations:
- Distributed Systems and Syncing: Sequential integers are ideal for local-only storage. However, for applications that need to sync across multiple devices or merge data from different instances, a high-quality sequential random ID (like UUIDv7) is essential to avoid collisions during table merges.
- Security and Information Leakage: Sequential integers disclose the total count of records and the identity of neighboring records. UUIDv7 also discloses the creation timestamp. If total secrecy of the ID is required, UUIDv4 may be acceptable if insertion performance is not a critical bottleneck.
- The "Internal vs. Public" ID Pattern: A common architectural recommendation is to use a sequential integer as the internal primary key for joins and lookups, while maintaining a separate UUID column for public-facing identifiers to avoid exposing internal database structures.
"Random ID primary key is a bad idea period, whether it be the UU kind or the SQ kind, or any other kind. As far as my DB knowledge goes, this class of ID destroys all tree-algorithms."
Technical Definitions
- Clustered Index: An index that determines the physical storage order of rows in a table. In SQLite, the
rowidis the default clustered index. InWITHOUT ROWIDtables, the declared primary key becomes the clustered index. - B-Tree: The data structure used by SQLite to store table data. Random insertions into a B-tree force the engine to re-balance the tree to maintain order, which is computationally and I/O expensive.