Indexes as a workload trade-off

Understand

Mental model

An index is not a universal speed switch. It is an ordered access structure that can avoid scanning unrelated rows when predicates and ordering align with it. Every index also consumes storage, cache, vacuum work, and write amplification. The right question is which workload becomes cheaper overall.

Calibrate

What healthy usually looks like

Important query plans use selective, well-matched indexes where they reduce total work.
Duplicate and genuinely unused indexes are reviewed across a representative statistics window and all replicas.
Index growth, write cost, and vacuum impact remain proportionate to their read benefit.

Observe

Ask the database a bounded question

Find large indexes with few recorded scans so they can be investigated against the workload and statistics window.

SELECT
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC
LIMIT 40;

low observation cost

Interpret

Read the output

Start from a costly workload, then test whether an index changes its plan and total cost safely.
Design multicolumn indexes around real predicates and ordering rather than adding every referenced column.
Create production indexes with the lock and resource implications understood; `CONCURRENTLY` reduces blocking but costs more work and has failure states to clean up.

Avoid

Common traps

Dropping an index solely because `idx_scan` is zero on the primary.
Adding overlapping indexes without checking write amplification and cache pressure.
Using `EXPLAIN ANALYZE` on a write statement without accounting for the fact that it executes the statement.

Verify

Check your reasoning

Continue

Related runbooks

Verify independently

Sources

PostgreSQL 18: Indexes