Finally wrapped my head around when NOT to add an index—sharing what clicked for me

I spent my first couple years as a developer thinking indexes were basically free performance. Slow query? Add an index. Still slow? Add another one. My tables looked like a porcupine.

Then I inherited a write-heavy application where INSERT performance had degraded to the point where users were timing out. Turned out the previous team had added 23 indexes to a table that received 50,000 writes per hour. Every single insert was updating 23 different data structures.

Here's the mental model that finally helped me:

Indexes are a trade-off, not a freebie. You're pre-paying with write performance and storage to get faster reads later. For read-heavy tables (product catalogues, reference data, user profiles that rarely change), index liberally. For write-heavy tables (event logs, analytics, audit trails), be surgical.

Composite indexes have an order, and it matters. An index on (status, created_at) is not the same as (created_at, status). The first column is the primary sort. If you're always filtering by status first, put it first. I've seen teams create multiple redundant indexes because they didn't realise a composite index on (a, b, c) can serve queries filtering on just (a) or (a, b).

Partial indexes are underused. If you're only ever querying WHERE status = 'active' and 90% of your rows are inactive, a partial index like CREATE INDEX idx_active_users ON users(email) WHERE status = 'active' gives you a fraction of the storage cost with the same lookup speed.

The thing that really drove it home: run EXPLAIN (ANALYZE, BUFFERS) on your actual queries and look at whether your indexes are even being used. I found three indexes on a production table that hadn't been touched in months because the query planner had decided a sequential scan was faster anyway.

What's the most expensive indexing lesson you've learned? Or if you're still figuring this out, what's confusing you about them?