Indexes aren't free — every one is a standing tax paid on writes, on disk, and on every planning cycle. They speed up reads by maintaining a second sorted copy of the data, and that copy has to be kept correct, stored, and considered. The senior framing: an index is a write-cost liability you justify with a concrete read pattern, not something you sprinkle on "just in case."
1. Write amplification
This is the big one. Every INSERT, and every UPDATE that changes an indexed column, must update every applicable index as well as the table.
- A table with 6 indexes turns one logical insert into 7 structural writes.
- Each index update is its own WAL traffic, its own page dirtying, its own potential page split.
-- inserting one order updates the heap PLUS all of:
CREATE INDEX ON orders (customer_id);
CREATE INDEX ON orders (created_at);
CREATE INDEX ON orders (status);2. Storage and cache pressure
An index can rival or exceed the table's own size. That space costs disk, but more importantly it competes for the buffer cache — pages spent caching a rarely-used index are pages not caching hot data, so over-indexing can slow the whole system, not just writes.
3. Planner overhead
More indexes on a table mean more candidate plans for the optimizer to enumerate and cost on every query against it. It's usually small, but on wide tables with many indexes it adds measurable planning latency, especially for short OLTP queries where planning is a real fraction of total time.
4. Maintenance overhead
Indexes bloat under updates/deletes, eventually needing REINDEX. They also need their own statistics and VACUUM work. More indexes, more of this.
The discipline
- Index for real, measured query patterns, not hypotheticals.
- Periodically drop the dead weight — find never-used indexes via
pg_stat_user_indexes(idx_scan = 0). - Prefer one well-ordered composite over several overlapping single-column indexes.
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY relname;