Database Indexes: Faster Reads Without Guesswork

An index is an extra data structure that exchanges storage and write work for an access path. It does not make a table universally fast. This query-debugging workshop begins with a slow customer-order lookup, reads the execution plan, designs a composite index, and checks whether the improvement survives skew, writes, cache pressure, and an online production build.

The core idea

An index is a workload-specific access path, never a universal speed switch. Its key order and type must match predicates and ordering, while its read benefit must exceed added write, storage, cache, replication, and migration cost. Execution plans and representative data decide.

Debugging checkpoint: read the slow plan

The optimizer estimates access costs from statistics and available paths. Resist changing configuration immediately. First capture query, binds, plan, actual rows, loops, buffers, and timing, then form a hypothesis that one additional observation can disprove.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id,total,created_at FROM orders
WHERE customer_id=42 AND status='open'
ORDER BY created_at DESC LIMIT 50;

ANALYZE executes the statement; use caution with writes. Large estimate errors point toward statistics or correlation issues.

Keep timestamps in one time base and preserve request, transaction, or query identifiers. Reproduce with common, rare, cold-cache, and warm-cache values. A false lead appears when one cached parameter or warm development database represents production. The decisive signals are estimated/actual rows, loops, buffers, and temporary I/O; inspect their distributions and dimensions rather than a single average.

Step: build the composite access path

Start with a small, reversible implementation. Equality columns usually precede range and ordering components for this query family. The immediate action is to match the important predicate prefix and requested order

CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);

The engine can seek to one customer/status range and read newest entries without a separate sort. Column order is workload-specific, not a slogan.

Read the example from top to bottom and identify which line establishes the guarantee and which lines merely implement it. In a real system, repeat the exercise with every supported prefix and sort direction. The step is incomplete if three single-column indexes are assumed equivalent to the composite ordering. During rollout, record scan type, sort nodes, rows examined, and p95 latency; compare it with a baseline before claiming success.

Comparison: B-tree, partial, covering, or specialized

Index type and contents must support the operators actually used. A comparison is only useful when the criteria match the workload, so choose the smallest structure that removes measured work

Treat each row as a hypothesis, not a universal verdict. Weight correctness guarantees before convenience when a mistake is expensive. Prototype skewed distributions and realistic projections. Beware the conclusion when a low-cardinality column is indexed alone without measuring matched rows; benchmark data without representative distribution and contention is especially misleading. Revisit the choice using index size, heap fetches, cache hits, and update cost.

Step: use a partial or covering index carefully

Start with a small, reversible implementation. Active rows may be a tiny subset, while narrow result columns can be included. The immediate action is to encode only stable predicates and worthwhile projection values

CREATE INDEX idx_queued_jobs
ON jobs (run_at) INCLUDE (queue)
WHERE status = 'queued';

PostgreSQL can keep this index compact for schedulers; queries must imply its predicate. Frequently changed included columns still increase maintenance.

Read the example from top to bottom and identify which line establishes the guarantee and which lines merely implement it. In a real system, repeat the exercise with index-only eligibility, updates, and status transitions. The step is incomplete if wide text values bloat pages or query predicates do not match. During rollout, record index pages, heap fetches, WAL, and scheduler latency; compare it with a baseline before claiming success.

Failure reconstruction: the read fix slows ingestion

Imagine the alert has fired. Five overlapping indexes turn one row update into six structure changes and more log volume. The first response should be to compare mixed workload before and after rather than celebrating one query, preserving evidence before retries or manual repair change the state.

baseline: 8k writes/s, query p95 420 ms
candidate: 6k writes/s, query p95 35 ms
decision depends on SLOs, queue growth, and replica lag

These are illustrative fields, not claimed benchmark results; measure your system.

Walk the timeline twice: once from the caller's view and once from the durable system of record. The two stories often diverge at a timeout, commit, queue acknowledgement, or cache boundary. Recreate bulk loads, hot updates, checkpoint pressure, and replica catch-up. The likely trap is that a read-only benchmark hides write amplification and cache eviction. Confirm recovery by watching commit latency, WAL/redo, page splits, storage, and lag, not merely by seeing one successful request.

Runbook phase: build and prune in production

Objective. Online or concurrent build reduces some blocking but still consumes CPU, I/O, space, and lock opportunities.

Procedure. check long transactions and capacity, build with engine-specific syntax, inspect validity, observe, then remove only proven overlap

PostgreSQL CREATE INDEX CONCURRENTLY cannot run in a transaction block and can leave an invalid index after failure. MySQL online DDL capabilities vary by operation and version; inspect the chosen algorithm and lock behavior.

Name the person authorized to stop the phase and make every command repeatable. Before proceeding, complete cancellation, failover, disk pressure, and rollback of application use. Stop rather than improvise if “online” is interpreted as zero contention or a constraint-supporting index is dropped. The dashboard for this phase should expose build phase, lock waits, free space, lag, invalid indexes, and index usage. Capture start and finish checkpoints so another operator can determine exactly how far the run advanced.

Index review experiments

1. Rehearse read the slow plan

Before sign-off, restate this article-specific claim in one sentence: The optimizer estimates access costs from statistics and available paths. Build a controlled exercise that includes common, rare, cold-cache, and warm-cache values. Record the expected durable state, response, and operator action before running it. During the exercise, deliberately introduce the condition where one cached parameter or warm development database represents production. The team must explain whether to retry, reject, compensate, roll back, or wait. Preserve estimated/actual rows, loops, buffers, and temporary I/O with enough dimensions to compare the expected and observed path. Finish by assigning ownership and documenting the threshold that would force the design to be reconsidered.

2. Rehearse build the composite access path

Before sign-off, restate this article-specific claim in one sentence: Equality columns usually precede range and ordering components for this query family. Build a controlled exercise that includes every supported prefix and sort direction. Record the expected durable state, response, and operator action before running it. During the exercise, deliberately introduce the condition where three single-column indexes are assumed equivalent to the composite ordering. The team must explain whether to retry, reject, compensate, roll back, or wait. Preserve scan type, sort nodes, rows examined, and p95 latency with enough dimensions to compare the expected and observed path. Finish by assigning ownership and documenting the threshold that would force the design to be reconsidered.

3. Rehearse b-tree, partial, covering, or specialized

Before sign-off, restate this article-specific claim in one sentence: Index type and contents must support the operators actually used. Build a controlled exercise that includes skewed distributions and realistic projections. Record the expected durable state, response, and operator action before running it. During the exercise, deliberately introduce the condition where a low-cardinality column is indexed alone without measuring matched rows. The team must explain whether to retry, reject, compensate, roll back, or wait. Preserve index size, heap fetches, cache hits, and update cost with enough dimensions to compare the expected and observed path. Finish by assigning ownership and documenting the threshold that would force the design to be reconsidered.

4. Rehearse use a partial or covering index carefully

Before sign-off, restate this article-specific claim in one sentence: Active rows may be a tiny subset, while narrow result columns can be included. Build a controlled exercise that includes index-only eligibility, updates, and status transitions. Record the expected durable state, response, and operator action before running it. During the exercise, deliberately introduce the condition where wide text values bloat pages or query predicates do not match. The team must explain whether to retry, reject, compensate, roll back, or wait. Preserve index pages, heap fetches, WAL, and scheduler latency with enough dimensions to compare the expected and observed path. Finish by assigning ownership and documenting the threshold that would force the design to be reconsidered.

5. Rehearse the read fix slows ingestion

Before sign-off, restate this article-specific claim in one sentence: Five overlapping indexes turn one row update into six structure changes and more log volume. Build a controlled exercise that includes bulk loads, hot updates, checkpoint pressure, and replica catch-up. Record the expected durable state, response, and operator action before running it. During the exercise, deliberately introduce the condition where a read-only benchmark hides write amplification and cache eviction. The team must explain whether to retry, reject, compensate, roll back, or wait. Preserve commit latency, WAL/redo, page splits, storage, and lag with enough dimensions to compare the expected and observed path. Finish by assigning ownership and documenting the threshold that would force the design to be reconsidered.

6. Rehearse build and prune in production

Before sign-off, restate this article-specific claim in one sentence: Online or concurrent build reduces some blocking but still consumes CPU, I/O, space, and lock opportunities. Build a controlled exercise that includes cancellation, failover, disk pressure, and rollback of application use. Record the expected durable state, response, and operator action before running it. During the exercise, deliberately introduce the condition where “online” is interpreted as zero contention or a constraint-supporting index is dropped. The team must explain whether to retry, reject, compensate, roll back, or wait. Preserve build phase, lock waits, free space, lag, invalid indexes, and index usage with enough dimensions to compare the expected and observed path. Finish by assigning ownership and documenting the threshold that would force the design to be reconsidered.

Conclusion: index questions, not columns

A useful index follows a real predicate, range, order, and projection. Execution plans with representative data validate it. Every extra structure taxes writes, storage, cache, replication, and maintenance. Build with engine-specific operational safeguards and remove overlap only after usage evidence, constraint review, and an observation window. The durable lesson is to state guarantees in language clients and operators can verify, then build the smallest mechanism that satisfies them under retries, concurrency, deployment, and partial failure. Revisit the decision when traffic, risk, or ownership changes.

Index choices interact with locking and snapshots, covered in Database Transactions and Isolation Levels Explained. Broader implementation material lives in the Prime Axiom resource library.

Authoritative references

• PostgreSQL: Indexes
• PostgreSQL: EXPLAIN
• MySQL: Optimization and Indexes

Planner and online-build behavior varies by database release; inspect the manuals for the exact engine version used in production.