Heads up: posts on this site are drafted by Claude and fact-checked by Codex. Both can still get things wrong — read with care and verify anything load-bearing before relying on it.
why → how

Why UUIDv7 is quietly replacing autoincrement IDs

Autoincrement IDs can't be minted client-side and leak how many rows you have. Random UUIDs trash your index. UUIDv7 is the boring fix almost nobody noticed shipping.

Data intermediate Apr 29, 2026 · updated Aug 25, 2026 · 12 min read
On this page

The picture version

Six pictures for a reader who has never designed a database key. The prose below fills in the seams the pictures skip.

1 · The problem

Two receipts, a week apart, and now you know their sales figures.

?order=10432 your receipt ?order=10771 a week later 339 orders a week You didn’t hack anything. You subtracted.
A counter that goes up by one per order tells anyone holding two of them how fast the business is going. That leak is free to the observer and invisible to the shop — and it’s only the first of two problems with counting.

2 · The second problem

Somebody has to hand out the next number, and only one somebody can.

a serveranotheranother the one counter everybody has to ask it nobody can create a row without a round trip You can’t generate an id in the browser, or offline, or in two data centres at once.
A counter has to live in exactly one place to stay a counter, so everything that needs an id has to go and ask. That rules out generating an id anywhere except at the single point that owns it — awkward for anything distributed, offline, or client-side.

3 · The obvious fix, and its bill

Random ids solve both. Then the index falls over.

the index, kept in sorted order — and where five new random ids land in it five inserts, five different pages, none of them still warm The privacy leak is gone and so is the bottleneck. In exchange, every insert is a jump somewhere new.
Random identifiers can be made anywhere and reveal nothing, which fixes both earlier problems at once. But an index keeps its keys in order, so uniformly random keys scatter inserts across the whole of it — and pages that were just used are no help for the next one.

4 · Why order is what the index wants

Keys that arrive in order all land in the same place. That place stays in memory.

random keys every page has to be fetched, changed, written keys that climb all of them land in the same page, which stays hot The counter had this property by accident. Randomness threw it away. So the goal is to get it back without getting the counter back.
Rising keys append to the same end of the index over and over, so that page stays in memory and the work per insert is small. That is the property the counter was quietly providing, and it is the one thing worth rescuing from it.

5 · The fix

Put the clock at the front and the dice at the back.

what time is it random read first when sorting, so ids climb through the day makes each one unguessable, and unique without asking anyone Sorting compares the front first — so the clock decides the order and the dice only break ties within the same instant.
Leading with a timestamp makes the identifiers ascend, and filling the rest with randomness keeps them unguessable and independently generatable. Both properties come from the same layout, because sorting reads the front of the value first.

6 · Keep this card

The whole thing on one index card.

the shape = time in front, so new ids sort to the end + randomness behind, so nobody has to be asked ∴ you keep the index happy without a counter It isn’t free: the id now says roughly when the row was made. that is much less than a counter leaks, but it is not nothing — so don’t use one where the timing is sensitive
Picture to keep: a numbered ticket where the first half is the clock on the wall and the second half is a dice roll. Everyone in the world can print their own ticket without asking anyone, and the tickets still stack in roughly the order they were printed.

Why it exists

You’re looking at a receipt from an online shop and the URL ends in ?order=10432. Out of curiosity you order something a week later: ?order=10771. You now know, to within a few percent, how many orders that business took last week — and so does every competitor who ever bought from them. That order ID is the running example for this post. Somewhere in the shop’s schema is one line deciding what it looks like, and every option on the menu costs something.

For most of the last twenty years, that line had two boring answers and an argument between them.

Engineers picked one and lived with the trade-off. UUIDv7 is the version that finally gets to stop picking. It keeps everything that made v4 nice — client-generated, no coordination, collision-free in practice — and puts a timestamp at the front, so the IDs sort in roughly the order they were created. That one change makes them play nicely with B-trees again.

It’s specified in RFC 9562 (May 2024), which replaced the old UUID RFC 4122 and added v6, v7, and v8. v7 is the one people actually adopted.

Why it matters now

The trade-off used to be tolerable because services were small and inserts were rare relative to reads. Two things changed:

Postgres 18 (released September 2025) shipped a built-in uuidv7() function. Prisma added UUIDv7 schema support in 2024. MySQL and SQLite don’t ship native v7 generators yet — their built-in UUID() functions predate v7 — so for now you get v7 there via libraries or manual generation. The trajectory is clear enough that “use UUIDv7” is becoming the unremarkable default for new schemas, which is exactly when it’s worth understanding why.

The short answer

UUIDv7 = 48-bit Unix-millisecond timestamp + 74 random bits (with version & variant tags)

Picture to keep: a numbered ticket where the first half is the clock on the wall and the second half is a dice roll. Everyone in the world can print their own ticket without asking anyone, and the tickets still stack in roughly the order they were printed.

A v7 UUID is a UUID-shaped wrapper around “what time is it” and “some randomness.” Because the timestamp sits at the most-significant end, two v7s generated a millisecond apart sort in the right order; two generated in the same millisecond fall back to randomness for tie-breaking. You get v4-style independence and B-tree-friendly ordering at the same time.

How it works

Follow the order ID through each option and watch what breaks.

Autoincrement. The database owns a counter; each order gets the next number. Perfect insert behaviour, four or eight bytes, readable in a log. Why it breaks: three separate ways. The ID doesn’t exist until the row does, so a mobile client can’t reference its own order before the round trip completes; a single counter is a single point of coordination when several services or shards want to insert; and the value is public information about your business, as the receipt showed.

Fix: let the client roll a random 128-bit number — UUIDv4. All three problems vanish at once. No coordination, no round trip, nothing leaked. Why it breaks: see the B-tree section below — the fix is quietly catastrophic for the index.

Fix: keep the randomness, but put the clock in front of it. That’s v7. The 128-bit layout, roughly (version and variant nibbles take a few bits out of the random fields):

| 48-bit unix_ts_ms | ver=7 | 12 bits rand_a | var | 62 bits rand_b |

Two properties fall out of that layout:

  1. Lexicographic order ≈ creation order. Because the timestamp is the high-order bytes, comparing two v7s as raw bytes (or as strings, since the canonical hex form preserves order) gives you “older first” almost for free. New rows insert at the right edge of the B-tree, which is the cheap case: hot pages stay in cache, splits happen at the end, and the index doesn’t fragment.
  2. No coordination needed. Each generator just reads the clock and rolls up to 74 random bits. (The spec lets implementations spend some of those bits on sub-ms timestamp resolution or a monotonic counter; whatever is left is random.) For a fully random v7, two services on two continents producing one each in the same millisecond collide with probability ~1 in 2^74 — for any workload that fits on Earth, you can treat that as zero. Implementations that trade randomness for monotonicity have a smaller pool, but it’s still cosmically large.

Why v4 hurts your database

To see why ordering matters, watch a B-tree index take inserts. Leaf pages are stored in primary-key order on disk. With an autoincrement key every new order goes at the end — one hot page, always in memory, always the target. With v4 UUIDs each new order’s key is a random number between 0 and 2^128, so it lands in a random leaf page. That page probably isn’t in cache. The database faults it in, mutates it, marks it dirty, eventually writes it back. Multiply by every insert: you’ve converted a sequential write workload into a random one, which on both spinning disks and SSDs is a category change in cost.

The classic symptom is the nasty one, because it’s invisible during development: a service that benchmarks fine on a fresh database gets mysteriously slower as the table grows past whatever fits in the buffer pool. Nothing changed in the code. The table simply outgrew the cache, and “random page read on every insert” stopped being free.

v7 puts insertions back at the right edge. You get almost the same one-hot-page behavior as autoincrement — randomness in the low bits means inserts spread across the few most recent leaf pages rather than landing on a single one — but that’s worlds closer to sequential than v4’s scatter, and your buffer pool will feel the difference.

Why v7 over a plain timestamp + random tail

You could roll your own “timestamp prefix + random suffix” ID and skip the RFC. People did, for years — Twitter’s Snowflake, ULID, KSUID, all variations on the theme. v7 is interesting because:

Where it gets fuzzy

You started with UUIDv7 = 48-bit Unix-millisecond timestamp + 74 random bits. What did this post add? — + the fact that the timestamp is at the front. UUIDv1 had a timestamp too and is not sortable, because the bytes are laid out in an order that scatters the high bits. Nothing about v7 is clever; the entire benefit comes from putting the clock where a byte-wise comparison reads it first. That’s the whole trick, and it’s why ?order= can stop being a number your competitors can read.

Check yourself

Before you go — a team switches from UUIDv4 to UUIDv7 and insert throughput improves a lot. Then they add a second index, on email, and throughput drops back down. Did v7 stop working?

Answer

No — v7 is doing exactly what it did before, on the primary key. But a secondary index is its own B-tree, sorted by its key, and email addresses arrive in no particular order. Every insert now appends neatly to the hot right edge of the primary key index and lands on a random leaf of the email index. The general model: ordering helps the index whose key is ordered, and no others. Which is also why nobody claims v7 makes everything faster — it makes the primary key insert pattern sequential, and most tables have several indexes.

And one more — could you use the timestamp inside a v7 ID to decide which of two orders was placed first?

Answer

Only loosely, and it’s a genuinely bad habit. The timestamp comes from whatever machine generated the ID, so two orders minted on two servers whose clocks disagree by 100 ms can sort the wrong way round — and nothing in the ID tells you that happened. v7’s ordering is a storage locality hint: it’s designed to make inserts land near each other, and it’s fully doing its job even when it’s off by a few milliseconds. If the ordering has to be correct — for auditing, for causality, for “who bid first” — you need a database sequence, a logical clock, or a server-assigned timestamp. Use the property you were sold, not the one it resembles.

Going deeper