What are the downsides of adding too many indexes to a table?

Indexes are not free — they're a deliberate tradeoff of write cost and storage for read speed, and over-indexing is a genuine, common production problem.

Write amplification

Every INSERT must add an entry to every index on that table. Every UPDATE that touches an indexed column must remove the old index entry and insert a new one (even if the underlying row didn't physically move). Every DELETE must remove entries from every index. A table with 10 indexes turns one logical INSERT into up to 11 physical write operations (1 for the row + 10 for the indexes).

-- If orders has 8 indexes, this single INSERT triggers 9 total index/data writes
INSERT INTO orders (customer_id, total, status, ...) VALUES (...);

Storage and cache pressure

Each index is a full, separate data structure — a table with several large composite indexes can easily have more total on-disk size in its indexes than in the actual table data. This also means the database's memory cache (buffer pool) has to compete between caching hot table data and hot index pages; excess unused indexes waste cache space that could otherwise hold frequently-accessed data.

Confusing the optimizer

More indexes mean more candidate plans for the optimizer to evaluate, and query planning time itself grows (usually negligible, but non-zero on very complex queries). More meaningfully, several overlapping/redundant indexes (e.g., (a), (a, b), and (a, b, c) all existing simultaneously when only (a, b, c) is needed, since it already covers queries that only need a or a, b) waste maintenance cost without adding real query benefit, since a composite index's leading-column prefix already serves those narrower queries.

Lock contention

In engines with more index-level locking overhead, concurrent writers touching the same index page (e.g., inserting into the "hot end" of a sequential index) can serialize on that page, and more indexes multiply the surfaces where this kind of contention can occur.

• Regularly audit indexes for ones that are never used by the optimizer (pg_stat_user_indexes in PostgreSQL, sys.dm_db_index_usage_stats in SQL Server) and drop them.
• Consolidate overlapping composite indexes into the widest one that covers all the narrower use cases, when column order allows it.
• Add indexes deliberately, backed by an actual slow query and its execution plan — not speculatively "just in case."
• Remember that a UNIQUE constraint and a FOREIGN KEY each typically create an index implicitly — don't double-count these when reasoning about how many indexes a table "really" has.