Stop Using UUIDv4 as Primary Keys: Switch to UUIDv7

Have you ever designed a new database table and reflexively typed id: uuidv4() without giving it a second thought? I've been there. For years, UUID version 4 (UUIDv4) has been the unquestioned golden standard for distributed systems. It guarantees global uniqueness, effectively hides your actual data volume from competitors (unlike auto-incrementing integers), and allows your front-end applications to generate IDs before even making a network request.

But here is the harsh reality: if you are using UUIDv4 as the primary key in a relational database like PostgreSQL or MySQL, you might be quietly suffocating your write performance.

Today, let's look at why UUIDv4 is a silent performance killer for databases, and why modern engineering teams are making the switch to time-sorted alternatives like UUIDv7 and ULID.

The Auto-Increment vs. UUID Dilemma

Historically, the most common way to uniquely identify a row was an auto-incrementing integer (SERIAL in PostgreSQL or AUTO_INCREMENT in MySQL). These are incredibly fast and efficient. Because they increase sequentially (1, 2, 3...), they are very friendly to the database's internal storage mechanisms.

However, integers have massive downsides in modern applications. If your user ID is 500, a malicious actor can guess that user 501 exists and attempt an Insecure Direct Object Reference (IDOR) attack. Furthermore, anyone can see your exact growth rate by simply creating an account today and another one next week. Lastly, in a microservices architecture, generating sequential integers across multiple independent databases without collisions is a distributed systems nightmare.

Enter UUIDv4. A 128-bit string of pure randomness. It solves all the distributed generation and security problems. But it introduces a completely new architectural flaw at the storage layer.

The B-Tree Nightmare and Index Fragmentation

To understand why UUIDv4 is problematic, we need to talk about B-Trees (Balanced Trees). Most relational databases use B-Tree structures to store their indexes. In MySQL's InnoDB engine, the primary key is the clustered index, meaning the actual row data is stored alongside the index tree.

When you use a sequential primary key like an integer, new records are neatly appended to the far right side of the B-Tree. The database just allocates new memory pages as needed, filling them up efficiently. The process is smooth and requires minimal disk I/O.

UUIDv4, by definition, is entirely random. When you insert a million rows with UUIDv4 primary keys, the database has to constantly rebalance its B-Tree. The first record might be inserted on the left branch, the second on the far right, and the third right in the middle.

This scattershot insertion pattern leads to a catastrophic phenomenon known as a page split.

Databases store data in fixed-size blocks called pages (typically 8KB or 16KB). When a page is full and a new random UUID needs to be inserted right into the middle of it, the database is forced to split that page into two. It has to move half of the data to a newly allocated page just to make room.

This process causes massive Write Amplification. Your database is doing significantly more disk I/O than necessary. Over time, these page splits result in "Index Bloat"—pages that are only partially full, wasting precious RAM and disk space. Once your table grows large enough that the index can no longer fit in RAM, every random insert requires reading pages from the physical disk, causing your write performance to fall off a cliff.

The Solution: Time-Sorted Identifiers

What we really need is an identifier that offers the best of both worlds: the collision resistance and distributed nature of a UUID, combined with the sequential, database-friendly sorting of an auto-incrementing integer.

Enter Time-Sorted Identifiers.

By encoding a timestamp at the very beginning of the ID and filling the rest with randomness, we get an ID that is globally unique but naturally sorts chronologically.

UUIDv7: The New Official Standard

Recently formalized in RFC 9562, UUID version 7 is exactly what the industry has been waiting for. It was specifically designed to solve the database locality issues created by UUIDv4.

The structure of a 128-bit UUIDv7 is straightforward:

• 48 bits: A Unix timestamp in milliseconds.
• 74 bits: Cryptographically secure random data.
• 6 bits: Version and variant indicators.

Because the most significant bits represent the time of creation, UUIDv7s generated in the same millisecond are random, but those generated across different milliseconds are strictly sequential. When inserted into a database, they naturally append to the right side of the B-Tree. Page splits are drastically reduced, and write performance remains fast and predictable, even at scale.

The greatest advantage of UUIDv7 is its backward compatibility. It has the exact same 128-bit format as UUIDv4 (e.g., 018e6b12-9c9c-7000-a123-456789abcdef). This means you can drop it directly into your existing PostgreSQL uuid columns without running any complex schema migrations!

Here is how easily you can implement it in Node.js:

import { v7 as uuidv7 } from 'uuid';

const createNewUser = async (email: string) => {
  const user = {
    id: uuidv7(), // Generates a time-sorted UUID
    email,
    createdAt: new Date()
  };
  
  await db.users.insert(user);
  return user;
};

ULID: The URL-Friendly Alternative

Before UUIDv7 became an official standard, ULID (Universally Unique Lexicographically Sortable Identifier) was the preferred time-sorted solution.

Like UUIDv7, a ULID combines a 48-bit timestamp with 80 bits of randomness. However, ULIDs are encoded using a customized Base32 alphabet.

An example ULID looks like this: 01ARZ3NDEKTSV4RRFFQ69G5FAV.

Pros of ULID:

• They are shorter (26 characters compared to the 36 characters of a hyphenated UUID).
• They are URL-safe and don't contain hyphens, making them incredibly easy to double-click and copy-paste.
• They are case-insensitive.

Cons of ULID:

• Relational databases typically do not have a native ulid data type. You often have to store them as VARCHAR(26) or CHAR(26), which consumes more space and memory than a native 16-byte uuid type in Postgres.

The Verdict

If you are starting a new project or adding new tables today, my recommendation is clear: Stop using UUIDv4.

1. If your database has a native UUID type (like PostgreSQL): Use UUIDv7. It is an official IETF standard, it requires zero schema changes if you are already using UUID columns, and it completely resolves B-Tree fragmentation.
2. If you prioritize aesthetics and URL sharing: ULID is a fantastic choice, especially if you are using NoSQL databases or don't mind storing IDs as strings.
3. If you have a legacy system full of UUIDv4: You don't need to rewrite history. Because v4 and v7 share the exact same database footprint, you can simply update your application code to generate UUIDv7 for new records. They will coexist perfectly in your database, and your new inserts will immediately benefit from better performance.

It is time to stop making your database work harder than it has to. Make the switch to time-sorted identifiers, and your future self—along with your infrastructure bill—will thank you.