Understanding Database Indexes: When They Boost Queries and When They Hurt Writes

When a query starts scanning millions of rows, the first instinct is to add an index. Indexes act like a book’s table of contents, letting the engine jump directly to matching rows instead of reading the whole table. Modern relational databases store those indexes as B‑trees, giving logarithmic lookup time for reads. But every time a row is inserted, updated, or deleted, the same structure must be adjusted, adding measurable write latency. Understanding that trade‑off helps teams avoid the common pitfall of over‑indexing.

What happened

Indexes improve SELECT performance by allowing the engine to locate rows without a full table scan. Most relational systems—PostgreSQL, MySQL, SQL Server—implement indexes as B‑trees, which provide O(log n) search complexity compared to linear scans. When a table contains millions of rows, an appropriately targeted index can reduce query time from seconds to milliseconds.

At the same time, each INSERT, UPDATE, or DELETE must maintain every relevant index. A table with dozens of indexes can see write latency increase sharply; the source cites a scenario with 30 indexes where every mutation had to touch all of them, dramatically slowing the system. The article advises running a proof of concept and measuring before adding large numbers of indexes.

In practice, developers often pair indexing with other strategies such as caching frequently read data in Redis or refactoring queries. Starting with a small set of well‑chosen indexes and iterating based on real‑world metrics tends to yield the best balance between read speed and write cost.

Why it matters

Read‑heavy applications benefit directly from faster lookups, lower CPU usage, and reduced latency, which translates to better user experience and lower cloud costs. Conversely, write‑heavy workloads suffer when every data change triggers multiple index updates, leading to higher CPU, I/O contention, and potential lock contention. Over‑indexing also inflates storage requirements and can confuse the query planner, causing sub‑optimal execution plans. Teams that understand the trade‑off can design schemas that scale predictably as data volume grows.

How to think about it

1. Identify the hottest read queries using logs or an EXPLAIN plan. 2. Prioritize indexes on columns that appear in WHERE clauses, JOIN conditions, or ORDER BY clauses with high selectivity. 3. Start with a primary key and one or two secondary indexes; measure the impact on both read latency and write throughput. 4. Use covering indexes (including needed columns in the index) to avoid extra lookups. 5. Regularly review index usage statistics; drop indexes that show little or no scan activity. 6. For write‑intensive tables, consider alternative patterns such as denormalization, materialized views, or external caches instead of piling on indexes.

FAQ