Random UUIDv4 primary keys slowed our inserts 6x; switching to UUIDv7 fixed it

Our product analytics service writes about 2,500 events per second into one PostgreSQL table. Over three months the ingest lag alert started firing every afternoon, and by April the consumers could not keep up at all: sustained insert throughput had dropped from around 9,000 rows per second in load tests to about 1,500. The database was not busy in the usual sense. CPU sat at 25 percent. What we saw instead was disk:

$ iostat -x 5 nvme1n1
Device  r/s     w/s     rkB/s     wkB/s   r_await  w_await  %util
nvme1n1 6120.4  2890.2  48963.2  187420.8   1.92     3.40   98.7

Six thousand reads per second on a write-only workload. And the WAL volume was far larger than the data we were inserting.

Reproducing it

We built two tables on a test instance with the same hardware class, the same 64-byte payload, and only the primary key generator changed, then loaded 200 million rows into each with pgbench and a custom script:

CREATE EXTENSION IF NOT EXISTS pgcrypto;  -- for gen_random_bytes()
CREATE TABLE events_v4 (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), payload bytea NOT NULL);
CREATE TABLE events_v7 (id uuid PRIMARY KEY DEFAULT uuidv7(),           payload bytea NOT NULL);

-- insert_v4.sql, run by pgbench
INSERT INTO events_v4 (payload)
SELECT gen_random_bytes(64) FROM generate_series(1, 100);

pgbench -n -c 16 -j 8 -T 600 -f insert_v4.sql analytics
pgbench -n -c 16 -j 8 -T 600 -f insert_v7.sql analytics

Once each table's primary key index was well past the size of shared_buffers and the OS page cache, the v4 table fell to roughly a sixth of the v7 table's insert rate. Below that size, the two were within 10 percent of each other. That explained why the problem appeared slowly as the table grew.

What we ruled out

• Autovacuum fighting the inserts. An insert-only table triggers only insert-driven vacuums, and pg_stat_progress_vacuum showed none running during the slow periods.
• Lock contention. pg_stat_activity showed IO / DataFileRead waits, not Lock waits.
• Secondary indexes. We dropped the two secondary indexes on a copy; the gap remained. The primary key alone reproduced it.
• Checkpoint tuning. Raising max_wal_size reduced the spikes but not the steady-state slowdown.

Root cause

gen_random_uuid() produces version 4 UUIDs, which are 122 random bits. Each new key lands at a random position in the primary key B-tree. Once the index is larger than memory, almost every insert needs a leaf page that is not cached, so it reads it from disk, modifies it, and later writes it back. Random inserts also split pages all over the tree, leaving leaves around 70 percent full instead of near 100 percent, which makes the index bigger and the cache problem worse. On top of that, the first change to each page after a checkpoint writes a full page image to WAL, and with random keys almost every insert touches a page not yet modified since the last checkpoint. Our WAL was dominated by 8 KB page images to record 16-byte keys.

What is happening under the hood

A B-tree keeps keys sorted. If new keys always arrive at the high end, as with a sequence or a time-ordered id, every insert goes to the rightmost leaf. That one page stays hot in memory, it fills completely before splitting, and PostgreSQL even has a fast path for it: it remembers the rightmost leaf and skips descending the tree for each insert.

UUID version 7, defined in RFC 9562 (published May 2024), puts a 48-bit Unix timestamp in milliseconds at the front, followed by random bits. Values created later sort later, so inserts cluster at the right edge like a sequence while keeping global uniqueness without coordination. PostgreSQL 18, released on 25 September 2025, added a built-in uuidv7() function. Its implementation also uses 12 extra bits for sub-millisecond precision, so ids generated by one backend within the same millisecond still increase. It also added uuidv4() as an alias for gen_random_uuid(), and uuid_extract_timestamp() now understands v7 values.

You can see the difference directly in the index statistics using the pgstattuple extension:

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT 'v4' AS t, pg_size_pretty(index_size) AS size, avg_leaf_density, leaf_fragmentation
FROM pgstatindex('events_v4_pkey')
UNION ALL
SELECT 'v7', pg_size_pretty(index_size), avg_leaf_density, leaf_fragmentation
FROM pgstatindex('events_v7_pkey');

 t  |  size   | avg_leaf_density | leaf_fragmentation
----+---------+------------------+--------------------
 v4 | 8517 MB |            70.12 |              49.87
 v7 | 6024 MB |            90.05 |               0.01

Same row count, 40 percent bigger index for v4, half its leaf pages out of physical order.

WAL volume tells the same story. We ran one batch of 100,000 inserts after a checkpoint on each table:

CHECKPOINT;
EXPLAIN (ANALYZE, WAL, COSTS OFF)
INSERT INTO events_v4 (payload) SELECT gen_random_bytes(64) FROM generate_series(1, 100000);

 Insert on events_v4 (actual time=4108.553..4108.554 rows=0 loops=1)
   WAL: records=200413 fpi=97482 bytes=812339104

The v7 run of the same batch reported fpi=1206 and about 21 MB of WAL. Nearly 800 MB versus 21 MB for the same rows.

Environment

Minimal reproduction

The table definitions and pgbench scripts above are the whole reproduction. To make the effect show up quickly on a laptop, lower shared_buffers to 128 MB and load 30 million rows: the v4 insert rate falls off a cliff once its index outgrows memory, while the v7 rate stays flat.

Who is affected

Any B-tree indexed column filled with random values: UUIDv4 primary keys, random string ids, hashes used as keys. It matters only once the index exceeds available memory, which is why small and medium tables never show it. MySQL InnoDB is hit even harder, because the primary key is the clustered index and the whole row lives in the leaf, so random keys scatter the table itself. Read-heavy tables with few inserts are barely affected.