Your primary key knows what time it is

Postgres 18 came out three days ago, and somewhere in the release notes, below the async I/O subsystem and the skip scans, sits a function that looks almost too small to mention: uuidv7(). One new way to generate a sixteen-byte identifier, in a database that has been generating sixteen-byte identifiers since 2008. It's the feature I'm most excited about, and this essay is about why — because behind that little function is a decade-long argument about primary keys, an elegant piece of bit-level design, and a B-tree pathology that has taxed every system that ever typed id uuid DEFAULT gen_random_uuid() PRIMARY KEY.

The argument it settles

Every team that outgrows a single service has the same fight about primary keys, and both sides are right.

The bigint camp points out that sequential integers are what B-trees are built for: new rows get new, ever-larger keys, inserts land at the right edge of the index, everything stays compact and cache-warm. The uuid camp points out that sequences are a central choke point: you can't mint one on a phone that's offline, or in three regions at once, or in application code before the transaction starts — and that /invoice/312 leaks your invoice count to anyone who can count. Random UUIDs solve all of that: any node can generate one with no coordination, collisions are a probabilistic joke, and the identifier tells outsiders nothing.

The trouble is where that randomness goes. A UUIDv4 is 122 random bits, and when you index random values, every insert lands on a random leaf page of the B-tree. Hold that thought — it's the whole third act of this essay. First, the fix.

Sixteen bytes, read left to right

UUIDv7 comes from RFC 9562 (May 2024, the spec that retired the crusty old RFC 4122), and the entire idea fits in one sentence: keep the randomness, but put a timestamp in front of it. The layout:

 unix_ts_ms      ver  rand_a   var  rand_b
 48 bits         4    12       2    62

Concretely, here's a UUIDv7 minted at nine in the morning on the day this essay went up:

01998f8c-e680-7d4a-9c1b-5e2f8a3d7c90
└──────┬─────┘ │└─┬┘ │└──────┬──────┘
  unix ms      7  rand_a     more random bits
               ver   (variant hides in the '9')

The first 48 bits — the first twelve hex characters, 01998f8ce680 — are the number of milliseconds since the Unix epoch, stored big-endian so the textual sort order is the numeric sort order. Decode them and the identifier confesses its own birthday:

hex            0x01998f8ce680
ms since 1970  1 759 050 000 000
UTC            2025-09-28 09:00:00.000

(I liked this decode chain enough to build a little live inspector for it — generate one and watch the timestamp fall out.)

The 7 after the second dash is the version nibble, same place every UUID version keeps it. Two more bits after the third dash are the variant marker every RFC UUID carries. Everything else — 74 bits — is random: 12 bits of rand_a sitting between the version and the variant, and 62 bits of rand_b filling the tail.

The format ends there. No MAC addresses (v1's privacy mistake), no hashed namespaces (v3/v5's niche), no clock-sequence-and-node ceremony. A big-endian timestamp, then noise.

The logic behind every field

The proportions look arbitrary until you interrogate them, and then every number has a reason.

Why 48 bits of time? Because 48 bits of milliseconds runs out in the year 10889. Forty bits would have died in 2004, before the spec was written. Sixty-four would waste two bytes of every key on dates no filesystem will survive to see. Forty-eight is the smallest width that makes overflow somebody else's problem, eight thousand years from now.

Why milliseconds? Because the timestamp's job is ordering, not timekeeping. Millisecond precision is coarse enough that ordinary wall clocks can supply it honestly, fine enough that keys generated by the same process mostly sort in creation order. It's deliberately not claiming more precision than a fleet of NTP-synced servers can actually deliver.

Why keep 74 random bits? Because the timestamp is public information: every process on Earth that generates an ID this millisecond shares the same 48-bit prefix. Uniqueness has to come from what's left, and 74 bits means two IDs minted in the same millisecond collide with probability 2⁻⁷⁴. You'd need to generate about 200,000 IDs in one millisecond before the birthday math gives you even a one-in-a-trillion chance of a duplicate. The decentralized-generation superpower of v4 survives intact; only the placement of the entropy changed.

Why big-endian, why front-loaded? So that ORDER BY id is ORDER BY created, then chance, and so a plain bytewise comparison — the only comparison an index wants to do — sorts by time. The property has a name, k-sortability: keys sort by creation time up to some small disorder k (here, within-millisecond ties and clock skew between machines). Not a total order. Nobody sane promises a total order across a distributed system. Ordered enough.

None of this was invented in the RFC, and the spec says so plainly. Twitter's Snowflake IDs put a timestamp in the high bits in 2010. ULID did it with 128 bits and crockford-base32 in 2016; KSUID with 160 bits the year after; MongoDB's ObjectId had been doing it since before any of them. For a decade this was folklore: everyone rediscovered "timestamp first, randomness after" independently, each with their own encoding, because the standard offered nothing. RFC 9562 is the standards process doing the thing it does best: noticing that the whole industry has converged on a design, and writing it down so the databases can build it in.

What random keys do to a B-tree

A Postgres B-tree index is a tree of 8 kB pages. The leaf level is a sorted sequence of keys; to insert one, Postgres descends to the leaf page where that key belongs and slots it in. All the economics of indexing hide in one question: which leaf page?

With UUIDv4 primary keys, the answer is: a uniformly random one. Every insert dives into a different part of the keyspace, so every leaf page in the index is equally likely to be touched next. That single fact fans out into three costs:

Your cache stops working. The working set for inserts is the entire index, because there is no locality — the page you need next is never the page you just wrote. While the index fits in shared_buffers, fine. The moment it doesn't, some fraction of inserts eat a disk read, and that fraction grows with the table. Insert throughput doesn't degrade gracefully; it falls off a cliff shaped exactly like your index-size-to-RAM ratio.

Your pages end up half empty. When a leaf page fills, Postgres splits it in two, each half-full, expecting future inserts nearby to fill them. With random keys those neighbors arrive at a trickle spread across the whole keyspace, so the index settles into a steady state of pages hovering between half and two-thirds full. That's not a rounding error; it's the same data taking up substantially more pages, which means more cache pressure, which feeds cost number one.

You write the WAL more than you'd like. The first time a page is dirtied after a checkpoint, Postgres logs the entire 8 kB page — a full-page write — so crash recovery has a sound base to replay onto. Touch a handful of hot pages ten thousand times and you log a handful of page images. Touch ten thousand random pages once each and you log ten thousand of them. Random inserts maximize distinct-pages-dirtied per checkpoint, which is precisely the variable full-page writes bill you by. That's replication bandwidth, checkpoint I/O, and disk, all taxed by key layout.

Now run the same reasoning with UUIDv7. Every new key is, give or take a millisecond of jitter, larger than every key before it. Every insert descends to the same rightmost leaf page — a page so hot it never leaves the buffer cache. When it fills, Postgres applies an old optimization it has carried for exactly this pattern: a split at the rightmost edge doesn't cut the page in half, it starts a fresh empty page to the right, leaving the old one packed. The index grows like a log file: dense, sequential, append-only. Cache misses, gone. Split waste, gone. The full-page-write multiplier, collapsed — thousands of inserts now dirty the same few pages per checkpoint instead of thousands of pages.

And there's a read-side dividend that's easy to miss: the key order now correlates with insert time. "Last hour of orders" — the query every dashboard, every sync job, every debugging session actually runs — becomes a narrow range scan over physically adjacent index pages, instead of a scatter across all of them. The pages holding recent data are the pages already warm in cache, because they're the ones you just wrote. With v4 keys, recent rows are smeared uniformly across the index and none of this is true.

Same sixteen bytes. Same randomness budget, near enough. The only thing that moved is where the entropy sits, and it changes the physics of the index underneath it.

What Postgres 18 actually shipped

You could use UUIDv7 with Postgres before — app-side generation, the pg_uuidv7 extension, or a plpgsql function circulating in gists of varying trustworthiness. What Postgres 18 changes is that the database can now mint them itself:

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

No extension, no trigger, no app-side discipline that every service writing to the table must share. And because the timestamp rides inside the key, it comes back out — uuid_extract_timestamp(), in core since Postgres 17, reads those first 48 bits back as a timestamptz:

SELECT uuid_extract_timestamp(id) FROM orders LIMIT 1;
--    2025-09-28 09:00:00.000+00

But the implementation detail worth an essay of its own is what Postgres does with rand_a — those 12 bits between the version and variant nibbles. The RFC leaves them as random, but it explicitly blesses an alternative (its "method 3"): spend leftmost random bits on extra clock precision. Postgres 18 takes the offer. It fills rand_a with a 12-bit sub-millisecond fraction — the microseconds-and-change the 48-bit field is too coarse to hold, giving the timestamp roughly 244-nanosecond resolution.

Why bother? Monotonicity. With random rand_a, two UUIDs from the same backend in the same millisecond sort by coin flip — and a busy INSERT ... SELECT mints thousands per millisecond, briefly recreating little pockets of the random-insert problem at the index's right edge. With a sub-millisecond fraction there, each value from a given backend is strictly greater than the one before. The keys don't just cluster at the rightmost page; they arrive in order, the exact pattern the rightmost-split optimization was built for. Across backends and across machines, ordering is still approximate — it's k-sortable, not sorted, and nothing will change that. But within a session it is now exact, guaranteed by construction rather than by luck. The cost: 62 truly random bits instead of 74. Collision math at 2⁻⁶² per same-millisecond pair — still comfortably in cosmic-ray territory.

There's a small flourish for migrations, too: uuidv7(interval) shifts the embedded timestamp, so backfilled historical rows can carry keys that sort where their data belongs rather than clumping at import time. And gen_random_uuid() got an honest alias, uuidv4(), so the two options finally sit side by side in the docs wearing their version numbers.

The fine print

Fairness demands the trade-offs, and UUIDv7 has real ones.

The timestamp is not a secret. Anyone who sees the ID learns, to the millisecond, when the row was created. For an order ID that's usually harmless; for /users/{id} it dates every account, and for anything where creation time is itself sensitive — say, records whose timing implies a medical visit — it's a leak. The RFC says plainly that UUIDs of any version must not be treated as security capabilities; v7 just makes the advice unignorable. If an identifier must reveal nothing, v4 still exists, and this is the one criterion where it wins.

The right edge is a hot spot. All inserts converging on one leaf page is the point — but under extreme concurrent insert rates, that page's lock becomes a rendezvous for every writing backend. It's the classic rightmost-page contention that sequential bigint keys have always had; v7 buys v4's problems away by accepting bigint's, at throughputs most systems will never see.

And bigint didn't lose. Half the size (which compounds — the primary key is re-stored in every secondary index and every foreign key), no entropy budget to reason about, and perfect ordering. If one database is the sole minter of your IDs and you don't mind them being guessable, a sequence remains the boring, correct choice. UUIDv7 isn't for beating it; it's for the moment you need IDs minted in many places at once and refuse to pay the B-tree tax that used to cost.

That's the real story of uuidv7(), and why a one-line function earns a place next to async I/O in a major release. Not a new identifier — a truce. The decade-old fight between "sequential is what indexes want" and "random is what systems want" ends with sixteen bytes that read left to right: what the index wants first, what the system wants after. The upgrade is one line in a migration: uuidv7() where gen_random_uuid() used to be. The rightmost leaf page does the rest.