• Wordpress

Kannan Rajendiran

September 3, 2026

.

4 Min read

Indexing is often treated as a free performance upgrade, added liberally to any column that shows up in a slow query without much further thought. In practice, database indexing basics involve a genuine tradeoff, and adding an index carelessly can slow down writes, bloat storage, and in some cases even make read performance worse than having no index at all. This guide covers when to add database index structures, and when leaving a table unindexed is actually the better call.

How Indexes Actually Speed Up Reads

An index works similarly to the index at the back of a book: instead of scanning every row in a table to find matching data, the database consults a separate, sorted structure that points directly to the relevant rows. For queries filtering or sorting on a specific column, particularly on large tables, this difference in lookup time can be dramatic, turning a full table scan into a fast, targeted lookup.

This benefit scales with table size. On a table with a few hundred rows, the difference between an indexed and unindexed lookup is often negligible, but on a table with millions of rows, an appropriate index can be the difference between a query returning in milliseconds versus several seconds.

The Write Performance Tradeoff

Every index added to a table has to be updated whenever a row is inserted, updated, or deleted, which means write operations get progressively slower as more indexes are added to the same table. This is one of the core index performance tradeoffs developers underestimate, particularly on tables that see frequent writes, such as logging tables or high-traffic order tables, where an overly indexed schema can quietly become a bottleneck on the write side even as read queries stay fast.

Storage overhead compounds this further, since each index effectively duplicates a sorted copy of the indexed columns, which adds up meaningfully on very large tables with several indexes applied.

Which Columns Are Actually Worth Indexing

Columns frequently used in WHERE clauses, JOIN conditions, or ORDER BY statements are the strongest candidates for query optimization indexing, particularly foreign key columns that are joined against constantly. Columns with low cardinality, meaning few distinct values such as a boolean status flag, generally benefit far less from indexing, since the database still ends up scanning a large portion of the table even with an index in place.

Composite indexes, which cover multiple columns together, can be more effective than several single-column indexes when queries consistently filter on the same combination of columns, but column order within a composite index matters significantly and should match how the columns are actually queried, not simply the order they appear in the table schema.

A Practical Way to Decide

Rather than guessing which columns need an index, most modern databases provide query planning tools, such as EXPLAIN in PostgreSQL and MySQL, that show exactly how a query is being executed and whether an existing index is being used or ignored. Running EXPLAIN before and after adding a candidate index gives a concrete before-and-after comparison rather than relying on assumption, and it also reveals cases where an index exists but is not actually being used by the query planner for reasons worth investigating separately.

The PostgreSQL documentation on indexes covers this in detail and is a solid reference for understanding how different index types behave under different query patterns, which is worth reading before making sweeping indexing decisions across a production schema.

Reviewing Indexes Periodically, Not Just Once

Query patterns change as an application grows, and an index that was essential a year ago can become dead weight once the feature that relied on it is deprecated or refactored. Periodically reviewing index usage statistics, most database engines track how often each index is actually used, and removing ones that see little to no use, is a low-effort way to keep write performance healthy without sacrificing the read speed that matters most to your application today.