What are the disadvantages of having too many indexes?
Every index uses disk and memory, and must be updated on each INSERT, UPDATE and DELETE, which slows writes. Extra indexes can also confuse the optimizer and slow maintenance such as bulk loads and backups.
Explain it simply
Think of a library where every book is also listed in ten different catalogues: by author, title, subject, publisher and so on. Finding a book is quick, but every new arrival must be written into all ten catalogues, and the catalogues take shelf space.
Example
A table payments has 6 indexes. Inserting one row means 1 table write plus 6 index writes. A bulk load of a million UPI transactions can become several times slower than with 2 indexes.
-- check unused indexes in PostgreSQL
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;Costs in short:
- Storage: indexes can be as large as the table itself.
- Write overhead: INSERT, UPDATE of indexed columns and DELETE touch each index.
- Memory: indexes compete for the buffer cache with data.
- Maintenance: rebuilds, vacuuming and backups take longer.
Good practice is to index for real queries, drop unused and duplicate indexes, and for large one-time loads drop and recreate indexes afterwards.
What to say in the interview
"Indexes trade write speed and space for read speed. I add them for known hot queries, review index usage statistics, and drop unused or redundant ones."
Common mistakes
- Adding an index for every slow query without checking existing ones.