Some engines store a physical row address (page and slot) in each secondary-index entry, while others store the table's primary-key value instead. Compare the two designs and their consequences for reads and for updates.

## The problem both designs solve A secondary index must be able to get from a key to a row. It needs a locator, and there are only two families: point at *where the row is* (physical), or point at *what the row is* (logical, via the primary key). Every consequence below follows from that one choice. ## Physical locators Used with heap storage, the entry carries something like (file, page, slot). **Read path.** One page read gets the row. There is no second tree to walk, no key comparisons, no dependency on primary-key width. For selective queries this is the fastest lookup design there is. **Update path.** Rows are not always stable. Updating a variable-length column can make a row outgrow the free space on its page. Now it must move - and every secondary index entry pointing at the old address is wrong. Repairing all of them would make a single update proportional to the number of indexes, which is unacceptable, so engines use one of two mitigations: - **Forwarding pointers / migrated rows**: leave a stub at the original slot pointing to the new location. Secondary indexes stay untouched, but every lookup through them now costs two page reads. Accumulate enough of these and a table degrades measurably until reorganized. - **Version-chain designs**: keep the new version elsewhere and reach it from the original location, which has the same double-hop character. **Other properties.** Locators are small and fixed width, so index entries are compact and fanout is high. Insert order and physical order coincide, so an index on an append-ordered column tends to be well clustered. But any physical reorganization of the table invalidates every secondary index, so a reorganization implies rebuilding them. ## Primary-key locators Used with clustered / index-organized storage, where rows live in the leaves of the primary-key B+Tree. **Read path.** Two traversals: the secondary tree to find the key, then the clustered tree to find the row. On a large table that is perhaps three extra page reads per matched row instead of one - measurably more expensive, though the upper levels of the clustered tree are almost always cached, so the marginal cost in practice is often one or two real reads. **Update path.** Because the reference is logical, the row can move anywhere inside the clustered tree - page splits, defragmentation, whole-table reorganization - without touching any secondary index. That removes an entire class of maintenance. The symmetric cost: changing a primary-key *value* rewrites the locator in every secondary index, which is one strong argument for immutable surrogate keys. **Other properties.** Entries carry the full primary key, so a wide key inflates every secondary index, reducing fanout and buffer-pool efficiency. On the plus side, the primary-key columns are implicitly available in every secondary index, which lets more queries be answered without touching the table at all, and range queries on the primary key are automatically clustered. ## How to choose, in practice Usually you do not - the engine's storage model decides. What you control is how well you live with it: - On clustered storage, keep the primary key **narrow and immutable**. A compact surrogate key with a unique constraint on the natural key is the standard answer, precisely because the key is replicated into every secondary index and into every lookup comparison. - On heap storage, watch for **row growth patterns** - updates that fill previously empty variable-length columns are the classic generator of forwarding pointers - and schedule reorganization when the forwarded-row count climbs. - In both, remember the lookup can be avoided entirely when the index already holds every column the query needs, and that on clustered storage the primary key is free inside every secondary index. ## The interview shape A strong answer states the two locator families, gives one concrete cost each (one page read versus a tree descent), and names the maintenance consequence each design generates (forwarding pointers versus wide entries and primary-key-update fan-out). Candidates who only recite "clustered is faster" without the locator mechanism have memorized a conclusion rather than understood the structure.