The Great Index Fragmentation Debate: Do UUIDs Really Kill Performance?

"Never use UUIDs as a primary key, they destroy index performance" is one of the most repeated pieces of database folklore in software engineering — stated often, benchmarked rarely, and almost never qualified with how much, under what conditions, or compared to what. This post looks at the actual mechanism behind the claim, measures it directly at the B-tree page level rather than just timing inserts, and works through when the effect is real, when it's overstated, and what actually mitigates it.

---

The Claim, Precisely Stated

The concern isn't about UUIDs as a data type — it's specifically about using a randomly-ordered value as a clustered index key (the row-storage order itself, not just a lookup index). The claim has three parts:

1. Random insertion order forces the B-tree to insert into the middle of existing pages rather than appending to the end.
2. When a page fills up mid-tree, it splits — the engine allocates a new page and redistributes roughly half the rows into it, typically leaving both resulting pages under-full.
3. Repeated splits over the table's lifetime leave a large fraction of partially empty pages, which means more total pages for the same row count — more disk I/O per query, worse cache density, and larger index files.

Each of those three steps is mechanically true. What's contested is the magnitude — and that's where the debate usually goes wrong, because "UUIDs are slow" gets stated as if it's a fixed, universal cost rather than a variable one that depends heavily on engine, page size, fill factor, and dataset-to-cache ratio.

---

Measuring It Directly: Page Fill Ratio, Not Just Insert Time

Insert timing (covered in our companion benchmark post) shows the symptom — slower writes. To see the actual mechanism, you need to look at the B-tree pages themselves: how many pages does the index actually use, and how full is each one?

SQLite exposes this directly through its dbstat virtual table, which reports real per-page statistics (payload bytes, unused bytes, page size) for every page in a database file. Using WITHOUT ROWID tables (which cluster rows physically by primary key, the same mechanic as a SQL Server clustered index or InnoDB primary key), the following measures actual leaf-page fill ratio after inserting 200,000 rows with a sequential BIGINT key vs. random UUIDv4 vs. time-ordered UUIDv7:

cur.execute("""
    SELECT pagetype, count(*), sum(payload), sum(unused), sum(pgsize)
    FROM dbstat('main')
    WHERE name = 't' AND pagetype = 'leaf'
    GROUP BY pagetype
""")
_, leaf_pages, total_payload, total_unused, total_size = cur.fetchone()

fill_ratio = total_payload / total_size * 100
ideal_pages = -(-total_payload // 4096)          # best-case page packing
overhead_pct = (leaf_pages / ideal_pages - 1) * 100

Measured results (median of 3 runs, 200,000 rows, 4KB pages)

The uncomfortable-for-both-sides result

The fragmentation overhead percentage barely moved — 17.99% for sequential inserts vs. 18.93% for fully random ones, less than a single percentage point. That's real, measurable, and directional (random is worse), but it is nowhere near the catastrophic difference the strongest version of the "UUIDs kill performance" claim implies. Most of the size difference between BIGINT and the UUID variants here is simply the 8 extra bytes per key, not fragmentation — UUIDv4 and UUIDv7 produced identical page overhead in this test, despite one being random and the other time-ordered, which tells you something important: at this scale, key width mattered more than key order for the storage footprint.

This doesn't mean the folklore is wrong — it means the folklore is usually describing a different scenario than "200k rows, everything fits in cache, one big batched transaction." The effect scales with factors this test deliberately controlled away.

---

What the Simple Benchmark Doesn't Capture

The modest result above is honest, but it's also specific to conditions that don't match every production system. The real-world cases where fragmentation becomes a serious problem typically add one or more of these:

In other words: this benchmark's numbers are a floor, not a ceiling. A busy, long-lived, disk-bound production table with several secondary indexes will see a larger gap than what's measured here — but "larger" still needs to be benchmarked on your actual engine and workload, not assumed.

---

Mitigations, Ranked by Effectiveness

1. Use a time-ordered UUID (UUIDv7) instead of random (UUIDv4)

// .NET 9+
Guid orderId = Guid.CreateVersion7(); // mostly-ascending, splits mostly append rather than scatter

This is the highest-leverage fix available today: it keeps UUID's coordination-free, distributed generation while restoring most of the insertion locality that BIGINT enjoys.

2. Use engine-native sequential GUID generation (SQL Server)

CREATE TABLE Orders (
    Id UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
    CustomerId INT NOT NULL,
    CreatedAt DATETIME2 NOT NULL
);

NEWSEQUENTIALID() is SQL-Server-specific and not safely predictable client-side (see the coordination pitfalls covered in our real-world bugs post), but it directly solves the clustering problem for single-instance workloads.

3. Tune fill factor to leave split headroom

-- SQL Server: leave 20% free space per page up front, reducing split frequency
ALTER INDEX PK_Orders ON Orders REBUILD WITH (FILLFACTOR = 80);

-- Postgres: similar concept, though Postgres doesn't cluster by default (see below)
ALTER TABLE orders SET (fillfactor = 80);

A lower fill factor trades some space and read density up front for fewer splits later — it doesn't eliminate the mechanism, but it delays and dampens it.

4. Don't cluster on the UUID at all

-- Cluster on an auto-incrementing surrogate; keep the UUID as a
-- non-clustered unique column for external-facing identifiers
CREATE TABLE Orders (
    InternalId BIGINT IDENTITY(1,1) PRIMARY KEY,       -- clustered, sequential
    PublicId UNIQUEIDENTIFIER DEFAULT NEWID() UNIQUE,     -- non-clustered, random is fine here
    CustomerId INT NOT NULL
);

This sidesteps the entire debate: the clustering key stays sequential, and the UUID becomes "just another indexed column," where random order has a far smaller performance impact than it does as the clustering key (non-clustered index fragmentation is real too, but doesn't affect the physical row storage order the way clustered fragmentation does).

5. Periodic maintenance

-- Rebuild to reclaim fragmented space and restore fill ratio
ALTER INDEX PK_Orders ON Orders REBUILD;

-- Or reorganize for a lighter-weight, online-friendly defragmentation pass
ALTER INDEX PK_Orders ON Orders REORGANIZE;

This doesn't prevent fragmentation, but it resets it — appropriate for tables where regular maintenance windows are already part of operations.

---

A Note on Postgres

Postgres deserves a specific callout: it does not maintain a clustered table order by default the way SQL Server or InnoDB do. CLUSTER is a one-time, manual operation, not a continuously maintained property — meaning the entire premise of this debate (clustering key insertion order affecting physical page layout) applies differently there. Postgres primary keys are just a regular B-tree index over heap-stored rows in whatever order they were physically inserted; a random UUID primary key fragments the index, not necessarily the table heap, which changes the cost/benefit calculation for the mitigations above.

---

So — Do UUIDs Really Kill Performance?

Based on the mechanism and the measured data:

• The mechanism is real and well-understood: random clustering keys produce lower page fill ratios and more total pages than sequential ones. This isn't in dispute.
• The magnitude is workload-dependent, not fixed. In a cache-resident, batch-inserted, moderately-sized dataset, the measured overhead difference was under 1 percentage point — nowhere near "kills performance." In a large, disk-bound, high-churn table with multiple secondary indexes, the same mechanism compounds substantially further.
• "Kill performance" is the wrong framing; "adds a cost that's cheap to avoid" is the right one. UUIDv7, non-clustered UUID placement, and routine fill-factor tuning each address the mechanism directly, at low implementation cost, without giving up UUID's actual value proposition (coordination-free, distributed generation).

The honest conclusion is neither "UUIDs are fine, don't worry about it" nor "never use UUIDs as keys" — it's that the specific combination of random ordering and clustering is the actual cost driver, and both halves of that combination are independently avoidable without giving up UUIDs altogether.