You are designing an OLTP table and can choose whether the rows are physically ordered by a clustering key or stored unordered in a heap. What workload characteristics push you toward each, and what do you give up?

## Frame the decision correctly Physical row order is a single, table-wide choice, so the question is: *which one access pattern deserves physical locality, and is the cost of denying it to all the others acceptable?* If no pattern clearly dominates, the symmetry of a heap is the safer default. ## The case for clustering **Range and grouped access.** This is the strongest argument. If queries repeatedly ask for a contiguous run of key values — every event in a time window, every line item for an order, every row for one tenant — clustering makes that run physically contiguous. Instead of N random row fetches, you read a handful of leaf pages sequentially, each carrying many qualifying rows. The saving grows linearly with rows returned, so at hundreds or thousands of rows per query it is a step change rather than a tweak. **Primary-key point lookups.** Under clustering the row is the leaf payload, so a PK lookup is a single descent with no row fetch. In a heap it is a descent plus a fetch. One saved random access per lookup is modest individually but real at high QPS. **Sorted output for free.** Queries ordering by the clustering key can consume rows in order without a sort. **Locality of related rows.** Clustering a child table on a composite key that leads with the parent id physically groups children under their parent — often the single best structural optimization for parent-child read patterns. ## The case for a heap **Access spread across columns.** If a table is queried by five different columns with comparable frequency, clustering privileges one and makes the other four pay a second descent. A heap gives all five identical cost — a descent plus a direct fetch — which is a better shape when there is no dominant pattern. **A wide natural key.** Under clustering, the clustering key is embedded in every secondary index as the row reference, so a wide key multiplies across all of them. A heap uses a compact physical row id regardless of key width, decoupling key design from index footprint. **Random insert distribution.** If no candidate key correlates with arrival order, clustering forces scattered inserts, splits, poor page fill and a large dirty page set. A heap simply places rows where there is room. **Stable row addresses.** Physical row ids do not change on ordinary inserts, so index maintenance stays simple. (Updates that grow a row can still relocate it, leaving a forwarding pointer — the heap's own maintenance wart.) **Bulk load and churn.** Appending arbitrary data into a heap is close to sequential regardless of key values. ## What you sacrifice either way Clustering costs you: only one ordering, a second descent for every secondary-index lookup, sensitivity to insert order, and the key's width propagating into all indexes. Heap costs you: a row fetch on every index lookup, and key-ordered ranges degenerating into many random page reads because logically adjacent rows are physically scattered — the effect grows with result size, and the optimizer may abandon the index entirely for large ranges in favour of a full scan. ## A practical decision procedure 1. **List the top access patterns by volume**, noting for each whether it is a point lookup or a range/group, and on which columns. 2. **Is there a dominant grouped/range pattern?** If yes, that key is your clustering candidate. 3. **Check width.** If the candidate is wide, either narrow it (surrogate key) or reconsider clustering. 4. **Check insert correlation.** Will rows arrive roughly in that key order, or can you make them (time-ordered identifiers, or a leading tenant/parent id that concentrates writes per tenant)? If inserts will be uniformly random over the key, clustering trades read locality for write pain. 5. **Check the secondary paths.** How much traffic will pay the extra descent, and is that acceptable? Sometimes an index that carries the extra columns a query needs neutralizes it. 6. **If nothing dominates, take the heap** and let each index stand on its own. ## Engine reality check worth stating Some engines make this choice for you: certain storage engines always cluster on the primary key, others store heaps by default and offer index-organized tables or a one-off physical reorganization as an option. So in a real design conversation, step zero is knowing which model the target engine gives you and whether it is negotiable at all — the reasoning above then tells you how hard to work on key design within that constraint. ## The sentence to land "Cluster when one key dominates access — especially ranges — and inserts arrive roughly in that order and the key is narrow; otherwise take the heap's symmetry. You are choosing which single access pattern gets physical locality, and you only get to choose once."