UUIDv7 Sorts Like an Integer, Scales Like a UUID

UUIDv7 Sorts Like an Integer, Scales Like a UUID

Every backend team hits the same wall at some point: auto-increment integers are the best primary key you can build, and the worst one you can scale. They sort beautifully, they index tightly, and they leak your sales numbers to anyone who can read a URL. So teams bolt on UUIDv4 and quietly pay for it forever. UUIDv4 is random in the first byte, which means every insert lands at a random spot in a B-tree index. Your writes fragment, your pages split, and throughput drops. Snowflake fixed the ordering problem but handed you a coordination server and a clock to babysit.

UUIDv7, standardized in RFC 9562 in October 2024, is the compromise that finally lands. It keeps an integer's ordering and a UUID's decentralization, with no coordinator. Postgres 18 shipped a built-in uuidv7() function this year, and that is the real signal. A major database vendor does not ship an ID function for free unless a lot of teams were already asking for it. Here is why the default for a new schema in 2026 is shifting to UUIDv7, and where it still bites you.

The problem with the other three

There are really four ID strategies in active use, and each one wins somewhere and loses somewhere else.

• Auto-increment integer. Smallest, tightest, sorts perfectly. Loses the moment you run more than one writer, need to merge two databases, or want the ID to be meaningless. It is also a live feed of your business metrics.
• UUIDv4. Globally unique with zero coordination. But the bytes are uniform random, so a B-tree index treats every insert as a jump to a new leaf page. On a high-write table this is the difference between a steady index and a constantly rebalancing one.
• Snowflake. Time-ordered and 64-bit compact. But it depends on a node ID and a monotonic clock, so you either run a coordination service or you risk duplicate IDs when two nodes collide or a clock jumps backward.
• UUIDv7. Time-ordered prefix, random suffix, no central authority. It is the one that gives you the integer's ordering without the integer's single point of failure.

How UUIDv7 is laid out

The 128 bits are split into three parts. The first 48 bits are a Unix timestamp in milliseconds, which is what makes the ID sortable. The next 15 bits are a 16-bit version-and-counter field, and the final 63 bits are random data. The layout looks like this:

 0                   1                   2                   3
 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                         unix_ts_ms                            |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|          unix_ts_ms           |  ver  |         rand_a        |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
| variant |                       rand_b                        |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

Because the timestamp is the high-order field, two IDs created a second apart differ in the first six bytes. Sort the column and you get chronological order for free. That is the whole trick, and it is why an event log keyed on UUIDv7 replays in order without any extra sequence column.

Why it sorts like an integer

UUIDv4 scatters writes across the whole index because the leading bytes are noise. UUIDv7's leading bytes are a clock that only ever moves forward, so consecutive inserts land on consecutive index pages, mostly at the right end of the B-tree. In practice that means the write amplification and page splits you were paying for with UUIDv4 largely disappear, and you get integer-like insert performance while keeping the 16 bytes of global uniqueness. Postgres and MySQL both index it as a normal key; nothing about the storage format changes, only the distribution of the values does.

The gotchas nobody mentions

• It still leaks a timestamp. Anyone who reads the ID can tell roughly when a row was created and, with enough rows, infer insert rate. If that is sensitive, the ordering you want is also the thing you are exposing.
• 63 bits of random, not 122. You give up entropy compared to UUIDv4. Collision risk is still astronomically low in practice, but the "random" part is half the width people assume.
• Millisecond resolution. Two IDs generated in the same millisecond rely on the random bits to stay distinct. That is fine for one process, but do not assume strict monotonicity within a millisecond across machines.
• Older databases need a shim. Postgres before 18 and most MySQL versions have no native uuidv7(). You are generating the value in application code, which is exactly where the clock-skew bugs live.
• Do not re-order an existing v4 column. Retrofitting v7 onto a live table that already has random v4 keys does not re-sort history; it only makes new rows orderly.

When to reach for it, and when not to

Use UUIDv7 for new tables where you want global uniqueness, chronological order, and no central coordinator, and where the timestamp in the ID does not reveal anything you care about hiding. Event logs, message streams, audit records, and most high-write service tables are the natural fit, and a lot of teams are standardizing on it as the default for exactly that reason.

Reach for something else when the trade flips. If you must hide creation time, keep v4 or add encryption. If you want the smallest possible key and you are comfortable with one writer, an integer is still the fastest and most compact option. And if your stack already depends on Snowflake's 64-bit IDs, do not migrate for fashion; the coordination cost you accepted to get there is a real design decision, not a bug.

The short version is that UUIDv7 stopped being an interesting footnote and became a boring default. That is the highest compliment an ID scheme can get. When Postgres ships it into the core, the question for most teams is no longer whether to adopt it, but which of your random v4 columns you migrate first.