Database Indexing: When and What to Index

Indexes are like the index in a book. Without one, you gotta read every page to find what you want. With one, you jump straight to the right spot.

But heres the thing nobody tells you - indexes arent free. Every index you add slows down your writes. So how do you know what to index?

How Indexes Work

Without an index, the database reads every single row. With an index, it uses a B-tree structure to find rows in O(log n) time. Thats the difference between milliseconds and seconds.

What to Index

Always index:

Primary keys (done automatically)
Foreign keys (not automatic in PostgreSQL!)
Columns in WHERE clauses
Columns in JOIN conditions

-- Foreign keys need manual indexing in Postgres
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

Index when needed:

Columns used in ORDER BY
Columns in GROUP BY
Columns with high selectivity

The Selectivity Rule

Index columns that filter out most rows. Thats called high selectivity.

Indexing a boolean is_active column? Probably useless. 50% of rows match, so the database might just scan anyway.

Composite Indexes

When you query multiple columns together, use composite indexes:

-- Bad: Two separate indexes
CREATE INDEX idx_user ON orders(user_id);
CREATE INDEX idx_status ON orders(status);

-- Good: One composite index
CREATE INDEX idx_user_status ON orders(user_id, status);

Order matters! The leftmost column should be the most selective or most commonly queried.

-- This query uses the index
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

-- This also uses it (leftmost prefix)
SELECT * FROM orders WHERE user_id = 123;

-- This DOESNT use it efficiently
SELECT * FROM orders WHERE status = 'pending';

Finding Missing Indexes

PostgreSQL tells you when queries are slow:

-- Find slow queries
SELECT query, mean_time, calls
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 20;

-- Check if query uses index
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123;

Look for Seq Scan in the output. Thats a full table scan - usually means missing index.

The Cost of Too Many Indexes

Every write updates every index. 10 indexes? 10x the write overhead. Ive seen tables with 30+ indexes where inserts took seconds.

Quick Checklist

Before adding an index:

[ ] Is this column actually in WHERE/JOIN/ORDER BY?
[ ] Does the column have good selectivity?
[ ] Will it be used frequently?
[ ] Have you checked EXPLAIN ANALYZE?

Before production:

[ ] Test with production-like data volume
[ ] Monitor index usage after deployment
[ ] Remove unused indexes periodically