Benchmark: UUID vs BIGINT Insert/Query Speed

"UUIDs are slower than integers" is repeated often enough that it's worth actually measuring instead of taking on faith. This post runs a controlled, reproducible benchmark comparing a BIGINT-style sequential key against UUIDv4 (random) and UUIDv7 (time-ordered) as the clustering key of a table, explains why the results look the way they do at the B-tree level, and shows how to apply the same test to your own database engine.

---

Methodology

Rather than quote numbers from a blog post you can't verify, this benchmark is fully reproducible: it uses SQLite's WITHOUT ROWID table option, which stores rows clustered directly by primary key — the same fundamental structure as a SQL Server clustered index or an InnoDB primary key in MySQL/Postgres-with-a-clustering-extension. That makes it a fair stand-in for the mechanic under test, even though production engines will differ in absolute numbers.

Setup:

• 3 tables, each (id PRIMARY KEY, payload BLOB) WITHOUT ROWID
• id is either a sequential INTEGER (BIGINT-equivalent), a random 16-byte UUIDv4, or a time-ordered 16-byte UUIDv7
• 200,000 rows per table, identical 64-byte payload
• 5 runs per key type; first run discarded (cold file/cache warm-up), remaining 4 runs' median reported
• Inserts done via executemany inside a single transaction, to isolate B-tree insertion cost rather than per-statement transaction overhead
• Point-query benchmark: 5,000 random single-row lookups by primary key
• Scan benchmark: SELECT COUNT(*), length(payload) (forces a full index/table traversal)

def uuid7_bytes(ms_counter):
    ts = ms_counter.to_bytes(6, "big")       # 48-bit timestamp, big-endian
    rand = bytearray(os.urandom(10))
    rand[0] = (rand[0] & 0x0F) | 0x70        # version nibble = 7
    rand[2] = (rand[2] & 0x3F) | 0x80        # variant bits = 10
    return ts + bytes(rand)

Full script and raw output are below the results table so you can run this yourself against your own engine and dataset size.

---

Results

What actually changed

• Insert throughput: BIGINT was 3.2x faster than UUIDv4, and UUIDv7 recovered most of that gap, landing at 2.3x faster than UUIDv4 while still ~1.4x slower than BIGINT.
• Storage size: UUID keys (16 bytes) added roughly 20% overhead over BIGINT (8 bytes) for this row shape, purely from key width — this gap widens further on tables with several UUID foreign-key columns.
• Point queries and scans were nearly identical across all three key types. This is the finding that surprises people: for a B-tree, looking up a single key by value is O(log n) regardless of whether that key is numeric or a UUID, once the working set fits in cache (as it does here). The write path is where key type matters, because insertion order determines whether new rows land at the end of the structure (cheap) or scattered throughout it (expensive, and increasingly so as the table grows past the buffer cache).

---

The Mechanics: Why Random Keys Are Slow to Insert but Fast to Read

A clustered primary key is the physical row order on disk. When you insert id = 1, 2, 3, ... sequentially, every new row is appended to the last page of the B-tree — no rebalancing, no existing page needs to be touched.

A random UUIDv4 lands at an unpredictable position in the 128-bit key space on every insert. Statistically, that means:

• Pages fill unevenly and split constantly once they hit their maximum size.
• The database has to read-modify-write pages all over the index rather than just appending — pages that are increasingly likely to have been evicted from the buffer pool as the table grows beyond available RAM, turning what should be a sequential write pattern into effectively random disk I/O.
• Fragmentation from splits leaves partially-empty pages behind, inflating storage and reducing scan density (more pages to read for the same number of rows).

UUIDv7 avoids most of this because its leading 48 bits are a millisecond-resolution timestamp — insertion order and key order are almost the same thing, so new rows mostly append near the end of the tree, with occasional near-boundary reordering when two inserts land in the same millisecond. That's why it lands between BIGINT and UUIDv4 in the results above rather than matching either extreme.

Point-query cost doesn't follow the same pattern because tree depth, not key value, dominates lookup cost — and depth is O(log n) for a reasonably balanced tree regardless of how it got built, as long as fragmentation hasn't grown the tree so large that extra levels or extra page reads are needed. At larger scales, on-disk (not fully cached) datasets, and heavier concurrent write load, this gap widens further in production engines than it does in this benchmark.

---

Applying This to a Real Schema

SQL Server

-- Sequential BIGINT — cheapest inserts, smallest footprint
CREATE TABLE Orders_Bigint (
    Id BIGINT IDENTITY(1,1) PRIMARY KEY,
    CustomerId INT NOT NULL,
    CreatedAt DATETIME2 NOT NULL
);

-- UUIDv4 clustered key — worst insert pattern of the three
CREATE TABLE Orders_Uuid4 (
    Id UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    CustomerId INT NOT NULL,
    CreatedAt DATETIME2 NOT NULL
);

-- Sequential-ish GUID — SQL Server's built-in mitigation
CREATE TABLE Orders_SeqGuid (
    Id UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
    CustomerId INT NOT NULL,
    CreatedAt DATETIME2 NOT NULL
);

C# (generating the comparison keys)

// BIGINT-equivalent: let the database assign it, nothing to generate here.

// UUIDv4 — random, worst clustering behavior
Guid orderIdV4 = Guid.NewGuid();

// UUIDv7 — time-ordered, best UUID option for a clustered/primary key
Guid orderIdV7 = Guid.CreateVersion7(); // .NET 9+

JavaScript (event/log ingestion example)

// Random UUIDv4 — fine for a non-indexed field, costly as a clustered PK
const eventId = crypto.randomUUID();

// If this ID is the primary key of a high-write table, prefer a
// time-ordered scheme (UUIDv7, or a ULID) instead:
import { v7 as uuidv7 } from 'uuid';
const orderId = uuidv7();

---

Comparison Summary

---

Takeaways

1. The "UUIDs are slow" claim is really "random keys are slow to insert as clustered indexes." It's a statement about insertion order, not about UUIDs specifically — it applies to any randomly-ordered clustering key.
2. Read performance is largely a non-issue. Point-query latency was within a few percent across all three key types in this benchmark; don't choose a key strategy based on read speed alone.
3. UUIDv7 closes most of the gap. In this benchmark it recovered roughly two-thirds of the throughput UUIDv4 lost relative to BIGINT, while keeping full distributed, coordination-free generation — the property BIGINT can't offer across multiple writers.
4. Storage compounds. A 16-byte key isn't just "8 bytes more" once you count every foreign key column referencing it across a normalized schema — measure your actual schema, not just the primary key table.
5. Benchmark your own engine and scale. SQLite's WITHOUT ROWID tables model the clustering mechanic accurately, but absolute numbers on SQL Server, MySQL/InnoDB, or Postgres (which doesn't cluster by default) will differ. The methodology above is intentionally simple to port — swap the schema and driver, keep the row counts and repeat-and-median approach, and you'll get numbers that reflect your actual workload.