Indexes speed up reads. When do they hurt, and how would you find indexes that are not earning their keep?

Indexes hurt writes. Every INSERT, every DELETE and many UPDATEs must also update each index. So a write-heavy table with ten indexes pays for all ten on every write.

In PostgreSQL, updating an indexed column also prevents a HOT update (heap-only tuple update). A HOT update is a cheap update that skips the indexes. Without it, every index needs a new entry. Indexes also take disk space, and they compete for memory in the buffer cache.

Indexes also do not help everywhere:

• Low-selectivity columns, like a boolean where most rows match. A sequential scan (reading the whole table) is cheaper, so the planner ignores the index.
• Small tables. A sequential scan is cheaper here too.
• Duplicate indexes, such as (a) next to (a, b). The index on (a, b) already serves queries on a, so (a) is pure overhead.

To find indexes you can remove, check the idx_scan column in pg_stat_user_indexes. It counts how often each index was used. An index with zero scans over a long period is probably unused. Check this on the primary and on the replicas.