"The Worst Uses of UUIDs I've Seen" Part 1: Database Horror Stories

UUIDs are amazing. They give us global uniqueness, decentralization, and a comforting sense of order in the chaos of distributed systems.

But with great uniqueness comes great responsibility — and sadly, not everyone lives up to it.

In this first installment of UUID fails, we’re heading to the place where misused UUIDs wreak the most havoc: the database.

🧟‍♂️ Horror #1: VARCHAR(36) UUIDs in a Billion-Row Table

Context:

A startup built a microservice with a PostgreSQL backend. Each record used a UUIDv4 as the primary key… stored as VARCHAR(36).

Everything was fine — until it wasn’t.

As the table grew past 500 million rows, queries slowed to a crawl. Index size ballooned. Autovacuum choked. The Postgres box wept.

What went wrong?

• VARCHAR(36) means every UUID took 36 characters = up to 72 bytes on disk (due to variable-width encoding)
• Indexes grew huge and fragmented
• Binary comparisons were string-based (slower)

🧠 Lesson:

• Use UUID column types if supported (e.g., in PostgreSQL)
• Or store as BINARY(16) in MySQL or SQLite
• Never store UUIDs as VARCHAR unless you really need human-readable strings

🌀 Horror #2: Clustered Index Chaos with UUIDv4

Context:

A team was migrating from integer IDs to UUIDv4s in a MySQL InnoDB table. They updated the schema, flipped the primary key, and deployed.

Performance tanked overnight. Inserts slowed dramatically. Replication lag piled up.

Why?

MySQL InnoDB uses the primary key as the clustered index. UUIDv4s are completely random, so every insert landed in a random page of the index.

This led to:

• Massive page fragmentation
• Cache churn
• Insertion order chaos

🧠 Lesson:

• Don’t use UUIDv4 as a clustered primary key unless absolutely necessary
• Consider UUIDv7 or ULID for sortability
• Or keep a separate AUTO_INCREMENT primary key and add a UUID as a secondary unique index

🧨 Horror #3: Truncated UUIDs for UI Simplicity

Context:

A dev team wanted to make UUIDs “more readable” in URLs and admin dashboards.

Their solution?

They started using this short_id for lookups, links, and — worst of all — joins.

Things worked for a while… until collisions showed up in production.

🧠 Lesson:

• Truncating UUIDs destroys their collision resistance
• 8 characters = 32 bits = only 4.2 billion combinations
• That might sound like a lot — until you’re running a high-traffic app

Instead, consider:

• Full UUIDs with base64 or ULID formatting
• Or display short IDs, but always use full UUIDs for queries and joins

⚰️ Horror #4: Dual-format UUIDs in the Same Column

Context:

One table. One UUID column. Two dev teams. One used string UUIDs, the other used binary UUIDs.

Half the data was stored as '550e8400-e29b-41d4-a716-446655440000' (string), the other half as 0x550e8400... (binary).

Errors were subtle:

• Some queries worked
• Some silently failed
• Indexes worked inconsistently

No one noticed… until a migration wiped half the data due to mismatched joins.

🧠 Lesson:

• Be consistent in your UUID format and storage
• Pick one: native UUID type, binary, or string — and stick to it
• Normalize all inputs and enforce schema rules at the application layer

🏴‍☠️ Horror #5: UUIDs Without Indexes

Context:

An event-tracking system used UUIDs as foreign keys. But no one created indexes on those columns.

Every time they queried by event ID or session ID, Postgres had to scan millions of rows.

Eventually, they added indexes — but not before burning a week on “mysterious slow queries.”

🧠 Lesson:

• UUIDs aren’t magic — they still need indexes
• Foreign key = index it
• UUID lookups without indexes = performance hell

💀 Horror #6: Sorting UUIDv4s and Expecting Order

Context:

A BI team used ORDER BY uuid_column ASC to sort records “by creation time.”

Which... doesn’t work.

UUIDv4s are random. Their order is not correlated with creation time. The team built entire dashboards off the illusion of ordered IDs.

🧠 Lesson:

• UUIDv4s are not time-sortable
• Use UUIDv7, ULID, or an explicit created_at timestamp
• Don’t rely on UUIDs for temporal ordering unless they were designed for it

Final Thoughts: UUIDs Aren’t Evil — Just Misunderstood

All of these horror stories share the same root cause:

> Misunderstanding how UUIDs actually work under the hood.

UUIDs are not:

• Ordered (unless UUIDv1, v7, ULID, etc.)
• Efficient by default (especially in text form)
• Immune to poor indexing or schema design

But when used properly, they’re an amazing tool for distributed databases and microservice architectures.

So next time you reach for a UUID:

• Think about storage format
• Plan your indexing
• Avoid truncation
• And maybe — just maybe — ask someone who’s lived through the horror 💀

Stay tuned for Part 2: "API Token Fails and Security Nightmares"