UUIDv4 as Primary Key Drops Inserts by Up to 38% in Postgres, Test With 100 Million Rows Reveals

A test published by Nikolay Samokhvalov compares UUIDv4, UUIDv7, and bigint as primary keys across three identical tables in Postgres and measures the real cost of each in inserts, WAL, and index size.

Postgres 18 introduced the native uuidv7() function, which generates sortable identifiers as defined by RFC 9562. Even so, a good share of production tables still define id uuid default gen_random_uuid(), that is, UUIDv4: 122 random bits with no relation to insertion order. The question every data modeler should ask before accepting that default is simple: does a random primary key actually matter?

A test conducted by Nikolay Samokhvalov, published on the PostgresAI blog and discussed with Kirk Wolak on episode 194 of the Postgres.FM podcast, answers with numbers. The central conclusion: as long as the index fits in memory, the difference is irrelevant. Once it no longer fits, the cost shows up on three distinct fronts, each with a practical implication for whoever designs the schema.

How the Test Was Set Up

The methodology avoids the classic mistake of comparing keys on different servers without controlling variables. Three versions of the same events table were created, identical except for the primary key: UUIDv4, UUIDv7, and bigint (via generated always as identity). Each one received the same secondary indexes on user_id and created_at, the same load via pgbench with 16 clients running one INSERT per transaction, and synchronous_commit = on.

The environment ran Postgres 18.6 under cgroup with limited memory (1.5 GiB or 3 GiB, split between shared_buffers and page cache), precisely to force the index past available memory without having to wait for a terabyte-scale table. Seven runs on cloud servers, with each hypothesis written down before the first row was inserted, according to the author.

In Memory, the Key Doesn't Matter

As long as the primary key index fits in shared_buffers, the type of key doesn't change the insert rate: UUIDv4 ran between 0.95 and 1.10 times the rate of UUIDv7 across four different servers. That explains why so many quick benchmarks, run with just a few million rows, conclude that "UUIDv4 is fine". The bottleneck in that regime is the commit flush, shared among the 16 clients, not the index.

The problem shows up afterward. In the test, UUIDv4 began to drop at around 26 million rows, exactly the point where its index exceeds 1 GiB of shared_buffers. The drop is steep up to about 65 million rows, when the combined random-key indexes exceed the 3 GiB cache limit, and then it settles into a slow decline.

The Number That Matters: 0.62 to 0.69

At the 100 million row mark, already out of memory, UUIDv4 inserted at a rate of 0.62 to 0.69 relative to the ordered key on the same server: 0.64 to 0.69 against UUIDv7 and 0.62 against bigint. In plain terms: up to 38% slower to write the same row, just because the primary key is random.

The reason is structural. A B-tree keeps keys in order, so a new key goes to the leaf where it fits. With UUIDv7 or bigint, every insert lands on the rightmost leaf, which never leaves memory. With UUIDv4, the insert can land on any leaf across the whole index, which turns the entire index into the table's working set.

In practice, each UUIDv4 insert outside the cache costs a page read and, often, the write of a dirty page evicted beforehand, and it's the client's own backend that does this work while the transaction waits. Samokhvalov measured this split: on one server, half of UUIDv4's extra time was spent on commit flush and the other half on data file I/O.

Kirk Wolak summed up the debate about what to call this effect in the graph, which in the chart looks more like a ramp than a step:

A performance hill.

Kirk Wolak, on episode 194 of the Postgres.FM podcast

But he himself added that, for whoever was failing to deliver the work on time and now fails to deliver it at all, the practical effect still looks like a cliff.

WAL Pays the Price Twice

The second cost front is WAL, and it doesn't depend on memory, but on how many index leaves each checkpoint cycle touches. With full_page_writes = on, the first change to a page after a checkpoint writes a full image of it to the WAL; subsequent changes until the next checkpoint write only small records.

UUIDv7 and bigint keep touching the same rightmost leaf: they pay one full image and then insert many rows into it for free. UUIDv4 spreads inserts across nearly every leaf touched in the cycle, so each full image is amortized over just a few rows. Result measured between 90 and 100 million rows: UUIDv4 wrote 1.5 to 1.9 times more WAL per row than the reference key, on the same server.

This has a direct consequence for whoever operates standbys or pays for cloud I/O storage:

• A longer checkpoint cycle (from 5 to 30 minutes, in the test) reduced UUIDv4's WAL per row by 39%, but didn't noticeably change the ratio between UUIDv4 and UUIDv7
• wal_compression = lz4 helped little (7% reduction), because a leaf full of random UUIDs compresses poorly
• In an Aurora Standard simulation at 1,000 inserts/s, applied to the measured counters, UUIDv4's extra reads would cost between $457 and $486 more per month once the index outgrows the instance cache
• On a standby, WAL replay paused and then resumed showed UUIDv4 2.4 times slower per row before the primary's memory limit, and 5.5 times slower after

Bigger Index, Even With the Same Number of Keys

UUIDv4 and UUIDv7 take up the same space per index entry, 28 bytes against 20 bytes for a bigint. The difference in total size comes from leaf fill: a random insert into a full leaf splits it in half, and both halves end up partially empty. In the test, UUIDv4 leaves averaged 68% full (close to the classic ln 2 figure for random inserts in a B-tree), against the default fillfactor of 90% for UUIDv7 and bigint.

The practical result: a UUIDv4 index that grew organically ends up 33% bigger than an equivalent UUIDv7 index. A REINDEX restores UUIDv7's size, but new random inserts go back to splitting the compacted leaves.

UUIDv7 Also Has a Cost, and That Matters for Modeling

Adopting UUIDv7 without understanding its quirks is trading one problem for another, smaller but real:

• It leaks the creation time: the first 48 bits are a millisecond timestamp, and Postgres 18 also adds a sub-millisecond fraction. Whoever decodes the bits knows when that row was created
• It has less entropy: a uuidv7() value in Postgres 18 carries 62 random bits (64 on macOS and Windows builds), against 122 for UUIDv4. Don't use it as a secret or as an access-granting URL token
• Random deletion also causes bloat: deleting the 30% oldest rows and reloading didn't grow the index, but deleting 30% of random rows left the primary key 1.30 times bigger, because new keys never reoccupy old leaves
• Clock skew between clients degrades ordering: with clients whose clocks varied ±60 seconds from each other, the index ended up only 69% full with 45% of leaves out of order. The author's recommendation is to generate uuidv7() on the server, not in the application

One gain that offsets much of this is time-based partitioning while still searching by id. Since the timestamp is embedded in the key, a table partitioned by RANGE on id lets the planner do partition pruning even on a simple id = $1 lookup: the test measured 41,000 lookups per second against 2,600 on a UUIDv4 table partitioned by created_at, which has to visit every partition.

Migrating an Existing Table

Since the column type stays uuid, switching the generator on a table already in production doesn't require a rewrite: it's an ALTER TABLE over the default, which takes an ACCESS EXCLUSIVE lock for a short time. To avoid getting stuck behind a long transaction, the author recommends a low lock_timeout with retry:

set lock_timeout = '500ms'; -- increase if needed
alter table events alter column id set default uuidv7();

This only works if inserts rely on the column's default; if the application generates the id explicitly, it's the application that needs to change. Old values remain UUIDv4, and uuid_extract_timestamp() returns NULL for them, so any code that reads the time from the id needs to handle both versions (uuid_extract_version() tells one from the other).

When It's Not Worth Switching

In small tables, whose index will never leave shared_buffers, switching from UUIDv4 to UUIDv7 delivers no perceptible gain, according to the test's own numbers. In applications that already generate the id on the client before any database validation, switching generators without fixing the clock skew problem can cancel out much of the benefit. And where the id is publicly exposed as an access token, UUIDv7's reduced entropy is a real reason to keep UUIDv4, or to migrate to a token scheme separate from the primary key.

Outside these cases, the test supports a concrete modeling recommendation: in tables that grow beyond a few tens of millions of rows, a random primary key isn't just an aesthetic identifier choice, it's an I/O and WAL performance decision that pays off, or charges you, as the table grows.