Why UUID v7 Beats UUID v4 as a Clustered Index – Performance Test Results

Background

UUID (Universally Unique Identifier) is a 128‑bit identifier usually shown as 32 hexadecimal characters in an 8‑4‑4‑4‑12 format. UUID v4 is generated by a random or pseudo‑random number generator, giving a huge key space (≈3.26×10¹⁶) and a collision probability below 0.01 %. UUID v7 also uses 128 bits but embeds a millisecond‑resolution Unix timestamp in the first 48 bits, making it time‑ordered while the remaining 74 bits are random.

Advantages and Disadvantages of UUIDs vs Sequential IDs

Advantages

Uniqueness : Extremely low conflict probability allows IDs to be generated independently without a central coordinator.

Security : Hides record creation order, preventing malicious inference of data patterns.

Disadvantages

Storage overhead : UUID occupies 16 bytes versus 4 bytes for INT or 8 bytes for BIGINT.

Input difficulty : Manual entry is cumbersome due to length and randomness.

Query efficiency : Larger row size reduces the number of rows per page, increasing I/O and slowing queries.

Index fragmentation : Random UUID v4 keys can cause frequent page splits in clustered indexes.

Experiment Setup

The experiment uses MySQL (default clustered index), Docker, Node.js and Go. A docker‑compose file launches MySQL with a persistent volume that is removed after each run. The test inserts 1 million rows with UUID v4, then repeats with UUID v7, and finally with an integer primary key.

Table chat_messages contains id, chat_id, sender_id, message and created_at. The id column type is INT, BINARY(16) for UUID v4, or BINARY(16) for UUID v7.

Procedure

Run docker‑compose up.

Connect to MySQL.

Create chat_messages with UUID v4 column.

Insert 1 M rows one‑by‑one, recording the elapsed time.

Stop Docker and delete the volume.

Wait 1 second to let the system free memory.

Create chat_messages with UUID v7 column.

Insert 1 M rows, recording the time.

Stop Docker and delete the volume.

Wait 1 second.

Create chat_messages with integer INT primary key.

Insert 1 M rows, recording the time.

Stop Docker and delete the volume.

Wait 1 second.

Results

Single‑Thread Insertion (Node.js)

UUID v4: 24,345,338.406 ms

UUID v7: 23,579,840.357 ms

INT: 23,678,315.195 ms

UUID v4 is about 3.24 % slower than UUID v7.

Go Multi‑Thread Insertion (7 threads, 5 M rows)

UUID v4: 20,634,873.510 ms

UUID v7: 16,750,775.022 ms

INT: 164,567,295.364 ms

UUID v4 is 23.18 % slower than UUID v7 and 25.30 % slower than INT in this scenario.

Go Multi‑Thread Insertion (single‑thread, 2 M rows)

UUID v4: 263,207,709.854 ms

UUID v7: 255,926,080.539 ms

INT: 257,598,898.253 ms

UUID v4 is 2.84 % slower than UUID v7.

Analysis

Because UUID v7 embeds a timestamp, its values are almost monotonic. When inserted into a clustered index, new keys are appended to the leaf pages, avoiding random page splits and reducing buffer‑pool churn. UUID v4’s randomness leads to poor locality: new keys may need to be placed before existing ones, causing frequent page reorganisations, higher I/O, and more pressure on the buffer pool.

The buffer pool caches data pages and newly created pages. Random UUID v4 inserts scatter records across many pages, forcing the engine to load and evict pages constantly. Ordered UUID v7 (or sequential INT) keeps inserts localized, allowing the buffer pool to retain hot pages longer and reducing disk writes.

Index layout diagrams (shown in the original images) illustrate how B+‑tree leaf pages split when unordered keys are inserted, whereas ordered keys keep pages densely packed.

Why Serial (INT) IDs Still Outperform UUIDs

In MySQL (16 KB pages) and PostgreSQL (8 KB pages), an INT row occupies roughly 271 bytes, while a UUID row occupies about 307 bytes. Fewer rows per page mean fewer page reads/writes, so integer keys generally achieve higher throughput despite the same ordering advantage that UUID v7 provides.

Future Work

Rust performance testing to compare against the existing Node.js/Go stack.

Measuring B+‑tree index size for UUID v4, UUID v7 and INT to quantify storage impact.

Implementing a database connection pool to improve resource utilization.

References

https://www.youtube.com/watch?v=f53-Iw_5ucA

https://www.intl-spectrum.com/Article/r848/IS_UUID_V4_UUID_V4_Random_Generation

https://towardsdatascience.com/are-uuids-really-unique-57eb80fc2a87

https://datatracker.ietf.org/doc/html/rfc4122

https://blog.bhanunadar.com/pros-and-cons-of-using-uuid-as-primary-key-in-postgres/

https://www.ietf.org/archive/id/draft-peabody-dispatch-new-uuid-format-04.html#timestamp_granularity

https://www.sqlservercentral.com/articles/how-bad-are-bad-page-splits

https://buildkite.com/blog/goodbye-integers-hello-uuids