> ## Content Index > Fetch the complete content index at: https://debugly.dev/llms.txt > Use this file to discover other available public pages before exploring further. # The Index That Made Inserts Four Times Slower - URL: https://debugly.dev/too-many-indexes-slower-writes/ - Published: 2026-09-12T07:30:00.000Z - Updated: 2026-10-17T05:58:00.000Z - Description: Every index is a second write. On an insert heavy table, nine well meaning indexes turned a fast append into a small transaction against ten data… - Author: Rohit Bhadani - Tags: Performance, PostgreSQL, Databases The table took inserts at a healthy rate for years. Then a quarter of feature work added indexes, one per query that anyone complained about, and insert latency quadrupled. Nobody had changed the write path. The writes had simply acquired nine extra obligations. I have lost on-call weeks to this exact shape of slow burn, so the order below is the order I fix it in. Indexes are usually discussed as a read optimisation, and the cost side is mentioned as an afterthought. On an insert heavy table the cost is the whole story, because every index must be updated on every write, and that update is real IO, real WAL and real lock contention. This was Postgres 16.3 with an events table doing a few thousand inserts per second, and the diagnosis came from comparing write amplification before and after. ## The mechanics of the tax An insert into a table with no indexes writes one heap tuple and its WAL. With each additional index, the insert also inserts a key into that index's B tree, which means finding the right leaf page, possibly splitting it, and writing WAL for that too. So the cost of an insert grows roughly linearly with index count, and the constant is not small, because index pages are hot and their splits are expensive. Nine indexes means each logical insert touches ten structures. At a few thousand inserts per second, that is tens of thousands of structure updates per second, and the buffer cache now has to hold nine index trees instead of one. The cruel part is that most of those index writes are for indexes that are rarely read. An index added for one slow report, queried twice a day, is being updated on every one of your millions of inserts. The read benefit is amortised over two queries. The write cost is paid on every write, forever. ## Seeing the amplification The blunt instrument is to compare the rate of index writes to heap writes. Postgres exposes per index statistics: ```sql SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes ORDER BY idx_scan DESC; ``` `idx_scan` is how often the planner chose the index. An index with a handful of scans and millions of rows is a write tax with almost no read benefit. Those are the candidates to drop. The deeper view is `pg_stat_user_tables`, where `n_tup_ins` tells you inserts and the difference in index sizes over time shows the churn. But the simple scan count versus table size comparison catches most offenders. Also look for duplicate and redundant indexes. An index on `(a)` is redundant if you already have `(a, b)`, because the leftmost prefix covers it. Redundant indexes are pure tax. There are queries to list indexes whose leading columns are prefixes of others, and running them on any mature schema is reliably depressing. ## Why the reads did not get faster The indexes were added to fix slow reads, and the reads stayed slow, which is what made the quarter feel like a loss. The reason is that the slow reads were not missing index problems. They were problems an index cannot fix: queries that fetch huge result sets, queries that must recheck the heap because of low visibility, and queries whose plan is fine but whose data is simply large. This is the same territory as [the index not being used](https://debugly.dev/postgres-index-not-being-used-explain-analyze/), but in reverse. There, the index exists and is ignored. Here, the index is maintained constantly and barely used. Both are cases of paying for an index without collecting its benefit. ## What actually fixed it **Drop the dead indexes.** Every index with near zero scans on a hot write table should go, after a watching period. Dropping an index is fast and reversible, which makes it the cheapest performance win in the database. The fear that "someone might need it" is exactly the hoarding instinct that created the tax. **Consolidate.** Replace several single column indexes with one composite index that serves the real query patterns, ordering columns by selectivity and by the queries' filter and sort shapes. One good index beats four hopeful ones, and it cuts the write amplification to a quarter. **Use partial and covering indexes.** If the hot query filters on a small slice, a partial index over just that slice is tiny and cheap to maintain. A covering index with INCLUDE lets an index only scan satisfy the query without the heap, which is the visibility map story in [why an index only scan still read the heap](https://debugly.dev/mvcc-index-only-scans-visibility-map/). **Make index addition a reviewed decision.** The rule that prevents the next quarter: an index is added with the query it serves, the plan that proves it is used, and a review of the table's write rate. On a cold table, add freely. On a hot write table, an index is a capacity decision, not a free optimisation. ## The number that matters The honest metric is inserts per second versus index count, watched together. When someone proposes an index on a hot table, the question is not "will this speed a read" but "is this read frequent enough to pay for a write on every insert, forever". Most proposed indexes fail that arithmetic, and the arithmetic is the whole review. ## The rule Indexes are not free metadata. Each one is a standing obligation on every write, paid whether or not the index is ever read. Audit scan counts against write volume, drop the dead ones, consolidate the hopeful ones, and treat each new index on a hot table as a capacity trade with a named beneficiary. The table will thank you on the write path, and the reads you actually care about will get their index, singular, chosen on purpose. For the read side of the same coin, where the plan ignores the index you paid for, see [postgres index not being used](https://debugly.dev/postgres-index-not-being-used-explain-analyze/). --- Published on [debugly.dev](https://debugly.dev/) by [Rohit Bhadani](https://debugly.dev/about/) | Founder of [Krova Cloud](https://krova.cloud/?ref=debugly.dev)