What Nobody Tells You About UUIDs as Database Primary Keys

UUIDs are popular as database primary keys. The pitch is appealing: globally unique identifiers that you can generate anywhere, no coordination with a central authority, no information leak from sequential IDs, no merge conflicts when you shard or replicate. The pitch is also incomplete. UUIDs have real costs that most blog posts and tutorial videos skip over.

Here is what nobody tells you, with the actual tradeoffs and the cases where UUIDs are still the right call.

The four costs of UUIDs as primary keys

1. Size: 16 bytes vs 8 bytes vs 4 bytes

A UUID is 16 bytes. An auto-incrementing 64-bit integer is 8 bytes. An auto-incrementing 32-bit integer is 4 bytes. The size difference sounds small in absolute terms, but it compounds through every index, every foreign key, every join, every row in the table.

A concrete example: a table with 10 million rows, where the primary key is referenced by 5 other tables (each with 10 million rows of their own), has the primary key stored 60 million times. With a UUID, that is 960 MB of primary-key storage. With a 64-bit integer, that is 480 MB. With a 32-bit integer, that is 240 MB. The UUID costs 2-4x the storage of the integer alternatives.

Storage is cheap, but bandwidth and cache are not. A row with a UUID primary key is 16 bytes larger than a row with a bigint primary key. Over millions of rows in a query result, the extra bytes add up. They also make indexes bigger, which means fewer index entries fit in memory, which means more disk reads, which means slower queries.

2. Index fragmentation with random UUIDs (v4)

This is the cost nobody mentions and the one that hurts the most. A v4 UUID is random. When you insert a new row, the database has to put the row at a random place in the B-tree index, not at the end. Each random insert is roughly 2-5x slower than an append (which is what a sequential integer does) because the database has to:

• Find the right page in the B-tree for the new value
• If the page is full, split it (and possibly split parents)
• Write the new entry and the split pages to disk

Sequential inserts never split pages because the new value is always at the end. Random inserts split pages constantly, which is expensive. Over time, the index becomes fragmented even if you have plenty of free space, because the splits create partially-filled pages that are scattered across the disk.

The performance hit is real but not catastrophic for small tables (under 1 million rows). For larger tables (10 million+ rows), the difference is 2-5x slower writes and 1.5-2x slower reads. For very large tables (100 million+ rows), it can be the difference between a healthy database and one that needs constant re-indexing.

3. v7 UUIDs fix most of the fragmentation, but not the size

UUID v7 was introduced in RFC 9562 (2024) specifically to address the index fragmentation problem. A v7 UUID is time-ordered: the first 48 bits are a millisecond timestamp, the rest is random. The first 48 bits of a new UUID are always greater than the first 48 bits of the previous UUID (modulo clock skew), which means new inserts go to the end of the index, just like a sequential integer.

The fragmentation problem disappears with v7. The size problem does not. A v7 UUID is still 16 bytes. The 2-4x storage cost remains. If you are willing to accept the size cost, v7 is a much better default than v4 for primary keys.

4. The information leak is a feature, not a bug

The "information leak" of sequential integer IDs is that you can tell how many orders a company has processed (the next ID is N+1), how many users signed up today, and how fast a competitor is growing. The fix is to use UUIDs for the public-facing ID (in URLs, in APIs) but sequential integers for the internal primary key. You get the obscurity of UUIDs where it matters (the URL) and the performance of integers where it matters (the index).

This is a hybrid pattern: an internal id BIGINT AUTO_INCREMENT PRIMARY KEY column for performance, and a separate public_id CHAR(36) UNIQUE column for the public-facing identifier. The public_id is indexed (for fast lookups by URL) but it is not the clustered primary key, so the B-tree fragmentation does not affect the main writes.

When UUIDs are the right primary key

UUIDs are the right primary key when:

• The database is distributed across multiple nodes or regions, and you cannot rely on a central auto-increment. The cost of generating globally unique IDs without coordination is exactly what UUIDs solve.
• The IDs are generated in the application layer, not the database. A mobile app, a browser, an edge function, or a disconnected client needs to create an ID that will be valid in the central database later. The only way to do that without coordination is a UUID.
• The IDs cross system boundaries. A user ID that is the same in the web app, the mobile app, the analytics warehouse, and the support tool needs to be a UUID, because the systems do not share an auto-increment sequence.
• You need cryptographic guarantees against enumeration. Sequential IDs let an attacker scrape your entire database by incrementing the URL. UUIDs do not.

For a single-server database, with a single application, where the IDs never leave the system: use a bigint auto-increment. The performance is 2-5x better, the storage is 2-4x smaller, and you can always add a public_id column later if you need to expose UUIDs externally.

The v4 vs v7 decision

If you are going to use UUIDs as primary keys, use v7. The index fragmentation problem with v4 is severe enough that no serious database engineer uses v4 as a clustered primary key anymore. v7 is a drop-in replacement that fixes the fragmentation without giving up the global uniqueness or the application-layer generation.

The UUID Generator on this site produces both v4 and v7. The v4 vs v7 comparison post covers the differences in more detail. The short version: v7 for primary keys and for any index that gets frequent writes, v4 for everything else (external IDs, message IDs, session tokens, one-time identifiers where the time-ordering is not useful).

Encoding and storage

UUIDs are typically written as 36 characters (8-4-4-4-12 with hyphens) or 32 characters (no hyphens). For database storage, the canonical form is 16 bytes (binary) or 36 bytes (string). For URL-safe identifiers, base32 or base64 is sometimes used to compress the 16 bytes to 22-24 characters, but this loses the standard UUID format and complicates interoperability.

The pragmatic choice: store as 16 bytes (binary) in the database, expose as the standard 36-character format in URLs and APIs. The storage is compact, the URL is human-readable, and any tool that understands UUIDs (which is most of them) will parse it.

Migration considerations

If you have an existing database with integer primary keys and you want to add UUIDs, do not change the primary key. Add a public_id column (a UUID v7) and a unique index on it. The application layer generates the public_id at insert time and uses it for all external references. The internal integer remains the primary key for performance. The migration is a non-event: add the column, backfill, update the application to expose public_id, and you're done.

If you have an existing database with v4 UUIDs as primary keys and you want to migrate to v7, the migration is harder. You have to change the primary key type, which is a multi-step migration that involves a new column, a backfill, a swap, and a drop. The reason to do it is the 2-5x write performance gain, which can be worth it for a write-heavy system.

The honest summary

UUIDs are not free. They are 2-4x larger than integers, 2-5x slower to index when random, and harder to migrate away from once you start. Use them where the benefits are real (distributed systems, application-layer ID generation, cross-system references, anti-enumeration security). Use integers where the benefits are not (single-server databases, internal-only IDs, write-heavy primary keys). The hybrid pattern — integer for the primary key, UUID for the public identifier — gets you the best of both: the performance of integers where it matters, the obscurity and portability of UUIDs where they matter. The UUID Generator on this site produces both formats; the v7 generator produces the time-ordered variant that is the right default for new systems. The cost of a UUID is not the 16 bytes; it is the 2-5x index fragmentation you do not see until the table has 10 million rows.