Why random UUIDs make your database slower as it grows

A random primary key isn't just a slightly slower primary key. Every insert lands at an unpredictable point in the index, splitting a page that should have stayed full — and unlike most costs in a database, this one gets worse as the table gets bigger. The fix is a one-word change at schema-design time and a painful migration later, which is the only real reason to care about it today. Here's the mechanism, and the measurements.

The standard says it out loud

This isn't folklore or a benchmark someone ran once. It's written into the specification that defines UUIDs. RFC 9562 (May 2024, which obsoleted the original RFC 4122) introduced UUIDv7 specifically to fix it, and the reasoning is stated plainly:

UUID versions that are not time ordered, such as UUIDv4, have poor database-index locality. This means that new values created in succession are not close to each other in the index; thus, they require inserts to be performed at random locations.

"Poor database-index locality" is doing a lot of work in that sentence. Unpacking it is the rest of this post.

What "appending at the right" actually buys you

A B-tree index stores rows in sorted order across fixed-size pages. Insert a key and the database must put it in the page where it belongs — and if that page is full, split it into two, each roughly half full.

Now look at what PostgreSQL's own documentation says about how it packs those pages:

For B-trees, leaf pages are filled to this percentage during initial index builds, and also when extending the index at the right (adding new largest key values). If pages subsequently become completely full, they will be split, leading to fragmentation of the on-disk index structure.

Read the parenthetical carefully: "adding new largest key values" is a special case the database explicitly optimises for. The default fill factor is 90, so a key that's always the new maximum lands in the rightmost page, which gets packed to 90% and then a fresh one is started. No splitting, no fragmentation, and the pages end up physically next to each other on disk.

A random key gets none of that. It lands in the middle somewhere. That page is likely full, because it was packed to 90% ages ago. So it splits — two pages, each around 45% full, and the new one is allocated wherever there's free space rather than next to its sibling. Do that a few million times and you have an index that's twice the size it needs to be, stored in an order that has nothing to do with its logical order.

The randomness is the whole problem. It's not that UUIDs are big (though 16 bytes versus 8 does matter); it's that consecutive inserts have no relationship to each other.

The measurements

A published benchmark from the PostgreSQL consultancy credativ compared two identical schemas on PostgreSQL 18, differing only in whether the primary key was generated by uuidv4() or uuidv7(). On a one-million-row table, the index internals came out like this:

Two of those rows deserve a second look.

The density numbers match the documentation exactly. UUIDv7 landed at 89.98% — which is the documented default fill factor of 90, hit essentially perfectly, because every insert went to the right-hand edge. UUIDv4 settled at 71%, the long-run average of endlessly splitting full pages in half. That's not a coincidence; it's the mechanism showing up in the measurement.

The contiguity numbers are the starker pair. Zero contiguous links versus 3,812. Under the random key, not one leaf page was physically adjacent to its logical neighbour. A range scan that should be a sequential read becomes a scattered one.

The part that actually matters

Index size is a footnote. This is the finding worth internalising — the same benchmark timing 50 million inserts:

• Into an empty table: 20m 40s (v4) versus 1m 46s (v7)
• Into a table that already held 50 million rows: 46m 17s (v4) versus 1m 40s (v7)

Look at what happened to each column. The random key more than doubled — 20 minutes became 46 — purely because the index it was inserting into was bigger. The time-ordered key didn't move at all; 1m 46s became 1m 40s, which is noise.

That's the shape of the problem in one comparison. A benchmark on an empty table is the friendliest possible case for a random key, and it's where the gap is smallest. Every day your table grows, the gap widens. This is why the problem tends to arrive as "the database has been getting gradually slower for months and nothing changed," which is the most annoying kind of performance problem to diagnose — the same reason connection exhaustion is so confusing when it hits.

Standard caveats apply: this is a third-party benchmark with no hardware specified, and your numbers will differ. Take the direction and the mechanism, not the decimal places.

What to actually do

1. On new tables, use uuidv7(). PostgreSQL 18 ships it as a built-in function — no extension, no application code. It sits right next to uuidv4() in the manual.
2. On older versions or other databases, generate v7 in the application. Most language ecosystems have a library now, and RFC 9562 is short enough to check an implementation against.
3. Don't migrate a working table just for this. Changing a primary key type means rewriting the table and every foreign key that references it. Wait until you're rewriting it anyway. The cost of choosing correctly is zero; the cost of fixing it is a migration weekend.
4. If you're stuck on v4 at scale, lower the fill factor. The documentation explicitly suggests values between 50 and 90 to "smooth out" the rate of page splits on insert-heavy tables. It's a mitigation, not a cure.
5. Consider whether you needed a UUID at all. A bigint identity column is half the size and beats both on every index metric. UUIDs buy you client-side generation and no coordination between writers — real benefits, worth paying for when you need them. They are not a default.
6. Know what you're leaking. A v7 UUID contains its creation timestamp, and Postgres will hand it back with uuid_extract_timestamp(). If your IDs appear in URLs, anyone can read the exact millisecond a record was created — and by comparing two, infer your growth rate. For most applications that's harmless. For some it genuinely isn't.

Our opinion

This is a default, not an optimisation, and the distinction matters more than the benchmark does. Optimisations get earned with profiling. Defaults get chosen once, in the ten seconds it takes to write a column definition, and then live for the life of the product. Choosing the good one costs nothing. We think that asymmetry — free now, expensive later — is the entire argument, and the performance numbers are just what makes it vivid.

We'd also be honest about scale, because the internet is not. Most tables will never notice this. If you have fifty thousand rows and a write every few minutes, random keys are completely fine and you should spend your attention elsewhere. This matters for high-write tables that grow without bound — events, logs, messages, orders, telemetry — and it matters there precisely because those are the tables nobody thinks about until they're enormous.

And a small correction to some received wisdom: "UUIDs are slow" is the wrong lesson to draw. Random UUIDs are slow. The UUID part is fine. The property that hurt you was never the format — it was generating keys with no relationship to the ones before them, which is also why choosing an ID scheme is a design decision about your data, not a formatting preference. It belongs in the same conversation as designing an API that lasts, because your IDs outlive almost every other decision you make.

How Ashvara helps

We pick key strategy when we design the schema — whether an identity column is genuinely enough, where client-generated IDs earn their cost, and what the ID reveals once it's in a URL. It takes a few minutes at the start and it's the kind of thing nobody gets to revisit cheaply.

That's everyday backend and API work for us. If your database has been getting quietly slower for months with no change you can point to, tell us what you're seeing — index bloat from random keys is worth ruling out early, and it's a quick thing to check.

Sources: RFC 9562: Universally Unique IDentifiers (UUIDs) (IETF, May 2024); PostgreSQL 18 documentation — UUID functions and CREATE INDEX fill factor; credativ's UUIDv4 vs UUIDv7 benchmark on PostgreSQL 18. Benchmark figures are third-party and hardware was not specified.