---
title: "Secondary index"
slug: "secondary-index"
category: "glossary"
tags: ["glossary", "backend", "database", "postgres", "performance", "query-optimization"]
status: "stable"
last_updated: 2026-05-14
summary: "A secondary index maps non-primary-key column values to row locations, enabling fast lookups and sorted scans without a full table read."
related:
  [
    "[[backend/postgres]]",
    "[[backend/prisma]]",
    "[[glossary/foreign-key]]",
    "[[glossary/materialized-view]]",
    "[[glossary/deadlock]]",
    "[[cheatsheets/postgres-explain]]",
  ]
---

## Overview

This page is the atomic definition. Index strategy and query planning live at [[backend/postgres]] and [[cheatsheets/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

```sql
-- 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

- [[backend/postgres]] - PostgreSQL index types and planner statistics.
- [[cheatsheets/postgres-explain]] - how to read `EXPLAIN` output to verify index use.
- [[glossary/foreign-key]] - FK columns always need a secondary index on the child table.
- [[glossary/materialized-view]] - materialized views are often indexed for fast refresh.
- [[glossary/deadlock]] - index scans acquire row-level locks that can cause deadlocks.

## Citing this term

> See [[glossary/secondary-index|Secondary index]] (llmbestpractices.com/glossary/secondary-index).

## Related

- [[backend/postgres]]
- [[backend/prisma]]
- [[cheatsheets/postgres-explain]]
- [[glossary/foreign-key]]
- [[glossary/materialized-view]]