Why UUIDv7 Outperforms Traditional UUIDs for Database Primary Keys

Introduction

When we talk about distributed primary keys we often think of UUIDs, especially UUIDv4, but they have serious drawbacks. Recently UUIDv7 has emerged as a time‑ordered alternative.

1. Drawbacks of traditional UUIDs

Disorder (main issue) : Randomness breaks B+Tree index insertion order, causing index splits, larger storage, slower range queries.

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

Random placement forces frequent node splits and rebalancing, reducing write performance.

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

Range queries and sorting become inefficient.

Large storage footprint :

Uses roughly twice the space of an auto‑increment BIGINT.

Larger indexes consume more memory/disk, lowering cache efficiency and query speed.

2. UUIDv7 core breakthrough: time‑ordered design

UUIDv7 embeds a Unix millisecond timestamp in the most‑significant 48 bits, making the identifier globally monotonic. Its 128‑bit layout is:

<code>0               1               2               3
0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1
┌─────────────────────┬─────┬─────┬─────────────────────────────┐
│   Unix ms timestamp │ Ver │ Var │        Random bits          │
│        (48 bits)    │ (4) │ (2) │          (74 bits)           │
└─────────────────────┴─────┴─────┴─────────────────────────────┘</code>

Design key points :

High‑precision time prefix (48 bits) : Millisecond‑level Unix time ensures strict temporal ordering (requires NTP sync).

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

3. How ordering solves performance problems

B+Tree index optimization : New UUIDv7 values are always larger, so they are appended to the index tail, avoiding middle‑node splits.

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

Range query acceleration : Time‑ordered IDs allow converting conditions like

WHERE id > '2025‑06‑01'

into timestamp range filters, dramatically shrinking scan ranges.

4. UUIDv7 vs other versions

5. Practical usage: generating and storing UUIDv7

Java example using

UuidCreator

:

<code>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 from binary
    public static UUID fromBytes(byte[] bytes) {
        ByteBuffer bb = ByteBuffer.wrap(bytes);
        return new UUID(bb.getLong(), bb.getLong());
    }
}
</code>

SQL table definition (binary UUID primary key):

<code>CREATE TABLE users (
    id BINARY(16) PRIMARY KEY, -- store UUID as binary
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);
</code>

Insert and query example (using the utility methods above):

<code>// 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");
    }
}
</code>

6. FAQ

5.1 Will UUIDv7 ever collide?

Collision probability is astronomically low. UUIDv7 combines a 48‑bit millisecond timestamp (lasting ~8.5 million years) with 74 random bits, yielding 2^122 possible values (~5.3×10^36). Even at a billion IDs per second, the chance of duplication is < 10⁻¹⁵.

Only extreme clock‑rollback scenarios that generate >2^74 IDs within the same millisecond could cause a clash, which is practically impossible.

5.2 What is clock rollback and how does it affect UUIDv7?

Causes: NTP sync errors, power glitches, VM host time adjustments.

Impact: New UUIDv7 timestamps may be smaller than previous ones, increasing collision risk if random part also repeats.

Mitigation:

Use redundant time sources (GPS, atomic clocks, multi‑level NTP).

Monitor drift with algorithms such as Kalman filtering.

Avoid VM clock drift by preferring physical hosts.

Generation‑time fault tolerance:

Timestamp continuation: When rollback is detected, keep using the last known timestamp until it surpasses the rollback window.

Random‑bit expansion: Allocate extra random bits during rollback to further reduce collision probability.