UUID v4 vs. UUID v1: Primary Key Optimization & Collision Probabilities

UUIDv1 leaks the MAC address of the machine that generated it and sorts by time. UUIDv4 leaks nothing and sorts randomly, which is what destroys your B-tree. UUIDv7 gives you the time ordering without the hardware identifier, and it is what you should reach for now.

128 bits, laid out

All UUID versions are 128 bits rendered as 32 hex characters in 8-4-4-4-12 groups. Six of those bits are fixed overhead: four for the version and two for the variant.

 xxxxxxxx-xxxx-Mxxx-Nxxx-xxxxxxxxxxxx
                │    │
                │    └─ variant: top 2 bits = 10 for RFC 4122
                └────── version: 1, 4, 7 ...

UUIDv1 spends its bits on a timestamp and a hardware identifier:

bits	field	contents
60	timestamp	100-nanosecond intervals since 1582-10-15 (the Gregorian reform date)
14	clock sequence	random, re-rolled if the clock goes backwards
48	node	the MAC address of the generating machine
6	version + variant	fixed

The timestamp is split across three fields in a counter-intuitive order — time_low, time_mid, then time_hi — which is why v1 UUIDs do not sort chronologically as strings even though they contain a timestamp. The low bits of the clock lead the string, so consecutive UUIDs look unrelated.

UUIDv4 spends almost everything on randomness:

bits	field	contents
122	random	cryptographically random
6	version + variant	fixed

The v1 privacy leak is real and retrospective

Those 48 node bits are the generating machine's MAC address. Any v1 UUID you have ever emitted into a URL, an API response, a log file or a filename carries the hardware identity of the server or laptop that produced it, permanently and in public.

import uuid
u = uuid.UUID('a8098c1a-f86e-11da-bd1a-00112444be1e')
print(u.version)   # 1
print(u.node)      # 73596465246  -> 00:11:24:44:be:1e
print(u.time)      # 100-ns intervals; convertible to a wall clock

Two documented consequences: the Melissa macro virus investigation in 1999 used the MAC address embedded in Office document GUIDs to tie files to a specific machine, and the same technique has been used since to correlate pseudonymous accounts that shared a generating host. If you have v1 UUIDs in public identifiers, you have published part of your infrastructure inventory and a timeline of when each row was created.

Most modern libraries mitigate by substituting a random node ID — Python's uuid.uuid1() does when it cannot read a MAC — but you cannot assume that of UUIDs generated years ago by other code.

The v4 collision math, done properly

122 random bits gives 2^122 ≈ 5.3 × 10^36 values. The useful question is not "when do we exhaust the space" but "at what point does a collision become likely", which is the birthday problem.

For a space of size N and n draws, the probability of at least one collision is approximately:

p ≈ 1 - exp(-n² / (2N))

With N = 2^122:

UUIDs generated	collision probability
10^9 (a billion)	≈ 9.4 × 10^-20
10^12 (a trillion)	≈ 9.4 × 10^-14
2.6 × 10^18	≈ 50%

import math
def collision_probability(n: int, bits: int = 122) -> float:
    return 1 - math.exp(-(n ** 2) / (2 * (2 ** bits)))

print(collision_probability(10 ** 9))    # 9.4e-20
print(collision_probability(10 ** 12))   # 9.4e-14

Generating a billion v4 UUIDs a second, you would need roughly 85 years to reach a one-in-a-billion chance of a single duplicate. v4 collisions are not a practical concern. What *is* a practical concern is your random source: a v4 generated from Math.random() rather than a CSPRNG has far less entropy than 122 bits and is both guessable and collision-prone. Use crypto.randomUUID() in Node and browsers, uuid.uuid4() in Python, gen_random_uuid() in PostgreSQL.

Why v4 primary keys slow a database down

This is the real cost, and it has nothing to do with collisions.

A B-tree index keeps keys in sorted order on disk in fixed-size pages. Insert behaviour depends entirely on where new keys land:

• Sequential keys (an auto-increment integer, or a time-ordered UUID) always append to the rightmost leaf page. That page is already in memory, fills up, splits once, and moves on. Each page ends roughly full.
• Random keys (v4) land in a uniformly random leaf page. With an index larger than your buffer pool, every insert is a read of a cold page from disk, a modification, and a write. Pages split down the middle rather than at the end, so they settle around 50–70% full, which inflates the index and reduces how much of it fits in memory — making the next insert more likely to miss. The effect compounds.

Two further costs specific to some engines:

• InnoDB clusters the table on the primary key, so a random PK randomises the physical row order too, not just an index. Range scans over "recently created" rows become scattered reads.
• Secondary indexes in InnoDB store the primary key as the row pointer. A 16-byte binary UUID (or worse, a 36-byte string) is copied into every secondary index, inflating all of them.

If you must use v4 as a key, at minimum store it as 16 raw bytes (BINARY(16), uuid, UUID) and not as a 36-character string. The string form costs 2.25× the space in every index and makes comparisons byte-wise over ASCII.

UUIDv7 is the answer to the ordering problem

UUIDv7, standardised in RFC 9562 (which replaced RFC 4122 in 2024), puts a plain Unix millisecond timestamp in the most significant bits:

bits	field
48	Unix epoch milliseconds, big-endian
4	version (7)
12	sub-millisecond sequence or random
2	variant
62	random

Because the timestamp leads, v7 UUIDs sort chronologically as bytes *and* as strings. You get append-mostly insert behaviour like an auto-increment integer, with 74 bits of randomness and no hardware identifier.

// Node 19+ has v4 built in; v7 comes from the uuid package (v9.1+).
import { randomUUID } from 'node:crypto';
import { v7 as uuidv7 } from 'uuid';

const a = uuidv7();
const b = uuidv7();
console.log(a < b);              // true — lexicographic order is time order
console.log(randomUUID());       // v4: no ordering guarantee

// The timestamp is readable without a library:
const ms = parseInt(a.replace(/-/g, '').slice(0, 12), 16);
console.log(new Date(ms).toISOString());

# uuid6 package, or Python 3.14+ stdlib uuid.uuid7()
from uuid6 import uuid7
from datetime import datetime, timezone

a, b = uuid7(), uuid7()
assert str(a) < str(b)

ms = int(a.hex[:12], 16)
print(datetime.fromtimestamp(ms / 1000, tz=timezone.utc).isoformat())

Storing them without wasting the index

The storage decision matters as much as the version. A UUID is 16 bytes; the canonical text form is 36 characters.

engine	do this	not this
PostgreSQL	uuid (native, 16 bytes)	varchar(36), text
MySQL / MariaDB	BINARY(16)	CHAR(36), VARCHAR(36)
SQL Server	uniqueidentifier	nvarchar(36) — 72 bytes with UTF-16
SQLite	BLOB	TEXT

A CHAR(36) key costs 2.25× the bytes of BINARY(16) in the table *and* in every secondary index, and in InnoDB the primary key is copied into every secondary index, so the waste multiplies. On a hundred-million-row table with four secondary indexes the difference is measured in tens of gigabytes.

MySQL has a specific trap and a specific fix. UUID_TO_BIN(uuid) stores the bytes in canonical order; UUID_TO_BIN(uuid, 1) swaps the first two time fields of a v1 UUID so the result sorts chronologically. The swap flag is meaningless for v4 (there is no time to move to the front) and *harmful* for v7, whose bytes are already in time order — swapping them destroys the ordering you chose v7 for.

-- MySQL 8: v7 stored as raw bytes, no swap flag.
CREATE TABLE orders (
  id BINARY(16) PRIMARY KEY,
  created_at TIMESTAMP(3) NOT NULL,
  INDEX (created_at)
);
INSERT INTO orders (id, created_at) VALUES (UUID_TO_BIN(?), NOW(3));
SELECT BIN_TO_UUID(id) AS id FROM orders WHERE id = UUID_TO_BIN(?);

-- PostgreSQL: native type, and v7 generated in the application or by pg_uuidv7.
CREATE TABLE orders (
  id uuid PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT now()
);
-- v4 server-side, if you do not need ordering:
ALTER TABLE orders ALTER COLUMN id SET DEFAULT gen_random_uuid();

One more option worth knowing: ULID solves the same ordering problem as v7 with a 26-character Crockford base32 text form that is sortable as a string and shorter than a UUID. It is not an RFC standard and not a UUID, so library and database support is thinner. If you want time-ordered IDs and your stack already speaks UUID, v7 is the one with the standard behind it.

Choosing

use	pick
Primary keys, especially in a clustered index	v7
Public identifiers where creation time must not leak	v4
Idempotency keys, API request IDs, nonces	v4
Deterministic IDs derived from a name or URL	v5 (SHA-1 of a namespace + name)
Anything new	not v1

The one real trade-off: v7 leaks creation time to anyone holding the ID. If your IDs are public and the creation timestamp is sensitive — competitors counting your signups by comparing two IDs, say — that is a reason to use v4 and accept the index cost, or to keep a v7 primary key internally and expose a separate v4 or opaque token externally. That split is usually the right answer anyway.

Migrating an existing v4 column: add a v7 column, backfill new rows, and switch the key in one migration. You cannot retrofit ordering onto existing v4 values, because the information was never there.

Need identifiers for a fixture or a seeded table: the UUID generator produces them in bulk.