How do we decide whether an index is worth its write amplification cost to keep?

For discretionary performance indexes, compare query benefit with write and maintenance cost. First preserve indexes needed to enforce uniqueness or integrity rules, even when they have little read traffic. The right test is not “does the planner ever use it?” but “under representative insert, update, and delete load, does this index still produce a net gain after extra DML overhead, reduced HOT opportunities, and ongoing maintenance work?” PostgreSQL’s docs make that tradeoff explicit: the system keeps indexes synchronized when the table is modified, that adds overhead to data manipulation operations, and indexes can prevent the creation of heap-only tuples (PostgreSQL index introduction, PostgreSQL HOT storage).

What does one more index cost?

Treat every secondary index as a permanent tax on writes. PostgreSQL keeps indexes synchronized when the table changes, so inserts, deletes, and many updates do extra work. That cost is most visible on hot tables, where even a modest read benefit may not justify the always-on maintenance burden. The index may still be worth it, but only if it serves a query that matters enough to justify paying that tax continuously (PostgreSQL index introduction).

Assign each discretionary index an owner query. Before considering removal, check constraint dependencies and rare workloads; query counters alone do not establish redundancy.

Which updates make an index unusually expensive?

Update-heavy tables are where bad indexes become visible. HOT eligibility requires that the update not modify columns referenced by the table’s indexes, excluding summarizing indexes such as BRIN, and that the page have enough free space. When HOT applies, PostgreSQL can avoid creating new index entries for the updated row version; summary indexes may still need updates. When HOT does not apply, creating and cleaning up row versions becomes more expensive (PostgreSQL HOT storage).

That means an index on frequently changing columns like status, updated_at, or a soft-delete flag can cost far more than an index on stable lookup keys. Fillfactor also matters here: PostgreSQL’s HOT documentation says decreasing a table’s fillfactor can increase the likelihood of sufficient page space for HOT updates. Lowering fillfactor helps only when the update is otherwise HOT-eligible; it does not make an index on a frequently changed column free.

Should we keep the broad index, or redesign it?

Before dropping an index, try to narrow its scope. Good alternatives are often cheaper than the original design: a narrower key, fewer covering columns, or a partial index for the rows the query actually needs.

Worked example: suppose orders receives constant status changes, and one dashboard query needs recent open orders. A wide index on (status, created_at, customer_id, total) might improve that dashboard, but every status change now has to maintain that index too. A better design could be a smaller partial index on open orders keyed by (created_at) with a predicate on status = 'open'. If that keeps dashboard latency acceptable, measure whether the smaller structure reduces overall maintenance; changing a predicate column still affects index membership and HOT eligibility.

The decision boundary is simple: keep the widest index only if smaller designs fail under a production-like test.

How do we change it safely?

Measure before and after with the same write mix, not just replayed reads. Make sure planner statistics are current before comparing plans; PostgreSQL’s index introduction notes that you might need to run ANALYZE regularly so the planner can make educated decisions. If you need to rebuild rather than drop, PostgreSQL’s REINDEX documentation says REINDEX CONCURRENTLY rebuilds without taking locks that prevent concurrent inserts, updates, or deletes, while standard REINDEX locks out writes until completion. The same page also lists bloat and changed storage parameters such as fillfactor as valid rebuild reasons.

Checklist:

• Name the exact query the index serves.
• Confirm that query is important in latency or throughput terms.
• Test write-heavy load with and without the index.
• Try narrower, partial, or less-covering variants first.
• Rebuild concurrently when maintenance, not existence, is the problem.
• Remove only discretionary indexes after dependency and workload checks.

Q: Is occasional index usage enough reason to keep it?
No. Usage is only a hint. Keep it only if the saved read work justifies its always-on write cost.

Q: Should we lower fillfactor instead of removing the index?
Only when HOT-eligible updates are being blocked by lack of page space. If the updated column is indexed, fillfactor does not remove the index-maintenance cost.

Next step: pick one update-heavy table, map each discretionary index to its owner query, and load-test the weakest candidate against a narrower or partial replacement.

Reviewed: 2026-09-06