Database indexing strategy -- when and what to index

Missing indexes cause slow queries. Too many indexes slow down writes and waste storage. Developers either index everything or nothing.

Index strategy: (1) Index columns in WHERE clauses that filter large tables. (2) Index JOIN columns. (3) Index ORDER BY columns to avoid sort. (4) Use composite indexes matching query patterns (leftmost prefix rule). (5) Do NOT index: small tables, low-selectivity columns (boolean, status with few values), columns only used with functions (unless functional index). (6) Monitor: pg_stat_user_indexes shows unused indexes. (7) Covering indexes include all SELECT columns to avoid table lookup.

An index trades write performance and storage for read performance. Each index is updated on every INSERT/UPDATE/DELETE. The goal is the minimum set of indexes that cover your query patterns.