Overview
This page is the atomic definition. Index strategy and query planning live at postgres and postgres-explain.
Definition
A secondary index is a separate data structure (typically a B-tree in PostgreSQL) that maps one or more non-primary-key column values to the physical row location (heap tuple identifier). The query planner uses the index to satisfy WHERE, ORDER BY, and JOIN conditions without scanning every row in the table (a sequential scan). Index overhead: every INSERT, UPDATE, and DELETE must update all affected indexes, adding write amplification. A table with ten indexes takes roughly ten times the write cost per row change. Index types in PostgreSQL: B-tree (default, covers equality and range queries), GIN (arrays, full-text, JSONB), GiST (geometric, PostGIS), BRIN (large sequential tables), Hash (equality only). Partial indexes (WHERE active = true) and expression indexes (LOWER(email)) cover specific access patterns with smaller index size.
When it applies
Add a secondary index to any column that appears in WHERE, JOIN ON, or ORDER BY clauses in slow queries. Confirm the index is used with EXPLAIN (ANALYZE, BUFFERS). Remove unused indexes; they add write overhead with no benefit.
Example
-- Partial index: only active users, much smaller than a full index.
CREATE INDEX users_email_active_idx ON users (email)
WHERE active = true;Related concepts
- postgres - PostgreSQL index types and planner statistics.
- postgres-explain - how to read
EXPLAINoutput to verify index use. - foreign-key - FK columns always need a secondary index on the child table.
- materialized-view - materialized views are often indexed for fast refresh.
- deadlock - index scans acquire row-level locks that can cause deadlocks.
Citing this term
See Secondary index (llmbestpractices.com/glossary/secondary-index).