Database Indexing Strategy - What to Index and When

Queries are slow but adding indexes randomly doesn't help and sometimes makes things worse. Need a systematic approach to database indexing.

Indexing decision framework:

When to add an index:

1. Columns in WHERE clauses (especially with high selectivity)
2. Columns in JOIN conditions
3. Columns in ORDER BY (avoids sort)
4. Columns in GROUP BY
5. Foreign keys (almost always)

When NOT to index:

1. Small tables (<1000 rows) - full scan is faster
2. Columns with low cardinality (boolean, status with 3 values)
3. Tables with heavy write workload (indexes slow writes)
4. Columns rarely used in queries

Index types (PostgreSQL):

-- B-tree (default): equality, range, sorting
CREATE INDEX idx_users_email ON users (email);

-- Partial index: only index what you query
CREATE INDEX idx_active_users ON users (email)
  WHERE status = 'active';
-- Smaller index, faster queries for active users

-- Composite index: column ORDER matters
CREATE INDEX idx_orders_user_date
  ON orders (user_id, created_at DESC);
-- Supports: WHERE user_id = 1 ORDER BY created_at DESC
-- Also supports: WHERE user_id = 1 (uses leftmost prefix)
-- Does NOT support: WHERE created_at > '2024-01-01' alone

-- Covering index (index-only scan)
CREATE INDEX idx_orders_covering
  ON orders (user_id) INCLUDE (total, status);
-- No table lookup needed for: SELECT total, status WHERE user_id = 1

-- GIN for full-text, arrays, JSONB
CREATE INDEX idx_tags ON posts USING gin (tags);

-- Expression index
CREATE INDEX idx_lower_email ON users (lower(email));

Diagnosis:

EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;
-- Look for: Seq Scan (missing index), Sort (could use index)
-- Check: actual rows vs estimated rows (stale stats -> ANALYZE)

Indexes trade write speed and disk space for read speed. The right indexes make queries 100-1000x faster. The wrong indexes waste space and slow down writes with no query benefit.

Optimizing database query performance