Your UUID Column Is 9x Bigger Than It Needs to Be

A UUID is 16 bytes. In a surprising number of schemas, it occupies 144 bytes — and the index copy occupies 144 more.

That's not a rounding error. It's the difference between an index that fits in memory and one that doesn't.

The 16-byte value stored as 144 bytes

If your ORM maps a UUID to VARCHAR(36) or CHAR(36) — extremely common, because the string form is what the API returns — MySQL with utf8mb4 reserves 36 × 4 = 144 bytes per value. Put an index on it and the index stores the same 144 bytes per entry again, plus row overhead.

An 8 KB index page holds roughly:

• ~54 keys for CHAR(36) utf8mb4 (144 + ~6 bytes overhead)
• ~372 keys for BINARY(16) (16 + ~6 bytes)

So a one-million-row index is ~16 MB in the compact form and ~144 MB in the string form — nine times the I/O to walk it, nine times the buffer pool it occupies, and string comparison (memcmp over up to 36 bytes) instead of a single 16-byte compare.

Postgres escapes this: its native uuid type is 16 bytes, so the damage there is usually a text column chosen by habit. For MySQL the fix is one line:

id BINARY(16) NOT NULL PRIMARY KEY
-- write:  UUID_TO_BIN(?, 1)   -- the 1 swaps the time fields for index locality
-- read:   BIN_TO_UUID(id, 1)

The duplicate you fear isn't the one you'll get

Nobody gets UUID collisions. A v4 UUID has 122 random bits: at a billion stored UUIDs the birthday probability is about 9 × 10⁻²⁰. The duplicates people actually hit come from their own code — a Math.random()-seeded generator, a column with a '' default, or a "friendly" truncated ID.

Truncation is worth doing the math on, because it fails sooner than people expect. An 8-hex-character prefix is 32 bits, and the birthday bound puts a 50% chance of a collision at roughly 77,000 rows. Twelve characters (48 bits) pushes that to ~20 million. If you show short IDs in URLs, generate them deliberately (Base62, sized to the row count you'll ever have) instead of slicing a UUID — and note that a time-ordered v7 UUID is worse for this, because every ID created in the same window shares its leading timestamp characters.

Random inserts are the other cost

v4's first bytes are random, so each insert lands on an arbitrary B-tree leaf. Once the table outgrows cache, every insert becomes a read-miss on a cold page plus a split — which is why write throughput falls off a cliff at a few million rows even though "UUIDs are just 16 bytes."

UUID v7 (or ULID) fixes this by making the timestamp the high-order bits, so inserts append at the right edge. The tradeoff is real: v7 leaks creation time, so don't use it for IDs that must not reveal when something happened.

Rule of thumb: store 16 bytes, index a time-ordered value, and never compare UUIDs as truncated strings.

If you need to generate, format, or sanity-check UUIDs while wiring this up, I keep a small local-only generator here — nothing is uploaded: https://codetoolbox.pro/tools/uuid-generator.html