You can make a hot query index-only by adding four more columns to an existing index on a write-heavy table. How would you decide whether that is worth doing, and what would you measure before and after?
answer
- Covering = targeted denormalization, paid on writes
- Benefit is a rate: rows × executions
- Update amplification breaks HOT-style optimizations
- Cache displacement hurts other queries
- Verify with heap fetches, roll out reversibly
basics
~20 sWeigh the read saving — eliminated per-row table fetches times the query rate — against the write cost: every insert, delete and update of those columns now maintains a wider entry, plus extra space and cache pressure. Measure query latency, write latency, index size and cache hit rate before and after.
solid answer
~1 minFrame it as a purchase: you are buying read latency with write cost and memory. **Value side.** How many rows does the query touch, how often does it run, and how many random row fetches disappear? A query running 500/s that fetches 2,000 scattered rows each time is worth a lot; one running hourly is worth nothing. Also check whether covering flips the plan shape entirely — turning a table scan into a bounded index scan is a step change, not a percentage. **Cost side.** Every added column is written on every insert and delete, and any update to one of them now maintains this index too — including breaking in-place/HOT-style update optimizations that only apply when no indexed column changes. Wider entries mean fewer per page, a bigger index, more cache displacement, longer rebuilds and larger backups. **Then measure**, before and after, over a real cycle: p95/p99 for the target query, write throughput and latency for the table's main write paths, index size, buffer-cache hit ratio, and — if the index is meant to be index-only — the heap-fetch counter, which decides whether you got the benefit at all. If the columns are only projected, attach them as non-key payload rather than extending the key.
code
sql · 8 lines-- 1. payload instead of extending the key
CREATE INDEX idx_orders_cover ON orders (tenant_id, created_at)
INCLUDE (total, currency, note, channel);
-- 2. partial index when the hot query always carries the same qualifier
CREATE INDEX idx_orders_open_cover ON orders (tenant_id, created_at)
INCLUDE (total, currency)
WHERE status = 'OPEN';go deeper
Know the shape of the tradeoff: covering makes the read faster and every write slower, and it uses more space.
Quantify both sides — rows fetched times execution rate against per-write maintenance — and name INCLUDE and partial indexes as cheaper alternatives.
Own the measurement plan: latency percentiles, write throughput, index size, cache hit ratio and the heap-fetch counter, gathered across a full cycle, with a reversible rollout.
Treat it as a capacity and architecture call — write amplification budget per table, cache working-set planning, an explicit rule for when covering is offered, and documentation so the index set stays intentional.
## What you are actually trading A covering index is a **targeted denormalization**: a narrow, ordered copy of some columns maintained synchronously by the engine. Like every denormalization, it makes one read path fast and every write path a little slower. The decision is a rate comparison, not a principle. ## Estimating the benefit honestly Three questions: 1. **How much random I/O disappears?** Roughly rows-matched per execution, minus what the cache would have absorbed. Fetching 5,000 scattered rows per execution is a real cost; fetching 5 is not — index-only scans buy little for small result sets, and people frequently add wide indexes to point lookups where the payoff is negligible. 2. **How often does it run?** Multiply by execution rate. Benefit is a rate, not a per-query number. 3. **Does the plan shape change?** If the predicate matches a large fraction of the table, the alternative is a full table scan; covering can turn that into a scan of a much smaller structure. That is the case where a wide index is most likely to be worth it. ## Estimating the cost honestly - **Insert/delete.** Every index on the table is maintained; a wider entry means more bytes logged and written, and more pages touched. - **Update amplification.** This is the subtle one. Engines optimize updates that touch no indexed column — PostgreSQL's HOT updates avoid touching indexes entirely when the new version fits on the same page and no indexed column changed. Adding a frequently-updated column to an index can disqualify a large share of updates from that optimization, so the cost is not proportional to the column's size but to how often it changes. - **Space and cache.** A wide index competes with the table and with other indexes for buffer cache. Displacing hot table pages can make *other* queries slower — the effect people forget to measure. - **Operational weight.** Bigger backups, slower rebuilds and reindexing, longer replica catch-up. ## Cheaper alternatives to consider first - **Trim the query's projection.** Often one rarely-needed column is the only reason covering fails; dropping it from the `SELECT` may be free. - **Payload instead of key.** If the columns are only projected, attach them as non-key columns so internal pages stay narrow and fanout stays high. - **Partial index.** If the hot query always carries the same qualifying predicate, a filtered index over that subset gives covering at a fraction of the size and write cost. - **Extend an existing key rather than adding a new index.** Reusing one key beats keeping two overlapping ones. ## What to measure, before and after - **Target query:** p95 and p99 latency, rows returned versus pages read, and the plan node actually chosen. - **Index-only effectiveness:** the heap-fetch counter. On a write-hot table this frequently reveals that the benefit never materialized because visibility checks still force row fetches — the single most common reason a wide covering index disappoints. - **Writes:** throughput and latency of the table's main insert/update paths, plus write-ahead-log volume, which is a good proxy for maintenance amplification. - **Memory:** buffer-cache hit ratio for the table and for other hot objects, to catch displacement. - **Size:** index size relative to the table, and its growth rate. Measure over a full cycle including batch windows, not a quiet afternoon. ## Making it reversible Build the new index online/concurrently, keep the old one until the plan has demonstrably moved, then drop the old one. Where the engine supports invisible/disabled indexes, use that for a reversible trial. Write down the decision — which query shape, expected benefit, measured cost — next to the schema, so the next engineer extends the key rather than adding a sibling index. ## What a strong answer sounds like Quantified benefit versus quantified cost; explicit mention of update amplification and cache displacement, not just "writes get slower"; the cheaper alternatives considered first; the heap-fetch check that verifies the benefit is real; and a reversible rollout with a documented rationale. Saying "no" when the query is a low-rate point lookup on a write-hot table is a good answer, not a cop-out.
- Why can adding one frequently-updated column to an index cost far more than its size suggests?Engines optimize updates that change no indexed column — PostgreSQL's HOT update can keep the new row version on the same page and skip index maintenance entirely. Once that column is indexed, those updates no longer qualify, so each one must insert a new entry into every index and leaves dead entries behind. The cost therefore scales with how often the column changes, not with how many bytes it occupies.
- How would you know the wide index failed to deliver its benefit?Check whether the plan actually chose the index-only path and, if it did, look at the heap-fetch count — a high count means visibility checks are still forcing per-row table access, so you pay the wider index for nothing. Pair that with before/after p95 latency for the target query and write latency for the table. If reads did not improve while writes did degrade, revert.
saying these in an interview costs you the question
- Judging the index only by the target query's latency and never measuring write paths
- Adding a hot, frequently-updated column to an index without considering update amplification
- Assuming a covering index helps point lookups that return a handful of rows
- Ignoring buffer-cache displacement effects on unrelated queries
- Deploying the wide index and dropping the old one in the same change, leaving no rollback