A service uses a random version-4 UUID as the clustered primary key of its largest table. At 200 million rows, insert throughput has fallen by more than half and the buffer cache hit rate keeps dropping, although the table is append-only and no query changed. Which explanation is correct?

What is being tested

Whether you can reason about where in an index a write lands, not just how big the key is. This is the most common avoidable data-modelling mistake in new services, and it is invisible until the table outgrows memory.

The mechanism

A B-tree index keeps entries in key order. With a monotonically increasing key, every insert goes to the right-hand edge: the same leaf page is touched repeatedly, it stays in the buffer cache, and it is written out once per checkpoint however many rows went into it.

A random key sends each insert to a different, unpredictable leaf. Three costs follow, and they compound:

1. Cache behaviour. The pages you need are spread over the whole index, so the hot set is the index, not its tail. Once the index exceeds RAM, every insert becomes a read of a page from storage before it can be modified.
2. Page splits. Inserting into the middle of a full page splits it, which costs two page writes and leaves both pages half-empty. Index size inflates, which makes the cache problem worse.
3. Write-ahead amplification. Many engines write a full page image the first time a page is touched in a checkpoint cycle. Touching 1,000 scattered pages per second instead of one produces far more log volume for the same rows.

And the key width is a real secondary effect: 16 bytes against 8, repeated in every secondary index, because a secondary index entry carries the primary key.

Why the other options fail

• "The width alone explains it." Width doubles the index; it does not halve throughput at a cliff. The cliff is the working set crossing RAM, which is a locality effect.
• "Collisions." A 122-bit random value does not collide in practice, and a B-tree does not resolve collisions by scanning - that is a hash-table failure mode imported into the wrong structure.
• "A rebuild fixes it permanently." A rebuild compacts the index and throughput recovers for a while, then decays again, because the insert pattern that caused it has not changed. A fix that has to be repeated on a schedule is a symptom, not a fix.

What to use instead

A time-ordered identifier. UUIDv7, standardised in RFC 9562 (2024), puts a millisecond timestamp in the high bits and randomness in the low bits, so inserts land at the right-hand edge while keys stay globally unique and unguessable enough for most purposes. ULID and snowflake-style ids do the same thing.

If the identifier must stay random for external exposure, keep a monotonic internal key as the clustered key and the random id as a unique secondary column. One extra index lookup on fetch-by-public-id is a far smaller cost than random insert locality.

When this is the wrong answer

In a distributed store that partitions by key hash or key range, monotonic keys create a hot partition - every write goes to the same shard, and randomness is doing necessary work. There, the right answer is a key with a high-entropy prefix and the timestamp further in, or an explicit bucketing scheme.

And at small scale none of it matters: a table that fits comfortably in RAM behaves the same either way. The threshold to watch is index size against available cache, not row count.