Covering Index

ELI5 — The Vibe Check

A covering index includes all the columns your query needs, so the database never has to look at the actual table. It's like putting all the answers on the index tab of a textbook so you never need to flip to the actual chapter. Index-only scans are the fastest scans because the index IS the data.

Real Talk

A covering index (or index-only scan) includes all columns referenced in a query within the index itself, eliminating the need for heap lookups. PostgreSQL supports this with the INCLUDE clause. Covering indexes trade additional storage and write overhead for significantly faster read performance on specific query patterns.

Show Me The Code

-- Covering index with INCLUDE (PostgreSQL 11+)
CREATE INDEX idx_users_email_covering
ON users(email)
INCLUDE (name, created_at);

-- This query uses index-only scan:
SELECT name, created_at FROM users WHERE email = 'alice@example.com';

When You'll Hear This

"That covering index turned our most expensive query into an index-only scan." / "Add INCLUDE columns to avoid heap fetches on your hot queries."