SQL-Engineering-Handbook

10 — Index Maintenance, Redundancy & Myths

Introduction

Every prior file treated indexing as a design-time decision: create the right index, verify it with EXPLAIN, move on. In production, an index is not a static artifact — it degrades under write load, can become redundant as schemas evolve, and is surrounded by more confidently-stated folklore than almost any other database topic. This file covers what happens to an index after it’s created: how it degrades, how to keep it healthy, how to spot when you have too many of them, and which commonly repeated claims about indexing are simply wrong.

Index lifecycle: healthy, degraded, maintained

Learning Objectives

Business Motivation

A transactions table (File 09’s banking case study) takes thousands of inserts and updates daily against its (account_id, occurred_at) index. Eighteen months in, with no code changes and no data growth beyond expected volume, query latency against that index has crept up 40%. This is index bloat/fragmentation — a maintenance problem, not a design problem — and no amount of re-reading Files 02-08 will fix it, because those files are about choosing the right index, not keeping one healthy.

Why Indexes Degrade

A B+Tree (File 02) stays balanced through page splits and merges as data is inserted, updated, and deleted. Over many write cycles, this produces:

Architecture Discussion

Fragmentation and bloat are consequences of how B+Trees handle in-place modification (File 02’s page-split mechanics) combined with how each engine handles row versioning:

Syntax

-- MySQL: rebuild a table (and all its indexes) to remove fragmentation
OPTIMIZE TABLE transactions;

-- MySQL: refresh cardinality/statistics without a full rebuild
ANALYZE TABLE transactions;

-- PostgreSQL: reclaim dead tuple space
VACUUM (ANALYZE) transactions;
-- PostgreSQL: rebuild an index from scratch (blocking unless CONCURRENTLY)
REINDEX INDEX CONCURRENTLY idx_transactions_account_time;

-- SQL Server: rebuild vs. reorganize depending on fragmentation level
ALTER INDEX idx_transactions_account_time ON transactions REBUILD;
ALTER INDEX idx_transactions_account_time ON transactions REORGANIZE;

-- Oracle: rebuild an index online
ALTER INDEX idx_transactions_account_time REBUILD ONLINE;

Syntax Breakdown

Fillfactor

FILLFACTOR (PostgreSQL, SQL Server) reserves empty space within each index page at creation/rebuild time, specifically to absorb future in-page updates without forcing an immediate page split. A table with frequent UPDATEs to indexed columns benefits from a lower fillfactor (e.g., 70-80%) traded against slightly larger initial index size; a mostly-static or append-only table should stay near the 100% default, since there’s little future in-page modification to reserve space for.

-- PostgreSQL: reserve 20% free space per index page for future updates
CREATE INDEX idx_transactions_account_time
    ON transactions (account_id, occurred_at)
    WITH (fillfactor = 80);

MySQL InnoDB has no direct FILLFACTOR equivalent; innodb_fill_factor influences page fill on some operations but is not a per-index tunable the way PostgreSQL/SQL Server’s is.

Monitoring & Maintenance Scheduling

Maintenance should be observed and scheduled, not run reflexively:

Scheduling: rebuild/vacuum during low-traffic windows for any operation with meaningful locking cost; prefer CONCURRENTLY (PostgreSQL) or ONLINE (Oracle) variants where available and where the extra time cost is acceptable, specifically to avoid a maintenance window at all for tables that can’t tolerate one (e.g., File 09’s audit_log, which is both high-write and compliance-critical to keep available).

Duplicate & Redundant Index Analysis

A composite index (a, b, c) already serves any query that a single-column index on a, or a composite index on (a, b), would serve — per the leftmost prefix rule (File 03). This means:

INDEX(a)        -- fully redundant if INDEX(a, b, c) exists
INDEX(a, b)     -- fully redundant if INDEX(a, b, c) exists
INDEX(a, b, c)  -- the superset — keep this one
INDEX(a, c)     -- NOT redundant — c is not a leftmost-reachable
                --  prefix continuation of (a, b, c) without b

(a, c) is the case that trips people up: it looks like a subset of (a, b, c)’s columns, but because c isn’t adjacent to a in the composite’s actual column order, a query filtering WHERE a = ? AND c = ? cannot use (a, b, c) to seek on c — it can only use the a prefix and must post-filter c, exactly as File 03 describes. (a, c) is a legitimately separate, non-redundant index if that query shape is frequent.

Practical redundancy check: an index is a candidate for removal if every column-order prefix it defines is also a prefix of some other existing index on the same table, with equal or better trailing-column coverage. MySQL’s sys.schema_redundant_indexes view (Performance Schema, MySQL 8.0+) automates exactly this check.

SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = DATABASE();

Index Myths

Myth: “More indexes always improve performance.” False — every index adds write cost (File 06) that compounds indefinitely, not once. Past a certain point, additional indexes on a write-heavy table make the system slower overall even as they speed up specific reads.

Myth: “Every column in a WHERE clause should be indexed.” False — a low-selectivity column (File 07) indexed in isolation is frequently ignored by the optimizer entirely, making the index pure write overhead with zero read benefit. Selectivity, not clause membership, determines indexing value.

Myth: “If EXPLAIN shows the query using an index, the query is fast.” False — File 08 covers this directly: an index can be used and still be a poor plan choice if statistics are stale, if the index only narrows the result marginally, or if the query still requires a large Using filesort/Using temporary step downstream of the index seek. “Uses an index” and “is fast” are correlated, not synonymous.

Myth: “A primary key index solves every performance problem on a table.” False — a primary key accelerates lookups by that key alone. A table queried predominantly by other columns (File 09’s orders.customer_id, for instance) gets no benefit from its primary key index for those queries; every table’s secondary access patterns need their own, separately designed indexes.

Myth: “Indexes are free once created.” False — this is File 06’s entire premise restated as a myth: every index costs storage indefinitely and write throughput on every future INSERT/UPDATE/DELETE touching its columns, forever, not just at creation time.

Business Scenarios

Common Mistakes

Interview Questions

  1. Why does an index degrade over time even without any schema changes?
  2. What’s the practical difference between ANALYZE TABLE and OPTIMIZE TABLE in MySQL — what does each actually fix?
  3. Why is VACUUM not optional maintenance in PostgreSQL, unlike in most other engines’ equivalent operations?
  4. Given INDEX(a, b, c), is INDEX(a, c) redundant? Justify your answer using the leftmost prefix rule.
  5. Refute this claim in your own words: “the query plan shows it’s using an index, so the query is optimized.”
  6. When would you choose a lower fillfactor for an index, and what are you trading away by doing so?

Summary

Index health is not a one-time design decision — fragmentation and bloat accumulate under normal write load and require engine-specific maintenance (OPTIMIZE TABLE, VACUUM, ALTER INDEX REBUILD) on a monitored, not reflexive, schedule. Composite indexes can become silently redundant as schemas evolve, and are worth auditing directly rather than assuming more indexes are automatically safer. Most indexing folklore collapses under the same scrutiny this module has applied throughout: “does this claim hold given how the optimizer actually makes decisions,” not “does this sound like reasonable advice.”

Practice

See 12_PRACTICE_PROBLEMS.md, Maintenance & Myths section.

Further Reading

See resources/documentation.md.

This site is open source. Improve this page.