Covering index

A covering index is an index that contains all the columns required to satisfy a query, eliminating the need to access the base table. It is a form of vertical partitioning encoded at the index layer: the database answers the query entirely from the index structure, which is typically smaller and more cache-friendly than the full table. The trade-off is increased write amplification — every column in the covering index must be maintained on every insert, update, or delete — and the risk that the query optimizer will fail to recognize that the index covers the query, producing an unnecessary table lookup. The covering index is therefore not merely a performance trick; it is a bet that the query workload will remain stable enough to justify the storage and maintenance cost.

The most common covering index is a B-tree whose key columns match the query's WHERE clause and whose included columns match the SELECT list. Modern query optimizers can perform an Index-only scan when the covering index is sufficient, but cardinality estimation errors can still cause the optimizer to choose a table scan instead. The covering index is a systems-level commitment to a specific query pattern.