Understanding UUIDv7: Time‑Ordered IDs for Database Primary Keys

Introduction

Hello, I am Tianlu. When we talk about distributed primary‑key IDs we often think of UUIDs, but many overlook their drawbacks. Recently, leadership suggested reconsidering UUIDs as primary keys because of the emergence of UUIDv7.

1. Drawbacks of Traditional UUIDs

Traditional UUIDs (especially v4) are completely random, which brings several problems:

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

Random UUIDs cause inserts at unpredictable locations, leading to frequent index‑tree splits and re‑organization, dramatically reducing write performance.

They break clustered index (e.g., InnoDB) physical order, increasing disk I/O.

Range queries and sorting become inefficient.

Storage overhead : UUIDs occupy twice the space of a 64‑bit auto‑increment integer, making indexes larger, consuming more memory/disk, and slowing queries.

2. UUIDv7 Core Breakthrough: Time‑Ordered Design

UUIDv7 embeds a timestamp in the most‑significant bits, achieving a globally monotonic increasing identifier. 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毫秒时间戳    │ Ver │Var  │          随机位            │
│      (48位)         │(4) │(2) │          (74位)            │
└─────────────────────┴─────┴─────┴─────────────────────────────┘</code>

Design key points :

48‑bit high‑precision timestamp : millisecond‑level Unix time ensures strict time‑based monotonicity (requires NTP synchronization).

74‑bit random suffix : guarantees distributed uniqueness and avoids MAC‑address leakage of UUIDv1.

3. How Ordering Solves Performance Issues

B+Tree index optimisation : New UUIDv7 values are always greater than previous ones, so they are appended to the index tail, preventing mid‑tree splits.

Buffer‑pool friendliness : Sequential writes keep new rows on a few data pages; when a page fills, the DB simply allocates a new page, reducing page eviction and disk I/O.

Range‑query acceleration : Time ordering allows queries like WHERE id > '2025-06-01' to be transformed into timestamp range filters, drastically shrinking scan ranges.

4. Practical Usage: Generating and Storing UUIDv7

Java example using the f4b6a3/uuid library:

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

SQL table definition using a binary 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:

<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>

5. Frequently Asked Questions

5.1 Will UUIDv7 ever collide?

Collision probability is extremely low: UUIDv7 consists of a 48‑bit timestamp plus 74‑bit random part, giving 2^122 (~5.3×10³⁶) possible values. Even at a generation rate of 1 billion per second, the chance of duplication is far below 10⁻¹⁵, effectively negligible.

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

Clock rollback occurs when a server’s clock moves backwards due to NTP errors, power issues, or VM host adjustments. If rollback happens within the same millisecond, newly generated UUIDv7 timestamps may be smaller than previous ones, raising collision risk.

Mitigation strategies :

Use redundant time sources (GPS, atomic clock, multiple NTP layers) to avoid single‑point failures.

Monitor clock drift with algorithms such as Kalman filtering and correct deviations in real time.

Avoid VM clock drift by preferring physical hosts.

On detection of rollback, continue using the last known timestamp until the clock catches up, or temporarily expand the random portion to lower collision probability.