Skip to content

← All writing

UUID v4 vs. v7: Which Should You Use for Database IDs?

· by Andergrove Software

A UUID is a 128-bit identifier you can generate anywhere without asking a central database, which is why distributed systems love them. The catch is the version you pick. UUIDv4 is fully random; UUIDv7 is time-ordered, a timestamp followed by random bits, standardised in RFC 9562. For database primary keys that difference is the whole game: random keys quietly hurt your index, time-ordered ones do not.

Here is what each version is, why random UUIDs fragment a B-tree index, and a decision table for choosing between v4, v7 and plain auto-increment. You can generate v4 UUIDs in bulk in the UUID generator, and this post shows how to produce v7 where your stack supports it.

What a UUID is, and why v4 won

A UUID (Universally Unique Identifier) is a 128-bit value, usually shown as 32 hex digits in five dash-separated groups: xxxxxxxx-xxxx-Mxxx-Nxxx-xxxxxxxxxxxx. The M position encodes the version. v4 fills almost all of its 122 free bits with randomness, so two generators essentially never collide and you need no central authority. That convenience made v4 the default for application-generated IDs.

The hidden cost of random primary keys

Most relational databases store rows in a B-tree ordered by the primary key. In MySQL's InnoDB the table itself is physically clustered on it. When the key is random, each new row lands at an unpredictable spot in that tree:

  • Inserts scatter across the whole index instead of appending at the end, causing page splits and fragmentation.
  • The "hot" pages you need in memory are spread out, hurting cache locality and write throughput.
  • The index ends up larger and slower than a sequential one.

With a sequential key, every insert appends to the rightmost page, which is cheap and cache-friendly. Random UUIDv4 throws that away. On a high-insert table the difference is measurable.

UUIDv7: time-ordered by design

UUIDv7 puts a 48-bit Unix millisecond timestamp at the front, then fills the rest with randomness (plus the version and variant bits). Because the most significant bits increase with time, v7 values sort in roughly creation order, so new rows append near the end of the index just like an auto-increment key, while keeping the "generate anywhere, no collisions" property of a UUID. (If epoch milliseconds are fuzzy, see Unix timestamps explained.)

You get UUID convenience without the index penalty. The trade-off: a v7 reveals its creation time and is loosely ordered, so do not use it where IDs must be unpredictable or must hide timing.

v4 vs. v7 vs. auto-increment

  • Auto-increment (bigint). Smallest at 8 bytes, fastest, perfectly sequential. But the database must assign it (no client-side generation), it leaks row counts, and it is awkward across sharded or multi-master setups.
  • UUIDv4. Generate anywhere, reveals nothing about order or time, no practical collisions. But 16 bytes, and index-hostile as a clustered primary key.
  • UUIDv7. Generate anywhere, index-friendly (sequential), no practical collisions. But 16 bytes, and it exposes creation time and order.

Rule of thumb: sequential internal IDs, use a bigint; distributed or client-generated keys where insert locality matters, use v7; public-facing IDs that must not reveal timing or order, use v4 (or a separate random token).

Practical guidance

  • Postgres: store UUIDs in the native uuid type (16 bytes), never as text. A v7 primary key behaves close to a bigserial for insert locality.
  • MySQL/InnoDB: the table is clustered on the primary key, so random v4 is especially costly here. Prefer v7 and store it as BINARY(16), not CHAR(36).
  • When not to use a UUID at all: small single-node tables where a bigint is simpler and smaller, or anywhere a v7's embedded timestamp would leak something you care about, in which case keep a bigint key and expose a separate random token.

Generate one

The Andergrove UUID Generator produces v4 UUIDs in bulk, entirely in your browser, with uppercase and no-dash options. For v7, most stacks now generate them natively: 

-- Postgres 18+
SELECT uuidv7();

// Node (npm i uuidv7)
import { uuidv7 } from 'uuidv7';
const id = uuidv7();

Generate a handful of v7s and you will see they share a leading prefix that grows over time. That ordering is exactly what keeps your index happy. For the timestamp sitting inside a v7, see Unix timestamps explained.