"PostgreSQL 18's uuidv7(): Time-Ordered UUID Primary Keys That Don't Fragment Your Index"
postgres uuidv7 primary key — how PG18's built-in uuidv7() creates time-ordered UUID primary keys that stop B-tree fragmentation, plus v4-to-v7 migration and caveats.
Every gen_random_uuid() insert lands on a random leaf page of your B-tree. At small scale nobody notices; at a few million rows your primary key index is bloated, page splits are constant, and write throughput quietly degrades. A postgres uuidv7 primary key fixes this at the source — PostgreSQL 18 ships a built-in uuidv7() function (no extension, no pgcrypto ) that generates time-ordered UUIDs, so new rows append at the right edge of the index like a sequence does, while keeping all the distributed-systems benefits of UUIDs. This guide covers the syntax, the v4-to-v7 migration, the timestamp-leak caveat, and the cases where bigserial still wins. Last updated: October 4, 2026 What you'll learn Why UUIDv4 fragments B-tree indexes and UUIDv7 doesn't Using uuidv7() as a column default in PostgreSQL 18 (no extension needed) Migrating an existing table from UUID v4 to v7 The UUIDv7 timestamp-leak caveat and how to think about it UUIDv7 vs bigint/bigserial: when the sequence still wins UUIDv7 vs UUIDv4: Why Your Index Cares About Sort Order A B-tree index keeps keys sorted. A UUIDv4 is 122 bits of randomness, so consecutive inserts land at random positions in the key space — the database constantly splits leaf pages, and the index fills with half-empty pages. This is the "random primary key slow" problem that shows up as steadily degrading insert performance and index bloat that VACUUM can't fix (only REINDEX or VACUUM FULL reclaims it). UUIDv7 flips this: the first 48 bits are a Unix millisecond timestamp, followed by version nibble and ~74 bits of randomness. Monotonic-ish time ordering means new keys sort after old ones, so inserts concentrate on the rightmost leaf pages. The index behaves like a bigserial primary key — dense pages, minimal splits — while…