skip to content

Does an UPDATE statement that changes only columns with no index on them still incur index maintenance cost? Explain what determines the answer.

level: middleimportance: should knowfreq 42%

answer

  1. key unchanged → no key maintenance
  2. pointer invalidated if the row relocates
  3. new version fits on page + no indexed col changed → skip all indexes
  4. fill factor buys update locality at read cost
  5. PK-pointer design: no fan-out, extra read lookup

basics

~20 s

Logically, only indexes containing a changed column need their keys updated. Physically, it depends on whether the row stays in place: if the storage engine writes a new row version elsewhere, every index pointing at that row must be updated too, even for untouched columns.

solid answer

~1 min

Two layers to this. **Logical layer.** An index entry is (key, row pointer). If none of the indexed columns changed, the key is unchanged, so no index needs a *key* change. Updating `last_login_at` on a table indexed only on `email` and `country` needs no index key maintenance at all. **Physical layer.** The pointer half can still be invalidated. Engines that create a new row version for each update — the usual multi-version approach — may place that version on a different page. Every index entry that pointed at the old location must then be given an entry for the new one, including indexes on columns that did not change. Engines optimise this: if the new version fits on the same page *and* no indexed column changed, they can keep the old index entries valid and skip the fan-out entirely. Miss either condition — the page is full, or an indexed column changed — and you pay full index maintenance. Engines that index by primary key rather than physical address avoid the relocation problem, but pay an extra lookup on every secondary-index read instead. Practical consequences: keep frequently-updated columns out of indexes, and leave free space on pages so in-place updates stay possible.

code

sql · 5 lines
sql
-- before: every heartbeat update touches a table carrying 6 indexes
UPDATE devices SET last_seen_at = now() WHERE device_id = ?;

-- after: hot column lives in a narrow table with one index
UPDATE device_heartbeat SET last_seen_at = now() WHERE device_id = ?;

go deeper

for a junior

Say that only indexes containing the changed columns need their keys updated, so updating non-indexed columns is much cheaper.

for a middle

Add the physical caveat: if the new row version cannot stay on the same page, every index must be updated to point at the new location.

for a senior

Discuss fill factor, the observable in-place-update ratio, reclaim of dead versions and entries, and splitting hot volatile columns into a narrow table.

for a principal

Weigh the two pointer designs — physical address versus primary key — as a write-path versus read-path cost allocation, and tie schema partitioning of volatile columns to the table's write service level.

## The logical answer An index stores a key derived from one or more columns, plus a way to reach the row. If an `UPDATE` does not touch any indexed column, the key values in every index remain correct. So in principle no index key needs rewriting, and an update to non-indexed columns should be as cheap as writing the row itself. This is why the standard advice — "don't index columns that change constantly" — has real teeth. A counter, a `last_seen_at`, a `retry_count`, a mutable `status`: each index on such a column converts every business update into an index delete-plus-insert, which is more expensive than a plain key change sounds, because the entry moves to a *different sorted position*, hence a different page. ## The physical answer The complication is what the index entry points *at*. **Physical row address.** If index entries hold a physical location (page number and slot), that pointer is only valid while the row stays there. Under multi-version concurrency control, an update does not overwrite the row — it writes a new version, leaving the old one visible to older transactions. If the new version fits on the same page, the engine can often keep the existing index entries valid by chaining old version to new within the page; index maintenance is skipped entirely. If the page has no room, the new version goes elsewhere, and now *every* index must gain an entry pointing to the new location — including indexes on columns that were not modified. So the same `UPDATE` statement can cost one page write or a full fan-out across every index, decided by whether there happened to be free space on the page. That is why fill-factor settings exist: deliberately leaving a percentage of each page empty at build time so later updates have room to stay local. It trades read density (fewer rows per page, so scans read more pages) for cheaper updates — a good trade for hot, frequently-updated tables and a poor one for append-only ones. **Primary-key pointer.** The other design has secondary index entries store the primary key value instead of a physical address. Rows can then move freely without invalidating any secondary index, so this class of update never fans out. The price is paid on reads: every secondary index lookup returns a primary key, which must then be used to descend the primary structure to reach the row — an extra tree traversal per row, on every query. Neither design is free; they move the cost between the write path and the read path. ## Additional cost even when keys don't change - **Old versions and dead entries.** Whether or not index keys change, updates create garbage: superseded row versions and, when relocation occurs, index entries pointing at versions no longer needed. A background reclaim process must find and remove them. Heavy update rates on a heavily-indexed table generate reclaim work proportional to the index count. - **Log volume.** Every index entry added or removed produces log records, which are written locally and shipped to replicas. - **Uniqueness checks.** Updating a column covered by a unique index requires a check against existing keys before the change is accepted, plus locking to prevent concurrent duplicates. ## What to do with this in design 1. **Separate the churn.** If a large row is indexed six ways but one column is updated thousands of times per second, moving that column into a narrow side table means the hot updates never touch the wide table's indexes at all. This is one of the highest-leverage fixes for update-heavy workloads. 2. **Don't index volatile columns without a strong reason.** A `status` column that every row passes through three times is the archetype: index it only if a genuinely selective query needs it, and prefer a narrow or restricted index shape. 3. **Leave page headroom on update-heavy tables** so in-place updates remain possible, and understand the read-side cost of doing so. 4. **Measure, don't assume.** Whether your updates stay in place is observable in engine statistics on most systems, and a falling in-place-update ratio is an early warning that pages have filled up and every update has quietly become a multi-index write. ## Answering this in an interview The answer that impresses is "logically no, physically it depends" followed by the row-relocation mechanism. Candidates who answer a flat "no, only indexes on changed columns are affected" are giving the textbook half; candidates who answer a flat "yes, all indexes are always updated" are giving the pessimistic half. The interesting content is the condition that decides between them.

  • What is a fill factor and when would you lower it?
    It is the percentage of each page filled when the table or index is built or rebuilt, with the remainder left free for future growth. Lowering it on an update-heavy table leaves room for new row versions to stay on the same page, avoiding index fan-out and page splits. The cost is lower data density, so scans and range reads touch more pages, which makes it a poor choice for append-only or read-mostly tables.
  • What is the tradeoff of secondary indexes storing the primary key instead of a physical row address?
    Storing the primary key makes rows relocatable without touching any secondary index, so updates and reorganisations never fan out, and the design is naturally paired with a clustered primary structure. The cost is on reads: every secondary index match yields a key that requires a second descent through the primary structure to fetch the row, roughly doubling the lookup work per matched row.
  • How does this affect the decision to index a mutable `status` column?
    It raises the bar considerably. Every status transition is an index entry delete plus insert at a different sorted position, and if it forces the row version to relocate, every other index on the table pays too. The index has to earn that against a real, frequent, selective query — and a restricted index covering only the rare states is usually the better shape.

saying these in an interview costs you the question

  • Answering a flat "no" without knowing that row relocation can force maintenance on all indexes.
  • Answering a flat "yes, all indexes are always rewritten" and missing the in-place optimisation.
  • Not realising updates create dead versions and entries that a background process must reclaim.
  • Thinking fill factor only affects storage size and not update behaviour.
  • Believing that an index on a frequently updated column is cheap because the row count does not change.

context

resolving links…