Database Indexing Strategy: The Queries We Actually Optimize For
Most indexing advice starts from the schema and asks 'what columns get filtered on.' We've found it's more useful to start from the slow query log. On a recent Laravel project for a logistics client, EXPLAIN ANALYZE showed that 80% of query time came from six queries out of a few hundred distinct shapes. We indexed for those six and left the rest alone.
A composite index only helps if the query's WHERE clause matches its column order left to right. We had a status/created_at index that silently stopped being used once a query added a tenant_id filter in front of status — Postgres just fell back to a sequential scan on a 4 million row table. The fix was reordering the index to (tenant_id, status, created_at), matching how the application actually filters.
Every index you add is a write-path tax. On a high-throughput orders table doing around 200 inserts per second, we measured a 15% drop in insert throughput after adding a fifth index that was only used by an internal admin report run twice a day. We moved that report to a read replica with its own index instead of paying the cost on every write.
Partial indexes solved a specific problem for us: a soft-deleted_at column where 95% of rows were deleted but 100% of queries only cared about the live 5%. Indexing WHERE deleted_at IS NULL cut that index's size by roughly 18x and made the common query path faster without touching rows nobody was querying anyway.
We treat pg_stat_user_indexes as part of routine maintenance now, not a one-off audit. Any index with near-zero scans after a full release cycle gets flagged for removal. On one project this caught three indexes left over from a feature that was removed six months earlier — dead weight on every write with zero read benefit.
