UUIDv4 is Killing Your Database Performance

UUIDv4 became the default primary key for many teams for good reasons. Clients can generate IDs without asking the database, IDs don't collide across services, and they don't leak row counts the way auto-increment integers do. The cost shows up later, once the table is large, and it's easy to blame on something else.

The problem is randomness. A B-tree index stays efficient when new keys arrive roughly in order. UUIDv4 is 122 bits of pure randomness, so every insert lands at a random spot in the index. On a small table nobody notices. On a table whose primary key index no longer fits in memory, every insert can turn into a random read, a page split, and extra write-ahead log.

This article explains what random keys do to a B-tree, how to measure it on your own database, and how UUIDv7 (standardized in RFC 9562) fixes it without giving up what made UUIDs attractive. It also covers native support in PostgreSQL and the cases where a time-ordered ID is the wrong choice.

What a B-tree does with your keys

A primary key index is a B-tree of fixed-size pages, 8 KB in PostgreSQL and 16 KB by default in InnoDB. Leaf pages hold keys in sorted order. To insert a key, the database finds the leaf page where the key belongs and writes it there. If that page is full, it splits the page in two and updates the parent.

Now compare two insert patterns.

• Increasing keys (a sequence, a timestamp-prefixed ID). Every new key belongs at the far right of the index. The same few pages get written over and over and stay in cache. When the rightmost page fills, PostgreSQL splits it so the old page stays almost full, governed by the index fillfactor (90 by default for B-trees), and starts a fresh page on the right. PostgreSQL also has a fast path that remembers the rightmost leaf, so increasing inserts can skip the descent from the root.
• Random keys (UUIDv4). Each new key belongs on an essentially random leaf. Which pages get written is spread across the whole index, so the whole index is your working set. Splits happen everywhere, and each one leaves two half-full pages behind. Classic B-tree analysis puts average page utilization under random inserts at about 69% (ln 2), compared with close to full for sequential inserts.

The original post compared it to a filing cabinet. Increasing keys go into the last drawer. Random keys mean opening a different drawer every time and occasionally reorganizing it to make room.

Where the cost actually comes from

"Fragmentation" gets the blame, but it helps to be specific. There are four separate costs, and how much each one matters depends on your workload.

1. The working set becomes the entire index

This is the big one. With increasing keys, inserts touch only the right edge of the index, so the cache needs just a few pages. With random keys, every leaf page is equally likely to be the next target. As long as the whole index fits in shared_buffers (or the InnoDB buffer pool), random inserts cost a bit more CPU and not much else. Once the index outgrows memory, a growing share of inserts need a disk read just to find the page to write into. Insert throughput then drops sharply, often right when the table becomes important.

2. Bigger, sparser indexes

Half-empty pages from random splits mean the same number of keys occupies more pages. More pages means more memory to cache them, more I/O to scan them, and larger backups. A UUID is also 16 bytes against 8 for a bigint, before any fragmentation.

3. More write-ahead log

After each checkpoint, PostgreSQL writes a full-page image to the WAL the first time a page is modified (with full_page_writes on, which is the default). Sequential inserts modify few distinct pages between checkpoints. Random inserts modify many. That means more WAL, more replication traffic and more archive storage for the same logical writes.

4. Clustered storage multiplies everything (MySQL/InnoDB)

In InnoDB the table itself is a B-tree clustered on the primary key. A random primary key fragments the table data, not just an index. Every secondary index also stores the primary key value, so a 16-byte random key makes every secondary index bigger too. If you're on MySQL, the case against UUIDv4 primary keys is stronger than on PostgreSQL, where the heap is unordered and only the indexes suffer.

Measure it on your own hardware

Don't take anyone's benchmark numbers on faith, including mine (I'm deliberately not quoting any). Your hardware, row width and memory settings decide the result. The following script runs on PostgreSQL 18 and compares index size, leaf density and WAL volume for the two key types. On older versions, use gen_random_uuid() for v4 and one of the v7 options described later.

CREATE EXTENSION IF NOT EXISTS pgstattuple;

CREATE TABLE t_v4 (id uuid PRIMARY KEY DEFAULT uuidv4(), payload text);
CREATE TABLE t_v7 (id uuid PRIMARY KEY DEFAULT uuidv7(), payload text);

-- Run in psql. Repeat the block for t_v7.
CHECKPOINT;
SELECT pg_current_wal_lsn() AS start_lsn \gset
INSERT INTO t_v4 (payload) SELECT 'x' FROM generate_series(1, 5000000);
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), :'start_lsn')) AS wal_generated;

SELECT 'v4' AS kind,
       pg_size_pretty(pg_relation_size('t_v4_pkey')) AS index_size,
       avg_leaf_density,
       leaf_fragmentation
FROM pgstatindex('t_v4_pkey')
UNION ALL
SELECT 'v7',
       pg_size_pretty(pg_relation_size('t_v7_pkey')),
       avg_leaf_density,
       leaf_fragmentation
FROM pgstatindex('t_v7_pkey');

For a realistic test, insert in many small transactions from several concurrent clients (for example with pgbench and a custom script), and make the table big enough that the index exceeds shared_buffers. A single bulk insert into a fresh table that fits in memory will make the difference look smaller than it is in production.

UUIDv7: the same 128 bits, in time order

RFC 9562, published in May 2024, replaced RFC 4122 and defined new UUID versions, including version 7. A UUIDv7 is still a 128-bit UUID, so it fits the same uuid column type, the same drivers and the same APIs. Only the bit layout changes:

Because the timestamp comes first and is big-endian, UUIDv7 values sort by creation time both bytewise and in PostgreSQL's uuid comparison. New keys land at or near the right edge of the index, which is exactly the insert pattern B-trees are built for. Within one millisecond, the remaining bits keep IDs unique. RFC 9562 lets implementations use rand_a for sub-millisecond precision or a counter to make IDs strictly increasing within one generator.

Across many generators (several app servers, each with its own clock), ordering is only approximate. That's fine. The index doesn't need perfect order. It needs new keys to cluster near the right edge instead of spreading across the whole tree.

Generating UUIDv7 in PostgreSQL

PostgreSQL 18 added native generation. uuidv7() returns a time-ordered UUID, and uuidv4() was added as an explicit alias of gen_random_uuid(). According to the documentation, the value is built from a millisecond Unix timestamp, a sub-millisecond timestamp fraction and random bits. An optional interval argument shifts the embedded timestamp.

CREATE TABLE orders (
    id          uuid PRIMARY KEY DEFAULT uuidv7(),
    customer_id uuid NOT NULL,
    total_cents bigint NOT NULL
);

-- Existing table on UUIDv4? New rows can switch over without touching old ones.
ALTER TABLE events ALTER COLUMN id SET DEFAULT uuidv7();

-- PostgreSQL 17+: inspect what you have.
SELECT uuid_extract_version(id), uuid_extract_timestamp(id)
FROM orders
LIMIT 5;

Switching the default on an existing table needs no migration. Old v4 keys stay where they are, and new v7 keys append to the right edge from then on. The index stops getting worse even though it doesn't get better.

PostgreSQL 13 to 17 have no built-in v7 generator. Your options are to generate IDs in the application (often the best choice anyway), install an extension such as pg_uuidv7 where your host allows it, or use a small SQL function that overwrites the first 48 bits of a random UUID with the current timestamp and sets the version bits:

CREATE OR REPLACE FUNCTION uuid_generate_v7() RETURNS uuid
LANGUAGE sql VOLATILE AS $$
  SELECT encode(
    set_bit(
      set_bit(
        overlay(uuid_send(gen_random_uuid())
                PLACING substring(int8send(floor(extract(epoch FROM clock_timestamp()) * 1000)::bigint) FROM 3)
                FROM 1 FOR 6),
        52, 1),
      53, 1),
    'hex')::uuid;
$$;

The two set_bit calls change the version nibble from 0100 (4) to 0111 (7). This function doesn't guarantee ordering within a millisecond, which is fine for index locality.

Generating UUIDv7 in the application

Generating in the application keeps one of the main benefits of UUIDs: you know the ID before the insert, so you can use it in idempotency keys, events or related rows within the same request. Mainstream libraries now support v7:

# Python 3.14+
import uuid
order_id = uuid.uuid7()

// npm: uuid (v10+)
import { v7 as uuidv7 } from "uuid";
const orderId = uuidv7();

// github.com/google/uuid
id, err := uuid.NewV7()

// com.github.f4b6a3:uuid-creator
UUID id = UuidCreator.getTimeOrderedEpoch();

Store it in a native UUID type: uuid in PostgreSQL, BINARY(16) in MySQL. Storing UUIDs as 36-character strings more than doubles the key size and slows comparisons, which undoes a good part of the benefit.

UUIDv7 or ULID?

ULIDs solve the same problem with the same basic idea: a 48-bit millisecond timestamp followed by 80 random bits. The difference is mostly ecosystem. UUIDv7 is an IETF standard that fits existing uuid columns, validators and drivers. ULIDs have a nicer default text form (26 characters, Crockford Base32, lexicographically sortable), but databases usually have no native ULID type, so you either store text or convert to 16 bytes yourself. For new systems on a database with a uuid type, UUIDv7 is the lower-friction choice. If you already use ULIDs and store them as 16 bytes, there's no reason to migrate.

When time-ordered IDs are the wrong choice

• The ID is public and creation time is sensitive. Anyone holding a UUIDv7 can read its creation time to the millisecond. That can reveal when an account was created, or the timing of business events. RFC 9562 also advises against using UUIDs as security capabilities. If IDs appear in URLs, decide whether the timestamp is acceptable to expose, or keep a separate random public identifier. The same thinking applies to why auto-increment IDs leak information.
• Distributed SQL databases that range-partition by key. In systems like Spanner or CockroachDB, monotonically increasing keys send all inserts to the same range, which creates a write hotspot. Random keys, or hash-sharded indexes, spread the load there. The trade-off flips compared with a single-node B-tree. Distributed ID generation has its own set of problems.
• You don't need distributed generation at all. A bigint identity column is half the size, perfectly ordered and simple. If IDs never leave your backend and one database issues them, a UUID of any version may be overkill.
• The table is small and will stay small. If the index fits comfortably in memory forever, UUIDv4's overhead is real but minor. Don't rewrite a working system over it.

Checklist

• New tables with UUID keys: default to UUIDv7 (DEFAULT uuidv7() on PostgreSQL 18, or generated in the application).
• Existing UUIDv4 tables: switch the column default or the generator to v7. No backfill is needed.
• Store UUIDs in a native 16-byte type, never as text.
• On MySQL/InnoDB, treat random primary keys as a higher priority to fix, because they fragment the table and inflate every secondary index.
• Check avg_leaf_density, index size versus memory, and WAL volume before and after, using pgstattuple and the LSN diff above.
• Keep UUIDv4 (or a separate random token) where creation time must not leak, or where a distributed database needs random keys to spread writes.
• Use bigint identity when IDs are internal and come from a single database.

The primary key decides where every new row goes in your most important index. Choose the order deliberately.