PostgreSQL indexing mistakes I keep seeing
Three patterns that quietly slow down internal dashboards, and how to spot them early.
#Introduction
Internal dashboards rarely die loudly. They slow down quietly, week by week, until someone says the system feels heavy. Nine times out of ten I find the same three indexing mistakes.
#Wrong column order
A composite index on (created_at, warehouse_id) does not help a query that filters warehouse first. Put equality columns before range columns — the difference is a sequential scan versus an instant lookup.
#Indexes nobody uses
Every index slows down writes, and internal systems are write-heavy. Check pg_stat_user_indexes quarterly and drop what has zero scans — I have removed a third of a client’s indexes with no downside.
#Wrapping up
EXPLAIN ANALYZE before optimizing, index for your real queries, and re-check after the data grows. Ten minutes a quarter keeps the dashboard instant.
EXPLAIN ANALYZE SELECT * FROM stock_moves WHERE warehouse_id = 3 AND created_at > now() - interval '30 days'; -- Seq Scan on stock_moves? Add the composite index.