A table is stored clustered on its primary key. Compare what happens on insert when that key is a monotonically increasing value versus a random one such as a version-4 UUID.

## Why insert order is physical under clustering In a clustered table, rows are stored in the leaf pages of a B+tree ordered by the clustering key. There is no "append anywhere" option: a new row must be placed in the leaf that covers its key range. So the distribution of incoming key values directly determines which pages the engine touches, dirties and splits. In a heap this concern largely disappears — new rows go to any page with space, usually at the end — which is exactly why this question is specific to the clustered model. ## Monotonically increasing keys With a sequence, identity column, or otherwise increasing key, every insert belongs at the right edge of the key space. - **One hot page.** All inserts hit the current rightmost leaf, which stays in memory. No read is needed before writing. - **Cheap splits.** When that page fills, engines commonly perform a right-edge optimization: rather than splitting 50/50, they leave the full page as-is and start a new page. Fill factor stays near 100%. - **Compact table.** High fill means fewer pages for the same rows: smaller table, better scan throughput, more rows per cached page. - **Small dirty set.** Only a handful of pages are dirty at any moment, so checkpoints and background flushing move little data, and write-ahead-log volume per insert is minimal. - **Good physical/logical correlation.** Rows inserted together are stored together, so "recent rows" queries have excellent locality. ## Random keys such as UUIDv4 A v4 UUID is essentially uniform random over a 128-bit space, so consecutive inserts land in unrelated parts of the tree. - **Read-before-write.** The target leaf is unlikely to be cached once the index exceeds memory, so an insert becomes a random read followed by a modification. Insert throughput becomes bound by random read latency. - **Splits everywhere, poor fill.** Random insertion into a full page causes a mid-page split leaving two pages roughly half full. Steady state for uniformly random inserts is around 65-70% average fill, so the table occupies noticeably more pages than the same data inserted in order — a meaningful footprint increase, and correspondingly less effective caching. - **Large dirty set.** Because writes scatter, many distinct pages are dirty simultaneously; checkpoints must flush all of them, converting what could have been a few sequential writes into many random ones. - **Whole index becomes the working set.** With ordered inserts, the hot part of the index is the right edge; with random inserts, the hot part is everything, so cache hit rate collapses once the index exceeds memory. This is the cliff people describe as "it was fine until the table hit N rows". - **Width cost on top.** A 16-byte UUID (or worse, a 36-character text representation) is wider than an 8-byte integer, lowering fanout and — under clustered storage — inflating every secondary index that carries it as the row reference. That is a separate penalty compounding the ordering one. ## The counter-consideration: hot-page contention Monotonic keys are not free. Because every insert targets the same rightmost leaf, that page becomes a contention point: concurrent inserters serialize on its latch. At high concurrency this shows up as latch contention on the right edge. This is a real tradeoff, not a reason to prefer random keys — mitigations are more targeted, and the random-key remedy trades a contention problem for a much larger I/O and footprint problem. ## Practical remedies - **Time-ordered identifiers.** UUIDv7-style values put a timestamp in the high-order bits, so they sort roughly by creation time while retaining global uniqueness and non-guessability in the low bits. This recovers nearly all the insert locality of a sequence. - **Narrow surrogate clustering key.** Cluster on a compact sequence and keep the random identifier as an ordinary unique column with its own secondary index. Inserts stay ordered; external lookups by UUID cost one extra descent. - **Reordering an existing UUID.** Where the identifier's byte layout puts a timestamp in a non-leading position, storing a byte-rearranged form so the time component leads restores ordering — an option only if you control the representation. - **Fill factor tuning.** Leaving free space in pages reduces split frequency for random inserts, at the price of a larger table up front. This softens the symptom rather than removing the cause. ## How to answer Lead with the mechanism — clustered storage forces the row into key position — then name the four consequences of randomness: read-before-write, splits with poor fill, a large dirty set, and the whole index becoming the working set. Then give the mitigation ladder, and be honest that monotonic keys concentrate contention at the right edge.