When and how to add database indexes: B-tree, GIN, partial, and composite indexes. Includes EXPLAIN ANALYZE examples and read/write tradeoffs.
When and how to add database indexes: B-tree, GIN, partial, and composite indexes. Includes EXPLAIN ANALYZE examples and read/write tradeoffs.
BeforeMerge offers hundreds of code review rules, guides, and detection patterns to help your team ship better code.
Indexes speed up reads at the cost of slower writes and additional storage. Knowing when and what to index is critical for database performance.
WHERE clauses frequentlyJOIN conditionsORDER BY with LIMITBest for equality and range queries.
CREATE INDEX idx_users_email ON users(email);
-- Used by:
SELECT * FROM users WHERE email = 'alice@example.com';
SELECT * FROM users WHERE created_at > '2024-01-01';
SELECT * FROM users ORDER BY created_at DESC LIMIT 20;Best for full-text search, JSONB, and array columns.
CREATE INDEX idx_posts_fts ON posts USING gin(fts);
CREATE INDEX idx_metadata ON items USING gin(metadata);
-- Used by:
SELECT * FROM posts WHERE fts @@ to_tsquery('typescript');
SELECT * FROM items WHERE metadata @> '{"status": "active"}';Indexes only rows matching a condition. Smaller and faster.
CREATE INDEX idx_active_users ON users(email)
WHERE is_active = true;
-- Only useful when query includes the same condition:
SELECT * FROM users WHERE email = 'alice@example.com' AND is_active = true;Multiple columns in one index. Column order matters.
CREATE INDEX idx_org_created ON posts(organization_id, created_at DESC);
-- Used by (leftmost prefix):
SELECT * FROM posts WHERE organization_id = 'abc' ORDER BY created_at DESC;
SELECT * FROM posts WHERE organization_id = 'abc';
-- NOT used by (skips first column):
SELECT * FROM posts WHERE created_at > '2024-01-01';Rule: Put equality columns first, range/sort columns last.
Always verify your index is being used:
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'alice@example.com';Look for:
Index Scan using idx_users_email on users (cost=0.29..8.31 rows=1 width=72)
(actual time=0.015..0.016 rows=1 loops=1)
Index Cond: (email = 'alice@example.com')
Planning Time: 0.085 ms
Execution Time: 0.032 msEvery index slows down INSERT, UPDATE, and DELETE:
| # Indexes | INSERT overhead |
|---|---|
| 0 | Baseline |
| 1-3 | Negligible (~5%) |
| 5-10 | Noticeable (~15-30%) |
| 10+ | Significant — audit and remove unused |
Find unused indexes:
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE '%pkey%'
ORDER BY pg_relation_size(indexrelid) DESC;