UUID Primary Keys: Locality, Page Splits, and the Cost You Must Measure

Generate a unique identifier without a round trip to the database, hand it to an application server, and the insert gets slower. The identifier is 16 bytes instead of 8 and everyone assumes that is the cost.

It is not the main cost. The main cost is that a random key cannot be appended.

An LSM engine has a different bill. Memtables sort keys before flushing, so random arrival does not translate into one random disk write per insert; key distribution instead changes overlap, compression, and compaction behavior. That is LSM compaction and the write stall, and it should not be reasoned about with B-tree page-split arithmetic.

A random UUID is still a valid unique tiebreaker in a composite keyset index such as (created_at, id). The cursor follows that composite B-tree; it does not perform one independent primary-key lookup per row unless the plan needs those lookups. The pagination contract is covered in why OFFSET gets slower the deeper you scroll.

What a page split actually is

A B+ tree index stores keys in sorted order across fixed-size pages, typically 8 KB. Leaf pages are linked to each other so a range scan walks them left to right, and the data is in key order within that chain.

                        [50000 | 90000]
                       /                \
          [10 20 30]  /                  \  [95000 99000]
                      /                      \
  leaves:  [1 5 9 14 22 31 40 47]  ->  [55 61 68 77 88]  ->  [95000 99000]

Sequential keys have one useful property: they concentrate arrivals at the right edge, letting an engine apply its rightmost-page optimizations. When that leaf fills, it splits or allocates a new page according to engine-specific rules.

With random keys, each insert targets a leaf determined by the key range. If that page has free space, no split occurs. If it is full, the engine splits it. The durable penalty is a wider active set of dirty leaf pages and lower cache locality—not “one split per random insert.”

That is the whole mechanism. Random keys turn a streaming write into a scattered one, and scattered writes cannot be served from cache.

The arithmetic

Take a table with 50 million rows and a 120-byte heap tuple.

heap
  bigint primary key    8 bytes per row  ->  400 MB of key
  uuid primary key     16 bytes per row  ->  800 MB of key

primary key index, per entry (item pointer + key + heap location)
  bigint                8 + 8  + 8 = 24 bytes ->  1.2 GB
  uuid                  8 + 16 + 8 = 32 bytes ->  1.6 GB

Widening the key costs you 400 MB in the heap and 400 MB in the index. On a machine with 64 GB that is noise, and it is entirely predictable.

The fragmentation cost is not predictable, because it depends on the shape of your writes and the size of your buffer.

sequential insert
  writes concentrate on the rightmost leaf set

random insert
  writes spread across the existing leaf set;
  a split occurs only when the selected page is full

Do not turn that diagram into a fixed multiplier. Fill factor, tuple width, deduplication, deletion history, engine split policy, and rebuild history decide leaf density. Random insertion can leave a tree less dense and dirties more pages, but “one page per row” and “the index doubles” are not credible without a measurement from the actual index.

The second effect is harder to see. With sequential keys, the pages you write are the pages you just wrote, so they are already in the buffer pool. With random keys, every insert targets an unrelated page. Once the working set of active leaf pages exceeds the buffer pool, a large fraction of those inserts become a read followed by a write, and the read misses.

 same logical insert rate

 sequential   small active leaf set, likely resident
 random       broad active leaf set, more cache pressure

The actual counts come from the index size and workload. Measure buffer hits, device reads, WAL/redo bytes, leaf density, and split counters where the engine exposes them.

Postgres and MySQL break differently

This is where the advice usually goes wrong, because the storage models are not the same and the symptom appears in a different place.

InnoDB stores the row in the clustered index, and secondary-index records carry the primary-key columns. A random, wide primary key therefore affects the table’s insertion locality and also enlarges every secondary index. A secondary lookup uses that stored primary-key value to reach the clustered record; its cost depends on the access pattern and cache, not simply on the fact that the key is random.

Postgres has no clustered index. The heap is a stack of pages in physical insertion order, and a primary key is an ordinary unique B-tree of (key, tid) where the tid is a physical location. The heap does not fragment, because it is not sorted by anything.

What changes in PostgreSQL is the primary-key B-tree’s write locality and the correlation between key order and heap insertion order. A range scan by a sequential key often visits nearby heap tuples; a range scan by random UUID order follows TIDs scattered through the heap. The insert itself still places a heap tuple according to PostgreSQL’s free-space decisions and updates the B-tree; it does not insert the heap row into a UUID-sorted location.

sequential key
  index order  1  ->  2  ->  3  ->  4
  heap page    1     2     3     4      aligned

random uuid key
  index order  7f  ->  1a  ->  c3  ->  42
  heap page    1     9     3     17     scattered

Autovacuum churn comes from MVCC updates and deletes, not from UUID randomness by itself. A random UUID can make the primary-key index’s active leaf set broader and UUID-ordered range scans less heap-local. Whether that matters depends on cache size, query shape, update volume, and the number of secondary indexes. EXPLAIN (ANALYZE, BUFFERS) and pgstatindex are the evidence; a storage-model slogan is not.

Why the key was randomised in the first time

The reason to avoid a database sequence can be legitimate: an application may need an ID before opening a connection, accept offline writes, or merge independently generated records. That does not make every sequence a contention bottleneck. PostgreSQL sequences cache values and are not rolled back with transactions; benchmark the allocator before replacing it for throughput reasons.

Every reason to want UUIDs is a reason to want distributed uniqueness. Randomness is not on the list. It is a side effect of the cheapest way to get uniqueness without coordination, and it is the part that costs you.

UUIDv7 makes this explicit. It is 16 bytes like any other UUID, and it is still coordination-free, but the leading 48 bits are a Unix millisecond timestamp in big-endian order.

 UUIDv4   0f8e 4c2a 9b1d 4e77 a3f0 6c21 d95b 8e14
          no ordering whatsoever

 UUIDv7   0198 4c2a 9b1d 7a31 b0c4 2f8e 6d21 55f0
          |---------- timestamp ----------|random

UUIDv7 places a 48-bit Unix-millisecond timestamp first, so values from later milliseconds normally sort later. Within one millisecond, RFC 9562 permits random bits or optional sub-millisecond/counter schemes; plain random tails do not preserve generation order. Clock rollback and skew between nodes also need an explicit policy. The result is time-ordered locality, not a universal monotonic sequence.

RFC 9562 defines the format. .NET exposes Guid.CreateVersion7, and PostgreSQL 18 exposes uuidv7(). For other runtimes and older database versions, choose a maintained implementation and test its byte ordering in the exact database/provider combination you use.

The three options, honestly

Use a sequence. A bigint or identity column gives compact, ordered keys and database-owned allocation. It does not provide gaplessness—rollbacks and cache loss can leave holes—and it does not let an offline client mint an authoritative ID. For a database-centered write path it is often the simplest choice; verify contention rather than assuming it.

Use UUIDv7. Coordination-free, time-ordered, and 16 bytes. It usually narrows the active B-tree leaf range compared with v4. It does not guarantee strict global order across clocks or within a millisecond unless the generator adds and preserves monotonic state.

Use a provider-specific sequential GUID. This predates UUIDv7 and may arrange bytes specifically for SQL Server’s comparison order. Do not invent one by overwriting arbitrary “low bits”: UUID textual order, .NET Guid comparison, and database byte order have historically differed.

 RFC byte order        != legacy GUID field order in every API
 database comparison  != a promise made by a home-grown byte shuffle

 test: generate a burst, insert it, ORDER BY the stored column,
       and compare database order with intended creation order

Its locality and information leakage depend on the exact algorithm. If you are starting now, prefer standardized UUIDv7 where the provider and schema compare it in the intended order.

What to avoid is the fourth option that is really the third option by accident: a v4 UUID in the primary key, described internally as “we’ll make it sequential later”.

Doing it in EF Core

EF Core value generation is provider-specific. The SQL Server provider automatically generates sequential GUID values for generated primary keys; Guid.NewGuid() is v4, but ValueGeneratedOnAdd() alone does not justify a cross-provider claim about which generator will run. Inspect the provider model and generated values.

// Provider-specific: inspect the configured generator and stored ordering.
modelBuilder.Entity<Order>()
    .Property(o => o.Id)
    .ValueGeneratedOnAdd();

On SQL Server, the provider’s generated GUID primary-key convention is already sequential. A database default is an alternative when database-side generation is a deliberate ownership decision:

modelBuilder.Entity<Order>()
    .Property(o => o.Id)
    .HasDefaultValueSql("NEWSEQUENTIALID()");

On Postgres 18 and later, uuidv7() exists and does the same thing in the database. On older versions, generate v7 client-side in the value generator, which avoids the round trip entirely and keeps ordering across application nodes.

sealed class Uuid7ValueGenerator : ValueGenerator<Guid>
{
    public override Guid Generate(EntityEntry entry) => Guid.CreateVersion7();
}

modelBuilder.Entity<Order>()
    .Property(o => o.Id)
    .ValueGeneratedOnAdd()
    .HasValueGenerator<Uuid7ValueGenerator>();

Changing the generator does not rewrite old identifiers, but that does not automatically require changing them. Rebuilding a PostgreSQL B-tree sorts existing entries and can restore leaf density now; future v4 inserts will spread writes again, while new v7 inserts will concentrate near the current time range. Rewriting primary keys is a much larger referential migration and is justified only if UUID-ordered range locality—not merely index density—is a measured requirement.

CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Run with care on a production-sized index; this inspects index pages.
SELECT avg_leaf_density, leaf_fragmentation
FROM pgstatindex('orders_pkey');

avg_leaf_density and leaf_fragmentation are direct B-tree measurements. pg_class.reltuples / relpages mixes estimates and page types and is not a reliable fragmentation metric. Pair the page statistics with insert latency, cache misses, WAL volume, and the query that is actually slow before planning a migration.

A production-ready architecture

The insert path is where locality is won or lost, and it is worth drawing because the fix is a single decision at the boundary rather than an ongoing cost.

  application node
       |
       v
  +--------------+   ordered keys    +-------------------+
  | id generator | ----------------> | B-tree index      |
  | sequence or  |                  | narrow active set |
  | uuidv7()     |                  | edge, no split    |
  +--------------+                  +---------+---------+
                                            |
                                   row lands in place
                                            v
                                  +-------------------+
                                  | secondary indexes |
                                  | local pointers    |
                                  +-------------------+

  random key instead
       |
       v
  +--------------+
  | id generator | --> keys arrive in arbitrary order
  +--------------+     -> broad active leaf set
                         -> split only when target is full
                         -> more cache pressure when the set is large

A delivery checklist:

1. Find out what your database is actually generating before changing code. Client-side v4, a sequence, and a database default are three different problems.
2. Measure B-tree leaf density/fragmentation and the application symptom; no single ratio proves the key is the bottleneck.
3. Treat a key-type change as a referential migration across every foreign key, event, cache, and external contract.
4. If you need coordination-free uniqueness, move to UUIDv7 rather than maintaining a monotonic GUID counter.
5. Generate the key client-side if you can, so identifiers exist before a connection does.
6. Prefer changing the generator for new rows before rewriting old identifiers; the old v4 values remain valid keys.
7. Choose REINDEX CONCURRENTLY or an online engine-specific operation when availability requires it, and rehearse disk/lock costs.
8. In PostgreSQL the heap is not maintained in primary-key order. CLUSTER can reorder it once, but later writes do not preserve that order.
9. Re-check the plan for the most frequent range query after the change, because a better-locality index can change what the planner picks.
10. Watch leaf splits/density, WAL or redo bytes, cache misses, and insert latency after the change. Do not use autovacuum as a proxy for UUID locality.

Failure stories worth testing

Insert 500,000 random UUIDs, then 500,000 sequential bigints, into the same shape of table

Compare leaf density, split behavior, WAL/redo bytes, device IO, and insert latency between the two. Keep key width, fill factor, table shape, cache state, and concurrency identical.

Measure buffer pool hit rate before and after the generator change

pg_stat_database reports blks_hit and blks_read. A table that is write-heavy with a random key will show a much higher read share for the same row count. If the number does not move, the fragmentation is not your bottleneck and you should stop optimising it.

Run the single most frequent query against a warm cache and a cold one

A random UUID primary key hides its cost best when everything is cached. Restart the database, run the query once, and time it. The cold number is closer to what a cache miss actually costs on a busy instance.

Check whether every application node generates v4 or whether one generates v7

Mixed generators are worse than either one alone, because the index receives an intermittent but unsorted stream. This shows up as fragmentation that appears to fix itself on a busy day and returns on a quiet one, which is genuinely hard to diagnose from metrics.

Rebuild the random-key index, then continue inserting

The rebuild should improve current leaf density because it sorts existing entries. Continue with v4 inserts and watch the active set spread again; continue with v7 inserts and watch writes cluster near the recent-key range. This separates “repair existing index layout” from “change future write locality.”

Common mistakes

Mistake	What actually happens	Better decision
Treating UUID and random UUID as the same choice	You pay the fragmentation cost and get none of the benefits of ordering	UUIDv7, or a sequence if a single writer is available
Assuming a bigger key is the whole cost	InnoDB also copies the primary key into secondary indexes	Measure total index footprint and IO
Blaming UUIDs for vacuum churn	MVCC updates/deletes create dead tuples	Measure B-tree and vacuum mechanisms separately
Saying rebuild cannot help	A rebuild sorts and repacks current entries	Rebuild for current density; change generation for future locality
Claiming sequences are always contended	Cached allocators are often cheap, but not gapless	Benchmark the actual writer topology
Rewriting every old v4 ID	Foreign keys and external contracts turn it into a risky migration	Change new generation first; rewrite only for a proved need
Inventing a sequential GUID byte shuffle	Provider comparison order may differ from the text	Use UUIDv7/provider support and test stored order
Storing a UUID as char(36)	36 bytes for 16 bytes of information, and slower comparison	Use a native uuid or binary(16) column
Testing fragmentation on a table that fits entirely in cache	The problem is invisible at small scale by construction	Test at a size that exceeds the buffer pool
Optimising the key size but not the write pattern	Read amplification stays exactly where it was	Fix ordering first, width second

The complete story in one minute

A B+ tree stores keys in ordered leaf pages. Time-ordered inserts concentrate on a small recent leaf set; random inserts spread across the tree. A random insert does not imply a split or physical read—both depend on free space and cache state—but the broader active set can increase cache pressure, dirty-page churn, and split frequency. Those are measurements, not constants.

InnoDB stores the row in the clustered primary-key index and copies primary-key columns into every secondary index, so width and locality both matter. PostgreSQL keeps heap tuples separately and stores TIDs in its primary-key B-tree, so random UUIDs mainly affect that B-tree’s write locality and UUID-ordered heap correlation. They do not inherently create extra autovacuum work.

The requirement is usually preallocated, coordination-free uniqueness, not randomness. UUIDv7 adds time locality with a 48-bit millisecond prefix while retaining distributed generation, but it is not a strict global sequence across nodes or within one millisecond. A database sequence remains simpler for database-owned writes. Change the generator for new rows first, measure again, and rewrite old identifiers only when a real range-locality requirement justifies the contract migration.

Technical references

• RFC 9562: UUID version 7 and monotonicity considerations
• .NET: Guid.CreateVersion7
• EF Core SQL Server: GUID value generation
• PostgreSQL: pgstattuple and pgstatindex
• MySQL: InnoDB clustered and secondary indexes