A table has 14 indexes and writes have become slow. How do you decide which to remove?

The approach

1. Get usage statistics, not opinions. Every major engine reports index scan counts — PostgreSQL's pg_stat_user_indexes, SQL Server's sys.dm_db_index_usage_stats, MySQL's sys.schema_unused_indexes. An index with zero scans since the last statistics reset, over a period covering month-end and reporting cycles, is a candidate for removal.

The caveat that matters: check the window covers everything. An index used only by a quarterly job looks unused in a 30-day sample, and dropping it turns a two-minute report into a two-hour one.

2. Find redundant prefixes. An index on (a, b) makes a separate index on (a) redundant, because the composite serves any left-to-right prefix. This is the most common source of accumulation and the safest deletion — it removes write cost with no read consequence at all.

3. Find duplicates and near-duplicates. (a, b) and (b, a) are different and both may be needed; (a, b) and (a, b, c) usually means the first can go.

4. Check selectivity. An index on a low-cardinality column that the planner never chooses is pure write cost.

5. Look for constraint-backing indexes and leave them alone — dropping the index behind a unique constraint or foreign key breaks the constraint.

Before dropping anything

Make it reversible. Record the exact definition so it can be recreated. In engines that support it, disable or make the index invisible first rather than dropping — the planner stops using it, and if something degrades you re-enable in seconds instead of rebuilding for hours on a large table.

Drop one at a time, with a period of observation between. Dropping six at once and finding a regression means you do not know which one caused it.

Why the write cost is real

Every index is maintained on every write to its columns. Fourteen indexes means an insert does one table write plus up to fourteen index writes, each potentially causing a page split. It also inflates WAL volume, which slows replication and increases backup size — so the cost is not only local.

What a strong answer adds

Asking how the fourteen accumulated. Almost always: an index added per slow query, without reading the plan, and never revisited. The process fix — check the plan first, and review indexes when adding one — prevents recurrence, which the deletion alone does not.