9.7 What does an index cost you?

The cost

In a book: Every time an author adds a paragraph, the index must be updated too. A book with five different indexes (by word, by name, by place...) takes much longer to revise, and the extra index pages make it thicker.

In the system: Every index makes reads faster and writes slower:

• Inserts: the row goes in the table and an entry goes in every index on that table.
• Updates: changing an indexed column means removing the old entry and adding a new one.
• Deletes: entries are removed from every index.
• Space: each index takes disk, and its hot parts take memory.

Figure: in the test run, five indexes made inserts about five times slower.

On a write-heavy table, every extra index is felt on every write. Examples: an events table taking thousands of inserts a second, or an orders table during a sale.

Good habits:

• Index for your actual queries, not "every column, just in case".
• Remove unused indexes. Most databases can report index usage (PostgreSQL's statistics views, for example).
• Prefer one good composite index over several single-column ones that overlap.
• Add indexes to big, busy tables carefully. Building one can lock or slow the table; many databases offer an online option (PostgreSQL's CREATE INDEX CONCURRENTLY).

Memory code: "Every index is a tax on every write."

---