Why You Shouldn’t Use UUID v4 as a Database Primary Key

Database architecture: The performance impact of primary key selection on B-Tree storage engines.

When bootstrapping a modern web application, choosing a primary key strategy seems simple. Traditional auto-incrementing integers (1, 2, 3...) introduce obvious security and distributed architecture flaws: they expose business volume to competitors, allow scrapers to crawl records through simple integer enumeration, and require a single central database sequence to coordinate writes.

To bypass these limitations, engineering teams routinely turn to UUID v4. Generating a Version 4 UUID is supported out of the box in virtually every runtime, allowing distributed microservices to generate unique record IDs independently without waiting for database round-trips.

In development and low-volume staging environments, UUID v4 appears flawless. But once production tables scale past a few million rows, database performance encounters a sharp degradation. Write throughput drops, disk I/O spikes, and memory caches clear prematurely. The issue is not that UUIDs are inherently bad; the issue is that purely random UUID v4 values directly combat the underlying B-Tree data structures that power relational databases.

1. How Relational Databases Store Primary Keys

To understand why UUID v4 degrades database performance, you have to look at how storage engines organize data on disk.

In relational databases like MySQL (InnoDB), SQLite, and SQL Server, tables are structured as Clustered Indexes. In a clustered table:

• The primary key defines the physical ordering of the data on disk.
• Records are stored inside a balanced search tree (B+ Tree) divided into fixed-size memory blocks called pages (typically 16 KB in MySQL and 8 KB in PostgreSQL indexes).
• Within each page, rows are sorted in ascending chronological or numerical order.

When using sequential IDs (like BIGINT AUTO_INCREMENT), every new record inserted has a value higher than the previous one. The database engine navigates directly to the rightmost leaf page of the B-Tree, appends the row to the end of the page, and moves forward. Writes execute as clean, sequential disk operations.

2. The Three Architectural Penalties of UUID v4

Because a Version 4 UUID is generated from 122 bits of raw pseudorandom entropy, its hexadecimal representation is uniformly distributed across the entire key space. Inserting random keys into an ordered B-Tree triggers three distinct performance bottlenecks:

1. Frequent Page Splits and Write Amplification

When records arrive in purely random order, new inserts do not append to the end of the index. An incoming insert may target a leaf page in the middle or beginning of the tree.

If that target 16 KB page is already full, the database cannot simply insert the row. It must execute an expensive Page Split:

1. It allocates a brand new page on disk.
2. It moves approximately 50% of the rows from the existing page to the new page to make room.
3. It updates parent node pointers throughout the B-Tree hierarchy.

This turns a single write operation into multiple disk read-and-write cycles, causing severe write amplification that wears down SSD storage arrays and degrades transaction throughput.

2. The "Buffer Pool Cliff" (Memory Cache Thrashing)

Relational databases keep frequently accessed index pages inside an in-memory cache known as the Buffer Pool (or Shared Buffers in PostgreSQL).

When keys are sequential, the database only needs to keep the most recent, active leaf pages in RAM. But when primary keys are random, every insert targets a random page across the entire physical table.

• As long as your entire database fits in RAM, performance remains acceptable.
• The moment your table and indexes exceed available memory, performance falls off a cliff.
• To insert a single row, the database must evict a healthy page from RAM, read the cold target page from disk, perform the page split, and write it back. Disk I/O becomes the primary system bottleneck.

3. Cascading Foreign Key Bloat

A primary key rarely stays confined to its own table. It propagates across your schema as a foreign key in join tables, order items, audit logs, and user associations.

• A standard BIGINT integer consumes 8 bytes of storage.
• A native binary UUID consumes 16 bytes of storage.
• An unoptimized VARCHAR(36) string representation consumes 36 bytes of storage.

Across a database with dozens of tables and multi-column composite indexes, using 16-byte random UUIDs doubles or quadruples the memory required to hold foreign key indexes in cache.

3. What to Use Instead: 4 Production-Tested Alternatives

Alternative 1: UUID v7 (RFC 9562) — The Modern Standard

Published in May 2024 by the IETF in RFC 9562 (which formally replaces the legacy RFC 4122 specification), UUID v7 is the definitive replacement for UUID v4 in database design.

UUID v7 combines a 48-bit Unix timestamp in milliseconds with 74 bits of cryptographically secure randomness:

|--- 48 bits (Unix Timestamp ms) ---|-- 4b (ver) --|-- 12b (seq) --|-- 2b (var) --|--- 62 bits (Random) ---|

Because the timestamp sits at the beginning of the identifier, UUID v7 values are naturally sequential. New records append to the right edge of B-Tree indexes, eliminating page splits while retaining 100% compatibility with standard 16-byte UUID database column types.

You can inspect timestamp structures and generate identifiers instantly using our UUID Generator and Unix Timestamp Converter.

Alternative 2: The Hybrid Pattern (Internal BigInt + Public UUID)

If your application runs on a centralized relational database and does not require client-generated keys, the Hybrid Pattern offers maximum efficiency:

• Internal Primary Key: Use a standard, hidden BIGINT AUTO_INCREMENT column for internal database joins, foreign keys, and clustered index ordering.
• Public Identifier: Add a separate public_id UUID UNIQUE column (generated using UUID v4).

Your internal joins run at peak hardware speed using 8-byte integers, while your external APIs and URLs expose only the non-enumerable UUID v4 string.

Alternative 3: ULID

ULID encodes a 48-bit timestamp and 80 bits of randomness into a 26-character string using Crockford's Base32 character set. It is lexicographically sortable, avoids hyphens, and is case-insensitive.

Alternative 4: Snowflake IDs / TSIDs

Pioneered by Twitter, Snowflake identifiers compress time, worker IDs, and sequence counters into a single 64-bit signed integer. They fit cleanly inside standard BIGINT (8-byte) columns, retaining the storage footprint of legacy auto-increment keys while preventing B-Tree page splits completely.

4. Architectural Comparison Matrix

Strategy	Size	Time-Sorted?	B-Tree Friendly?	Prevents ID Enumeration?
BIGINT Auto-Increment	8 bytes	Yes	Yes (Maximum)	No (Vulnerable)
UUID v4 (Random)	16 bytes	No	No (Severe Splits)	Yes
UUID v7 (RFC 9562)	16 bytes	Yes	Yes (Append-only)	Yes
ULID	16 bytes	Yes	Yes (Append-only)	Yes
Snowflake / TSID	8 bytes	Yes	Yes (Append-only)	Yes

---

Frequently Asked Questions

Why does UUID v4 cause page splits in relational databases?

A Version 4 UUID is generated from random entropy, meaning incoming keys have no sequential order. When an insert targets a leaf page in a clustered B-Tree that is already full, the database engine must allocate a new page and move half the records over. This operation—called a page split—causes write amplification, fragments physical storage, and degrades insertion speeds.

What is the difference between UUID v4 and UUID v7?

UUID v4 is composed entirely of random bits (aside from version and variant markers). UUID v7 replaces the first 48 bits with a millisecond-precision Unix timestamp, followed by random bits. This ensures UUID v7 values sort sequentially in chronological order, making them compatible with database B-Tree indexes while remaining globally unique.

Does PostgreSQL suffer from the UUID v4 performance penalty?

Yes, but with nuances. In PostgreSQL, standard tables are unordered heaps, meaning raw table inserts do not suffer page splits. However, primary keys in PostgreSQL automatically create a unique B-Tree index. That primary key index suffers from the exact same random page splits, cache eviction, and memory bloat as MySQL or SQL Server.

Can UUID v7 leak sensitive creation times?

Yes. Because UUID v7 embeds a 48-bit Unix timestamp in its initial bytes, anyone who inspects the identifier can decode the exact date and millisecond the record was created. For public credentials where timing metadata must remain private (such as password reset tokens or secret API keys), use purely random UUID v4 instead.

Why is storing UUIDs as VARCHAR(36) bad practice?

Storing a UUID as a formatted string (VARCHAR(36)) consumes 36 bytes of storage per row, compared to 16 bytes when stored as a native binary UUID type. Across millions of rows and secondary foreign key indexes, string representations more than double disk usage and force database memory caches to evict active pages prematurely.

Is ULID better than UUID v7?

Both use 128 bits of data and embed a 48-bit timestamp. ULID is formatted as a 26-character Crockford Base32 string, making it easier to read and select in text editors. UUID v7 is standardized under IETF RFC 9562, making it the preferred drop-in choice for native UUID column types in PostgreSQL, MySQL, and enterprise database drivers.