Indexes and storage/maintenance: does each index consume extra disk space and incur overhead during INSERT/UPDATE/DELETE operations when the indexed value changes?

Introduction / Context:
Indexes accelerate read queries but are not free. This item tests whether you know that indexes require extra storage and must be maintained whenever indexed columns change, affecting write performance.

Given Data / Assumptions:

• An index is a separate data structure (e.g., B-tree, hash).
• Write operations may touch both the base table and relevant indexes.
• Storage and performance costs matter in design.

Concept / Approach:
Creating an index materializes additional metadata to speed lookups. Each index consumes disk (and memory when cached). When rows are inserted, updated, or deleted, index entries must be created, modified, or removed, adding CPU and I/O work. Clustered and nonclustered indexes differ in structure, but both have maintenance overhead. Thus, indexing strategy balances read performance against write costs and storage.

Step-by-Step Solution:

Verification / Alternative check:
Measure transaction latency before and after index creation on a hot column; write latency typically increases due to index maintenance.

Why Other Options Are Wrong:

• Limiting overhead to clustered or primary indexes ignores maintenance on all index types.
• Storage medium (SSD vs. HDD) affects speed, not the logical requirement to maintain indexes.
• “Incorrect” denies the fundamental trade-off of indexing.

Common Pitfalls:
Over-indexing tables and indexing low-selectivity columns (e.g., boolean flags), which adds cost without benefit. Periodically review unused indexes.

Final Answer:
Correct