JANITHJAYASINGHE
← ALL POSTS

Databases · Nov 2026 · 4 min read · 02/05

UUIDs vs auto-increment IDs at scale

What unpredictable IDs cost you in a ticketing system issuing millions of tickets, and two ways to keep the security without the slowdown.

Illustration of a database emitting ordered blocks of data that fan out into a large network of nodes

While building a production-grade event ticketing system issuing millions of tickets, we chose UUIDs over auto-increment IDs for security: unpredictable IDs mean no one can enumerate ticket numbers. But at scale, we felt the performance cost directly.

Here's what we learned.

Size matters more than it looks

Auto-increment IDs are typically 4 bytes (INT) or 8 bytes (BIGINT). UUIDs are 16 bytes. That's up to a 4x jump per row, and it compounds fast once you add indexes.

Random vs. sequential inserts

Auto-increment IDs are always ordered, so new rows append cleanly to the end of a B-tree. UUIDs (pre v6/v7) are random, so every insert lands in a random spot in the index, causing page splits, fragmentation, and slower writes over time.

The index bloat multiplies

On engines like InnoDB, every secondary index internally stores the primary key value alongside its own columns. So if your primary key is a 16-byte UUID and you have 5 secondary indexes, you're paying that 16-byte cost 5 times over. Not once. It adds up to real, avoidable storage overhead.

Joins slow down too

Bigger keys mean less cache-friendly comparisons and more random I/O once fragmentation kicks in. Joins on UUID columns get measurably slower than joins on integers.

But we can't just drop UUIDs

In distributed systems, when merging databases, or when ID unpredictability is a security requirement, UUIDs are often non-negotiable.

Two fixes we landed on

Use UUIDv6/v7 instead of v4. These are time-ordered (v7 embeds a Unix timestamp prefix), so inserts stay roughly sequential and B-tree fragmentation drops significantly. This fixes the fragmentation problem, but not the storage size problem, since you're still at 16 bytes.

Hybrid key pattern. Use a BIGINT as the actual internal primary/clustered key for performance: fast inserts, compact indexes, cheap joins. Then expose a UUID (v4 or v7) as the public-facing identifier via a unique secondary index. You get the security benefit of an unguessable public ID and the performance of integer-based internals.

UUIDs vs auto-increment isn't really an either/or. It's about knowing which cost you're paying and where you actually need the tradeoff.

Curious how others have handled this at scale. Pure UUIDv7, hybrid keys, or something else entirely?

NEXT POST →Distributed transactions: 2PC vs Saga