Stackbook Logo
data-stateestablished · low operational burden

Covering and Partial Indexes

Also known as: index-only-scan, filtered-index

Intent

Shape indexes so common queries are answered from the index alone and indexes stay small by covering only relevant rows.

Problem

A plain index finds matching rows but still requires a lookup into the table for other columns, doubling I/O on hot queries. Indexing every row, including those never queried, bloats storage and slows writes. Generic indexes leave performance on the table.

Forces

  • •Hot queries repeat and are worth optimizing individually
  • •Each index adds write and storage cost
  • •Rows queried are often a small subset of the table
  • •Wider indexes cost more memory

Solution

Overview

These two techniques refine ordinary B-tree indexes. A covering index lets a query finish without touching the table. A partial index stores entries only for rows you actually search.

How it works

PostgreSQL examples.

-- covering: filter on user_id, return status and total from the index
CREATE INDEX idx_orders_user_cover
  ON orders (user_id, created_at) INCLUDE (status, total);

-- partial: only unprocessed jobs are indexed
CREATE INDEX idx_jobs_pending
  ON jobs (created_at) WHERE state = 'pending';

EXPLAIN SELECT status, total FROM orders WHERE user_id = 7;
-- expect: Index Only Scan

If ninety-nine percent of jobs are done, the pending index is a hundredth of the size of a full one.

Column order still matters inside the key. Equality columns come first, then the range or sort column, and payload columns go in INCLUDE so they do not affect ordering. Index-only scans also depend on the engine knowing that pages are all visible to current transactions; in PostgreSQL, vacuum maintains this map, so a table with heavy updates may still read the heap. Review the pg_stat_user_indexes counters to find indexes with zero scans and drop them.

Pitfalls

  • Assuming the index is used without checking EXPLAIN output.
  • Writing a query predicate that does not imply the partial index condition.
  • Including large text columns in a covering index.
  • Never removing indexes after queries change.

Key takeaways

  • Cover hot queries to enable index-only scans.
  • Index only the rows you query with partial indexes.
  • Confirm usage with query plans and index statistics.
  • Prune unused indexes regularly.

✓ When to Use

  • •Hot read queries returning a few columns
  • •Tables where queries target a small subset, such as pending or active rows
  • •Read-heavy systems with spare write capacity

✗ When Not to Use

  • •Write-heavy tables where each extra index hurts
  • •Rarely executed queries
  • •Queries selecting many columns, making the index nearly as big as the table

Pros

  • +Index-only scans avoid table reads
  • +Partial indexes are small and cheap to maintain
  • +Targeted speedups for known queries

Cons

  • −Wider indexes use more memory and slow writes
  • −Partial index is used only if the query predicate matches
  • −Index set can sprawl without governance

Cost Profile

Infrastructure

Low - modest extra storage

Operational

Low - review index usage periodically

Cognitive

Medium - read query plans and predicates

Failure Modes

  • Query predicate differs slightly so the partial index is not chosen

  • Index-only scan falls back to table reads when the visibility map is stale

  • Dozens of overlapping indexes slow every write

Real-World Examples

Alternatives

    Related Patterns

    • b-tree-index
    • database-indexing

    Competency Domains

    data statereliability ops