UUID v4 vs UUID v7 vs ULID: Best Database Primary Keys
When architecting a distributed system, relying on traditional auto-incrementing integers (BIGSERIAL or AUTO_INCREMENT) for primary keys quickly becomes a liability. Sequential integers expose business metrics to competitors, create central bottlenecks in multi-region databases, and complicate data migration.
To solve this, the software industry universally adopted UUID v4 (Universally Unique Identifiers). Because a Version 4 UUID is generated using pure cryptographic randomness, developers can safely generate them across thousands of decentralized microservices without fear of collision.
However, as these databases scale from thousands to millions of rows, a critical architectural flaw emerges: purely random identifiers destroy relational database performance. To solve the database performance crisis caused by UUID v4, the engineering community developed time-ordered alternatives. Today, architects are migrating to UUID v7 (standardized in RFC 9562) and ULID. Understanding the mathematical differences between these formats is essential for building scalable, high-write data tiers.
1. The Database Crisis: Why UUID v4 Breaks B-Tree Indexes
To understand why UUID v4 causes performance degradation, you must look at how storage engines organize data on disk.
In relational databases like PostgreSQL, MySQL (InnoDB), and SQL Server, indexes are structured as balanced search trees (B-Trees). These trees store data in fixed-size memory blocks called pages (typically 8KB or 16KB). Within each page, records are sorted in ascending order.
- Sequential Writes (Fast): When using auto-incrementing IDs, every new record is larger than the last. The database simply navigates to the rightmost leaf page and appends the data. The page fills up to 100%, and a new page is seamlessly created.
- Random Writes (Slow): Because a UUID v4 is entirely random, an incoming insert could belong anywhere in the index. The database must constantly find arbitrary pages in the middle of the B-Tree to insert new rows.
The Three Penalties of Random Insertions:
- Costly Page Splits: If an insert targets a leaf page that is already full, the database must physically split the page in half, move 50% of the rows to a new page, and rebalance the tree pointers. This causes severe write amplification.
- Buffer Pool Cache Thrashing: Databases cache active index pages in RAM. With sequential keys, only "newest" pages are cached. With random UUIDs, every insert hits a random page, forcing the database to constantly evict healthy pages to fetch cold ones from SSDs.
- Index Bloat: Page splits leave pages partially empty, making the physical index file 30% to 50% larger than necessary.
2. UUID v7 (RFC 9562): The Modern Time-Ordered Standard
In May 2024, the Internet Engineering Task Force (IETF) published RFC 9562, formally replacing the aging RFC 4122 standard. The crowning achievement of this update was the introduction of UUID v7.
UUID v7 solves the B-Tree fragmentation problem by replacing the first 48 bits of randomness with a Unix Epoch timestamp in milliseconds.
|--- 48 bits (Unix Timestamp ms) ---|-- 4b (ver) --|-- 12b (seq) --|-- 2b (var) --|--- 62 bits (Random) ---|
- Why it fixes the database: Because the first 6 bytes represent the current time, UUID v7 values generated closely together are naturally sequential. They append to the right edge of the B-Tree, eliminating page splits.
- Cryptographic Safety: It retains up to 74 bits of cryptographically secure randomness, ensuring collision resistance.
- Drop-In Compatibility: At exactly 128 bits, UUID v7 fits perfectly into native
UUIDcolumn types in PostgreSQL and MySQL without schema changes.
3. ULID: The Developer-Friendly Alternative
Before UUID v7 was standardized, developers created ULID (Universally Unique Lexicographically Sortable Identifier) to solve the exact same problem.
Like UUID v7, a ULID combines a 48-bit timestamp with 80 bits of randomness. The primary difference lies in its textual representation. While UUIDs are 36-character hexadecimal strings with hyphens, ULIDs are encoded using Crockford's Base32, resulting in a compact 26-character string (e.g., 01HT7M2V2ZFA6B37951S43H7MD).
Advantages and Disadvantages of ULID
Advantages: ULIDs are URL-safe, case-insensitive, and double-click selectable in a terminal (since they lack hyphens).
Disadvantages: Because it is not an official IETF standard, databases lack a native ULID column type. Storing it as a VARCHAR(26) consumes more disk space and RAM than a native 16-byte binary UUID.
4. Architectural Comparison Matrix
| Feature | UUID v4 | UUID v7 | ULID |
|---|---|---|---|
| String Length | 36 chars (hyphens) | 36 chars (hyphens) | 26 chars (Base32) |
| B-Tree Friendly? | ❌ No (Random) | ✅ Yes (Append) | ✅ Yes (Append) |
| Native DB Column | ✅ Yes (UUID) | ✅ Yes (UUID) | ❌ No (VARCHAR) |
| Best Use Case | Secret API tokens | Primary Keys | NoSQL / Frontend |
5. Production Guidelines: Which Should You Use?
If you are starting a new project on a relational database in 2026, UUID v7 is the definitive standard. It provides the decentralized generation of v4, the insert speed of auto-increment integers, and native 16-byte storage efficiency.
ULID remains an excellent choice for NoSQL databases (like DynamoDB), S3 object storage keys, and frontend state management where URL ergonomics matter more than byte-level storage efficiency.
When to Keep Using UUID v4: Because UUID v7 and ULID embed a public Unix timestamp, anyone inspecting the ID can reverse-engineer the exact millisecond the record was created. For security-sensitive contexts—such as password reset links, public session IDs, and OAuth tokens—you must generate UUID v4 to ensure the token remains mathematically unguessable and leaks no temporal metadata.
Frequently Asked Questions
Can I migrate an existing database from UUID v4 to UUID v7?
Yes. Because both formats are 128-bit values and fit into standard UUID column types, you can update your application code to generate UUID v7 for all new records. Existing UUID v4 records will remain intact, and the database index will simply append new v7 records sequentially going forward, gradually improving performance.
How do you store ULIDs efficiently in PostgreSQL or MySQL?
If you store ULIDs as VARCHAR(26), you waste 10 bytes per row compared to binary storage, which bloats indexes. For maximum performance, developers often write a custom database function to cast the 26-character Base32 string into a 16-byte UUID or BYTEA type before inserting it into the database.
Do PostgreSQL and MySQL natively generate UUID v7?
Native database function support is rolling out in newer engine versions following the official RFC 9562 publication (e.g., PostgreSQL 17 introduces uuid_generate_v7()). For older versions, applications generally generate the UUID in the backend runtime (Node.js, Python, Java) and pass the ID in the SQL INSERT statement.
Does UUID v7 guarantee uniqueness in the exact same millisecond?
Yes. If a single microservice generates 10,000 UUID v7s within the same millisecond, the 48-bit timestamp remains identical, but the internal sequence counters and 74 bits of cryptographic randomness ensure no two identifiers collide.
Is ULID an official standard like UUID?
No. ULID is an open-source specification created by the developer community. While widely adopted and stable, it has not been formally standardized by the IETF. This is exactly why UUID v7 was created—to bring the performance benefits of ULID into an officially recognized RFC framework.