Explain Database Index Benefits and Write Costs

# Explain Database Index Benefits and Write Costs Why do relational databases use indexes, and why is creating an index on every column usually a poor design? Explain how an index changes read behavior, write behavior, storage use, and query-planner choices. Include examples of a query that would likely benefit from an index and a column that may not. ### Clarifying Questions to Ask - Which database engine and index family should the discussion assume? - Is the workload primarily transactional, analytical, or mixed? - Are composite indexes and covering indexes in scope? ### What a Strong Answer Covers - How an index avoids scanning every row for selective lookups or ordered access. - The write amplification, storage, cache, and maintenance costs of each added index. - Selectivity, access patterns, composite-key order, and whether an index can cover a query. - Cases where the optimizer may prefer a table scan despite an available index. - A measurement-driven method using query plans and production-shaped workloads. ### Follow-up Questions - When would a composite index on `(tenant_id, created_at)` be more useful than two single-column indexes? - Why can an index on a low-cardinality boolean column be ineffective? - How do index-only scans and clustered storage change the trade-off?