UUIDv7 and the end of the random primary key

The argument has been going since about 2012. One side says integers are the right primary key: they're small and sequential, and the B-tree loves them. The other says UUIDs are: you can generate them anywhere without a round trip to the database, they don't leak how many customers you have, and merging data from two systems never collides. Both sides were right, and the argument never ended because each was describing a real cost the other side was paying.

PostgreSQL 18 ships uuidv7() in core. It's a UUID whose first 48 bits are a millisecond timestamp and whose remaining bits are random. That one change removes the cost the integer side kept pointing at, and I think it settles the argument. The numbers below are from a real table.

What was actually wrong with UUIDv4

The size was never the main problem. Sixteen bytes against eight is a real difference, but it's not the one that hurts.

Randomness is what hurts. A B-tree index on a random key gets written in random places. Every insert lands on a page picked by a roll of the dice, so over time every page of the index is a bit dirty, every page might split, and the index's working set is the whole index. On a table where recent rows are the hot ones, which is nearly every table, the index for 200 million rows needs all 6 GB in memory to insert fast, where a sequential key would need the last few hundred pages.

I measured this properly on an events table with about 200 million rows: same instance, same schema, one column type swapped.¹

Key type	Index size	Insert throughput	Page splits per minute	Buffer cache hit on index
bigint identity	4.3 GB	baseline	41	99.7%
uuid v4	6.1 GB	0.62x baseline	2,870	91.2%
uuid v7	6.1 GB	0.96x baseline	58	99.5%

v4 and v7 are the same size, both 16 bytes, and everything else differs. The v7 index gets written at its right hand edge, like the integer one, because the timestamp prefix sorts new keys after old ones. The splits go away, the hot pages stay hot, and the cache hit rate comes back.

▶ The same inserts into a uuid v4 and a uuid v7 index: an animation that plays in the original post.

The 4 percent gap left between v7 and bigint is the size difference plus the random tail within a millisecond. I can live with that gap. I couldn't live with the 38 percent gap to v4, and it's why this table had for years used an integer key plus a separate public_id uuid column, with an extra index to look rows up by it.

The other thing v7 gives you for free

The timestamp is in the key, and you can get it back out:

SELECT uuid_extract_timestamp('019627f3-9a5c-7c3b-8e8a-2b0c4a4d9f11');
-- 2026-04-11 14:22:07.836+00

So a query like "events in the last hour" can use the primary key index, and a range scan on the primary key is also a time range scan. On the events table that let me drop a separate index on created_at that only existed for range queries.²

There's a gotcha here worth being precise about. The timestamp in a v7 UUID is when the key was generated. It isn't when the row was committed, or when the event happened. If the application generates the key and the insert gets retried twenty seconds later, or the key comes from a machine whose clock is a few hundred milliseconds off, the key's timestamp and the row's created_at won't agree. For sorting and coarse range queries that doesn't matter. For anything that has to be exact, keep created_at as a column and treat the key's timestamp as an approximation. I kept the column and dropped the index on it.

▶ What a uuid v7 key carries: an animation that plays in the original post.

Generating it in the right place

The whole point of UUIDs was being able to generate them outside the database. uuidv7() in Postgres doesn't take that away, it just adds a server side option. Both work, and the choice comes down to where you want the clock.

SQL

CREATE TABLE events (
  id          uuid PRIMARY KEY DEFAULT uuidv7(),
  kind        text NOT NULL,
  payload     jsonb NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

TypeScript

import { v7 as uuidv7 } from "uuid";

// Generated in the app: the id exists before the insert, so it can be
// used in logs, in the outbox message, and in the retry key.
const event = { id: uuidv7(), kind: "order.created", payload };
await sql`INSERT INTO events ${sql(event)}`;

Python

import uuid  # 3.14 added uuid7()

event_id = uuid.uuid7()
cur.execute(
    "INSERT INTO events (id, kind, payload) VALUES (%s, %s, %s)",
    (event_id, "order.created", Json(payload)),
)

If the application generates the key, make sure every generator in the fleet uses the same algorithm. RFC 9562 fixes the layout and the mainstream libraries follow it, but a couple of older "ulid as uuid" shims put the timestamp in a slightly different place, and those keys interleave badly with real v7 keys in the same index.

One detail matters when the application generates keys. Within one millisecond, the random bits decide the order. Postgres's implementation also uses some of those bits as a sub-millisecond counter, so two keys generated back to back on the same server are monotonic, and most client libraries do the same. So keys from one process are strictly increasing, which is what the B-tree wants, and keys from different processes in the same millisecond come in arbitrary order, which is fine.

Migrating a table that has v4 keys

You can't change the keys of existing rows without changing every foreign key that points at them, and you shouldn't try. What you do is stop the bleeding: new rows get v7 keys and old rows keep theirs.

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

If the database generates the keys, that's the whole migration. If the application does, it's a dependency bump and a one line change in the model.

The index doesn't improve straight away, because the old random keys are still spread through it. It improves from the right hand side outwards. Every new page is a tightly packed v7 page, and the old pages stop getting written and settle down. Once the write pattern has calmed down you can afford a REINDEX CONCURRENTLY, and after that the old part of the index is packed too. All that's left is the historical randomness in the key order, which costs nothing on a page that never changes.

On the events table I changed the default on a Tuesday, watched the split rate drop over the next two days as the hot region of the index turned all v7, and ran the reindex the following weekend.³ The public_id column and its index are gone, the integer key is gone, and the primary key is the only key.

When integers are still right

Small lookup tables. A countries table with 249 rows doesn't need a 16 byte key, and a smallint is the honest choice.

Tables only ever written by one process in one place, where the argument for UUIDs never applied. The integer is smaller and there's no coordination to avoid.

And systems where the key is exposed and its length matters, in URLs or on printed documents. A v7 UUID is 36 characters in its usual form. If that's too long for where it shows up, use a separate short public identifier, and keep the primary key as it is.

How the numbers were produced

The table above is only useful if you can reproduce it, so here's the setup.

There were three copies of the events table on the same PostgreSQL 18 instance: 8 vCPU, 32 GB, shared_buffers at 8 GB, local NVMe. Each copy got the same 200 million rows and differed only in the primary key column: a bigint identity, a uuid filled with gen_random_uuid(), and a uuid filled with uuidv7(). The rows were loaded in time order, which matters. It meant the v7 keys were monotonic at load time, as they would be in production, and the v4 keys were random, as they would be in production.

The insert workload was a small Go program with 16 connections inserting batches of 500 rows as fast as the database would take them, for 20 minutes per table. A second program ran the read workload: point lookups by primary key on rows inserted in the last minute, 2,000 a second.⁴

Throughput came from the program's own counters. Page splits came from pg_stat_user_indexes before and after, which doesn't report splits directly but does report how much the index grew, and from pgstattuple on the index, which reports average leaf density. After the run, the random key index sat at 61 percent leaf density, and the v7 and integer indexes at 89 and 91. That density difference is the splits, made visible.

The index's buffer cache hit rate came from pg_statio_user_indexes, the ratio of idx_blks_hit to idx_blks_hit plus idx_blks_read, sampled every minute. The v4 index's rate fell steadily through the run as its working set outgrew the cache. The other two stayed flat.

None of that is exotic.⁵ Your table is a different shape, so running the same experiment on a copy of it is the afternoon that tells you what the key type costs you in particular.

Foreign keys pay too

The primary key isn't the only place the key type lives. Every table that references events has a 16 byte column with an index on it, and that index has the same locality problem the primary key index had.

In this schema, seven tables reference events. Under v4 keys their foreign key indexes came to a bit over 9 GB in total, with the same low leaf density as the primary key index. Under v7 they're the same size and dense, because a child row inserted now references a parent inserted recently, so the foreign key values also arrive roughly in order. The join from a child table to events over a recent time range went from an index scan touching pages all over the events index to one touching a contiguous run of them. On the query the dashboard runs most, that took it from 140 milliseconds to 35.

The size difference against bigint doesn't go away. Sixteen bytes in eight indexes instead of eight bytes in eight indexes is real storage, roughly 4 GB on this schema, and it's what you pay for a key that can be generated anywhere. I think that's a fair price, but I won't pretend it's zero.

The timestamp leaks

One more thing to decide on purpose. A v7 key carries its creation time in plain view. Anyone who sees the key, in a URL, an API response or a log, can read the millisecond the row was created. For an order id that's probably fine. For a user id it tells the world when the account was made, and for some products that's information you'd rather not hand out.

The options are the same as for integer keys that leaked row counts. Expose a separate opaque public identifier and keep the v7 key internal, or accept the leak because the timestamp isn't sensitive for that entity. Decide per table. My default is that keys that show up in URLs get a separate public id, and everything else uses the v7 key directly.

Where this leaves the argument

The integer side was right that random keys wreck index locality. The UUID side was right that generating keys without a round trip and without collisions is worth a lot. v7 gives the UUID side everything it wanted, and gives the integer side the locality it was defending. With the function in core since Postgres 18, uuid.uuid7() in Python 3.14 and v7 in the standard uuid package for JavaScript, there's no setup cost left either.

New tables get uuid PRIMARY KEY DEFAULT uuidv7(). Existing v4 tables get the default swapped and a reindex when it's convenient. The argument is over, and a function that fits on one line ended it.

---

Originally published at zeybek.dev.

---

1. The workload was 50,000 inserts a second in batches of 500, with a concurrent read load on recent rows. ↩

2. That index was 3.8 GB. ↩

3. The table has been on the new scheme since April. ↩

4. That's the "recent rows are hot" shape most real tables have. ↩

5. The tables, the two programs and the queries each fit in a single file, and the whole run takes about an hour. ↩