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 onemail; 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.
Connections
- Part of
- Storage
- Learn first
- B-Tree
- Builds on this
- Inverted IndexLocal vs Global Secondary Index
- In real systems
- PostgreSQLMySQLMongoDBpgvector
Recall
These cards are also in your review deck, scheduled with spaced repetition.