UUID as Primary Key: The Complete Storage & Indexing Tradeoffs Guide

Choosing a UUID as a primary key is one decision with several independent sub-decisions hiding inside it: which UUID version, which storage type, which database engine, and whether the table's indexing pattern can tolerate random insert order. This guide lays out the full tradeoff space in one place — the mechanics, the numbers, and a decision framework — with pointers to deeper dives on each specific sub-topic.

Why This Decision Is Harder Than "UUID vs Integer"

Most comparisons flatten this into a binary choice. In practice there are at least four independent axes:

1. UUID version — v4 (random) vs v7 (time-ordered) vs v1 (timestamp + MAC, largely legacy)
2. Storage type — native uuid (Postgres) / BINARY(16) (MySQL) vs text-based storage
3. Database engine's clustering behavior — whether the table itself is physically ordered by the primary key (MySQL/InnoDB) or not (Postgres, by default)
4. ID generation location — database-generated vs application-generated

Getting any one of these wrong produces a measurable performance or storage regression, independent of the other three. This guide walks through each.

Axis 1: UUID Version

-- Postgres 13+: v4, no extension needed
SELECT gen_random_uuid();

-- Postgres 18+ or via extension: v7
SELECT uuidv7();

-- MySQL: v1 by default
SELECT UUID();

v4 is the safe, widely-supported default when insert locality doesn't matter. v7 is increasingly the better default for primary keys specifically, because it resolves the single biggest performance cost of UUID keys — random insert order — without giving up coordination-free generation. v1 persists mostly in legacy schemas and should generally not be a new design choice given the MAC-address exposure.

Axis 2: Storage Type

This is a pure storage-efficiency question, and it has a correct answer regardless of which UUID version is chosen.

Storing a UUID as text roughly doubles its footprint for zero functional benefit — no database in common use gains query capability from text storage that it lacks with native/binary storage. This is covered in full depth, with migration scripts, in How to Store UUIDs Correctly in PostgreSQL and Storing UUIDs Efficiently in MySQL.

-- Postgres: correct
CREATE TABLE orders (id UUID PRIMARY KEY DEFAULT gen_random_uuid());

-- MySQL: correct
CREATE TABLE orders (id BINARY(16) PRIMARY KEY);
INSERT INTO orders (id) VALUES (UUID_TO_BIN(UUID(), 1));

Axis 3: Clustering Behavior — Why the Same UUID Costs More in MySQL

This is the axis most comparisons miss entirely, and it's the reason "UUID primary key overhead" numbers you find online vary so widely by source.

Postgres (heap-organized by default): Table rows are not physically ordered by primary key. Indexes point to a physical row location (TID) independent of key value. A random UUID primary key still causes B-tree page splits within the primary key index itself, but secondary indexes are unaffected by the PK's structure.

MySQL/InnoDB (clustered by primary key): The table is the primary key's B-tree. Every secondary index stores the primary key value as its row locator. A wide, random primary key therefore inflates every index on the table, not just itself.

-- Postgres: secondary index cost is independent of PK width
CREATE INDEX idx_tenant ON orders (tenant_id); -- stores tenant_id + physical TID

-- MySQL: secondary index cost scales with PK width
CREATE INDEX idx_tenant ON orders (tenant_id); -- stores tenant_id + full 16-byte id

This is why a random UUIDv4 primary key is a bigger liability on MySQL than on Postgres, all else equal — and why the fix (BINARY(16) + either swap_flag for v1 or UUIDv7 generation) matters more urgently there. Full mechanics in MySQL UUID Functions Explained.

Axis 4: Where the ID Is Generated

-- Get the generated ID without a second round trip
INSERT INTO orders (tenant_id, total) VALUES (42, 199.99)
RETURNING id;

// Application-generated: ID is known before the row is persisted,
// useful when building an object graph with FKs before SaveChanges()
public class Order
{
    public Guid Id { get; set; } = Guid.NewGuid();
    public int TenantId { get; set; }
}

Application-side generation is usually preferable when the ID is needed immediately for related-object construction (e.g., building OrderItems that reference Order.Id before the parent is saved). Database-side generation is preferable when you want a single source of truth for ID format and don't want every application/service touching the database to independently implement correct UUID generation.

Measured Impact: Putting the Axes Together

Combining a native (correctly typed) UUID storage choice with each version, on a 5-million-row Postgres table with one secondary index:

The table size is nearly identical between v4 and v7 (both are 16 bytes) — the difference is entirely in the index, where v7's append-mostly insert pattern avoids the page-splitting and low fill-factor that random v4 inserts cause. Full benchmark methodology in How Much Storage Do UUID Primary Keys Actually Cost at Scale?

Comparison With Non-UUID Alternatives

Composite natural keys are covered separately, since they solve a different problem — eliminating the surrogate key entirely rather than optimizing its representation. See Composite Keys vs UUIDs for that comparison in full.

Decision Framework

Does a stable, unique, immutable business key already exist for this entity?
├── Yes → Strongly consider a composite natural key instead of any surrogate.
└── No → Continue.

Do you need globally unique IDs generated without central coordination
(offline clients, multi-region writers, merging data from independent systems)?
├── No → Use BIGINT/BIGSERIAL. It's smaller and faster on every axis that doesn't require this.
└── Yes → Continue.

Is this a high-insert-volume table where index bloat/page-splitting matters
(large tables, tight latency budgets, InnoDB clustering)?
├── Yes → Use UUIDv7 (or ULID), stored natively (`uuid` / `BINARY(16)`), not text.
└── No  → UUIDv4 is fine; the insert-locality cost is real but often not decision-relevant
          at smaller scale.

On MySQL specifically: always use BINARY(16). The clustering penalty for CHAR(36)
compounds across every secondary index, more severely than the equivalent
mistake on Postgres.

Conclusion

"Should I use a UUID primary key" isn't answerable as a single yes/no — it decomposes into version, storage type, engine behavior, and generation location, each with its own measurable cost. Get the storage type right (native binary, never text) regardless of anything else — that's a strict win with no tradeoff. Then choose UUIDv7 over v4 by default for new schemas unless there's a specific reason to need pure randomness, since it closes most of the insert-locality gap at no storage cost. And before reaching for a UUID surrogate key at all, check whether the table already has a natural key that makes the surrogate unnecessary in the first place.

---

Tools and Versions

Need some UUIDs for testing? Try our tools for generating UUIDv4   UUIDv7   ULID   NanoID and decoding validating.
For a detailed comparison of UUID versions, visit our Complete Guide to UUID Versions.