Indexes and Access Paths

A Fast Query Is a Maintained Path

A slow query often arrives as a simple request: “add an index.” The request sounds local. One query hurts, one index should fix it, and the incident can close. In production, the index rarely stays local. It changes the write path, the planner’s choices, backup size, restore time, migration windows, cache pressure, and the set of structures someone must understand during an outage.

An index is a maintained access path for a specific shape of question. It promises that the system will keep enough ordered, grouped, tokenized, partitioned, or approximate structure around so a query can avoid doing work at read time. The price is paid on writes and operations. Every insert, update, delete, import, rebuild, replica catch-up, and restore may now have more state to maintain.

Treat indexes as owned production infrastructure. A defensible index has an explicit query benefit, write tax, rollout risk, and retirement rule.

Start With the Query Fingerprint

Do not begin with a column list. Begin with the question the system must answer.

A query fingerprint records the normalized query or API path, the predicates, the sort order, the join or lookup pattern, the result size, the call frequency, the latency target, the actor that depends on it, and the expected data distribution. It should also include representative parameters: the tiny tenant, the large tenant, the empty result, the common case, the pathological case, and the case after another year of growth.

Consider a support queue with this common read path:

SELECT id, customer_id, priority, updated_at, assignee_id
FROM tickets
WHERE tenant_id = ?
  AND status = 'open'
ORDER BY updated_at DESC
LIMIT 50;

The query is not merely “filter by status.” It is a tenant-scoped, open-ticket, newest-first, top-N access path with a small result size and a user waiting on the page. An index on status alone may still leave the engine walking through too many rows, sorting too much data, or fetching scattered records. An access path such as (tenant_id, status, updated_at DESC) is closer to the shape because it narrows by tenant and status, then reads in the requested order.

That index still does not solve every ticket query. It may not help a global aging report, a customer-specific history view, a full-text search over ticket bodies, or a dashboard grouped by assignee. A good proposal says what the index accelerates and what it deliberately does not accelerate.

What an Index Buys

An index gives the engine an alternate route to the base data. For the support queue, the route buys lookup and ordering: it narrows the candidates and delivers the newest ones first. If the index also stores every value projected by the page, it may cover the query and avoid fetching the base records. Coverage can remove work from the read path, but it duplicates more data and makes more updates touch the index.

Some indexes protect correctness as well as speed. A unique index gives the engine a maintained structure in which to reject a duplicate key. That is a different promise from making a page load quickly, and it should not be retired merely because an index-usage counter looks quiet.

The familiar names describe different structures and promises. A primary index defines the main physical or logical access path; secondary indexes serve additional reads. Composite indexes order several fields together. Partial or filtered indexes maintain entries only for records matching a predicate. Full-text and inverted indexes map terms to documents. Geospatial indexes organize proximity and shape. Bitmap indexes can combine low-cardinality predicates efficiently in some analytical designs. Vector indexes trade exactness, memory, build time, and update behavior for approximate similarity search.

They are all called indexes because they move work away from the read that needs an answer. They should not share one generic cost model. A B-tree split, a posting-list merge, and a vector-graph rebuild are different operational events.

Selectivity Is About Distribution

Selectivity is the fraction of rows an access path can exclude. Cardinality is the number of distinct values. Both matter less as isolated properties than as properties of the workload distribution.

A boolean column has low cardinality, but that does not automatically make it useless. If half the table is active, a plain index on active may not help a query much. If only a small fraction of rows are active for a particular tenant and the page always asks for the newest 50, a partial or composite index may be valuable. The difference is not the column; it is the surrounding shape.

Skew is where many index designs become fragile. A query that is selective for most tenants may be broad for the largest tenant. A status value that used to be rare can become common after a product change. A time-range predicate can be selective for recent interactive reads and unselective for monthly reporting. The planner and the operator both need statistics that describe the real distribution, not only the average row count.

The practical review question is: for the parameters that matter, how many candidate records does this access path still force the engine to inspect, sort, join, fetch, or discard?

Composite Indexes Encode an Order of Work

Composite indexes are where teams often memorize rules without understanding the mechanism. A composite ordered key is not a bag of columns. It is an ordered structure. The leading fields decide which parts of the tree, run, shard, or segment can be skipped first. Equality predicates on leading fields usually narrow the path cleanly. A range predicate often limits how much of the remaining key order can be used. Sort order matters when the query needs the results already ordered.

Return to the support queue. (tenant_id, status, updated_at DESC) gives the engine a path to one tenant’s open tickets in newest-first order. Remove the tenant predicate, however, and the leading key no longer gives a global status-and-time order. Add assignee_id as a filter and the engine may still inspect and discard tickets because assignee is absent from the path. Ask for recently closed tickets by closed_at and the stored order is wrong. Search the ticket body for “refund” and an ordered tuple is the wrong structure altogether; the query needs a term index.

“Put the columns from the query into an index” misses the actual decision. The design is to choose which equality filters, ranges, ordering needs, projected values, and result-size limits deserve a maintained path—and in which order the engine can use them.

Planners Use Models, Not Certainty

Most query engines choose plans by estimating cost. They use row counts, statistics, histograms, correlation estimates, operator rules, memory assumptions, and sometimes feedback from previous execution. The planner is not a judge declaring the universally best index. It is a model deciding which path appears cheapest under current information.

Plans can change after ordinary events. A tenant grows. A bulk import shifts distribution. Statistics become stale. A new predicate value is much more common than old values. In the support queue, the planner may estimate that status = 'open' leaves a few hundred tickets for every tenant because that is true on average. One large tenant actually has 500,000 open tickets. For that parameter, walking an index and fetching scattered base rows may cost more than a broader scan. The SQL text did not change; the distribution invalidated the estimate behind yesterday’s good plan.

Index work therefore includes plan evidence. Capture the current plan, the proposed plan, and the plans for representative parameter sets. When the database exposes actual row counts or actual execution statistics, compare estimates with reality. A large estimate error is a production signal: the index may be fine, but the planner’s model may be too wrong to choose it reliably.

Plan stability is not the same as freezing one plan forever. The goal is to know which plan shape is acceptable, which shift is dangerous, and which monitor will catch the dangerous shift before a user-facing path degrades.

The Write Tax Is Paid by Other Paths

Every maintained access path must stay synchronized with the source data. Inserts add entries. Deletes remove entries, mark entries obsolete, or create tombstones depending on the storage engine. Updates may be cheap when non-indexed fields change and expensive when indexed values change. Unique indexes can require coordination to reject conflicting writes. Covering indexes duplicate projected values. Specialized indexes may tokenize, encode, quantize, merge, compact, or rebuild.

The tax reaches beyond foreground latency. Extra structures consume storage and cache residency. Their changes add log volume and replica-apply work, which can lengthen catch-up after lag. Retained versions and tombstones complicate cleanup. Backups grow; restores and replays take longer; a failover may bring a cold index back into memory slowly. Building or replacing the structure introduces migration time, I/O competition, lock risk, and a deploy order that must remain safe if the change stops halfway.

The owner of the slow read is not always the owner of the write tax. In the ticket system, support benefits from the new page path while ingestion pays to maintain it on every status change. A reporting view can make checkout writes slower. A search feature can increase ingest cost. A uniqueness guarantee can introduce contention on a signup path. A serious proposal names the paying paths, not only the benefiting one.

When many indexes accumulate around one table or collection, ask whether the model is carrying too many read shapes directly. The better design may be a derived view, a search document, a summary table, a queue-fed projection, or a narrower product requirement.

Build and Rollout Are Part of the Design

Index creation is production work. Some systems build indexes online with reduced blocking. Others require locks, throttling, separate phases, replica-first rollout, or maintenance windows. Large backfills can compete with foreground I/O. A build that is safe on an empty staging database may be unsafe against a large, hot production dataset.

Before rollout, answer four operational questions.

First, what can the build block? Identify writes, reads, schema changes, replication apply, vacuum or compaction work, and backup windows that might interact with the build.

Second, how will the team know the index is being used for the intended query? Capture before and after plans, query latency, rows examined, sort behavior, and index-usage counters where the engine provides them.

Third, how will the team know the index is hurting the system? Watch write latency, lock waits, storage growth, replication lag, cache hit rate, maintenance debt, and build progress.

Fourth, what is the rollback? Dropping an index can also be work. Replacing a bad index may require building a second one before removing the first. A rollout plan without a rollback path is only a hope that the cost model is correct.

Index Proposal Template

Use this template before adding or changing an index in a production system:

An index without removal criteria is a permanent tax with a temporary story.

When the Index Outlives the Query

Index sprawl usually begins with reasonable local decisions. One index serves a status page, another a report, a third an old API version, and a fourth a temporary migration. On a mutable table, every surviving structure remains in the write and recovery path even after its original read becomes rare or disappears.

Usage counters are clues, not verdicts. A restart or failover may reset them. A month-end report may be quiet for weeks. A unique index may enforce an invariant without appearing in ordinary read plans. Before removal, find the queries or constraints the index was meant to serve, observe a representative business cycle, inspect dependencies, and capture the write and storage cost that removal is expected to recover.

Removal deserves the same care as creation. Predict the replacement plan, test the rare and skewed parameters, watch query latency and rows examined after the change, and keep a path to rebuild if an overlooked workload suffers. The owner and review date in the proposal turn retirement from guesswork into routine maintenance.

An index is successful when it moves a justified amount of work away from an important read and the system can afford where that work lands. Once that bargain no longer holds, keeping the structure is not caution. It is an unexamined production tax.

Exercise

Take three queries from one service that touch the same table, collection, or indexable dataset. Design one composite or specialized access path that clearly helps the first query, only partly helps the second, and does not help the third. Explain the difference using field order, predicate shape, sort order, coverage, selectivity, and result size.

Then choose one existing index that might be unused or over-specialized. Write the evidence required before removing it, the rollback plan if removal hurts a rare query, and the owner who should make the final call.