Primary keys shape the foundation of your data model. UUIDs are popular because they’re globally unique and easy to generate — but when used as primary keys in relational databases like PostgreSQL or MySQL, they introduce performance costs that most teams don’t realize until production traffic grows.

At scale, these trade-offs become significant.

This article explains why numeric IDs outperform UUIDv4, how they affect database internals, and what a balanced approach looks like in real systems.

The Problem With UUIDv4

UUIDv4 values are random by design.
In a B-Tree index — the default index type in PostgreSQL and MySQL — randomness leads to inefficient storage and degraded write performance.

Below are the core reasons UUIDs hurt performance when used as primary keys.

1. Random Inserts Fragment the B-Tree

Sequential numeric IDs (INT/BIGINT) insert neatly at the end of the index.

UUIDv4 inserts land anywhere in the tree, causing:

• Index page splits
• Increased write amplification
• Higher I/O
• Gradual index bloat

Over time, this results in slower inserts and degraded throughput.

2. UUIDs Increase Storage Footprint

UUID: 16 bytes
INT: 4 bytes
BIGINT: 8 bytes

Larger keys mean:

• Larger indexes
• More memory usage
• More pages to scan
• Worse cache locality

This directly affects:

• Query latency
• Replication performance
• Cloud database costs

3. UUIDs Hurt Ordering and Sorting Performance

Sorting UUIDs is computationally expensive.

Sequential IDs offer:

• Faster ordering
• Efficient pagination
• Predictable range queries

UUIDs require costly comparisons and cause scattered memory access patterns — noticeably slower at scale.

Example:

• ORDER BY id DESC LIMIT 20 → extremely fast with INT
• Same query on UUID → noticeably slower as data grows

4. UUID-Based Joins Are Heavier

Using UUIDs in foreign keys increases overhead across your schema:

• Larger row sizes
• Larger secondary indexes
• More memory required during JOIN operations

As tables grow, this cost compounds.

When UUIDs Do Make Sense

UUIDs are not bad — they’re just not optimal as primary keys in most relational systems.

They are ideal when:

1. You need global uniqueness across distributed services

UUIDs shine in decentralized architectures.

2. You expose IDs publicly

UUIDs provide security through unpredictability.

3. You generate IDs on the client

Offline-first or mobile-heavy systems benefit from client-side ID generation.

Even in these cases, UUIDs generally work best as secondary identifiers, not as primary keys.

Our Approach: The Best of Both Worlds

For most applications, we recommend:

Primary Key: BIGINT auto-increment (sequential)

✔ Optimized for indexing
✔ Fast inserts and joins
✔ Predictable, efficient pagination

Public Identifier (Optional): UUID or NanoID

✔ Safe for URLs
✔ Hard to guess
✔ Decoupled from database structure

Example schema:

id BIGSERIAL PRIMARY KEY,
public_id UUID DEFAULT gen_random_uuid() UNIQUE

This gives you:

• High-performance database operations
• Stable, small indexes
• Secure, public-facing identifiers
• Cleaner API responses

Real-World Impact

Teams that migrate from UUIDv4 to numeric sequential IDs often report:

• 30–60% faster write throughput
• Smaller index sizes
• Faster pagination and ordering
• Reduced vacuum and maintenance overhead in PostgreSQL

At scale, those improvements translate to lower costs, fewer performance incidents, and more predictable behavior.

Conclusion

UUIDs are useful — but not ideal as primary keys in relational databases.

If your priorities include:

• Faster writes
• Smaller indexes
• Improved join performance
• Reduced operational overhead

Then numeric IDs (INT/BIGINT) deliver the best long-term performance.

Use UUIDs where they shine:
as secondary identifiers for external usage, not as your main primary key.

Thank you for reading! 

Stay connected and stay updated on the latest trends in technology by connecting with me on LinkedIn.

For more insightful articles and updates, feel free to visit my Medium profile.

You can also explore my work and services at Techrill.com.

Happy coding and keep innovating!