Index Design Strategies
Database indexes are like book indexes — they help you find information quickly without reading everything. But unlike book indexes, database indexes must be maintained as data changes, creating a trade-off between read and write performance.
Index Types
Different index types suit different query patterns:
B-tree indexes are the default in most databases. They work well for equality comparisons (=) and range queries (<, >, BETWEEN). They're the right choice for most situations.
Hash indexes only support equality comparisons but are extremely fast for exact matches. Use them for columns where you only ever query with =.
GIN (Generalized Inverted Index) indexes excel at full-text search, array columns, and JSONB data in PostgreSQL. They're larger and slower to update but enable queries that B-tree can't handle efficiently.
GiST (Generalized Search Tree) indexes support geometric data and some full-text operations. They're specialized but powerful for spatial queries.
Composite Indexes
Indexes can cover multiple columns, but column order matters:
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
This index efficiently supports queries filtering on:
user_idaloneuser_idANDstatustogether
But it does not help queries filtering only on status. The index is organized by user_id first — finding all pending orders requires scanning the entire index.
Think of it like a phone book sorted by last name, then first name. Finding all "Smiths" is fast. Finding "John Smith" is fast. Finding all "Johns" regardless of last name requires reading the whole book.
When to Create Indexes
Columns in WHERE clauses that filter large tables benefit most from indexes. If you frequently query WHERE user_id = ?, index user_id.
JOIN conditions should be indexed. Foreign keys are common join targets — index them.
ORDER BY columns can use indexes to avoid sorting. This matters for queries returning many rows.
Foreign keys should almost always be indexed. Without indexes, deleting a parent record requires scanning the entire child table.
When NOT to Index
Small tables don't benefit much. If the table fits in memory, sequential scans are fast enough.
Low-cardinality columns — those with few unique values like boolean flags — make poor index candidates. An index on a status column with only three possible values rarely helps.
Frequently updated columns require index maintenance on every update. Consider whether the read performance gain justifies the write overhead.
Too many indexes slow down inserts and updates. Each index must be maintained when data changes. Profile your actual workload to find the right balance.
-- Check index usage in PostgreSQL
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE schemaname = 'public';
Unused indexes waste space and slow writes — remove them.