How does inserting keys in ever-increasing order — a sequence-generated id or an insert timestamp — differ inside a B+tree index from inserting randomly distributed keys such as random UUIDs? Consider split behaviour, page density, write I/O and concurrency.

## The one fact that drives everything An index entry must go into the single leaf whose key range covers its key. So the *distribution of new key values* decides where in the structure the write traffic lands. Everything else — split shape, density, cache behaviour, I/O pattern, contention — follows from that. ## Ever-increasing keys A sequence-generated integer or an insertion timestamp produces keys greater than every key already present. Consequences: **Only the rightmost leaf is ever written.** The rest of the index is read-only from the insert path's perspective. Its interior pages are never dirtied; only the rightmost leaf and the ancestors on its path change. **Splits are deliberately lopsided.** A midpoint split at the right edge would be wasteful: the left page would sit half-empty forever because no future key can fall into its range. Implementations therefore detect an append pattern and split so that the existing page keeps nearly all its entries and the new page starts almost empty, ready to absorb the continuing stream. This is often described as a 90/10 or a 100/0 split. **Density stays high.** Because pages are filled once and never revisited, the index approaches the density it would have if freshly built. **The write working set is tiny.** The rightmost leaf plus a handful of ancestor pages, which stay resident in the buffer cache regardless of index size. Inserts do not need to read a page from storage first, because the page they need is already hot. This property holds even when the index is far larger than memory — a decisive advantage. **I/O is sequential-ish.** New pages are allocated in order, so the index grows contiguously and the leaf chain stays close to physical order, which keeps later range scans efficient. **The cost is contention.** Every concurrent inserter needs the same page. They queue on its latch; the split of that page briefly serialises them harder. In a clustered organization where the table itself is ordered by the key, the contention extends to the physical block being appended to and to the buffer holding it. On a many-core machine with a high insert rate, this hot spot — not I/O — becomes the throughput ceiling. ## Randomly distributed keys A random UUID or a hash-derived key spreads inserts uniformly across the key space. **Splits happen everywhere, at the midpoint.** There is no append pattern to detect, so pages split evenly. Under sustained random insertion the long-run average density settles around two-thirds of capacity — meaning the index needs roughly 50% more pages than a densely packed one for the same entries, with the corresponding penalty on every range scan and on cache effectiveness. **The write working set is the whole index.** Any leaf can be the target of the next insert. While the index fits in memory this is survivable; once it exceeds memory, each insert may first have to *read* the target leaf from storage — a random read on the critical path of a write. Insert throughput can fall by an order of magnitude at that crossover, and the fall is abrupt rather than gradual. **Checkpoint and log pressure rise.** Dirty pages are scattered across the whole index, so each checkpoint writes far more distinct pages. Engines that log a full page image the first time a page is modified after a checkpoint pay that cost across many more pages, inflating log volume, replication traffic and backup size. **Physical ordering erodes quickly.** New pages come from wherever free space is, so the leaf chain's logical order diverges from physical layout, degrading large ordered scans. **But contention is spread.** Concurrent inserters touch different pages, so there is no single hot latch. For very high concurrency this is a genuine advantage of random keys, and it is the honest counterweight to everything above. ## Choosing, and the middle ground The usual advice — prefer sequential keys — is right for most systems, because density, cache residency and sequential I/O dominate. But it is a trade, not a law: - **Time-sortable identifiers.** Identifier schemes that put a timestamp in the high-order bits and randomness in the low-order bits (time-ordered UUID variants, ULID-style ids) give near-sequential insert locality while keeping the global uniqueness and client-side generation that made random UUIDs attractive. This is the standard resolution and should be the default recommendation when random UUIDs are on the table. - **Deliberate spreading.** When the rightmost-page hot spot is the real bottleneck, spread it on purpose: a leading partition or shard column that splits inserts across N insert points, hash partitioning, or (in engines that offer it) transforming the key so consecutive values land apart. Each trades some scan locality for concurrency. - **Narrow keys matter too.** A 16-byte random key does not merely scatter writes; it also halves the fanout relative to an 8-byte key, adding entries per page pressure and potentially a tree level, and it inflates every secondary index that carries the primary key as its row reference. ## Diagnosing which one is hurting you Sequential-key trouble looks like contention: high concurrency, waits concentrated on one page or buffer, throughput flat as you add cores. Random-key trouble looks like I/O: insert latency stable while the index fits in memory, then a cliff; a read-per-write pattern on the write path; checkpoint write volume far exceeding the logical data written; and an index noticeably larger than its live contents warrant.