Every write pays
An index is a promise your database has to keep on every single write.
Insert one row into a table carrying five indexes and the database does six pieces of work. The row itself, plus an entry in each index, and each of those is a walk down a tree, possibly a page split, all written to the recovery log first. Updates can be worse, because changing an indexed column means removing the old entry and adding a new one.
The rule of thumb you keep hearing holds: a table with ten indexes can write two to five times slower than the same table with two. Storage compounds it, and indexes on a busy table routinely outweigh the table.
The cost hides well. Each of your inserts still looks fast on its own. You find out at bulk-load time, or when replication starts falling behind, or when your log volume doubles overnight.
Indexes need a lifecycle
Give indexes the same lifecycle discipline you give code. Postgres records how often each one is used, and an index with zero uses after a month of real traffic is pure write tax. Drop it.
The accumulation pattern is archaeological, because it is archaeological. Every slow query in the product's history left an index behind, nobody has ever deleted one, and the table now carries eleven.
When the work is genuinely write-heavy, the structure itself changes. LSM-trees, the structure Cassandra and RocksDB use, gather writes in memory and flush them out as sorted files that never change. Scattered index updates become one long sequential write.
Pay for it on the read side, where a lookup may have to consult several files, and in the background work that merges them. B-tree against LSM is the same trade that adding or dropping an index is, reads against writes, settled in a different room.
Worked example
Diego's events table at an ad-tech company ingests 1,200 rows per second and carries 11 indexes accumulated over three years of dashboard requests. Replication lag starts brushing 30 seconds every evening peak, and bulk backfills that took an hour now take five. He queries pg_stat_user_indexes and finds five indexes with zero scans in six weeks, leftovers from retired features, holding 190 GB between them. Dropping them cuts insert latency from 9 ms to 4 ms and evening replication lag to under 2 seconds. For the quarterly backfill of 800 million rows he goes further: drop two more rebuildable indexes, load the data, recreate them afterward. The load runs in 90 minutes instead of five hours, because building an index once over sorted data is far cheaper than updating it 800 million times.