UUIDs: When to Use Them, the v4-vs-v7 Question, and the B-Tree Fragmentation Problem

UUIDs are everywhere in modern systems and the choice between flavors of them - v4 vs v7, mostly - is one of those decisions that has surprisingly real database performance implications. I learned this the hard way when a project I worked on used v4 UUIDs as the primary key for a high-write table, and over six months the insert latency tripled because the B-tree index was fragmenting. UUID v7, which is sortable by time, would have avoided the problem. This post is the explanation of why and what to actually use.

What a UUID is, briefly

A UUID is a 128-bit number, conventionally formatted as 32 hex digits split into five groups by hyphens: 550e8400-e29b-41d4-a716-446655440000. The format is defined by RFC 4122 (now updated by RFC 9562). There are several versions, distinguished by the structure of the bits, but only a few are in common use.

The math of uniqueness: a UUID has 122 random bits (after subtracting 6 bits for the version and variant fields). The number of possible UUIDs is roughly 5 × 10^36. To have a 50% chance of collision in random UUIDs, you would need to generate about 2.7 × 10^18 of them - billions of UUIDs per second for the lifetime of the universe. In practice, UUIDs do not collide.

UUID v4: the random one

Version 4 is the most common UUID. It is just 122 random bits, with 6 bits reserved to mark it as v4. Every UUID v4 generator uses cryptographically secure randomness (or at least should; Math.random-based generators exist and are wildly inappropriate).

The Toolium UUID Generator uses crypto.randomUUID(), which is built into modern browsers and pulls from the OS's cryptographic random source. The same source that generates session tokens and TLS keys.

The B-tree fragmentation problem

Here is where v4 goes wrong as a database primary key. Most database indexes (specifically, the primary key indexes in PostgreSQL, MySQL with InnoDB, and most other relational databases) are B-trees. B-trees are sorted, and inserting new rows is fastest when the new key is at or near the end of the existing sorted order, because the page in the tree where the new key goes is probably already in memory.

If your primary key is a v4 UUID, every insert generates a random key that could be anywhere in the existing sorted order. The database has to load the random tree page from disk, modify it, and write it back. Worse, the new key disrupts the page's fill factor; over time the pages need to be split, the index gets fragmented, and insert performance degrades.

For low-write tables, this does not matter. For high-write tables (event logs, audit trails, request metadata), it really does. A team I worked on saw a 3x increase in insert latency over six months due to v4 UUID fragmentation. The fix was either to switch to v7 or to use a different primary key type entirely.

UUID v7: the time-ordered fix

UUID v7, finalized in 2024 (RFC 9562), encodes a millisecond timestamp in the high-order bits. The format: 48 bits of Unix timestamp in milliseconds, 12 bits of version+random, 62 bits of random, 2 bits of variant. The result is a UUID that sorts in roughly chronological order: UUIDs generated later have lexicographically higher values.

This solves the B-tree problem. Insert performance stays consistent because new keys cluster at the end of the sorted index, where the database is already optimized for them.

v7 also has nice secondary properties. The timestamp is recoverable from the UUID, which is occasionally useful (you can tell when a record was created from its ID alone, without a separate created_at column). The ordering is monotonic per generator, so if you generate two UUIDs on the same machine within the same millisecond, they still sort correctly.

For new systems, v7 is the right choice for primary keys. The Toolium generator can produce both; pick v7 unless you have a specific reason for v4.

UUIDs vs auto-increment integers

The traditional alternative to UUIDs is a sequential integer primary key (an auto-incrementing ID column). Integers are smaller (8 bytes vs 16 bytes for UUIDs), faster to compare, and produce consistently good index performance.

The reasons to use UUIDs anyway:

• Distributed generation. If multiple services or shards generate IDs, integers require coordination (a central sequence generator, or assigned ranges per shard). UUIDs do not.
• Unguessable URLs. If your URLs include the ID, sequential integers let users enumerate (the next user after /users/47 is probably /users/48). UUIDs prevent this.
• Database merging. If you ever need to combine two databases, integer IDs would collide. UUIDs won't.
• Offline-first apps. Mobile apps that generate records offline and sync later need IDs that can be generated client-side without conflict.

If none of these apply, integers are simpler and faster. If any apply, UUIDs are worth the cost.

The privacy angle

UUIDs are unguessable, which is sometimes a security feature. A URL like /share/550e8400-e29b-41d4-a716-446655440000 is functionally a capability: anyone with the URL can access the resource, but the URL itself is impossible to guess. This is how "view-only link" sharing works in Google Docs, Dropbox, etc.

The flip side: do not put UUIDs in places that get logged or indexed if they grant access. URLs in browser history, server logs, analytics events - all of these can leak the UUID and grant access to whoever sees them.

The Toolium generator

The UUID Generator produces v4 or v7 UUIDs in your browser. Single or bulk (up to 1000 at a time, useful for seeding test data). The randomness comes from crypto.getRandomValues, which is cryptographically secure. Nothing is logged or transmitted; the UUIDs exist only in your browser tab.