The Time Traveler's Primary Key
Every table needs a way to tell its rows apart, and auto-incrementing surrogate keys have been the go-to solution since practically the dawn of time. They're simple. They're fast. And perhaps most importantly, they're correct. But distributed systems demand unique values cluster-wide, preferably without some kind of consensus model or key-server bottleneck. The Smart Money is always on algorithmic generation.
So along came the UUID. The standard has been through several iterations since its debut, but for the cost of 128-bits, it virtually guarantees algorithmically unique values. Unfortunately, UUIDs also tend to treat B-Tree indexes like particularly durable piñatas.
Why would something so convenient cause so much grief? Is there a way out? I'm glad you asked!
Everything, Everywhere, All at Once
The workhorse of the UUID world is version 4. Rather than relying partially on MAC addresses or namespaces, they're randomly generated. Postgres provides it for free through the gen_random_uuid() function.
Let's call it a few times:
SELECT gen_random_uuid() FROM generate_series(1, 4);
gen_random_uuid
--------------------------------------
5f8d2c1a-6b3e-4f9a-8c7d-1e2f3a4b5c6d
a1b2c3d4-e5f6-4a7b-8c9d-0e1f2a3b4c5d
3e9f7a2b-1c4d-4e5f-9a8b-7c6d5e4f3a2b
c7d8e9f0-a1b2-4c3d-8e9f-0a1b2c3d4e5fBeautiful. Now consider where those values go when they become a primary key:
CREATE TABLE customer (
id UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
full_name TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);By default, Postgres backs primary keys with a B-tree index. Such indexes maintain sorted order to enable predictable cache behavior and efficient lookups. When we insert an ordered BIGINT identity, every new value is larger than the last so it lands at the rightmost leaf page of the tree. That page is almost certainly already in memory because it's the same page touched by previous inserts. We fill it, it splits cleanly, we move on. The hot part of the index is a tiny sliver at the right edge.
A random UUID does the opposite of that. Each generated value is equally likely to sort before the very first row or after the very last, so every insert dives into a different, unpredictable leaf page. The page we need is rarely the page we just touched, which means Postgres must retrieve it from filesystem cache or worse. An unaware developer might watch as their insert throughput sags with seemingly no explanation.
That's not even the worst part. Because inserts land in the middle of already-full pages, the B-tree has to split those pages to make room. Over and over again. In random spots across the whole index. Those splits leave pages half-empty and highly fragmented, causing the index to balloon to hold the same number of keys. Such sparse pages inherently use more RAM, making less efficient use of shared_buffers and filesystem caches.
It turns out that the freedom to generate a key anywhere bought us the curse of writing it everywhere. What do you even do to fix that?
Just in Time
128 bits offers plenty of room for disambiguation. Unfortunately, UUID v4 packs it to the brim with meaningless noise. A key sorts well when newer values tend to be incrementally larger than older ones, the way a plain sequence works. So what if we kept the global uniqueness of a UUID, but arranged for the bits to climb over time?
That's exactly where UUID version 7 comes in (as standardized in RFC 9562). The secret sauce is the layout. The high-order 48 bits hold a Unix timestamp in milliseconds, most-significant bit first. A version and variant field take four bits in the middle to mark the thing as a v7. The remaining bits reflect the same kind of randomness associated with v4.
However, the Postgres implementation exercises the standard a bit more than simply dumping random values into the last 70+ bits. The standard permits 12 bits following the version information to provide "optional constructs to guarantee additional monotonicity". And indeed, the Postgres docs say "The timestamp is computed using UNIX timestamp with millisecond precision + sub-millisecond timestamp + random." So there's an additional 12 bits of sub-millisecond clock information.
If we examine a v7 UUID outright, it looks like this:
019fced9-8586-7ec5-9b3c-dd610e1b431f
The first two sections are the 48-bit UNIX timestamp. The third section starts with the UUID version number (there's our 7), and the next three characters ('ec5' in this case) are the extra time information. This means the first three UUID sections help guarantee the sort order, while the remainder embodies chaotic disorder.
In the context of a b-tree index, the first thing the index compares is the timestamp. So any UUID generated later this millisecond and sub-millisecond sorts after previous entries no matter what randomness follows. New keys land at the right edge of the tree, exactly where the ordered BIGINT used to put them. That one change restores everything UUID v4 broke: sequential locality, the hot working-set, and clean page splits.
Meanwhile, the random tail is still doing its job. Within any single millisecond, dozens of nodes can mint dozens of values and stay globally unique on the strength of the remaining 62 fully-random bits. That's quite a lot of room for activities! Time and entropy; two great tastes that taste great together.
If only there was a convenient way to get that into Postgres...
Fully Encapsulated
Postgres being the champion of extensions that it is, has an easy way to do this. And inevitably the pg_uuidv7 extension ended up saving the day early on. Short of that, you were limited to application-side generation, or maybe a pure SQL implementation like postgres-uuidv7. Cute, but given Postgres has v4 built in, why not v7?
While cloaked in legend, some say the Hacking Postgres 101 - ULID function podcast episode was the epicenter of what eventually became the new UUID v7 functionality introduced in Postgres 18. (If you're interested, I highly recommend watching the whole thing. It's only an hour and gives a lot of insight into how the Postgres sausage is made.)
There are actually two new functions: uuidv4() and uuidv7(), and they're just as easy to use as gen_random_uuid():
SELECT uuidv4() FROM generate_series(1, 4);
uuidv4
--------------------------------------
b2f8f000-bba7-432e-af41-910d55b50533
bdba26d1-42a2-468d-9d36-77a9ef473aaa
d35e35cf-e808-47e0-9bf0-fa5c654abd4c
9d57fa66-2e38-4ef4-ab68-c4e39655409a
SELECT uuidv7() FROM generate_series(1, 4);
uuidv7
--------------------------------------
019fced9-8586-7ec5-9b3c-dd610e1b431f
019fced9-8586-7f3d-9d77-28ee863c9457
019fced9-8586-7f48-a683-fdb6944b9f91
019fced9-8586-7f50-b967-0c3371637e18Remember what I said about the first two sections? Having been generated at the same millisecond, the first two segments of all four rows are the same in the v7 output. It's also more obvious that the third section is roughly sequential as well. And now that we have more samples to work with, there's no discernible pattern to the remaining two segments.
There's even a convenient function to extract the UUID version string:
SELECT uuid_extract_version(gen_random_uuid()) v4_rand,
uuid_extract_version(uuidv4()) v4,
uuid_extract_version(uuidv7()) v7;
v4_rand | v4 | v7
---------+----+----
4 | 4 | 7Or you can just remember it's the first character in the 3rd segment of the string representation. Your choice.
Now let's put the new UUID version to the test!
Bloatus Amongus
Let's make two tables that only differ by the UUID version and pour a million rows into each:
CREATE TABLE keys_random (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
payload TEXT
);
CREATE TABLE keys_v7 (
id UUID PRIMARY KEY DEFAULT uuidv7(),
payload TEXT
);
INSERT INTO keys_random (payload)
SELECT 'row ' || g FROM generate_series(1, 1000000) g;
INSERT INTO keys_v7 (payload)
SELECT 'row ' || g FROM generate_series(1, 1000000) g;
Same row count, same payload, same everything. Now ask Postgres how big each primary key index turned out, using pg_relation_size against the index the constraint created for us:
SELECT pg_size_pretty(pg_relation_size('keys_random_pkey')) AS random_idx,
pg_size_pretty(pg_relation_size('keys_v7_pkey')) AS v7_idx;
random_idx | v7_idx
------------+----------
38 MB | 30 MB Same data, and the random index reports roughly a quarter more heft for the privilege. To see why, we can lean on the pgstattuple extension, whose pgstatindex() function dissects a B-tree's internals:
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT 'random' AS tbl, avg_leaf_density, leaf_fragmentation
FROM pgstatindex('keys_random_pkey')
UNION ALL
SELECT 'v7', avg_leaf_density, leaf_fragmentation
FROM pgstatindex('keys_v7_pkey');
tbl | avg_leaf_density | leaf_fragmentation
--------+------------------+--------------------
random | 71.53 | 49.89
v7 | 89.98 | 0.00 Yikes! The v7 index packs its leaves to about 90 percent full and reports essentially zero fragmentation because every key arrived in order and filled each page to the brim before starting the next. The random index limps in around 71 percent density with nearly half its leaves fragmented, which is the disk-space cost of inserting into random spots and splitting pages that were already occupied. A denser index is a smaller index, and packed pages work better in shared buffers without supplementary reads.
Once the timestamp is doing its job, a tempting question surfaces: can we read that clock back out?
Telling Time
Here's the part that feels like finding random change in a vending machine. That timestamp in the high bits is just sitting there, and Postgres 18 gives us uuid_extract_timestamp() to pull it straight out of the key:
SELECT id, uuid_extract_timestamp(id) AS born_at
FROM keys_v7
ORDER BY id
LIMIT 3;
id | born_at
--------------------------------------+----------------------------
019f8a23-377c-725e-aa93-1c94b666dafe | 2026-07-22 14:03:11.612+00
019f8a23-377c-794c-9ec8-9b7a8a0ac984 | 2026-07-22 14:03:11.612+00
019f8a23-3781-7faa-bbc2-1812b24c01be | 2026-07-22 14:03:11.617+00We ordered by the primary key and received the rows in the order they were created. Try that with a v4 UUID! Oh wait, you can't! That means the primary key itself can double as a created_at column, assuming it's meant to represent row insert time rather than describe the data being stored.
Trouble in Paradise?
Of course, that same augmented versatility can be a type of double-edged sword. It's possible to introspect any externally visible UUID v7 content to distill the timestamp. That may not be a problem by itself, but it does mean that every v7 UUID technically leaks potentially relevant metadata.
The leak compounds when values travel in groups. Because the keys sort by time, two records created seconds apart carry nearly-identical prefixes. An observer watching a stream of them can measure creation rate over any interval. How many orders did a system take in the last hour? How many signups arrived overnight? That's all valuable business telemetry. Adjacent keys become plausible to enumerate in a way random ones simply aren't.
And ironically, all those inserts landing at the right edge of the index are wonderful for cache locality on a single node. But in some sharded or heavily-parallel write topologies, that same rightmost page becomes a contention point for every active writer on that index. The scattered v4, for all its bloat, at least spreads its writes evenly. What cured our cache misses can, in the wrong cluster, incur lock contention. Of course, BIGINT and other such types already have this attribute, so it's an overblown concern for the most part. But it is a point of contrast between the two versions.
So is UUID v7 the ultimate choice?
Every Snowflake Is Unique
Well... maybe, or maybe not. There's an implicit assumption here that distributed key generation requires 128 bits, and that's not quite the case. The pgEdge snowflake extension repurposes all bits in a standard BIGINT to achieve a similar effect as seen in UUID v7.
Here's a quick breakdown:
Bits 0-11 contain a counter for 4096 unique IDs per millisecond.
Bits 12-21 encode the local node identifier to avoid collisions, set with the snowflake.node GUC.
Bits 22-62 are a timestamp with millisecond precision.
Bit 63 remains unused for signing purposes.
And here's how that looks in action:
CREATE EXTENSION snowflake;
SELECT snowflake.nextval() FROM generate_series(1, 4);
nextval
--------------------
481092642763968512
481092642763968513
481092642763968514
481092642763968515
SELECT snowflake.format(snowflake.nextval());
format
-----------------------------------------------------------
{"id": 1, "ts": "2026-08-20 13:30:24.305+00", "count": 0}The arbitrary integer value inflation is a bit inconvenient but that's what happens when you treat a BIGINT like a storage layer. Either way, this means Postgres usually treats snowflake IDs the same way as it would a standard sequence. I say "usually" because there's some subtlety here. For instance, here's how an index on a snowflake column may look on a single node:
key_type | heap | idx | avg_leaf_density | leaf_fragmentation
-----------+-------+-------+------------------+--------------------
v4 random | 57 MB | 38 MB | 70.84 | 50.04
uuid v7 | 57 MB | 30 MB | 89.98 | 0.00
snowflake | 50 MB | 21 MB | 90.01 | 0.00
bigint id | 50 MB | 21 MB | 90.01 | 0.00At first glance, it would seem like storing a snowflake ID is indistinguishable from a plain identity column. But consider the position of the node ID within the key: counter - node - timestamp. That means generated values from node 8 come after those from node 1 for the same timestamp no matter what its counter says.
What happens when multiple nodes collectively produce enough values to overwhelm the Postgres index fast-path? That's right: index fragmentation. The degree of the fragmentation varies on collective cluster throughput, but it's there. My tests show it requires at least 100k cluster-wide inserts per second before the effect becomes visible. Higher rates mean more page splits and more fragmentation, though that seems to plateau similarly to UUID v4.
The other thing to consider is that, like UUID v7, the encoded value contains metadata. With snowflakes, it's not just the timestamp using snowflake.get_epoch(), but the node ID using snowflake.get_node() as well. This could expose sensitive cluster topology if ID values are publicly visible. As such, it's probably best to only use them internally.
Regardless, snowflake IDs demonstrate that UUIDs aren't the only solution for distributed clusters. UUIDs do benefit from being provided by core Postgres, however.
Gentlemen, Choose Your Keys
When it comes to UUIDs, most DBAs I know like to re-frame the question. Start by being honest about whether you need a UUID at all. For all their fanfare, 128-bit UUIDs are positively gargantuan even when compared to a BIGINT. Here's a few situations where any version of UUID is not a great match:
Data lives on a single node.
A primary instance hands out every key itself.
Keys aren't likely to circulate in application-space or public-facing situations.
In those cases, a plain BIGINT GENERATED ALWAYS AS IDENTITY is still the right answer more often than not. They sort perfectly by design, do not leak timestamp metadata, and are just as conservative with index pages as UUID v7. Without the requirement of distributed generation, the case for UUID is minimal.
Meanwhile, there's still a place for UUID v4 precisely because of its inscrutability. There are times when a key is intended to be exposed publicly. In cases where the creation time, ordering, or insertion rate are information that must remain private, the scattered nature of gen_random_uuid() becomes a feature rather than a bug. The cost of entry is sparse index pages and fragmentation. We used to pay that price begrudgingly, but now at least we have a choice.
If you already use UUIDs and have access to Postgres 18 (19 is coming soon!), take the new uuidv7() function for a spin. See how you like it. The 25% index size cost saving and integral sorting are genuine improvements over v4 that many DBAs would welcome.
