Why You Should Avoid UUIDv4 as Your Primary Key

In the world of database design, the choice of primary key is foundational. It influences everything from data integrity to application performance. Universally Unique Identifiers (UUIDs), particularly version 4 (random), have gained popularity for their ability to generate unique IDs across distributed systems without coordination. However, this convenience often comes at a significant cost to performance and efficiency. For many applications, especially those not operating at a massive, globally distributed scale, a sequential integer remains a superior choice.

The Hidden Costs of Random UUIDs

UUIDv4 keys are 128-bit values, typically stored as a 36-character string or 16-byte binary. Their randomness is both their greatest strength and their most critical weakness from a database perspective.

Index Fragmentation and Write Amplification: In B-tree indexes (the default for primary keys), new entries are inserted in sorted order. A sequential integer naturally inserts at the end of the index, minimizing page splits. A random UUID, however, forces the database to insert the new value somewhere in the middle of the existing index structure. This leads to frequent page splits, increased index fragmentation, and write amplification. The index becomes bloated, requiring more disk I/O and memory to traverse.

Cache Inefficiency: Database performance heavily relies on caching frequently accessed data pages in memory. Sequential access patterns have excellent locality of reference, meaning related rows are stored physically close together. Queries fetching ranges of data (e.g., WHERE id > 1000) are highly efficient. Random UUIDs destroy this locality. Fetching "recent" rows scatters reads across the entire table and index, drastically reducing cache hit rates and increasing latency.

Storage Overhead: A BIGSERIAL (8-byte integer) is half the size of a 16-byte UUID. This difference compounds in the primary key index and every foreign key index that references it. Larger indexes mean slower scans, more memory consumption, and increased storage costs.

Better Alternatives: Sequential, Time-Ordered, and Hybrid Keys

For most applications, a traditional auto-incrementing integer (PostgreSQL's SERIAL or IDENTITY) is the optimal default. It's small, fast, and keeps indexes dense. However, if you need global uniqueness or want to avoid exposing a guessable key, consider these alternatives.

UUIDv7 (Time-Sorted UUIDs): This newer standard embeds a timestamp in the most significant bits of the UUID. This provides the global uniqueness of a UUID while maintaining time-ordered insertion. This dramatically reduces index fragmentation and improves cache locality, as new inserts go near the "end" of the index. Support is growing in databases and application frameworks.

Composite/Hybrid Keys: Another effective pattern is to combine a sequential component with a unique component. For example, you could use a (shard_id, local_sequence) composite key. The shard_id provides a namespace, while the local_sequence is a per-shard sequential number. This retains good index locality while enabling distributed generation.

NanoID or Custom Sequential-Like Schemes: Libraries like NanoID can generate shorter, URL-friendly unique identifiers. While not inherently sequential, they can be configured to be more index-friendly than fully random data.

Key Takeaways for Database Design

• Default to Sequential: Use auto-incrementing integers (SERIAL/BIGSERIAL) unless you have a proven need for distributed, uncoordinated ID generation.
• If You Need UUIDs, Choose v7: For new systems requiring UUIDs, prefer UUIDv7 (time-sorted) over UUIDv4 (random) to preserve index performance.
• Measure the Impact: Before committing to a key strategy, benchmark insert throughput and query latency under realistic load. The performance delta can be an order of magnitude.
• Consider the Whole Stack: Remember that your primary key is replicated in every foreign key index. A poor choice amplifies its negative effects across your entire schema.

While UUIDv4 keys solve the problem of decentralized ID generation elegantly, they introduce substantial database overhead. In software engineering, every abstraction has a cost. For the critical path of your data layer—the primary key—that cost is often paid in indexing inefficiency, cache misses, and storage bloat. By understanding the trade-offs and opting for sequential or time-ordered identifiers where possible, you can build faster, more scalable, and more cost-effective applications. Make your primary key work for your database, not against it.