Index everything? Index nothing? Here's what actually works

Indexing is one of those things everyone knows they should do and almost nobody does systematically. The result is usually two bad extremes: tables with no indexes (slow reads) or tables with indexes on every column (slow writes, wasted storage).

How Indexes Actually Work

An index is a sorted copy of a column (or columns) that the database maintains alongside the table. When you query WHERE email = '[email protected]', without an index the database scans every row. With an index, it binary searches the sorted copy and jumps directly to the row. For a million-row table, that's the difference between milliseconds and seconds.

The cost: every INSERT, UPDATE, and DELETE must also update the index. Heavy-write tables pay a real price for indexes that never get used for reads.

The Columns That Always Need Indexes

• Foreign keys: any user_id, order_id, category_id column you JOIN or filter on
• Columns in your most frequent WHERE clauses
• Columns you sort on—ORDER BY created_at DESC on a large table without an index is painful

Composite Index Order Matters

-- This helps: WHERE status = 'active' AND created_at > '2024-01-01'
CREATE INDEX idx_orders_status_created ON orders (status, created_at);

-- This does NOT help the same query:
CREATE INDEX idx_orders_created_status ON orders (created_at, status);

The leftmost column must match your filter first.

The Practical Test

Run EXPLAIN ANALYZE on your slow queries in PostgreSQL. Look for Seq Scan on large tables—those are candidates for indexing. Index Scan is what you want to see.

Don't add indexes speculatively. Add them when you have a slow query and evidence that an index will actually help.