Most indexes are separate structures pointing at rows stored elsewhere. A clustered index is different: it is the table. Rows are stored physically in the index's order, so finding an entry means you already have the row.
That eliminates the fetch step for every lookup on the clustered key and makes range scans on it exceptionally fast, since the matching rows are physically adjacent.
What changed in 2026
- Sequential identifier design became a common concern. The interaction between random primary keys and clustered storage drove wider adoption of time-ordered identifiers — see UUID v7.
- Engine differences got clearer. Which databases cluster by default and which do not became a routine consideration in cross-engine work.
- Secondary index overhead got attention. The relationship between clustered key width and total index size became a documented design factor.
- Write patterns entered key selection. Insert order stopped being an afterthought.
Why key choice matters so much
Rows are stored in clustered key order, so an insert must go in the correct physical position.
A sequential key — an incrementing integer, or a time-ordered identifier — always inserts at the end. Pages fill in order, no reorganisation is needed, and the storage stays compact.
A random key — a random UUID, a hash — inserts at an arbitrary position. The target page is usually full, so it splits into two half-full pages. Do that continuously and you get a table of half-empty pages, more storage, more pages to read for any scan, and worse cache behaviour.
That is the single most consequential thing about clustered storage, and it is why random UUIDs as primary keys became a known performance problem — see UUID vs bigint keys.
| Key type |
Insert behaviour |
Storage |
| Sequential integer |
Appends |
Compact |
| Time-ordered identifier |
Appends |
Compact |
| Random UUID |
Splits pages |
Fragmented |
| Natural key, unordered |
Splits pages |
Fragmented |
Secondary indexes inherit the key
The second consequence people miss. In a clustered table, a secondary index cannot store a physical row location — rows move when pages split. It stores the clustered key instead, and lookups go through it.
So every secondary index contains a copy of the clustered key for every row. A wide clustered key inflates every secondary index on the table, and a table with several secondary indexes multiplies that overhead.
A 16-byte UUID clustered key against an 8-byte integer means every entry in every secondary index carries twice the key overhead. On a table with five indexes and many rows, that is substantial.
It also means secondary index lookups involve two traversals: find the clustered key in the secondary index, then find the row via the clustered index. Slightly slower than a direct pointer, and generally worth it for the clustering benefits.
When clustering pays
The benefit is concentrated in range access on the clustered key.
Time-series data clustered by timestamp: querying a date range reads physically adjacent pages, which is close to the best case storage can offer.
Multi-tenant data clustered by tenant identifier first: all of one tenant's rows sit together, so tenant-scoped queries read a contiguous region.
Parent-child data clustered by parent identifier: fetching all children of a parent is a contiguous read.
Where you only ever look up single rows by key, clustering provides the fetch-elimination benefit without the range benefit — still useful, less decisive.
Where your access is random across the whole table with no locality, clustering provides little and you are paying the insert-ordering constraint for nothing.
Common mistakes
- Random UUID as the clustered key. Page splits and fragmentation.
- A wide clustered key on a table with many secondary indexes. Multiplied overhead.
- Clustering on a column never range-scanned. No benefit from the ordering.
- Ignoring insert order. The dominant factor in write performance.
- Assuming all engines cluster. Behaviour differs; check yours.
- Composite clustered keys with the wrong column first. Locality follows the leading column.
FAQ
Which databases cluster by default?
It varies meaningfully between engines — some organise tables by the primary key automatically, others store rows in insertion order with all indexes separate. Check your engine before applying advice from another.
Can I change the clustering key?
Effectively it means rebuilding the table, which on a large table is a significant operation. Treat it as a decision made at design time.
Should I always use a sequential key?
For clustered tables, sequential insert order is strongly preferable. Time-ordered UUIDs give you distributed-friendly identifiers with sequential insert behaviour — see UUID v7.
Does clustering help writes?
Sequential inserts, yes — appending is cheap. Updates that change the clustered key are expensive, because the row physically moves.
Where to go next
For the key-type decision, read UUID vs bigint keys and UUID v7. For the fragmentation clustering can suffer, index bloat.