Why UUID Primary Keys Halve Your Database Throughput (And How to Fix It)

Problem: UUIDs Kill Write Performance

When a table uses a UUID as the primary key, each new row lands at a random location in the B‑tree index. This randomness prevents the database from predicting where to write next, leading to high CPU usage (up to 90%), severe insert latency spikes, and overall throughput loss.

Real‑World Observation

In a production feature rollout, write traffic surged while the API and connection pool remained healthy, but PostgreSQL CPU hit 90% and insert latency exploded. Re‑creating the table with a bigserial primary key dropped CPU by over 40% and brought p95 latency below 1 ms.

Why Random Primary Keys Hurt B‑Tree Indexes

Relational databases perform best when new rows are inserted in order. An auto‑incrementing bigint appends to the rightmost leaf page, keeping pages hot in memory and minimizing page splits. A random UUID forces the engine to jump to different pages for each insert, causing:

Frequent page splits

Cache misses and larger index structures

Higher write amplification

Increased index size (e.g., 3.2 GB vs 1.1 GB for 10 M rows)

Benchmark Results

-- Version A: UUID primary key
create table orders_uuid (
    id uuid primary key,
    user_id uuid not null,
    amount integer not null,
    created_at timestamptz not null
);

-- Version B: bigint primary key
create table orders_seq (
    id bigserial primary key,
    user_id uuid not null,
    amount integer not null,
    created_at timestamptz not null
);

Running the same write load on both tables produced the following metrics:

| Key type | Writes per second | Index size after 10M rows | p95 insert latency |
|----------|-------------------|----------------------------|--------------------|
| UUID     | 42k               | 3.2 GB                     | 6.3 ms             |
| Bigserial| 118k              | 1.1 GB                     | 1.1 ms             |

Using a UUID primary key reduced peak throughput by more than half.

Typical JPA/Spring Data Entity Using UUID

@Entity
class Order {
    @Id
    UUID id;
    UUID userId;
    int amount;
    Instant createdAt;
}

While this looks modern and convenient, the database does not care about the Java‑side object model; it only cares about the physical write pattern.

Better Modern Alternatives

Adopt a sequential bigint primary key for internal storage and keep a separate UUID column for external references:

create table orders (
    id bigserial primary key,
    public_id uuid not null,
    user_id uuid not null,
    amount integer not null,
    created_at timestamptz not null
);

The public_id can be exposed to clients, while id remains efficient for indexing.

If you need client‑generated keys, consider time‑ordered identifiers such as ULID or the newer time‑sorted UUID format, which keep newly generated keys clustered together.

Intuitive Analogy

Imagine the write path as a flow:

Client → API node → Database → B‑tree index on primary key

. A sequential key follows a warm, cached path, while a random UUID darts around the index like a chaotic dartboard, causing the storage engine to constantly jump pages and expand the tree.

Takeaway

If your database shows high CPU and latency under write‑heavy loads, check the primary‑key generation strategy first. Replacing a random UUID primary key with a sequential bigint (or a hybrid approach) is often the simplest way to regain throughput without changing business logic.

Author: The Concurrent Mind