Indexing

Definition

An index is a redundant, ordered copy of some columns that lets the engine find rows without reading the table. Every index accelerates some reads and slows every write, and occupies memory that competes with the data itself.

What to know beyond "add an index"

Composite index column order is the whole game. An index on (tenant_id, created_at) serves a query filtering on tenant and ordering by date. The same columns reversed does not. The rule of thumb: equality predicates first, then the range or sort column.

Covering indexes eliminate the table read. If the index contains every column the query needs, the engine never touches the heap. This routinely turns a slow query into a fast one without any change to the query.

Selectivity decides usefulness. An index on a boolean column with a 50/50 split is usually ignored, because reading half the table via the index is slower than scanning it.

Indexes are not free memory. The working set that matters is data plus indexes. A table with eleven indexes may no longer fit in cache, making everything slower — including the queries the indexes were added to help.

Write amplification. Every insert updates every index. On a write-heavy table, an unused index is a permanent tax.

Diagnostic method

Read the query plan, not the query. The specific things to look for: a sequential scan on a large table, an index that was available and not used (usually a type mismatch or a function applied to the column), a sort that could have been served by the index, and a row-count estimate far from reality, which means statistics are stale and every downstream decision is wrong.

Then check which indexes are never used. Most mature systems carry several that were added during an incident, helped nothing, and have been taxing every write since.

Failure scenarios

• A function on the indexed column — WHERE lower(email) = ... — which silently disables the index unless a matching expression index exists.
• Adding an index on a huge table without concurrent building, locking writes for the duration.
• Indexing to fix a query that should not exist, when the real problem is an N+1 pattern or a missing cache.
• Stale statistics after a bulk load, so the planner chooses a nested loop over millions of rows.

Trade-offs

Reads get faster; writes get slower; memory pressure rises; and the schema becomes harder to change because index builds on large tables are operationally significant. The discipline is to add indexes against measured queries and to periodically remove those that no query uses.

Interview question

"A query is slow. Walk me through your diagnosis before you consider adding an index, and tell me when adding one is the wrong fix."