A table stores its rows in primary-key order and someone proposes a 60-byte composite natural key as the primary key. The table already carries six secondary indexes. What are the consequences, and what would you propose instead?

seniorshould knowfreq 42%

answer

primary key = locator in every secondary index
width x number of indexes = amplification
fanout down, tree height up
cache residency is the real cliff
surrogate key + unique constraint on natural key

basics

~20 s

Every secondary-index entry embeds the primary key as its row locator, so a 60-byte key adds roughly 60 bytes per entry across all six indexes. Entries get wider, fanout drops, indexes grow, less of them fits in cache, and lookups and writes cost more. Prefer a narrow surrogate key plus a unique constraint on the natural key.

solid answer

~60 s

The knock-on effects matter more than the raw bytes. Wider entries mean fewer per page, so fanout falls and each tree gains levels; more pages means more I/O per scan and a smaller effective cache hit rate because the buffer pool holds proportionally less of each index. Every lookup also compares 60-byte keys instead of 8. Writes amplify too: inserts and updates write more index bytes and more log volume, and backups and replication carry it all.

I would use a narrow immutable surrogate key as the primary key and enforce the natural key with a unique constraint - which gives correctness without paying the width in six places.

In clustered storage the primary key is the row locator, so it is physically duplicated into every entry of every secondary index. With six indexes, a 60-byte key costs roughly 360 extra bytes per row before overhead, compared with about 48 for an 8-byte key - and that is on top of the clustered tree's own internal nodes, which also carry the key. The knock-on effects matter more than the raw bytes. Wider entries mean fewer per page, so fanout falls and each tree gains levels; more pages means more I/O per scan and a smaller effective cache hit rate because the buffer pool holds proportionally less of each index. Every lookup also compares 60-byte keys instead of 8. Writes amplify too: inserts and updates write more index bytes and more log volume, and backups and replication carry it all. I would use a narrow immutable surrogate key as the primary key and enforce the natural key with a unique constraint - which gives correctness without paying the width in six places.

code

sql · 12 lines

sql

CREATE TABLE shipment (
  id            BIGINT       GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  carrier_code  VARCHAR(12)  NOT NULL,
  origin_code   VARCHAR(12)  NOT NULL,
  tracking_ref  VARCHAR(32)  NOT NULL,
  status        VARCHAR(16)  NOT NULL,
  CONSTRAINT uq_shipment_natural
    UNIQUE (carrier_code, origin_code, tracking_ref)
);

-- secondary index entries now carry an 8-byte locator, not 56+ bytes
CREATE INDEX idx_shipment_status ON shipment (status);

go deeper

for a junior

Say that with clustered storage the primary key is stored again inside every secondary index, so a long key makes all of those indexes bigger.

for a middle

Quantify it across the six indexes and connect wider entries to lower fanout, more pages, and slower lookups; propose a surrogate key with a unique constraint.

for a senior

Lead with buffer-pool residency and write and log amplification, cover primary-key mutability cost, and note when a natural key is still acceptable.

for a principal

Make it a standard: narrow immutable keys, an index budget per table, and capacity modelling that ties index footprint to memory sizing and replica bootstrap time.

Why the primary key's width is not a local decision

In a table stored in primary-key order, the primary key appears in three places at once:

In the clustered tree's leaf rows, once per row - unavoidable, this is the data.
In the clustered tree's internal nodes as separator keys - so key width directly sets fanout of the primary structure.
In every entry of every secondary index, as the row locator.

The third is the one people forget, and it is the one that multiplies. Choosing the primary key is therefore a decision about the whole index set, not about one column list.

The arithmetic

Take ten million rows, six secondary indexes, and a secondary key of 12 bytes plus per-entry overhead.

With an 8-byte surrogate key, an entry is roughly 20 bytes plus overhead. Six indexes hold sixty million entries, order of 1.5 GB.
With a 60-byte natural key, an entry is roughly 72 bytes plus overhead - more than three times larger. The same six indexes now approach 5 GB.

Those numbers are illustrative, not precise, but the ratio is the point: index footprint scales with locator width, multiplied by the number of indexes.

Second-order effects, which usually hurt more

Fanout and tree height. Fewer entries per page means each internal node covers fewer children. A tree that was three levels deep may become four, adding a page read to every single lookup on every index.
Buffer-pool efficiency. Memory is fixed. Tripling index size means a third as much of each index stays resident, so lookups that used to be cache hits become physical reads. This is typically the effect that shows up as a mysterious latency cliff after data growth.
Write amplification. Each insert writes an entry into every applicable index; wider entries mean more bytes written, more log volume, more pages dirtied, more work for the flusher and for replication.
Comparison cost. Every descent through every index compares the locator-bearing entries. Comparing 60-byte composite keys, possibly across several columns with collation rules, costs meaningfully more CPU than comparing a single 8-byte integer.
Operational size. Backups, restores, replica bootstrap, and index rebuild times all scale with the bigger footprint.
Mutability risk. Natural keys change - a country reorganizes its codes, a customer's identifier is corrected. Because the key is the locator in six indexes, a single primary-key update rewrites entries across all of them, which is expensive and lock-heavy. Surrogate keys are immutable by construction.

What to propose instead

The standard design is a narrow, immutable surrogate primary key - a 4- or 8-byte integer from a sequence, or a compact ordered identifier - with a unique constraint on the natural key columns to preserve the business rule. This buys:

Small locators in all six secondary indexes.
A stable key that never needs updating.
The natural key still enforced by the database, and still indexed by the unique constraint's own index, so lookups by natural key remain fast.

The honest counter-arguments deserve acknowledgement: a surrogate key adds one more index (the unique constraint on the natural key) and an extra join hop when the natural key is what callers hold. If the table has no secondary indexes and is always accessed by the natural key, a natural primary key can be perfectly reasonable. The width argument gets its force from the multiplication across secondary indexes.

Random versus ordered surrogate keys

One caution: narrow is necessary but not sufficient. A 16-byte random identifier as the clustered key is both wider than an integer and randomly ordered, so inserts land on random pages, causing page splits, poor cache locality, and fragmentation. If globally unique identifiers are required, prefer a time-ordered variant so insert order approximates key order, and be aware you are still paying the width in every secondary index.

How to present this

Lead with the mechanism - "the primary key is the locator in every secondary index" - then quantify with a rough calculation, then give the second-order effects in order of practical impact (cache residency, tree height, write volume), then propose the surrogate-plus-unique-constraint design while acknowledging when a natural key is fine. That sequence turns a memorized rule into demonstrated understanding.

## Why the primary key's width is not a local decision In a table stored in primary-key order, the primary key appears in three places at once: 1. In the clustered tree's leaf rows, once per row - unavoidable, this is the data. 2. In the clustered tree's internal nodes as separator keys - so key width directly sets fanout of the primary structure. 3. **In every entry of every secondary index**, as the row locator. The third is the one people forget, and it is the one that multiplies. Choosing the primary key is therefore a decision about the whole index set, not about one column list. ## The arithmetic Take ten million rows, six secondary indexes, and a secondary key of 12 bytes plus per-entry overhead. - With an 8-byte surrogate key, an entry is roughly 20 bytes plus overhead. Six indexes hold sixty million entries, order of 1.5 GB. - With a 60-byte natural key, an entry is roughly 72 bytes plus overhead - more than three times larger. The same six indexes now approach 5 GB. Those numbers are illustrative, not precise, but the ratio is the point: index footprint scales with locator width, multiplied by the number of indexes. ## Second-order effects, which usually hurt more - **Fanout and tree height.** Fewer entries per page means each internal node covers fewer children. A tree that was three levels deep may become four, adding a page read to every single lookup on every index. - **Buffer-pool efficiency.** Memory is fixed. Tripling index size means a third as much of each index stays resident, so lookups that used to be cache hits become physical reads. This is typically the effect that shows up as a mysterious latency cliff after data growth. - **Write amplification.** Each insert writes an entry into every applicable index; wider entries mean more bytes written, more log volume, more pages dirtied, more work for the flusher and for replication. - **Comparison cost.** Every descent through every index compares the locator-bearing entries. Comparing 60-byte composite keys, possibly across several columns with collation rules, costs meaningfully more CPU than comparing a single 8-byte integer. - **Operational size.** Backups, restores, replica bootstrap, and index rebuild times all scale with the bigger footprint. - **Mutability risk.** Natural keys change - a country reorganizes its codes, a customer's identifier is corrected. Because the key is the locator in six indexes, a single primary-key update rewrites entries across all of them, which is expensive and lock-heavy. Surrogate keys are immutable by construction. ## What to propose instead The standard design is a narrow, immutable surrogate primary key - a 4- or 8-byte integer from a sequence, or a compact ordered identifier - with a **unique constraint** on the natural key columns to preserve the business rule. This buys: - Small locators in all six secondary indexes. - A stable key that never needs updating. - The natural key still enforced by the database, and still indexed by the unique constraint's own index, so lookups by natural key remain fast. The honest counter-arguments deserve acknowledgement: a surrogate key adds one more index (the unique constraint on the natural key) and an extra join hop when the natural key is what callers hold. If the table has *no* secondary indexes and is always accessed by the natural key, a natural primary key can be perfectly reasonable. The width argument gets its force from the multiplication across secondary indexes. ## Random versus ordered surrogate keys One caution: narrow is necessary but not sufficient. A 16-byte random identifier as the clustered key is both wider than an integer and randomly ordered, so inserts land on random pages, causing page splits, poor cache locality, and fragmentation. If globally unique identifiers are required, prefer a time-ordered variant so insert order approximates key order, and be aware you are still paying the width in every secondary index. ## How to present this Lead with the mechanism - "the primary key is the locator in every secondary index" - then quantify with a rough calculation, then give the second-order effects in order of practical impact (cache residency, tree height, write volume), then propose the surrogate-plus-unique-constraint design while acknowledging when a natural key is fine. That sequence turns a memorized rule into demonstrated understanding.

The team says storage is cheap, so index size does not matter. What is the counter-argument?
Disk is cheap but memory is not, and index size decides how much of the index stays in the buffer pool. Tripling index footprint against a fixed cache converts cache hits into physical reads, which shows up as a latency cliff rather than a bigger bill. Wider entries also lower fanout, which can add a level to every tree and therefore a read to every lookup, and they increase write and log volume on every insert.
Is a 16-byte random universally unique identifier an acceptable narrow surrogate key?
It is narrower than a 60-byte composite but still twice the width of a bigint, and the bigger problem is its randomness: in clustered storage, random keys scatter inserts across the whole tree, causing page splits, fragmentation, and poor cache locality on the insert path. A time-ordered identifier variant keeps insert order close to key order and largely removes that penalty. If a compact sequence-based integer is workable, it remains the cheapest option in both width and locality.

saying these in an interview costs you the question

Not realizing the primary key is duplicated into every secondary-index entry under clustered storage
Judging key width only by the clustered table's own size
Dismissing index growth because disk is cheap, ignoring buffer-pool residency
Choosing a mutable natural key without accounting for the cost of updating it across all indexes
Assuming any surrogate key is fine, including randomly ordered identifiers in clustered storage

A table stores its rows in primary-key order and someone proposes a 60-byte composite natural key as the primary key. The table already carries six secondary indexes. What are the consequences, and what would you propose instead?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

Why the primary key's width is not a local decision

The arithmetic

Second-order effects, which usually hurt more

What to propose instead

Random versus ordered surrogate keys

How to present this

▾common follow-ups2 questions

▾red flags5 flags

§ context

▾related questions6 questions

▾related topics3 topics

Why the primary key's width is not a local decision

The arithmetic

Second-order effects, which usually hurt more

What to propose instead

Random versus ordered surrogate keys

How to present this

answer

go deeper

context