Generating a UUID for a Database Primary Key

Generating a UUID for a Database Primary Key

A UUID solves the primary-key problem that auto-increment integers create the moment you split writes across two database replicas: no coordinating sequence, no collision, no central authority deciding who owns the next value. But the version you generate is not a cosmetic choice. UUID v4's pure randomness and UUID v7's built-in timestamp behave completely differently once that key becomes the clustering key of a B-tree index, and picking the wrong one on a high-write table shows up later as slower inserts, not as a bug you can spot in a code review.

What a B-tree index does with a random key

A relational database's primary key index is usually a B-tree, and a B-tree keeps its entries sorted so range scans and lookups stay fast. Every new row has to land at the position in the tree that matches its key value, not simply get appended to the end. An auto-increment integer always inserts at the rightmost edge of the tree, because every new value is larger than every existing one, and that predictable insert point is exactly why integer primary keys have historically been fast to write.

UUID v4 breaks that pattern completely. Because 122 of its 128 bits come from a cryptographically random source, a freshly generated v4 value has no relationship to the values already in the index.¹ Each insert lands at a random position somewhere inside the tree instead of at the edge, which forces the database to load, modify, and rewrite whatever page happens to hold that position, splitting an already-full page when the new row does not fit. At low write volume the cost is invisible. Under sustained high-throughput inserts, it compounds into page splits, cache misses, and index bloat that a purely sequential key never produces.²

Why write amplification gets worse as the table grows

The problem does not stay constant as a table grows. A small table's B-tree fits mostly in memory, so a random insert rarely triggers a disk read before the write completes. Once the index outgrows available cache, though, every random insert risks a cache miss, and the database has to pull an entire page from disk just to place one new row inside it.³ That per-row cost accumulates fastest on the tables most likely to need it least: high-volume, write-heavy tables like orders, events, or sessions.

How UUID v7 keeps inserts at the edge of the tree

UUID v7 exists specifically to remove that penalty while keeping the decentralized generation that made UUIDs attractive as primary keys in the first place. Its leading 48 bits encode a Unix millisecond timestamp,¹ so two v7 values generated a second apart differ in their very first characters, in the same direction every time. Sorted as plain strings or compared byte by byte, v7 values come out in the order they were created, which is exactly the monotonically ascending, creation-time-ordered property a B-tree index rewards.⁴

That timestamp prefix means every new v7 row inserts at, or very near, the rightmost edge of the index, the same position an auto-increment integer would use. You get the decentralized generation of a UUID and the sequential insert behavior of an integer key at the same time, without a database-managed sequence coordinating who gets the next value.

Generating and reading a v7 key for a new table

Generating a v7 UUID here shows exactly what that timestamp encoding looks like: the first 12 hexadecimal characters of the value are a direct big-endian encoding of the millisecond the ID was created. Clicking Regenerate All produces a fresh v4, v7, and v1 identifier side by side, so you can compare a random value against a time-ordered one from the same instant and see the difference in their leading characters directly, rather than taking the sorting claim on faith.

Deciding whether the switch is worth it for an existing table

Not every table needs this decision made carefully. A reference table that receives a handful of inserts a day will never notice index fragmentation regardless of which UUID version generates its keys, because the write volume never gets close to where the cost becomes visible, and benchmarking B-tree behavior for a table that inserts ten rows an hour would be effort spent solving a problem that does not actually exist in practice.

A table that logs events, sessions, or orders at meaningful volume is a different story. If that table already uses v4 and shows no symptoms, an urgent migration is not warranted; changing a primary key format on a live table with foreign key references is real, disruptive work. For a brand-new table, though, defaulting to v7 avoids ever having that conversation, since it costs nothing beyond generating the identifier this tool already produces.

When migrating is worth the disruption

Weigh the migration cost against the write pattern the table actually sees, not against a general rule that every UUID column must use one version. For anything new, the calculus is simpler: generate v7 by default and keep v4 for cases where global randomness matters more than sort order, such as identifiers you deliberately do not want anyone to guess the creation time of.

A useful signal to watch for on an existing table is index bloat or insert latency that correlates with write volume rather than with table size alone, since that specific pattern points at random-key fragmentation rather than a missing index or an unrelated query plan regression. If that signal shows up on a table already keyed with v4, treat the migration as a scoped, deliberate project rather than deferring it indefinitely.

When to use this

Reach for v7 whenever you are creating a new table's primary key column and want a UUID that also sorts in insertion order; use v4 when you specifically want an identifier with no derivable creation time.

Examples

Before: your schema currently uses id UUID DEFAULT gen_random_uuid(), which generates UUID v4 values with no relationship to insertion order.

Generating a v7 identifier instead produces a value whose first 12 hex characters encode the creation timestamp, so rows inserted later always sort after rows inserted earlier, matching how the table's own B-tree index prefers new writes to land.

Before: click Regenerate All once and note the leading characters of the V4 and V7 rows.

The V4 value's leading characters are unrelated to the moment it was generated, while the V7 value's leading 12 characters are a direct encoding of that same millisecond, which is why only the V7 row keeps the same relative order if you generate a second batch a moment later.