ORM habit of storing UUIDs as VARCHAR(36) bloats MySQL indexes ninefold

A 16-byte identifier stored as 144 bytes

A post on dev.to walks through a quiet but expensive schema mistake: when an ORM maps a UUID column to VARCHAR(36) or CHAR(36) — a common choice because the string representation is what APIs hand back — MySQL tables using the utf8mb4 character set reserve four bytes per character. A 36-character UUID therefore consumes 144 bytes, and any index on that column carries roughly the same payload again for every entry.

As the post explains, the practical consequence is index density. An 8 KB index page fits about 54 keys in the string form, but roughly 372 when the same values are held as BINARY(16). For a one-million-row table, that is the gap between an index of about 16 MB and one closer to 144 MB — nine times the buffer-pool footprint and nine times the page reads needed to traverse it. Lookups also pay a hidden comparison cost, since matching means comparing strings of up to 36 bytes instead of a single fixed 16-byte value.

The fix

For MySQL, the author recommends changing the column definition to:

id BINARY(16) NOT NULL PRIMARY KEY

with writes passing through UUID_TO_BIN(?, 1) and reads through BIN_TO_UUID(id, 1). That second argument matters: passing 1 reorders the time-related fields inside the UUID so values created around the same moment sit near each other in the index, improving locality.

Postgres users largely escape the problem by default, because the native uuid type is 16 bytes. There, the damage usually traces back to a text column chosen out of habit rather than a type-system limitation.

The collisions worth worrying about

The post also takes on duplicate-ID anxiety. Genuine v4 collisions are statistically negligible: with 122 random bits, the birthday-bound probability of any collision across a billion stored UUIDs is around 9 × 10⁻²⁰. The duplicates that actually show up in production come from application code — generators seeded with weak randomness such as Math.random(), columns that default to an empty string, or identifiers that were shortened by slicing characters off.

Truncation fails sooner than intuition suggests. According to the author's math, an 8-hex-character prefix carries only 32 bits, which gives a 50% chance of a collision at roughly 77,000 rows; extending to 12 characters (48 bits) pushes that threshold only to about 20 million. The recommendation for short IDs in URLs is to generate them deliberately, sized to the row count the system will ever reach, rather than cutting down a UUID. Time-ordered v7 UUIDs are a particularly bad source for such prefixes, because every ID created in the same window begins with the same timestamp characters.

Insert patterns are the second cost

Random v4 values also tax write performance. Because the leading bytes are random, each insert lands on an arbitrary leaf of the B-tree index. Once the table outgrows the cache, every insert means pulling a page from disk and splitting it, which the author identifies as the reason write throughput drops sharply once tables reach a few million rows.

UUID v7 or ULIDs address this by placing the timestamp in the high-order bits, so new rows append at the right edge of the index instead of scattering. The tradeoff, the post notes, is that such IDs disclose when a record was created, making them a poor fit for identifiers that must not reveal timing.

The article's overall guidance: keep identifiers at 16 bytes, let the indexed value be time-ordered, and avoid comparing UUIDs through truncated string forms.

Why it matters

Index size is a direct line item in database performance. An index that fits in the buffer pool is served from memory; one nine times larger forces disk reads onto the hot path for both lookups and inserts. Because this bloat typically comes from a default ORM mapping rather than an explicit decision, it can spread silently across every UUID column in a codebase — and the remedy is a contained, well-understood schema change with measurable payoff: smaller indexes, cheaper comparisons, and more predictable write throughput as tables grow.