Why UUIDv7 Is the New Go-To Primary Key for Distributed Databases

When choosing a distributed primary key, many think of UUIDs, especially the random v4, but they overlook serious performance issues. Traditional UUIDs suffer from unordered insertion, causing B+Tree index fragmentation, larger index size, higher I/O, and slower range queries.

Drawbacks of Random UUIDs

Indexes (especially B+Tree) work best with sequential inserts.

Random placement forces frequent node splits, degrading write throughput.

Disrupts clustered index order (e.g., InnoDB), increasing disk I/O.

Range queries and sorting become inefficient.

Storage cost is roughly double that of a 64‑bit auto‑increment integer.

UUIDv7’s Core Innovation: Time‑Ordered Architecture

UUIDv7 embeds a 48‑bit Unix millisecond timestamp in the most significant bits, followed by a 4‑bit version field, a 2‑bit variant, and 74 bits of random data, yielding a globally monotonic identifier.

┌─────────────────────┬─────┬─────┬─────────────────────────────┐
│   Unix millisecond  │ Ver │ Var │          random bits         │
│      (48 bits)      │ (4) │ (2) │          (74 bits)           │
└─────────────────────┴─────┴─────┴─────────────────────────────┘

Design Highlights

High‑precision time prefix (48 bits): Millisecond‑level Unix time ensures strict temporal ordering, assuming synchronized NTP clocks.

Trailing random bits (74 bits): Preserve distributed uniqueness without exposing MAC addresses.

How Ordering Improves Performance

B+Tree optimization: New UUIDv7 values are always larger than previous ones, so inserts append to the index tail, avoiding mid‑tree splits.

Buffer‑pool friendliness: Sequential writes concentrate new rows in a few pages; when a page fills, a new page is simply allocated, reducing page churn and I/O.

Faster range queries: The time component lets WHERE id > '2025‑06‑01' be translated into a timestamp range, dramatically shrinking scan windows.

Comparison with Other UUID Versions

A visual chart (image omitted) contrasts UUIDv7 with v1, v4, etc., highlighting its ordered nature and smaller collision risk.

Practical Usage: Generating and Storing UUIDv7

Java example using com.github.f4b6a3.uuid.UuidCreator:

import com.github.f4b6a3.uuid.UuidCreator;

public class UuidUtils {
    public static UUID generateUuidV7() {
        return UuidCreator.getTimeOrdered(); // generate UUIDv7
    }
    // Convert to binary for DB storage
    public static byte[] toBytes(UUID uuid) {
        ByteBuffer bb = ByteBuffer.wrap(new byte[16]);
        bb.putLong(uuid.getMostSignificantBits());
        bb.putLong(uuid.getLeastSignificantBits());
        return bb.array();
    }
    // Convert back from binary
    public static UUID fromBytes(byte[] bytes) {
        ByteBuffer bb = ByteBuffer.wrap(bytes);
        return new UUID(bb.getLong(), bb.getLong());
    }
}

// Usage
UUID id = UuidUtils.generateUuidV7();

SQL table definition storing the UUID as binary:

CREATE TABLE users (
    id BINARY(16) PRIMARY KEY, -- binary UUID
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

Insert and query example (JDBC):

// Insert
UUID userId = UuidUtils.generateUuidV7();
String sql = "INSERT INTO users (id, name) VALUES (?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setBytes(1, UuidUtils.toBytes(userId));
    ps.setString(2, "John Doe");
    ps.executeUpdate();
}
// Query
String query = "SELECT * FROM users WHERE id = ?";
try (PreparedStatement ps = conn.prepareStatement(query)) {
    ps.setBytes(1, UuidUtils.toBytes(userId));
    ResultSet rs = ps.executeQuery();
    while (rs.next()) {
        UUID id = UuidUtils.fromBytes(rs.getBytes("id"));
        String name = rs.getString("name");
    }
}

FAQ: UUIDv7 Reliability

Will UUIDv7 collide? The 48‑bit timestamp plus 74‑bit random space yields 2^122 combinations (~5.3×10^36). Even generating 1 billion IDs per second, the collision probability stays far below 10⁻¹⁵.

What about clock rollback? If the system clock moves backward and many IDs are generated within the same millisecond, a collision is theoretically possible but practically negligible. Mitigations include redundant time sources (GPS, atomic clocks, multi‑NTP), drift monitoring, and avoiding VM clock drift.

How to handle rollback in generation? Detect rollback and continue using the last timestamp until it surpasses the rollback window, or temporarily increase random bits to lower collision risk.

Overall, UUIDv7 offers a near‑unique, time‑ordered identifier that aligns with database indexing strategies, improves write performance, and simplifies range queries, making it a strong candidate for primary keys in distributed systems.