Pattern: Database indexing strategy

Queries are slow because of missing or poorly designed indexes. Adding too many indexes slows writes. Need a systematic approach.

Follow these indexing guidelines:

1. Index columns used in WHERE, JOIN, ORDER BY:

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

1. Composite index column order matters:

-- Index on (a, b, c) supports:
-- WHERE a = ? YES
-- WHERE a = ? AND b = ? YES
-- WHERE a = ? AND b = ? AND c = ? YES
-- WHERE b = ? NO (leftmost prefix rule)
-- WHERE a = ? AND c = ? Partial (only uses a)

1. Covering indexes (avoid table lookups):

CREATE INDEX idx_cover ON orders(user_id, status) INCLUDE (total);
-- SELECT total FROM orders WHERE user_id = 1 AND status = 'done'
-- Served entirely from index!

1. Partial indexes (index subset of rows):

CREATE INDEX idx_active ON users(email) WHERE active = true;
-- Smaller index, faster for queries on active users

1. Don't over-index:

-- Each index slows INSERT/UPDATE/DELETE
-- Remove unused indexes:
SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;

1. Index anti-patterns:

-- Don't index columns with low cardinality (boolean)
-- Don't index columns you never query on
-- Don't create redundant indexes (a) + (a, b)

1. Monitor:

EXPLAIN ANALYZE <your query>;
-- Look for Seq Scan on large tables -> needs index

Good indexes can make queries 1000x faster. Bad indexes waste storage and slow writes. The leftmost prefix rule is the most commonly misunderstood concept.