Postgres Indexing: When to Add, What to Avoid

Indexes are the highest-leverage performance tool in Postgres — and the easiest to misuse. Every index speeds one query shape and taxes every write. Here is how we decide what earns a place on disk.

Match the index to the query shape

• Equality + range on a high-cardinality column → B-tree (the default, and usually right)
• Full-text or JSONB containment → GIN (gin_trgm_ops for ILIKE, jsonb_path_ops for @>)
• Append-only time series scanned by recency → BRIN (tiny, fast to maintain)
• Hot subset (active orders, unread messages) → partial index WHERE status = 'open'

CREATE INDEX CONCURRENTLY idx_orders_open_created
  ON orders (created_at DESC)
  WHERE status = 'open';

-- Cover the query fully to enable index-only scans:
CREATE INDEX CONCURRENTLY idx_orders_covering
  ON orders (customer_id, created_at DESC)
  INCLUDE (total, status);

Find missing indexes with evidence

Enable pg_stat_statements and sort by mean_exec_time × calls, not by vibes. Run EXPLAIN (ANALYZE, BUFFERS) on the top five. Sequential scans on small tables are fine; sequential scans on million-row tables behind user requests are the backlog.

1. 01Create CONCURRENTLY — never lock writes for an index build
2. 02Measure with EXPLAIN (ANALYZE, BUFFERS) before and after, on production-shaped data
3. 03Revisit quarterly: workloads drift, and yesterday's perfect index is today's dead weight