← Back to blog

UUIDv7 vs UUIDv4: Why Your Database Index Cares

Published October 23, 2026 · 9 min read

Your table of UUID keys was fast at launch. A year later, inserts are slow, the disks are busy, and nothing in the code changed. In a 2018 PostgreSQL benchmark, insert throughput with random UUIDs fell to less than 30% of its starting speed once the index outgrew the database's memory cache. Sequential keys on the same hardware barely slowed down.

That's the whole UUIDv7 vs UUIDv4 story in one test. Version 4 is pure randomness, so every new key lands somewhere random in your index. Version 7 starts with a timestamp, so new keys line up at the end, the way auto-increment integers do. My take: if your database creates lots of UUID primary keys, make them v7. Keep v4 where a record's creation time should stay private.

Below: what a random key does to a B-tree, where the time goes, how v7 is built, the SQL for PostgreSQL 18 and MySQL, what v7 leaks, and when v4 is still the right call.

What a B-tree does with a random key

Most database indexes are B-trees: sorted lists of keys, split into fixed-size pages. In MySQL's InnoDB engine, a page is 16KB by default. The sorting is the point. To find a key, the database walks down a few levels of the tree to the one page that should hold it.

Inserting works the same way. A new key has exactly one correct page. With an ever-increasing key, that's always the last page, which is already in memory because you wrote to it a millisecond ago.

A v4 UUID has 122 random bits. Its correct page could be any page in the index. So each insert needs a different page, and on a big table that page is probably not in memory. Yves Trudeau at Percona laid out the cost for InnoDB: on tables much larger than the buffer pool, most inserts cause two I/O operations, one read to fetch the page and one write to flush it later.

Then there are page splits. When a key must go into a full page, the database splits that page in two. MySQL's documentation spells out the result. Insert in sequential order and pages end up about 15/16 full. Insert in random order and they end up anywhere from 1/2 to 15/16 full. Same rows, more pages, less of your index fits in memory.

Two B-trees side by side. On the left, UUIDv4 inserts land on random leaf pages across the index, causing page splits and half-empty pages. On the right, UUIDv7 inserts all land on the rightmost leaf page, which stays hot in memory and fills to about 15/16.
Random v4 keys land all over the index; v7 keys land on the last page. Diagram: CyberScryb.

Where the time goes: cache misses and extra WAL

The 2018 benchmark came from Tomas Vondra, a PostgreSQL contributor, writing on the 2ndQuadrant blog (now hosted by EDB). His test was deliberately simple: insert rows into a table with one UUID column and a unique index, on a slow 3-disk SATA array, for four hours.

When everything fit in the 4GB of shared buffers, the key type made almost no difference. Once the index outgrew shared buffers, random UUIDs dropped below 30% of their small-scale throughput. His time-prefixed and sequence-prefixed generators held their speed. Those weren't v7, but they used the same idea: put something increasing at the front of the key.

The write-ahead log (WAL) told the same story. PostgreSQL's WAL is the journal it writes before changing data files. With the default full_page_writes setting, PostgreSQL writes the entire page to WAL the first time that page changes after a checkpoint. Random keys touch a different page on almost every insert, so almost every insert pays that tax. In Vondra's medium-size test, random UUIDs generated more than 20GB of WAL, most of it full-page images. The time-based generator produced about 2.5GB. And the random run did that at 260 transactions per second versus 840.

WAL volume matters more than it sounds. Replicas, backups, and point-in-time recovery all ship or store it. Buildkite saw this firsthand. After switching to time-ordered UUIDs, along with several other changes over six weeks, it measured a 50% drop in the WAL rate of its primary database, with a similar drop in write I/O. That made read replicas easy to set up.

The UUID standard itself puts it bluntly: the difference between ordered and random inserts "can be one order of magnitude or more".

How UUIDv7 works, in 128 bits

UUIDv7 was defined in RFC 9562, published in May 2024. It replaced RFC 4122, the 2005 spec most UUID libraries grew up on. The layout is simple:

Forty-eight bits of milliseconds last until about the year 10889, so v7 has no Year 2038 problem hiding in it.

The timestamp sits in the most significant bits, so sorting the raw bytes sorts by creation time. The RFC says the text form sorts correctly too. That's the entire trick. Two keys made a second apart are neighbors in the index, and the newest key always goes on the right edge.

Here's the example from the RFC:

017F22E2-79B0-7CC3-98C4-DC0C0C07398F

The first 12 hex digits, 017F22E279B0, are the timestamp. In decimal that's 1645557742000 milliseconds. Paste that number into CyberScryb's epoch converter, which auto-detects milliseconds, and you get February 22, 2022, 19:22:22 UTC. The 7 that opens the third group is the version.

You still get 74 bits of randomness per key (or a mix of counter and random bits, which the RFC allows). That's plenty to keep separate machines from colliding without any coordination, which is why people reach for UUIDs in the first place.

UUIDv7 vs UUIDv4 in PostgreSQL 18 and MySQL

PostgreSQL 18: it's built in

PostgreSQL 18 shipped on September 25, 2025 with a native uuidv7() function, plus uuidv4() as a clearer name for gen_random_uuid(). PostgreSQL 17 doesn't have uuidv7(). The PostgreSQL 18 docs cover it:

CREATE TABLE orders (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  total_cents integer NOT NULL
);

SELECT id,
       uuid_extract_version(id)   AS version,  -- 7
       uuid_extract_timestamp(id) AS made_at
FROM orders
ORDER BY id DESC
LIMIT 5;

Two nice details. First, uuidv7() takes an optional shift interval, so a backfill can stamp keys with an older time, like uuidv7(interval '-30 days'). Second, PostgreSQL's version uses those 12 spare bits for sub-millisecond time. As Brandur Leach points out, this keeps IDs strictly increasing within one database connection, even if the system clock goes backward. Across connections, there's no such promise.

PostgreSQL stores table rows in a heap, not in key order, so the damage from v4 shows up in the index and the WAL rather than the table itself. That's exactly what Vondra measured.

MySQL: generate in the app, store as BINARY(16)

InnoDB hurts more. Its primary key is a clustered index: the table's rows live in the primary key's B-tree. So a random key scatters the rows themselves, not only an index. And every secondary index stores a copy of the primary key, which MySQL's docs say is why a short primary key pays off.

MySQL's built-in UUID() returns a version 1 UUID, not v7, so generate v7 in your application. Python 3.14 added uuid.uuid7() to the standard library. Then store the 16 bytes, not the 36-character string:

CREATE TABLE orders (
  id BINARY(16) PRIMARY KEY,
  total_cents INT NOT NULL
);

INSERT INTO orders (id, total_cents)
VALUES (UUID_TO_BIN('017f22e2-79b0-7cc3-98c4-dc0c0c07398f'), 1299);

Leave off UUID_TO_BIN's swap flag. It rearranges version 1 timestamps, and MySQL says for UUIDs that aren't version 1, it provides no benefit. A v7 is already in the right order.

The string-versus-bytes choice is bigger than it looks. Trudeau's example: a billion-row table with a char(36) UUID key and five secondary indexes stores that key six times per row, about 216GB of key values alone. In one schema he analyzed, about 70% of storage was UUID values. The RFC agrees: where you can, store UUIDs as the underlying 128-bit binary value.

What UUIDv7 leaks: the creation time

Here's the catch. Anyone who can see a v7 ID can read when it was made. You did it with the RFC example a few minutes ago.

A developer who blogs as MikeNotThePope ran into this while building an applicant tracking system. His user IDs were v7, so anyone could pull out when an account was created. The older the account, the older the applicant must be, which opens the door to age discrimination. He moved applicant IDs to v4. His words: "The database team will grumble about performance. I'll sleep well."

The same logic covers anything where timing is sensitive. When an account was opened. When a document was uploaded. How many orders you took this week, if someone collects enough IDs. Aiven's write-up on PostgreSQL 18 recommends keeping v7 for internal keys and exposing a random v4 as the public identifier.

The RFC's own security section is calm about it. It calls the embedded timestamp "a very small attack surface". It also says if a UUID plays any part in a security operation, use v4. And it says no UUID should be a security capability, meaning an ID whose mere possession grants access. A secret link needs a random token plus an access check, not any flavor of UUID.

When UUIDv4 is still the right call

The ID is public and its timestamp says too much. Applicant records, medical records, anything where "when" is personal. Use v4 for the public ID. You can still put a v7 or an integer primary key underneath.

Your database splits data by key range across servers. Ordered keys are a gift to one B-tree and a curse to a distributed one. Google's Spanner docs warn a key that always increases sends all new writes to the same server, and they recommend UUID version 4 for that reason. Check your database's docs before copying advice written for PostgreSQL or MySQL.

The table is small. In Vondra's test, when everything fit in memory, there was almost no difference. A settings table with 300 rows doesn't care.

You only need a few IDs. For test fixtures, config files, and one-off records, any version works. CyberScryb's free UUID generator makes v4 UUIDs in your browser using crypto.getRandomValues(), up to 100 at a time.

Switching from UUIDv4 to UUIDv7 without a migration

Good news: v7 is still a UUID. Same 128 bits, same column type. Buildkite noted this lets you keep an existing PostgreSQL uuid column and switch it from storing v4 values to v7. On PostgreSQL 18 that's one statement:

ALTER TABLE orders ALTER COLUMN id SET DEFAULT uuidv7();

Old rows stay where they are. New rows start appending at the right edge of the index. Two things change. You can't sort old rows by ID to get creation order, since v4 IDs carry no time. And any code that validates UUIDs by checking for version 4 will reject the new ones. Buildkite hit exactly that: when it first tried version 7, some customers' systems that validated UUIDs broke because they didn't know v7 yet. Grep your codebase and your API clients for UUID regexes before you flip the switch.

A few more mistakes worth skipping:

FAQ

Is UUIDv7 safe to use in URLs?

It's as hard to guess as you need for most IDs, with 74 random bits per key. But it reveals its creation time to anyone who sees it. If that time is sensitive, expose a v4 or a separate token instead.

Can I mix UUIDv4 and UUIDv7 in the same column?

Yes. Both are 128-bit UUIDs, and the column doesn't care which version it holds. Index locality improves for new rows, and old rows keep their random order.

Does PostgreSQL 17 have uuidv7()?

No. The function arrived in PostgreSQL 18. On older versions, generate v7 in your application and store it in the same uuid column.

Let the keys line up

A random key asks your database to keep the whole index in memory forever. A v7 key asks it to remember the last page. That's the difference you pay for in RAM, disk, and WAL.

Pick one table that gets the most inserts, check what generates its IDs, and if it's v4 with nothing sensitive in the timing, make v7 the default.