UUID Primary Keys in SQLite: A Performance Liability

Anders Murphy's analysis details the significant performance and storage penalties of using UUIDs as primary keys in SQLite, offering founders critical insights into database design.

The Answer Up Front

For developers building with SQLite, using UUIDs as primary keys is a poor choice that introduces substantial performance and storage overhead. The article by Anders Murphy provides a clear, data-backed argument against this common practice, demonstrating that sequential integer primary keys or specifically designed sequential UUIDs (like UUIDv7) are vastly superior. Founders should avoid UUIDs for primary keys in SQLite unless their application has an extremely strong, verified requirement for global uniqueness that outweighs significant performance degradation.

Methodology

This v0 review draws exclusively on Anders Murphy's published claims and findings in his article, "The perils of UUID primary keys in SQLite," accessed on June 6, 2026. The article itself serves as the primary signal. Murphy's analysis focuses on the technical reasons behind UUID performance degradation in SQLite, supported by reported benchmarks on insert operations. This review covers the author's stated problem definition, the technical explanations for performance issues, and the proposed alternative strategies. It does not include independent performance benchmarks, long-term workflow implications, or an exhaustive analysis of edge cases beyond what the article presents. Independent verification of the reported performance numbers is pending and will be a focus for future updates.

• Tool/Pattern Name: UUID Primary Keys in SQLite
• Version/Context: SQLite (specific version not detailed in source, but general architectural principles apply)
• Date Observed: June 5, 2026 (article publication date)
• Source Signal URL: https://andersmurphy.com/2026/06/05/the-perils-of-uuid-primary-keys-in-sqlite.html
• What's Covered: Founder's claims regarding performance characteristics, technical explanations for observed behavior, and recommended design patterns.
• What's NOT Covered: Independent performance benchmarks, real-world application workload testing, or long-term operational costs beyond direct performance.

What It Does

Anders Murphy's article dissects the common practice of using Universally Unique Identifiers (UUIDs) as primary keys within SQLite databases, exposing the underlying technical reasons for their poor performance. UUIDs are widely adopted for their global uniqueness, enabling distributed systems to generate identifiers without coordination. However, the article argues this benefit comes at a significant cost in SQLite.

Randomness and B-Tree Performance

The core issue stems from the inherent randomness of standard UUIDs (like UUIDv4). SQLite uses B-trees for its primary key indexes. When new rows with random UUID primary keys are inserted, they are not appended sequentially. Instead, they are inserted at arbitrary locations throughout the B-tree. This forces frequent page splits and rebalancing operations, leading to a highly fragmented index. The author claims this fragmentation increases disk I/O, reduces cache efficiency, and significantly slows down insert operations.

Observed Performance Degradation

Murphy reports stark performance differences. For example, inserting 100,000 rows with UUIDv4 primary keys was reportedly 20 times slower than inserting rows with sequential integer primary keys. The storage footprint also suffered, with the UUIDv4 table consuming 2.5 times more disk space than its integer-keyed counterpart for the same number of rows. These numbers highlight the tangible impact on both speed and resource utilization.

Proposed Solutions

The article offers actionable alternatives. The primary recommendation is to use sequential integer primary keys whenever global uniqueness is not strictly required across disconnected systems. For scenarios where UUIDs are necessary, the author suggests UUIDv7, which incorporates a timestamp component, making them largely sequential. This sequentiality allows new records to be appended to the end of the B-tree, minimizing fragmentation and restoring performance closer to that of integer primary keys. Another strategy involves using composite keys or storing UUIDs in a separate indexed column while retaining an integer primary key for internal SQLite operations.

What's Interesting / What's Not

What's particularly interesting about Murphy's analysis is its direct, technical explanation of a common pitfall. The article moves beyond anecdotal observations, detailing why UUIDs perform poorly in SQLite by explaining B-tree mechanics, page splits, and cache misses. This level of detail is crucial for founders and engineers making database design decisions, as it provides a foundational understanding rather than just a prescriptive warning. The reported performance numbers, while currently unverified by us, are compelling enough to warrant immediate attention for anyone considering UUIDs in SQLite.

What's less novel is the general concept of random primary keys causing B-tree fragmentation; this is a known issue in many database systems. However, SQLite's architecture, particularly its single-file nature and common use cases (e.g., embedded databases, local-first applications), makes this problem especially acute and often overlooked by developers accustomed to more robust, server-grade databases. The article does not delve into the complexities of distributed SQLite setups or how these UUID strategies might interact with replication, which could be a relevant consideration for certain advanced use cases.

Pricing

N/A. This review concerns a database design pattern, not a commercial tool with associated pricing. SQLite itself is public domain software.

Verdict

Our verdict is clear: avoid using random UUIDs as primary keys in SQLite. The performance and storage penalties, as detailed by Anders Murphy, are too significant to ignore for most applications. The reported 20x slowdown in inserts and 2.5x increase in storage are critical factors for any founder optimizing for resource efficiency and responsiveness, especially in mobile or embedded contexts where SQLite shines. Opt for sequential integer primary keys by default. If global uniqueness is a non-negotiable requirement, UUIDv7 offers a viable compromise, mitigating most of the performance drawbacks by introducing sequentiality. This is not a nuanced "it depends" situation; the data, as presented, strongly favors sequential keys in SQLite.

What We'd Test Next

Our next steps would involve independently replicating Anders Murphy's benchmarks across various SQLite versions (e.g., 3.39, 3.45) and on different hardware configurations, including both SSD and traditional spinning disk environments to quantify I/O impact. We would also expand testing beyond simple inserts to include read performance on fragmented versus optimized tables, and update operations. Furthermore, we would investigate the performance characteristics of UUIDv7 in more complex schema designs and under concurrent write loads. A detailed comparison of the VACUUM command's effectiveness in reclaiming space and improving performance on UUID-fragmented tables would also be valuable.

The investor read

The detailed analysis of UUID performance in SQLite highlights a critical, often overlooked, aspect of database design for local-first and embedded applications. As the trend towards edge computing and client-side data management accelerates, SQLite's role becomes more central. Tools and patterns that optimize its performance directly impact user experience and operational costs for a vast array of applications, from mobile apps to IoT devices. This signal indicates a continued need for foundational engineering knowledge and best practices, even for seemingly simple components. Companies building developer tooling or ORMs that abstract database interactions could gain significant traction by explicitly guiding users toward optimal primary key strategies for SQLite, potentially offering features that automatically implement sequential IDs or UUIDv7. The market rewards solutions that prevent hidden performance traps, especially in resource-constrained environments.

Pull quote: “For example, inserting 100,000 rows with UUIDv4 primary keys was reportedly 20 times slower than inserting rows with sequential integer primary keys.”