Database Index Strategy — Vocabulary and Discussion Language

A composite index (multi-column index) covers multiple columns. Column order matters: a composite index on (last_name, first_name) supports queries filtering on last_name or (last_name, first_name) — but not queries filtering only on first_name (without last_name in the WHERE clause).

A covering index includes all columns referenced in the query (SELECT, WHERE, ORDER BY, JOIN). This allows an index-only scan — the database never touches the main table heap. PostgreSQL calls this 'Index Only Scan'; it dramatically reduces I/O for read-heavy workloads.

Index bloat occurs when MVCC dead tuples accumulate in index pages after updates/deletes. The index grows in physical size without corresponding live data growth. In PostgreSQL, VACUUM reclaims space but REINDEX is needed to truly compact a bloated index.

A partial index indexes only rows matching a condition: CREATE INDEX ON orders (user_id) WHERE status = 'pending'. This is much smaller than a full index and faster for queries that always filter on status = 'pending' — common for active records vs. archived data.

Cardinality = the count of distinct values. High cardinality (e.g., user_id with millions of unique values) → high selectivity → index is very effective. Low cardinality (e.g., boolean status column with 2 values) → low selectivity → full table scan may be cheaper than index scan.