A query that scans a million-row table is a query that will eventually ruin someone's day. Indexing is the single most impactful thing you can do for database performance — but it's also easy to overdo.
The golden rule
Index for your queries, not your columns. An index is only useful if it's actually used by the queries your app runs. Add an index, then run EXPLAIN ANALYZE and confirm the planner picks it up.
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 AND status = 'shipped';
Composite indexes and column order
When a query filters on multiple columns, a composite index helps — but order matters. Put the column with the highest selectivity first, and align with equality conditions before range conditions.
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
When NOT to index
- Small tables (the planner will just seq-scan anyway).
- Columns that are rarely filtered.
- Write-heavy tables where every index slows inserts and updates.
Specialized index types
PostgreSQL offers more than B-tree:
- GIN — great for
jsonband full-text search. - GiST — for geometric/range data.
- BRIN — for huge, naturally-ordered tables (time-series).
CREATE INDEX idx_products_search ON products USING gin(to_tsvector('english', name));
Measure, don't guess
The pg_stat_user_indexes view tells you which indexes are actually being used. If an index has zero scans over weeks, it's dead weight slowing down your writes. Drop it.
Indexing is a balancing act — but with EXPLAIN and a little patience, you can keep your queries fast without drowning in write overhead.