UUIDs in Databases: Choosing the Right Version for Primary Keys and Indexing

A UUID (Universally Unique Identifier) is a 128-bit value that lets you generate unique IDs across any number of systems without coordination. That property makes UUIDs a popular choice for primary keys in distributed databases — but the version you pick has real consequences for index performance, storage size, and sortability. This guide covers the practical trade-offs of using UUIDs as database keys, version by version.

Why UUIDs Are Tempting for Primary Keys

• No coordination: Clients can generate IDs offline and insert later without collision risk
• No enumeration: UUIDs don’t reveal row counts or growth rate the way auto-increment IDs do
• Merge-friendly: Sharding, replication, and offline sync don’t require re-keying

The catch: a UUID stored as a string is 36 characters, compared to 4-8 bytes for an integer key — and a naive v4 UUID is random, which destroys the locality that B-tree indexes rely on. Those two facts drive most of the performance debate.

Version by Version: What Changes for Your Database

Version	Based On	Index Behavior	Best For	Watch Out For
v1	Timestamp + MAC	Roughly time-ordered	Time-ordered inserts	Leaks MAC address; low entropy
v4	Random	Random — index fragmentation, page splits	Default for most apps	Poor B-tree locality at scale
v5	SHA-1 of namespace + name	Deterministic	Dedup, reference keys	Needs careful namespace management
v7	Timestamp + random	Time-ordered — good index locality	Database primary keys	Newer; tooling support still growing

The v4 Randomness Problem, Explained

B-tree indexes stay fast when new keys are inserted near existing ones — that’s why auto-increment integers work so well. Random v4 UUIDs arrive at random positions in the index, forcing page splits, cache misses, and a bloated, fragmented index tree. On small tables you’ll never notice; on tables with tens of millions of rows, insert throughput and query latency degrade measurably.

Practical Recommendations

• New database work: prefer v7 — it keeps the no-coordination benefit while behaving like a sequential key in your indexes
• Existing apps on v4: don’t rewrite everything; instead store UUIDs as native binary types instead of 36-character strings, and consider a clustered index on a separate auto-increment key
• Never index v4 UUID strings with a non-clustered index if you can avoid it — that’s the worst combination of storage bloat and fragmentation
• For deterministic reference keys (same entity → same UUID), use v5 with a stable namespace

Generate and Test Your Own

If you’re evaluating UUIDs for a project, generate a batch and inspect them — check how many start with the same timestamp prefix (v1/v7) versus being fully random (v4), and measure the string length you’ll be storing. The UUID Generator at Today Calculator produces multiple versions with bulk generation, so you can compare formats side by side before committing to a schema.