UUID Generator

🟢📖 UUID v4 vs v7 — Which Should You Use?

💾 What a UUID costs once it is a primary key

Generating a UUID is free. Storing one is not, and the bill arrives in a place most teams do not look: the B-tree.

Start with the column type. A UUID is 16 bytes. Written as the canonical string it is 36 characters, and most schemas use CHAR(36) or VARCHAR(36) because that is what the API returns. At 100 million rows that difference is 3.6 GB versus 1.6 GB for the key column alone — and in InnoDB every secondary index stores the primary key in its leaf nodes, so five secondary indexes multiply the penalty five times over.

The larger cost is ordering. InnoDB clusters rows by primary key, so the key is the physical location. A v4 key is uniformly random: each insert lands in a random 16 KB page, which means a page that was 70% full gets split, half its contents copied, and a page that was in memory is evicted. A sequential key only ever appends to the rightmost page. This is why benchmarks consistently show random UUID primary keys writing several times slower than auto-increment integers, with far worse cache behaviour under load — the rows are the same, the access pattern is not.

The fix is not to abandon UUIDs; it is to use the version that sorts. v7 embeds a millisecond timestamp in the leading bits, so inserts arrive in roughly ascending order and the B-tree behaves much like it does with a sequence.

🔢 128 bits, of which 122 are yours

A v4 UUID is not 128 random bits. Six of them are fixed by the format: four hold the version number, and two hold the variant. That leaves 122 bits of entropy, about 5.3 × 10^36 possible values.

Those fixed bits are visible in every v4 string, which makes validation trivial. The 13th hex character is always 4, and the 17th is always one of 8, 9, a or b:

• f47ac10b-58cc-4372-a567-0e02b2c3d479 → version 4, variant 1
• 0190d4b2-7c31-7f8a-9b12-6d4e88a1c03f → version 7, variant 1

In practice the collision question is settled by the birthday bound rather than intuition: you would need on the order of 2.7 × 10^18 v4 UUIDs generated before a 50% chance of any collision — roughly 85 per second for a billion years. Collisions are not the risk. Clock handling, storage layout, and using the wrong version for the job are the risks.

🕰️ v1 leaks where you are; v7 breaks when the clock moves

UUID v1 is a privacy hazard. Its last 48 bits are the node identifier, and the original specification called for the machine's MAC address. Any document, log line, or URL containing a v1 UUID therefore carried a stable hardware identifier and a 100-nanosecond timestamp — enough to link activity from one machine across unrelated services. Modern libraries substitute a random node value, but the format still encodes sub-millisecond generation time, which is why v1 has no place in anything user-facing.

v7 has the opposite problem: it trusts the clock. Monotonicity is the whole reason to choose v7, and a clock that steps backwards — an NTP correction, a VM snapshot restore, a container resumed after suspension — can produce a UUID smaller than one already issued. On a primary key that means a new row inserting before existing rows, which corrupts the assumption everything downstream was built on.

RFC 9562 addresses this with three counter strategies. Method 1 keeps unused random bits from the previous millisecond and increments them. Method 2 uses a monotonic random counter seeded per millisecond. Method 3 adds sub-millisecond precision so IDs stay ordered even within a single tick. All three exist because two IDs generated in the same millisecond, in the same process, are otherwise only ordered by luck. If you generate v7 in more than one thread or instance without a shared counter, neighbouring IDs can interleave out of order no matter how correct the clock is.

See also: UUID v4 vs v7: Database Performance Guide · UUID Collision Estimator · Unix Timestamp Converter