--- title: "Why Adding More Indexes Eventually Makes Things Worse" published: 2026-03-11T12:36:20.000-04:00 updated: 2026-03-11T14:17:35.000-04:00 excerpt: "Every Postgres index is a flat tax on every insert. At high ingestion rates, that tax is the whole problem." tags: PostgreSQL, PostgreSQL Performance authors: Matty Stratton --- > **TimescaleDB is now Tiger Data.** The pattern is familiar. A query is slow. You run `EXPLAIN` and see a sequential scan. You add an index. The query drops from seconds to milliseconds. You do this a dozen times over two years and it works every time. Then write latency starts climbing and you can't figure out why. The queries are fast. The schema looks clean. Nothing is obviously wrong. Pull up `pg_stat_user_indexes`. Count your indexes. Now think about what happens at the storage layer every time a row lands in that table. The indexes didn't stop helping reads. They started hurting writes. Every index is a flat tax on every insert: one extra write operation per row, every time, no exceptions. At low ingestion rates, the tax is invisible. At high ingestion rates, it's the whole problem. ## What actually happens when you insert a row No handwaving here. Let's walk through the mechanics. A single `INSERT` into a table with five indexes doesn't write once. It writes six times: one heap tuple to the table's data pages, and one B-tree leaf page insertion per index. Each index insertion traverses the B-tree from root to leaf, finds the correct position, and writes the new entry. If the target leaf page is full, it splits. A split can cascade up the tree. Then there's WAL. One heap insert record. Five index insertion records. If it's the first modification to a page since the last checkpoint, Postgres writes a full 8 KB page image on top of all that. At one insert per second, this is completely invisible. At 50,000 inserts per second with five indexes, you're looking at 300,000 write operations per second. Not 50,000. Six times the logical write rate, minimum. That's your write amplification number. For this table configuration: 6x. More indexes, higher multiplier. ## The math that makes this concrete Take a table with five indexes and a 1 KB row. The heap tuple costs 23 bytes of header plus your 1,024 bytes of row data plus a 4-byte `ItemIdData` pointer. Each of the five B-tree index entries adds roughly 40 to 80 bytes. Then WAL: approximately 1.2 KB covering the heap insert plus all five index insertions. Add it up and you're writing roughly 2.5 to 3.5 KB for every 1 KB of logical data. At 50K inserts/sec, that's 125 to 175 MB/sec of actual I/O for 50 MB/sec of application data. The index tax at work. Now add two more indexes because a couple of new dashboard queries need covering indexes. You're at seven. The multiplier goes up. The WAL volume goes up. Write latency goes up. Autovacuum has more index pages to scan and maintain. The relationship is linear per index, but the effect compounds with ingestion rate. At 1K inserts/sec, two extra indexes barely register. At 100K inserts/sec, they're a real cost. Here's what the math looks like across different configurations: | Indexes | Write ops/sec @ 10K inserts | Write ops/sec @ 50K inserts | Write ops/sec @ 100K inserts | | 1 | 20,000 | 100,000 | 200,000 | | 3 | 40,000 | 200,000 | 400,000 | | 5 | 60,000 | 300,000 | 600,000 | | 7 | 80,000 | 400,000 | 800,000 | | 10 | 110,000 | 550,000 | 1,100,000 | The numbers are approximate (real-world I/O depends on page splits, full-page writes, and your specific index types), but the pattern is clear. Each additional index is a flat tax on every insert. The tax rate doesn't change. The bill does. ## Why timestamp indexes have a specific problem B-tree behavior for monotonically increasing keys is worse than for random keys. And most time-series tables insert in timestamp order. With a random key distribution, new inserts scatter across the B-tree's leaf layer. Any given leaf page gets a roughly even share of new entries. Splits happen, but they're spread out. With a timestamp key, every insert goes to the rightmost leaf page. The same page, over and over. That page fills up and splits. The new rightmost page fills up and splits. This is called a "hot right edge," and it means B-tree index maintenance for timestamp columns involves constant page splits concentrated in one area of the tree. The old leaf pages that were once the rightmost page sit mostly empty but remain allocated. Index size grows faster than data size. The index bloat you see in pg\_stat\_user\_indexes is a direct result of this pattern, not random fragmentation. For non-timestamp indexes on the same table (device ID, metric name, sensor type), inserts scatter across the tree instead, which means random I/O rather than sequential. So you get two different flavors of write overhead hitting the same table simultaneously: constant splits on the timestamp index, random I/O on everything else. ## The feedback loop All of that overhead is manageable if it stays constant. The problem is that it doesn't. It self-reinforces. You add indexes to fix slow queries. Write amplification increases. Write latency creeps up. Bloat accumulates faster. Autovacuum fires more frequently and has more index pages to clean. Autovacuum competes with your writes for I/O bandwidth. Write latency climbs higher. Slower writes mean rows sit in the buffer longer. Buffer pressure increases. The query performance you were trying to protect starts degrading anyway, now from I/O contention rather than missing indexes. The response is usually to check query plans again. Some queries have gone back to sequential scans because statistics are stale or the planner is making different cost estimates under load. So you add another index. The cycle repeats. This loop runs slowly enough that the connection between each index addition and the eventual write degradation is hard to see. Six months can pass between the two events. By that point, you've forgotten which indexes were added and why, and the symptom looks like a completely different problem. ## The diagnostic questions Before adding the next index, ask these: **How many indexes does this table already have?** Pull `pg_stat_user_indexes` and look at `idx_scan`. Indexes with low scan counts are paying full write overhead for queries that run rarely or never. **What's the actual write rate on this table?** Low ingestion rate tables can carry many indexes without much penalty. The math only gets ugly at high sustained rates. If you're inserting 100 rows/sec, ten indexes are probably fine. If you're inserting 50K rows/sec, every index counts. **Is the slow query a read problem or a write problem?** Adding an index to fix a slow query while write amplification is already the bottleneck treats the symptom and makes the underlying condition worse. **What's the index bloat trend?** Growing index size relative to table size, especially on timestamp columns, is the fingerprint of the hot right edge problem. You can measure it directly with `pgstattuple` or by comparing `pg_relation_size` for the index against the table over time. **Could a different query shape eliminate the need for this index?** Sometimes the answer is restructuring the query or adjusting the access pattern, not adding another index to support the query as written. ## When you're past the point where index pruning helps You can drop indexes with low idx\_scan counts. You can consolidate partial indexes. You can audit and remove redundant coverage. All of that is correct and worth doing. But for a table with continuous high-frequency ingestion, even a minimal index set still generates substantial write amplification. Three carefully chosen indexes on a 50K inserts/sec table is still 200K write operations per second. WAL volume is still 3–5x logical data volume. Autovacuum is still competing for I/O. Index pruning buys back headroom. It doesn't change the architecture. The write amplification problem for this class of workload is in the storage model itself. Row-based heap storage with B-tree indexes is how Postgres handles every table. It's the right design for most workloads. For sustained high-frequency, append-heavy ingestion, the overhead is intrinsic. It's not a configuration problem you can tune your way out of. This is what changes when the storage model changes. The reason the index tax is so expensive in row-based storage is that every row is an independent write event. One heap insert, one WAL record, one B-tree traversal per index. The cost is per-row because the storage is per-row. Columnar storage changes the unit of work. Instead of writing one row at a time, it batches thousands of row versions into a single segment before writing. One WAL record covers the whole batch. Index maintenance happens at the segment level, not the row level. The per-row tax that makes five indexes expensive at 50K inserts/sec gets amortized across thousands of rows per write. Write amplification drops from the 3 to 5x range to near 1:1. That's not a tuning improvement. It's a different cost structure for the same logical operation. We covered the full architecture in [The Postgres Optimization Treadmill](https://www.tigerdata.com/blog/postgres-optimization-treadmill), which walks through why these constraints exist in row-based Postgres and what it looks like when the storage layer is built for this workload pattern from the start. ## The bottom line Every index you've ever added was the right call at the time. That's not the argument here. The point is that the index tax is a _real cost_ with a specific multiplier, and that multiplier matters a lot more at 50K inserts/sec than it does at 500. If write latency is climbing on a table that looks well-indexed, pull the insert rate and count the indexes. Do the multiplication. The answer is usually sitting right there in the numbers. And if those numbers show you're paying five or more index taxes on every row, with no signs of the data slowing down, the question isn't which indexes to drop. It's whether the per-row cost structure is the right one for the workload.