Notesstorage🌱 seedling

Secondary Index

Also: Secondary Indexes · Non-Clustered Index · Composite Index · Covering Index · Multicolumn Index

An extra index, beyond the primary key, that finds rows by some other column or combination of columns. It is a sorted copy of those values with a reference back to each row, so it speeds the reads it matches and slows every write.

Why it exists

Without one, any query not on the primary key scans the whole table. Most real access paths (by email, by status, by date) aren't the primary key.

How it works

  • Usually a B-Tree of (indexed value → row reference). Values repeat, so engines append the row reference to the key to keep entries unique and duplicates adjacent.
  • The reference is either a physical location (Postgres heap tuple ID) or the primary key (InnoDB), which decides how updates hurt. See Clustered Index.
  • Composite index on (a, b, c): sorted by a, then b, then c. It serves filters on a leftmost prefix (a; a, b; a, b, c) as a contiguous range.
  • Covering index: holds every column a query needs, so it is answered from the index alone (an index-only scan). Postgres adds payload columns with INCLUDE.
  • Skip scan (Postgres 18, long in Oracle and MySQL 8): a filter on b alone seeks once per distinct a. Cheap only when a has few distinct values.

Trade-offs

Gains Costs
fast lookups, ranges and sorted output on that key every insert, delete and many updates write to it
index-only scans when covering disk, memory and cache pressure
enforces uniqueness when declared unique wrong column order makes it useless for some queries

In practice

  • PostgreSQL index-only scans skip the heap only for pages the visibility map marks all-visible; on hot tables they quietly fetch rows anyway.
  • In a sharded database, a secondary index is either local to each shard or global across them: see Local vs Global Secondary Index.

Pitfalls

  • Hiding the column: WHERE lower(email) = ? or an implicit cast can't use an index on email; index the expression.
  • Low selectivity: an index on a column with two common values is ignored by the planner but still paid for on every write.
  • Unused indexes are pure cost. Most databases report index usage; drop the dead ones.

Recall

These cards are also in your review deck, scheduled with spaced repetition.