Why Your Inserts Got Slower After You Switched to UUIDs.

By Muhammad Umar·February 24, 2026·7 min read·Issue #14

You switched your primary keys from bigserial to uuid. There were good reasons: you can generate them on the client, they do not leak how many customers you have, and merging data from two systems stops being a renumbering exercise.

Then the writes got worse. Not immediately, and not dramatically, but the table that used to absorb inserts without anyone noticing now shows up in the slow query log. The index is much larger than you expected. Your cache hit rate has drifted down and nobody changed the queries.

Same rows. Same columns. Same index. The only thing that changed is the order the keys arrive in, and it turns out that decides almost everything about what an insert costs.

The problem: an index is sorted, and you stopped arriving in order

If you have read why is my database query slow, you already have the model you need. An index is a sorted structure, and sorted is what makes it fast.

A sequential key means every new row belongs at the far right of that order. Always the same place. The database walks to the rightmost leaf page, adds the entry, and stops.

A random key means every new row belongs somewhere unpredictable. Ten thousand inserts land in ten thousand different places, spread across the whole index.

That difference produces four separate costs, and they compound.

The hot page stops being hot

With sequential keys, the page you are writing to was the page you wrote to a microsecond ago. It is in memory, and it stays in memory for as long as inserts keep coming. PostgreSQL even caches the rightmost block so it does not have to descend the tree each time.¹

With random keys, the page you need is one of thousands and was probably evicted long ago. Now an insert starts with a read from disk, and the useful pages you had cached get pushed out to make room for a page you will touch once.

Pages split down the middle

When a leaf page fills up it splits, and where the split happens depends on where you are inserting.

PostgreSQL detects sequential insertion and splits the rightmost page unevenly, leaving the left side full and starting a fresh page on the right.¹ Nothing is wasted, because nothing will ever be inserted into the left side again.

A split in the middle of the tree cannot make that assumption, so it divides the entries roughly in half. Both halves are now around half empty. Do that continually and the index holds far fewer entries per page than it could, which means more pages, a larger index, and less of it fitting in memory.

The write-ahead log gets much larger

This is the cost people miss, and it is often the biggest one.

To protect against a crash during a page write, PostgreSQL writes the entire page to the write-ahead log the first time that page is modified after a checkpoint, rather than just the change.²

Sequential inserts touch one page repeatedly, so you pay that full-page cost once and then write small records. Random inserts touch a different page nearly every time, so a large share of your inserts trigger a full page image. The same number of rows produces several times the log volume, which then has to be written, archived, and shipped to every replica.

The key itself is twice the size

A bigint is 8 bytes. A uuid is 16. That sounds minor until you remember that the primary key is copied into every secondary index and every foreign key referencing the table.

Wider entries mean fewer per 8 KB page, which means more pages for the same rows, which means a taller tree and more of your memory spent on index rather than data. Combined with the half-empty pages from splitting, the index can end up several times the size of the equivalent on a sequential key.

None of this is about UUIDs being slow. It is about randomness being expensive in a structure whose entire advantage is order.

The fix: keep the shape, drop the randomness

The useful thing about this diagnosis is that the benefits you wanted from UUIDs have almost nothing to do with the randomness. You wanted 128 bits of identifier you could generate anywhere. You did not specifically want those bits in an unpredictable order.

Use UUIDv7

Version 7 puts a millisecond timestamp in the leading 48 bits and fills the rest with randomness.³ Sort two v7 identifiers and they come out in roughly the order they were created.

v4  9f1c0e2a-7b3d-4c6e-8a91-5d2f7c4b8e10   leading bits: random
v4  1a7e5b93-2c48-4f0d-9b6a-3e8c1d5f2049   no relationship between them

v7  0193c8b1-4e2a-7c3d-8f91-2b6d4a7e5c08   leading bits: a timestamp
v7  0193c8b1-9f04-7a2e-b153-8c9e1f3d6b24   so they sort by creation time

Inserts land at the right-hand end again, the page stays hot, splits go back to being uneven, and the log volume returns to something like the sequential case. You keep client-side generation and you keep 128 bits.

Wrong when you chose UUIDs specifically so that identifiers reveal nothing. A v7 identifier tells anyone holding it roughly when the row was created, and two of them can be compared to see which came first. That is a real disclosure, and for some data it matters.

Keep a sequential key internally and expose a UUID

Two columns. A bigint primary key that every index and foreign key uses, and a separate random UUID that appears in URLs and APIs.

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id   uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
  customer_id bigint NOT NULL REFERENCES customers (id),
  created_at  timestamptz NOT NULL DEFAULT now()
);

This is the version with the best properties on paper. Your joins run on a compact ordered key, and nothing outside the database ever sees it. Enumeration attacks get nothing, and no timestamp leaks.

Wrong when the complexity is not free, and it is not. Every developer must know which identifier belongs in which context, and getting it wrong in one API handler leaks the internal sequence you were trying to hide. It also gives you a second unique index to maintain, which brings back some of the random-insert cost you were avoiding, just on a smaller structure.

Stay on v4 and lower the fillfactor

If you cannot change the identifier, you can at least stop the page splits. Setting a lower fillfactor leaves free space on each index page so a random insert has somewhere to go without splitting.

CREATE INDEX idx_orders_public ON orders (public_id) WITH (fillfactor = 70);

This trades space for split avoidance, which is the right trade when the alternative is splits that waste the space anyway.

Wrong when you expect it to solve the problem. It reduces splitting. It does nothing about cache locality or full-page writes, which are usually the larger costs. Treat it as relief rather than a fix.

How to tell whether this is actually your problem

Before rewriting a schema, confirm the diagnosis. Three things will tell you.

Compare index size against row count. An index far larger than the data it points at, on a table that only ever grows, is the fingerprint of continual mid-tree splitting.

Look at write-ahead log volume per transaction. If it is much higher than the size of the rows you are writing, you are paying for full page images, which points straight at scattered writes.

Watch the buffer cache hit ratio while inserting. Sequential inserts barely disturb it. Random inserts push out pages that were being used for reads, so a falling ratio during write-heavy periods is the tell.

When this is the wrong advice

When the table is small or write-light. Everything above scales with how much of the index fits in memory. A table of fifty thousand rows fits entirely in cache, every page is hot, and random insertion costs you nothing measurable. Rewriting identifiers there is work with no payoff.

When the disclosure matters more than the throughput. A creation timestamp embedded in an identifier is a genuine leak. If your rows are appointments, orders, or anything where ordering and timing are sensitive, take the write cost knowingly rather than sorting by time in public.

When your storage engine does not work this way. All of this describes B-trees with in-place updates. An engine built on log-structured merge trees writes sequentially regardless of key order, so random keys cost far less there. Check what you are actually running before assuming the advice transfers.

The takeaway

UUIDs did not make your database slow. Randomness did, in a structure whose whole purpose is keeping things in order, and the two arrived together because the standard identifier everyone reaches for happens to be random by design.

So separate the two requirements. You wanted an identifier you could generate anywhere without coordination. You never needed it to be unpredictable in its leading bits, and once you stop asking for that, the cost goes away.