UUIDv7 or UUIDv4 keys in Postgres: 11M rows, 22x slower

Use UUIDv7 instead of UUIDv4 as a primary key in PostgreSQL 18 if the table will grow past a few million rows. In our test on a laptop, 11 million inserts with gen_random_uuid() took 1,526 seconds. The same inserts with uuidv7() took 68 seconds. A plain bigint identity column took 59 seconds. The gap does not show up at 500,000 rows, which is why so many teams ship UUIDv4 keys and only notice a year later.

This post gives the full lab, the cause, the cost of the fix, and a safe migration path. It also covers the one case where UUIDv4 is still the right answer.

What is the difference between UUIDv4 and UUIDv7?

UUIDv4 is a 128-bit identifier made almost entirely of random bits. UUIDv7 is a 128-bit identifier defined in RFC 9562 whose first 48 bits are a Unix timestamp in milliseconds, followed by random bits. Both print as the same 36-character string, so applications cannot tell them apart.

The difference matters inside the database. PostgreSQL stores a primary key in a B-tree, which keeps entries sorted. A UUIDv4 lands at a random position every time. A UUIDv7 lands at the right edge, because newer timestamps sort after older ones.

PostgreSQL 18 added a native uuidv7() function, as Neon's PostgreSQL 18 guide explains. Earlier versions need an extension or a library that generates the value in the application.

Test setup: what we measured and how

We ran PostgreSQL 18.1 on an Apple M2 laptop with 8 GB of RAM. The test setup used shared_buffers=128MB and fsync=off, so the results show index behavior rather than disk flush speed. Each table had a primary key and one 100-character text column.

The methodology was simple. We inserted one million rows per batch with INSERT ... SELECT ... FROM generate_series, timed every batch, and read the WAL position before and after. After the run we called pgstatindex() from the pgstattuple extension to read leaf density.

Here is the schema for all three tables:

create table t_big(id bigint generated always as identity primary key, p text);
create table t_v4 (id uuid primary key default gen_random_uuid(), p text);
create table t_v7 (id uuid primary key default uuidv7(), p text);

Two caveats apply. The bigint table hit a full disk during an extra run, so its index figures come from 10 million rows and are scaled per row. And one laptop is not a production server. The shape of the curve is the finding, not the exact seconds.

Results: insert time per million rows

UUIDv4 insert time grew from 11 seconds per million rows to 350 seconds. UUIDv7 and bigint stayed between 4 and 9 seconds for the whole run.

The table below shows the batches that bracket the cliff:

Rows in table	bigint	UUIDv7	UUIDv4
1 million	4.3 s	4.7 s	10.9 s
4 million	5.5 s	4.9 s	68.8 s
7 million	6.6 s	6.1 s	184.3 s
11 million	8.7 s	9.2 s	349.5 s

The cumulative totals tell the same story. Eleven million rows took 58.9 seconds with bigint, 68.3 seconds with UUIDv7 and 1,526 seconds with UUIDv4. That makes UUIDv4 about 22 times slower than UUIDv7 and 26 times slower than bigint.

The first batch already shows a penalty of more than 2x for UUIDv4. The real break comes between 3 and 4 million rows, when the batch time jumped from 15 to 69 seconds. At that size the UUIDv4 index was roughly 167 MB, which is bigger than the 128 MB of shared_buffers. Every insert after that point needed a random page from disk.

Why random keys hit a wall

A B-tree insert needs the leaf page where the key belongs. With a sequential key, that page is the last one, and it is always in memory. With a random key, the page is anywhere in the index.

While the whole index fits in shared_buffers, random inserts are cheap. Once it does not fit, each insert may read a page from disk, modify it, and write it back later. This is why the slowdown tracks index size, not row count. A narrower table, a bigger server or more RAM moves the cliff to a later row count. It does not remove the cliff.

Random keys also split pages. A full leaf page that receives a key in the middle splits in two, and both halves are left partly empty. The next section shows how much space this wastes.

Index size and leaf density

The UUIDv4 index was 459 MB at 11 million rows. The UUIDv7 index was 330 MB. Leaf density, the share of each leaf page that holds data, was 64.96% for UUIDv4 and 89.99% for UUIDv7 (measured with pgstatindex, 2026).

These are the full pgstatindex results:

Metric	bigint (10M rows)	UUIDv7 (11M rows)	UUIDv4 (11M rows)
Index size	234 MB	330 MB	459 MB
Bytes per row	23.4	30.0	41.7
Leaf pages	29,933	42,146	58,469
Leaf density	90.04	89.99	64.96
Leaf fragmentation	0	0	49.83
Tree depth	2	2	3

Two numbers stand out. The UUIDv4 tree is one level deeper, so every lookup touches one more page. And half of the UUIDv4 leaf pages are out of physical order, which defeats read-ahead on range scans.

UUIDv7 reaches the density of bigint. It is bigger only because each key is 16 bytes instead of 8.

WAL volume: the cost nobody budgets for

Random inserts write more write-ahead log. Over the 11 batches, UUIDv4 generated 5,272 MB of WAL, UUIDv7 generated 3,829 MB and bigint generated 2,709 MB (our lab, 2026).

The reason is full-page writes. After each checkpoint, the first change to a page writes the entire 8 KB page to the WAL, a behavior the PostgreSQL WAL configuration guide calls full-page writes. Random inserts touch many different pages, so they trigger far more full-page writes than inserts that keep hitting the same hot page.

WAL volume matters outside the benchmark. It drives replica lag, backup size and cloud bills for storage and cross-region transfer. A table with UUIDv4 keys can double the replication traffic compared with a sequential key, at no benefit to the application.

How our numbers compare with earlier benchmarks

Our result agrees with earlier work on direction, but not on size. Jeremy Schneider's UUID benchmark write-up on Ardent Performance found that random keys fill a large share of the buffer cache and slow the whole workload. TigerData's analysis of sequential UUIDv7 ingestion reached a similar conclusion for time-series inserts.

Published ratios vary widely. Some blog posts report a 10x gain on 100 million rows, and others report almost no difference at one million. Both can be true. The ratio depends on how far the index exceeds memory, so a bigger server hides the problem for longer.

Our 22x figure is therefore a measurement of one machine with a deliberately small shared_buffers. On a server with 64 GB of RAM and a 40 GB table, expect the cliff much later and the gap to start smaller. Still, the index keeps growing with the table, and a hot random-insert workload eventually crosses any memory limit.

Treat the ratio as a warning sign rather than a forecast. The reliable parts are the density numbers, because they do not depend on cache size: 65% versus 90% leaf density (measured, 2026) holds on any hardware.

What does UUIDv7 cost you?

UUIDv7 costs 30 bytes of index per row instead of 23, and it exposes when each row was created. Neither is a reason to avoid it, but both belong in the decision.

Size. Foreign keys that reference the table carry the same 16-byte value. A table with five foreign key columns pointing at a UUIDv7 key pays the extra 8 bytes five times, in the heap and in each index.

Information leak. The first 48 bits are a timestamp. Anyone who sees an ID can read when it was created, using uuid_extract_timestamp(id). If your IDs appear in public URLs, a competitor can estimate your signup volume per day.

This is not a security flaw by itself. But it is a design fact. If creation time is sensitive, keep a separate public identifier.

Clock behavior. PostgreSQL 18 guarantees that uuidv7() values from one session increase monotonically. Across many sessions, values are only sorted to the millisecond. That is still close enough to keep inserts near the right edge, which is why our UUIDv7 density matched bigint.

When UUIDv4 is still the right choice

Keep UUIDv4 for any identifier that must be unguessable, and for tables that will stay small. A password reset token, an API key or a share link should come from a random source. A UUIDv7 contains a predictable prefix, which narrows an attacker's search.

Small tables also do not care. A lookup table with 20,000 rows has an index that fits in memory for the life of the product. Switching it gains nothing.

Use this checklist to choose:

1. Will the table pass about 5 million rows? Then use UUIDv7 or bigint.
2. Is the key exposed to users and must hide creation time? Use bigint internally and add a random public ID column.
3. Do you need IDs generated in the client before insert? Use UUIDv7. A bigint identity cannot do this.
4. Is the value a secret? Use UUIDv4 or a longer random token.

How to migrate an existing UUIDv4 table

You do not need to rewrite old rows. Change the default, and new rows start landing at the right edge.

On PostgreSQL 18:

alter table orders alter column id set default uuidv7();

Old rows keep their random IDs. The existing index stays fragmented, but it stops getting worse. New writes go to the right edge, so the hot part of the index is small and cached.

To recover the old space, rebuild the index without blocking writes, as described in the PostgreSQL REINDEX documentation:

reindex index concurrently orders_pkey;

This takes a long time on a big table and needs free disk equal to the index size. Run it off-peak. PostgreSQL 19 adds a REPACK command that rebuilds whole tables, which we covered in PostgreSQL 19 Changes Five Defaults, Not Zero.

If you are on PostgreSQL 17 or older, generate the value in the application. For example, the uuid package on npm exposes a v7() function, and the same default clause then stays as gen_random_uuid() for rows created by SQL scripts. Mixed defaults still work, because both versions are valid UUIDs.

Check your own table in five minutes

Do not trust our laptop. Measure your table with two queries.

First, read the density of your primary key index:

create extension if not exists pgstattuple;
select avg_leaf_density, leaf_fragmentation, tree_level
from pgstatindex('orders_pkey');

A density near 90 and fragmentation near 0 is healthy. A density in the 60s with fragmentation near 50 matches our UUIDv4 result.

Second, compare the index size to your memory budget:

select pg_size_pretty(pg_relation_size('orders_pkey')) as index_size,
       current_setting('shared_buffers') as shared_buffers;

If the index is several times larger than shared_buffers and inserts feel slow, you are on the wrong side of the cliff. On a managed service, also compare against the instance's total RAM, since the operating system cache absorbs part of the penalty.

The same effect hits other stores. A similar story appears in Redis vs Postgres for AI Agent Memory, Actually Measured, where the working set size decided the winner. And teams that give each agent its own branch, as in What Giving Every AI Agent Its Own Database Branch Costs, multiply any key design problem by the number of branches.

If your results differ from ours, tell us. Contact the NiharDaily editor with your PostgreSQL version, shared_buffers and row counts, and we will check the lab against your setup.

The bottom line: which key should you pick?

Pick bigint if the database is the only thing that creates IDs and the IDs stay private. Pick UUIDv7 if clients or services need to create IDs before they reach the database. Pick UUIDv4 only for secrets and small tables.

The 22x gap is not a law of nature. It depends on memory, index size and write rate. But the direction never changes: random keys cost more as the table grows, and sequential keys do not. Teams that choose UUIDv4 by habit pay for it later, on the busiest table, during the worst week.