Should You Use UUIDs as Primary Keys? Performance Myths, Real-World Trade-Offs, and the Ideal Architecture

Database schema design is always full of trade-offs, but few debates are as persistent as the one around UUIDs vs. numeric primary keys. If you've ever wondered whether a UUID primary key actually slows down your database — and what to do about it — this post is for you.

Let’s go methodically through the myths, the technical reality, and the architecture used by big-scale platforms.

On paper, UUIDs look great: they’re globally unique, safe to expose, and avoid ID collisions across systems. But under the hood, they affect performance in a few very specific ways.

One of the core reasons UUID primary keys are slower is how they behave inside a B-Tree index.

With a numeric auto-incremented primary key, inserts follow a predictable, sequential pattern. New rows always land at the end of the index — keeping it compact and efficient, with minimal page splits.

With UUIDv4, inserts are essentially random. New rows land unpredictably across many index pages, causing fragmentation and page splits on every write. The database must constantly reorganize existing pages to accommodate the new values.

A widely-used pattern — employed by Stripe, Shopify, GitHub, and many others — is to combine:

• An internal numeric primary key (auto-increment or sequence)
• A public UUID field for external exposure (URLs, API endpoints, webhooks)

This provides the write speed of sequential keys while retaining the safety and opacity of UUIDs. Internal joins and foreign keys stay fast and compact; external consumers never see the internal integer.

Why this works so well

This pattern avoids all the classic pitfalls of using UUIDs as primary keys.

The size difference is concrete:

• UUID: 16 bytes per value
• Integer (INT): 4 bytes
• Big integer (BIGINT): 8 bytes

That difference compounds when the same field is used as a primary key, a foreign key in many child tables, and the clustered index on disk. Larger keys mean larger indexes, which means more memory pressure and more I/O per query.

In a table with millions of rows and dozens of related tables, the cumulative storage and memory difference between a UUID PK and a BIGINT PK can be significant — especially when the database’s buffer pool is the bottleneck.

If you genuinely need UUIDs as primary keys — for instance in a distributed system where nodes generate IDs independently — there are safer and faster alternatives to UUIDv4:

Both maintain index locality — new values always land near the end of the B-Tree, keeping inserts efficient even at scale.

Even though the internal primary key is numeric, your APIs can fully expose UUIDs without architectural penalty. The typical request flow:

1. Client sends a request containing a UUID (e.g. GET /orders/01932f3a-...)
2. API queries the database using a dedicated index on the UUID column
3. Database resolves the numeric PK from that index in a single lookup
4. All internal JOINs and foreign key relationships use the efficient numeric key
5. Response returns to the client using the UUID — the internal integer is never exposed

The UUID index lookup adds one extra index read compared to querying by numeric PK directly, but in practice this is negligible. The wins in write performance, index size, and external security far outweigh this tiny overhead.