We Dropped 43 Indexes and Our Writes Got Twice as Fast

A client's PostgreSQL writes were getting slower every quarter. The table had 57 indexes. Only 14 of them were ever used. Every INSERT and UPDATE was paying a tax nobody had thought to audit.

The client's exact words were: "Our writes used to be fast. Now they're not. We didn't change anything." I've heard that last part enough times to know it's never true, but I also knew they believed it. From their perspective, the application code hadn't changed much. The schema looked the same as always. Traffic had grown, sure, but not dramatically — maybe 20% year over year.

What had changed, slowly and invisibly, was the number of indexes on their busiest table.

57 indexes on one table

The system was a logistics SaaS — order tracking, route planning, warehouse management. The core shipments table had about 40 million rows and was the center of gravity for nearly every operation. It got roughly 800 inserts per minute during business hours, plus a comparable number of updates as shipments moved through their lifecycle.

I was brought in to investigate why write latency on the shipments table had crept from 4ms to 18ms over the past year. Not catastrophic, but the trend line was pointed in the wrong direction, and the team was worried about the holiday surge.

My first move was checking the table structure. I ran a quick count on pg_indexes:

SELECT count(*) FROM pg_indexes
WHERE tablename = 'shipments';

I stared at that number for a moment. Then I pulled up the actual index list and started scrolling. There were single-column indexes, composite indexes, partial indexes, expression indexes. Some had names that told a story — idx_shipments_customer_id, sure, that makes sense. Others were less helpful: idx_shipments_temp_fix, idx_shipments_reporting_2024q3, idx_shipments_old_status_lookup.

The table had accumulated indexes like a rental house accumulates coats in the hallway closet. Everyone hangs one up, nobody ever takes one away.

The write-time tax

Every index on a table is a promise the database has to keep. When you insert a row, PostgreSQL doesn't just write to the heap — it updates every single index on that table. Same for updates on indexed columns. For the shipments table, each INSERT triggered 57 separate index maintenance operations. Each UPDATE on a tracked column did the same.

This isn't free. Index maintenance means B-tree traversals, page splits, WAL writes. With 57 indexes, each write was doing roughly 15x more index I/O than a table with three or four well-chosen indexes. The cumulative cost was the difference between that 4ms and 18ms.

Finding the dead weight

The fix was obvious in principle — drop the unused indexes. The hard part was figuring out which ones were actually unused. "Nobody uses that index" is easy to say and dangerous to get wrong.

PostgreSQL tracks index usage in pg_stat_user_indexes. The idx_scan column counts how many times the planner has chosen an index for a scan since the last statistics reset. I checked when stats had last been reset (never, as it turned out — the database had been running for 14 months since the last restart), which meant the scan counts were a solid picture of real-world usage.

SELECT
  indexrelname AS index_name,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
  AND relname = 'shipments'
ORDER BY idx_scan ASC;

The results were stark. 43 of the 57 indexes had an idx_scan of zero. Not low — zero. In 14 months of production traffic, the query planner had never once chosen them. A few more had scan counts in the single digits, probably from one-off manual queries someone ran months ago.

Those 43 dead indexes occupied 12 GB of disk space and were being dutifully maintained on every single write.

How it got this way

I talked to the team about the history and pieced together the pattern. Over two years of development, the usual cycle had played out. A developer hits a slow query. They add an index. The query gets faster. The migration file goes into version control. The PR gets merged. Nobody ever goes back.

Some indexes were created for queries that no longer existed — features that got rewritten or removed. A few were from a reporting system that had since been migrated to a separate read replica with its own schema. The _temp_fix index was eight months old. The _2024q3 one was created for a one-time data backfill.

The team wasn't careless. They just didn't have a process for index lifecycle management, which is true of almost every team I've worked with. Creating an index is a visible event — it's in a migration, it goes through review. Dropping one feels risky and gets no applause, so nobody does it.

The cleanup

I didn't drop all 43 at once. We took it in rounds. First, we dropped the 28 indexes with zero scans that referenced columns already covered by other composite indexes. No ambiguity there — they were pure redundancy.

Then we tackled the 11 indexes for defunct features. We cross-referenced each one against the current codebase, checking for queries that might use them. None did.

The last four required more care. They had zero scans but covered columns used in queries that were getting sequential scans instead. In two cases, the planner was choosing a different index that happened to be more efficient. In the other two, the queries were rare enough that a sequential scan was legitimately faster. We dropped all four.

Each round went through staging first, with a load test that replayed production traffic. Write latency dropped after every round.

The result

After removing all 43 indexes, average INSERT latency on the shipments table fell from 18ms to 8ms. UPDATE latency dropped by a similar ratio. Autovacuum times on the table went from 45 minutes to about 12 minutes, because there were fewer index pages to clean up. Disk usage dropped by 12 GB, which wasn't a huge deal financially but meant more of the working set fit in shared buffers.

The holiday surge came and went without issues.

The boring part that matters

We set up a quarterly index audit — a scheduled job that queries pg_stat_user_indexes and flags any index with fewer than 100 scans since the last check. It dumps the results into a Slack channel. Nothing fancy. The point isn't automation; it's making the invisible visible. Indexes accumulate because nobody's looking. A ten-line SQL query and a cron job turned out to be enough.

I keep coming back to a simple principle: anything you create in a database should have an owner and a reason to exist. Tables get that treatment naturally. Indexes almost never do. They're infrastructure that's easy to add and psychologically hard to remove, which means they pile up until someone notices the writes are slow and asks what happened.

When was the last time anyone audited the indexes on your busiest table?