Why Adding a Database Index Can Make Your App Slower
An index is the standard first answer to a slow query, and it’s usually right, but every index also has a real, measurable cost on the opposite side of the ledger: one documented benchmark found bulk inserts ran roughly 4x slower on a table with several extra indexes compared to the same table with only a primary key. For a read-heavy reporting table, that trade is obviously worth it. For a write-heavy table taking constant inserts, it can quietly become the actual bottleneck you’re trying to fix.
Key takeaways
- Every index has to be updated on every INSERT, UPDATE and DELETE, not just read That maintenance cost is real and measurable, a documented benchmark showed bulk inserts running roughly 4x slower with extra indexes present.
- A composite index usually beats two separate single-column indexes for the same query One composite index on (country, status) typically outperforms separate indexes on country and status individually, since it needs only one tree traversal to filter on both.
- Fragmentation is a real, measurable maintenance need, not a one-time setup task A commonly cited threshold is rebuilding an index once fragmentation exceeds roughly 30%, since a fragmented index degrades read performance and increases disk I/O over time.
What an Index Actually Costs, Not Just What It Saves
An index is a separate data structure maintained alongside your table specifically so the database engine can locate matching rows without scanning every row, which is exactly why it speeds up reads. But that same separate structure has to be kept in sync with the table's actual data, meaning every INSERT, UPDATE, and DELETE against the table now also has to update every index defined on it, not just write the new data once. A documented, if informal, benchmark comparing a table with only a primary key against the same table with several additional indexes found bulk insert operations running roughly 4x slower with the extra indexes present, a concrete, measurable cost, not a theoretical one.
This is precisely why over-indexing is a specifically named, common failure pattern in database performance guides, distinct from under-indexing: a table can genuinely have too many indexes, each individually justified by some query it once helped, collectively adding enough write-side overhead to become the actual bottleneck. The fix isn't avoiding indexes, it's treating each one as a real cost-benefit trade-off tied to your table's actual read/write ratio, not a free performance upgrade to apply liberally.
Run EXPLAIN ANALYZE on your actual slow query before adding an index, then run it again after, comparing real execution plans rather than assuming the index helped. This also surfaces whether the optimizer is even choosing to use your new index at all, sometimes it won’t, if a full table scan remains cheaper for your specific data distribution.
Finding Indexes That Are Actually Costing You Something
On PostgreSQL specifically, enabling the pg_stat_statements extension and querying it directly identifies which queries are consuming the most total execution time across your workload, your genuinely highest-impact optimization targets, rather than whichever slow query happened to get noticed recently. Running EXPLAIN ANALYZE against those specific queries then shows exactly which filter columns lack an index and are forcing a full table scan, which is where a new index typically earns its keep rather than adding pure overhead.
The other side of that audit matters just as much: identifying indexes that are rarely or never actually used by any query, and removing them, since an unused index provides none of an index's read benefit while still paying its full write-side maintenance cost on every insert, update and delete against that table. This kind of index cleanup is consistently named as a straightforward, high-ROI database performance task specifically because removing dead weight has essentially no downside once you've confirmed a specific index genuinely isn't serving any live query pattern.
Practical Indexing Decisions
What to actually check before adding or keeping an index
A read-heavy reporting table and a write-heavy transactional table warrant very different indexing decisions for the identical column.
One well-ordered composite index usually outperforms several separate single-column indexes for queries filtering on multiple columns together.
Adding an index doesn’t guarantee the query planner will choose to use it over a full table scan.
Indexes degrade in effectiveness as underlying data changes accumulate.
An index that’s never actually queried still pays its full write-side maintenance cost.
Who Should Weight This Most Heavily
- EXPLAIN ANALYZE and pg_stat_statements are free, built-in tools giving a direct, measurable answer
- Composite indexes often solve the same problem as multiple single-column indexes at lower total cost
- Removing genuinely unused indexes is a low-risk, high-ROI cleanup task
- Over-indexing is a genuinely common, easy-to-fall-into failure pattern, not a rare edge case
- Index fragmentation requires ongoing maintenance, not a one-time setup decision
- The optimizer may not use a new index at all, wasting its write-side cost with no corresponding read benefit
Exploring the wider developer toolkit
See our full cloud and developer tools guide for database, API and infrastructure comparisons.
Our Sources
Where this comes from
The benchmark figures, fragmentation threshold, and tooling recommendations here are drawn from multiple independent 2026 database performance engineering guides and a documented informal insert-rate benchmark, cross-checked for consistency given genuine variance by specific database engine and workload.
-
Insert-rate benchmark cited directly
The roughly 4x slower bulk-insert finding drawn from a specific, documented comparative benchmark test, not a generic estimate.
-
Fragmentation and maintenance guidance cross-checked
The ~30% fragmentation rebuild threshold verified across multiple independent 2026 database performance guides.
-
No claims of our own database benchmarking
This article explains published methodology, tools and cited benchmark data; it does not present our own original performance testing.
Frequently Asked Questions
Frequently asked questions
Can adding a database index actually make my application slower?
Yes, specifically for write operations, every index has to be updated on every INSERT, UPDATE and DELETE against that table, and a documented benchmark found bulk inserts running roughly 4x slower with several extra indexes present compared to a table with only a primary key.
How many indexes are too many for one table?
There’s no universal number, it depends on your table’s read/write ratio. A read-heavy reporting table can reasonably carry many indexes, while a write-heavy transactional table should carry only the ones actually earning their keep on real, measured query patterns.
How do I know if an index is actually being used?
Run EXPLAIN ANALYZE on the queries you expect it to help, and confirm the query plan actually uses the index rather than a full table scan. On PostgreSQL, pg_stat_statements combined with index usage statistics can identify indexes no live query pattern is actually using.
How often should I rebuild fragmented indexes?
A commonly cited general threshold is rebuilding once fragmentation exceeds roughly 30%, though the exact right threshold varies by specific database system and workload characteristics.
Is one composite index better than several single-column indexes?
Usually yes, for queries that filter on the same combination of columns together, a single composite index like (country, status) typically outperforms separate indexes on country and status individually, since it resolves the filter with one tree traversal instead of combining results from two.
Final take
- Every index adds real overhead to every INSERT, UPDATE and DELETE, not just reads
- A documented benchmark found bulk inserts running roughly 4x slower with extra indexes
- EXPLAIN ANALYZE and pg_stat_statements give a direct, measurable answer instead of a guess
An index is a genuine trade, not a free performance upgrade, it speeds up reads while adding real, measurable overhead to every write against the same table, and a documented benchmark showing roughly 4x slower bulk inserts with extra indexes present makes that cost concrete rather than theoretical. Composite indexes, periodic fragmentation maintenance, and actively removing genuinely unused indexes are the practical levers that keep an indexing strategy earning its cost rather than quietly becoming the write-side bottleneck it was meant to prevent.
