Your UUID Primary Keys Are Quietly Taxing Every Insert

Nobody gets paged because of a primary key type.

The problem shows up as a slow drift. Insert latency that was 2ms last quarter is now 6ms. WAL volume grows faster than the data you write. Replica lag spikes after a checkpoint. The cache hit ratio slides from 99.9% to 97% while the queries stay exactly the same.

Then someone runs EXPLAIN on the slow query, finds nothing obviously wrong, and blames the disk.

Sometimes the decision was made years earlier, in a line like this:

Random UUIDs (version 4) are a reasonable default for a lot of reasons. But a B-tree index pays for that randomness on every single insert, and the bill grows with the table.

A Library Where Every New Book Goes on a Random Shelf

Picture two libraries.

In the first, new books always go at the end of the last shelf. The librarian only needs to know where that one shelf is. When it fills up, they start a new shelf next to it. Every other shelf can sit untouched for years.

In the second, every new book has to go on a specific shelf chosen at random. To add a book, the librarian walks to a random aisle. Any shelf could be next, so every shelf has to stay reachable. When a full shelf receives a book, it gets split in half to make room.

A B-tree index on a bigint identity column is the first library. A B-tree on gen_random_uuid() is the second.

With sequential keys, every insert goes to the rightmost leaf page. PostgreSQL even has a fast path for this: since version 11 it caches the rightmost leaf and skips the tree descent entirely when the new key belongs there. The hot part of the index is one page plus the path to it.

With random keys, inserts are spread across the leaf level of the index. The upper levels of the tree are small and stay hot, but leaf pages make up well over 99% of a large B-tree, and all of them become the working set. Once that leaf working set grows much larger than the useful portion of shared_buffers, inserts are increasingly likely to need a page that isn't cached, and random key generation turns into extra I/O.

Why this matters in production: the trouble starts not at a particular row count but when the leaf working set outgrows memory. A table can run happily for a year, then degrade over a few weeks as the index gets bigger than your cache can comfortably hold. Nothing in your code changed.

The Benchmark

I loaded 10 million rows into three identical tables on PostgreSQL 18.6. The only difference was the primary key:

Rows went in as 1,000 committed batches of 10,000, with shared_buffers = 128MB so the indexes would outgrow memory the way a real production index eventually does. Before each run I issued a CHECKPOINT and reset the WAL statistics.

In this workload, UUIDv4 took 3.8x as long as bigint and 3.6x as long as UUIDv7, generated 60% more WAL than bigint, and produced an index 29% larger than UUIDv7. UUIDv4 and UUIDv7 are the same 16-byte data type, with the same row count on the same hardware. The only difference is the order the keys arrive in.

Benchmark scope: PostgreSQL generated the UUIDv7 values itself, from a single backend, so its near-perfect locality reflects PostgreSQL's native, backend-local monotonic generation. A workload where many application servers generate UUIDv7 independently will see more interleaving, because their clocks and generators differ.

Three separate mechanisms produce that gap.

Cost 1: The Index Stops Fitting in Memory

Look at the last row. The bigint and UUIDv7 indexes were read from outside shared buffers once in total. The UUIDv4 index was read 3.15 million times.

Those reads are consistent with inserts repeatedly needing leaf pages that were no longer resident in shared buffers, forcing PostgreSQL to fetch them again. The workload only ever adds new rows, yet the buffer cache is thrashing, because those new rows land on a randomly distributed set of leaf pages.

On my laptop most of those reads were served by the operating system's page cache and a local NVMe drive. On cloud block storage, where a miss is a network round trip, I'd expect the gap to be wider, not narrower.

Why this matters in production: the hit ratio drop also hurts your read queries. Random-key inserts push the pages your SELECTs need out of shared buffers, so lookups that were fast get slower even though nobody touched them.

Cost 2: Half-Empty Pages

Leaf density tells the second story. Sequential inserts grow the right edge of the index. PostgreSQL's default B-tree fillfactor is 90%, and that target is used when building an index and when splitting the rightmost page. The page left behind is never touched again, so it stays about 90% full.

Random inserts eventually split pages in the middle of the tree, and for those PostgreSQL divides the entries roughly evenly between the two pages. Each split leaves two half-full pages, and future random inserts only partly refill them. Under a sustained random-insert workload, average occupancy settles well below the fillfactor; here it landed at 70.1%.

That's the extra index size: the same keys spread across about 30% more pages. More pages means more memory needed to cache the index, which makes Cost 1 worse. The two feed each other.

Cost 3: Full-Page Writes Inflate WAL

This is the one most people never connect to their key type.

PostgreSQL writes every change to the write-ahead log before touching the data file. That's the aircraft black box that makes crash recovery possible. But there's a catch. If the server crashes halfway through writing an 8KB page to disk, the page could be half old and half new, and a small WAL record can't repair a torn page.

So with full_page_writes = on (the default, and it should stay on), the first modification of any page after a checkpoint writes the entire 8KB page image into WAL. Later changes to that page in the same checkpoint cycle only log the small delta.

The benchmark recorded 45 full-page images in each of the bigint and UUIDv7 runs, across 10 million rows. The point isn't that sequential inserts produce zero full-page images. What matters is how many distinct existing pages get modified for the first time after a checkpoint. Sequential workloads keep modifying a small hot set of pages at the right edge. Random UUIDv4 inserts spread those first modifications across the whole leaf level.

The UUIDv4 run recorded 121,490 full-page images, compared with 45 in the bigint run. At 8 KiB per uncompressed page image, the difference represents roughly 950 MiB of page-image payload, remarkably close to the roughly 942 MB difference in total WAL between UUIDv4 and bigint. The two numbers aren't expected to match exactly, since wal_fpi counts full-page images across the whole cluster and WAL also carries record headers and other records. But the three runs were otherwise identical, so the result strongly indicates that extra full-page images are the major contributor to the WAL gap.

Why this matters in production: WAL has to be generated and persisted according to the server's durability settings. In a replicated or archived system it also adds to the data that must be shipped, replayed, and retained. So random keys don't just affect the primary. In a write-heavy workload they can increase replication traffic, replay work on replicas, and WAL archive storage. The effect peaks right after each checkpoint, when every page becomes "first touch" again, which is one reason lag charts on these systems often have a sawtooth shape.

UUIDv7: Time First, Random Second

UUIDv7 (standardized in RFC 9562) keeps the 128-bit UUID format but changes the layout. The first 48 bits are a Unix timestamp in milliseconds. After the version and variant bits, the rest is random (or, in some implementations including PostgreSQL's, partly extra timestamp precision).

Because the timestamp occupies the most significant portion of the UUID, UUIDv7 values generated later generally sort later when they come from the same clock source. To a B-tree that looks almost exactly like a sequence: inserts go to the right edge, pages fill densely, and very few existing pages are touched per checkpoint. The benchmark shows it: UUIDv7 landed within a few percent of bigint on load time, matched it on full-page images and leaf density, and its only real penalty is a larger index because each key is 16 bytes instead of 8.

You still keep everything that made UUIDs attractive in the first place. Application servers, mobile clients, or separate services can generate IDs without asking the database. Different services or shards can generate IDs independently with an extremely low probability of collision. You can create the ID before the INSERT, which makes idempotent retries and outbox patterns simpler.

PostgreSQL 18 ships uuidv7() natively, so you no longer need an extension or application-side generation. Its implementation uses the 12 bits immediately after the millisecond timestamp for sub-millisecond precision, helping UUIDs generated by the same backend remain ordered even when many are created within the same millisecond. It also pairs with uuid_extract_timestamp(), which pulls the creation time back out of the ID.

What UUIDv7 Still Costs

It's much better, but it isn't free, and pretending otherwise is how teams get surprised later.

It leaks creation time. Anyone who sees a UUIDv7 can recover the time value encoded in it, with millisecond precision in the standard layout, which reveals roughly when the record was created. If your IDs show up in public URLs, a competitor can estimate your signup rate by creating two accounts a week apart. For user-facing identifiers where that matters, expose a separate random token and keep the v7 key internal.

It's still 16 bytes. The benchmark's heap was 805 MB for both UUID tables against 730 MB for bigint, and the UUIDv7 index was 41% larger than bigint's. That cost repeats in every foreign key column and every index that includes the key. A table referenced by five others pays it six times.

The right edge is a single hot spot. Sequential keys of any kind concentrate inserts on one leaf page. At very high concurrency, sessions can queue for the lock on that page. This affects bigint identity too, and it takes serious write concurrency before it shows up, but it's the trade you make for locality.

Order is not guaranteed across independent generators. If several application servers generate IDs, clock skew between them means IDs are roughly time-ordered, not strictly ordered. Inserts land near the right edge instead of exactly on it. For an index that's fine. For logic that assumes "larger ID means created later," it isn't.

Which Key Should You Use?

• bigint GENERATED ALWAYS AS IDENTITY: one database generates every ID and the IDs never need to be unguessable. Smallest, fastest, and the default unless you have a reason not to.
• UUIDv7: IDs are created outside the database (clients, multiple services, offline-first apps), data from several shards or regions needs to merge, or you need the ID before the row exists.
• UUIDv4: as a separate, opaque public identifier where unpredictability helps, such as resource URLs or share links, stored in a column with its own index. Not as the primary key of a high-write table. And not as an authentication token: keep those as dedicated security credentials.

Already Running on UUIDv4?

You don't need to rewrite the table. Because UUIDv4 is just a 128-bit value, you can switch the default:

Old rows keep their random IDs and still sort among them. New rows all carry timestamps from right now, so they fall into one narrow slice of the key range and concentrate their inserts on a small group of pages instead of the whole index. The existing index won't shrink or repack itself. If you want the density back, rebuild it in a quiet window without blocking writes:

It needs extra disk space and takes longer than a plain REINDEX, and if it fails partway it leaves an invalid index behind that you have to drop. Measure WAL volume and full-page images before and after so you know what you actually gained.

To see where your own tables stand today:

A leaf density near 70% on a primary key, unusually high wal_fpi compared with a sequential-key baseline, and a growing idx_blks_read on an insert-heavy table are the fingerprint of random keys.

The Takeaway

A primary key is not just an identifier. It decides where every new row lands in the index, and that decides how much of the index has to stay in memory, how full its pages stay, and how much WAL every insert writes.

Random keys spread that work across the whole index. Time-ordered keys keep it in one place. With uuidv7() built into PostgreSQL 18, you no longer have to choose between distributed ID generation and an index that ages well.

Benchmark setup: PostgreSQL 18.6 on an Apple M2 Pro (16 GB RAM, local NVMe) in a fresh, otherwise idle cluster, shared_buffers=128MB, max_wal_size=1GB, defaults for everything else (including full_page_writes, wal_compression, synchronous_commit and fillfactor), 1,000 committed batches of 10,000 rows, CHECKPOINT and WAL stats reset before each run. Absolute times will vary on other hardware. The ratios are the point.