What is a covering index, and how does it avoid a key lookup?

The problem a covering index solves

CREATE INDEX ix_orders_customer_id ON orders(customer_id);

SELECT id, customer_id, total FROM orders WHERE customer_id = 42;

The index on customer_id quickly finds matching rows, but total isn't in that index. So for every matching row, the engine must do an extra key/bookmark lookup back to the full table (or clustered index) just to fetch total. On a query returning many rows, that's many extra random I/Os.

Making the index cover the query

-- Option A: add 'total' as a regular index column
CREATE INDEX ix_orders_customer_covering ON orders(customer_id, total);

-- Option B (SQL Server): INCLUDE non-key columns at the leaf level only
CREATE INDEX ix_orders_customer_covering ON orders(customer_id) INCLUDE (total);

-- Option B (PostgreSQL): the equivalent is INCLUDE in CREATE INDEX (v11+)
CREATE INDEX ix_orders_customer_covering ON orders(customer_id) INCLUDE (total);

Now the same query can be answered entirely from the index's leaf pages — an index-only scan — with no trip to the base table at all. Every column the query needs (customer_id to filter, total to return; id is implicitly available via the clustering key) is present in the index.

Key columns vs INCLUDE(d)/STORING columns

Putting total directly in the index key (Option A) makes it part of the sort order too. That enlarges the key, affects ORDER BY/range-scan usefulness, and duplicates it into every level of the B-tree. An INCLUDE/STORING clause (Option B) stores the extra column only at the leaf level, not in internal nodes, and doesn't affect sort order. This is generally the more efficient choice when the extra column is only needed for the SELECT list, not for filtering or sorting.

Caveats

• Covering indexes trade write cost and storage for read speed. Every additional column stored means more data duplicated and updated on every write. Don't cover every possible query; reserve this for genuinely hot, high-value queries.
• SELECT * defeats covering indexes almost by definition. The engine can't predict which columns you'll need in the future, and a covering index for "all columns" is just the whole table. Covering indexes work best paired with narrow, deliberate SELECT lists.
• PostgreSQL's index-only scans additionally require the visibility map to confirm a page's rows are all visible to the current transaction. Under heavy write/vacuum churn, PostgreSQL can silently fall back to a regular index scan (with key lookups) even when the index technically covers the query.