The real cost of an extra index in Postgres
· 7 min
Indexes tend to get treated as a free lunch, "slow? add an index", but every index is a second data structure the database has to maintain, not just a read shortcut.
Every table write is one more write per index
An INSERT, UPDATE, or DELETE on an indexed column also has to update that index's own B-tree. With N indexes, the write cost of that column is roughly multiplied by N+1, on write-heavy tables that adds up fast, and can end up costing more than the read gain is worth.
Disk space nobody measures until it hurts
The space taken up by composite indexes or indexes on wide columns can rival the size of the table itself. That affects the page cache hit rate (shared_buffers), since more disk pages are competing for the same cache, an indirect effect that slows down the rest of the system without any single query looking guilty.
More ways for the planner to get it wrong
More indexes mean more execution plans for the planner to consider, and outdated statistics or a skewed data distribution can make it pick a worse index than expected. More options isn't strictly better if the cost estimates behind the choice are off.
When the index actually pays for itself
It's worth checking `pg_stat_user_indexes` for indexes that are never actually used, preferring one composite index that covers several query patterns over several narrow ones, and only adding a new index after confirming with `EXPLAIN ANALYZE` that the query in question is really doing a sequential scan that matters.