← All writing
FIELD NOTES / Databases · Performance

Before adding an index, ask a better question

Start with the shape of the workload. A faster query is only useful when it solves the actual bottleneck.

Start with the user-visible delay

A slow page may spend time waiting for the database, acquiring a connection, fetching remote data, or rendering an oversized response. Measure the whole request before choosing an optimization.

Capture the expensive queries and their frequency. A modest query repeated hundreds of times can cost more than one obviously slow query. Look at representative workloads rather than a single quiet development request.

Read the query as a data-access pattern

Which rows are filtered? Which columns determine ordering? How many rows are returned? Does the application need every column? Does it perform another query for each result?

These questions expose avoidable work. Selecting fewer rows, using bounded pagination, or fetching related records together can have a larger effect than adding an index to an inefficient access pattern.

An index has a cost

Indexes improve particular reads by maintaining an additional representation of the data. That representation takes space and must be updated when rows change. More indexes are not automatically better.

Choose candidate indexes from the actual filter and sort pattern. Check the database's query plan and compare behavior with representative data distributions. A plan that looks good on a tiny table may behave differently as the dataset grows.

Measure the tradeoff

Keep a baseline for latency and resource use. Change one meaningful variable, then compare under similar conditions. Include write-heavy paths in the review when adding indexes to frequently updated tables.

Be cautious with analysis tools that execute queries: use safe read queries and an appropriate environment, especially when investigating expensive operations.

Make the improvement durable

Record why the index exists near its migration or in the engineering review. Keep the query bounded as new features arrive. Watch tail latency as well as averages: a small number of slow requests can still dominate the user experience.

Performance work is a feedback loop: observe, form a hypothesis, change, and measure again.

KEEP EXPLORING

There's more
behind the code.

Explore all writing ↗
✳ Ask about Majid