Database Indexing Strategies That Actually Matter

Indexing is the single highest-leverage performance optimization in any database-backed application. A missing index can make a query 1,000x slower; a poorly designed index can waste gigabytes of storage and slow down writes. After optimizing databases across hundreds of client projects, these are the indexing strategies that make the biggest difference.

Composite Index Column Order Matters

For composite (multi-column) indexes, column order is critical. The index can be used for queries that filter on the leftmost columns but not for queries that skip them. The rule: put the most selective (highest cardinality) equality columns first, then range columns last.

-- For queries: WHERE status = 'active' AND created_at > '2025-01-01' AND user_id = ?

-- ✗ Bad: created_at first (range column before equality columns)
CREATE INDEX idx_bad ON orders(created_at, status, user_id);

-- ✓ Good: equality columns first, range column last
CREATE INDEX idx_good ON orders(status, user_id, created_at);

-- The good index supports all these queries:
-- WHERE status = 'active'
-- WHERE status = 'active' AND user_id = 123
-- WHERE status = 'active' AND user_id = 123 AND created_at > '2025-01-01'

Partial Indexes: Index Only What You Query

If 95% of your queries filter on active records, why index the inactive ones? Partial indexes include only rows matching a WHERE clause, dramatically reducing index size and improving both read and write performance.

-- Full index: 2.1GB covering all 50M orders
CREATE INDEX idx_orders_status ON orders(status, created_at);

-- Partial index: 180MB covering only active orders (5% of table)
CREATE INDEX idx_orders_active ON orders(created_at)
WHERE status = 'active';

-- 12x smaller, faster to scan, faster to update

Covering Indexes: Avoid Table Lookups

When all the columns your query needs are in the index, PostgreSQL can satisfy the query entirely from the index without touching the table (an index-only scan). This is a massive performance win for read-heavy queries. Use INCLUDE to add non-indexed columns to a covering index.

-- Query: SELECT name, email FROM users WHERE status = 'active' ORDER BY created_at DESC LIMIT 20

-- Regular index: finds rows by status, then looks up name/email from table
CREATE INDEX idx_users_status ON users(status, created_at DESC);

-- Covering index: returns name/email directly from index (no table lookup)
CREATE INDEX idx_users_status_covering ON users(status, created_at DESC)
INCLUDE (name, email);

When Not to Index

• Tables with fewer than 10,000 rows — sequential scans are often faster than index lookups on small tables
• Columns updated frequently in high-write workloads — every index slows down writes because the index must be updated too
• Low-cardinality boolean columns — an index on a column with only 2 values rarely helps (use partial indexes instead)
• Columns only used in INSERT-heavy tables with no reads — indexes are pure overhead without read queries

Indexing is an ongoing discipline, not a one-time task. As query patterns evolve, indexes must evolve too. Schedule quarterly index reviews: identify unused indexes (pg_stat_user_indexes.idx_scan = 0), find missing indexes (pg_stat_user_tables.seq_scan >> idx_scan), and right-size your indexing strategy.