UUIDs Explained: Versions, Collision Math, and Database Reality

The version nibble (first hex digit of the third group) declares the recipe:

• v4 — 122 random bits. The general-purpose default everywhere: crypto.randomUUID(), Python uuid4(), Postgres gen_random_uuid().
• v7 (RFC 9562, 2024) — 48-bit Unix-millisecond timestamp + random bits. Sorts by creation time; the modern choice for database primary keys.
• v1 — timestamp + node identifier (historically the MAC address, which famously helped identify the Melissa virus author in 1999). Legacy; leaks time and machine identity.
• v5 — SHA-1 of a namespace + name. Deterministic: same input, same UUID, forever. For deriving stable IDs from external keys (email → user UUID).
• Nil (all zeros) and Max (all Fs) — sentinels, occasionally useful, never generate them by accident.

The birthday paradox governs collisions: with N possible values, expect a 50% chance of one collision after ~1.2×√N draws. For v4's 2^122 space, that is ~2.7×10^18 UUIDs — a billion per second, sustained for 86 years, to reach a coin-flip's chance of a single duplicate. Generating a million UUIDs a day, the probability of ever colliding is so far below hardware-failure rates that it belongs to a different conversation.

Every real-world 'UUID collision' story resolves to broken generation, not probability: a non-cryptographic RNG seeded identically in cloned VM images, a forked process inheriting RNG state, a copy-paste of a hardcoded example ID. The lesson is operational — always use CSPRNG-backed library functions, never seed UUID generation, audit golden images — not mathematical.

B-tree indexes love sequential keys: new entries append to the same hot pages, caches stay warm. Random v4 keys land uniformly across the whole index — every insert touches a random page, causing splits, write amplification, and cache misses. On insert-heavy MySQL/InnoDB tables (where the primary key is the clustered index), the difference is measurable and painful; Postgres heap tables suffer less but still pay in index size.

UUIDv7 keeps global uniqueness while making keys time-ordered: the leading 48 bits are a millisecond timestamp, so consecutive inserts are index neighbors. For new schemas with meaningful write volume, v7 (or ULID, its spiritual sibling) is the sensible default; v4 remains fine for moderate volume and for keys that aren't clustered. Orthogonally: store UUIDs as the native 16-byte type (Postgres uuid, MySQL BIN_TO_UUID helpers), not as 36-character strings — indexes shrink by half and joins stop comparing text.

One caveat before making v7 your everything-default: the embedded timestamp is readable by anyone who sees the ID. Usually harmless; occasionally (public tokens where creation time is sensitive) a reason to stay with v4.

Sequential integer IDs in URLs invite enumeration — increment and scrape (the classic IDOR pattern). UUIDs close the enumeration hole: guessing a valid v4 is hopeless. But unguessability is not authorization; the server must still check that the requester may access the object. 'Random URL' as the only access control is how cloud-storage leaks happen.

Formatting notes that prevent petty bugs: RFC output is lowercase (parsers must accept both — normalize on ingest); Microsoft tooling brackets GUIDs in braces ({…}) that other systems reject; and validation is worth doing at boundaries — a 'UUID' column quietly accumulating test-strings and truncated values is a classic data-quality failure.

Need an ID with no coordination and no special requirements → v4. Designing a new table with heavy inserts and a UUID primary key → v7, stored natively. Need the same external key to always map to the same ID → v5 with a fixed namespace. Maintaining a system that emits v1 → know it leaks node identity and creation time, and plan migration. Whatever the version, generate through standard libraries and validate at the edges.