Index Strategy at Scale

4
4

Introduction

An index that works well for a small table may behave differently when the table grows to millions of rows. More data, changing query patterns, and concurrent writes can expose costs that were previously easy to overlook.

Index strategy at scale means designing for the workload and reviewing those decisions over time. The goal is to make important queries efficient while keeping storage and maintenance costs manageable.

Start with the Workload

Indexing every column is rarely a useful strategy. Begin with the queries that run frequently, process large amounts of data, or directly affect users.

Suppose an application frequently retrieves a customer’s recent orders:

SELECT order_id, created_at, total
FROM orders
WHERE customer_id = 101
ORDER BY created_at DESC
LIMIT 20;

This query reveals three requirements:

  • Locate orders for one customer.

  • Access them in creation-time order.

  • Stop after finding 20 qualifying records.

An index on (customer_id, created_at) is a useful candidate because its organization matches those requirements.

Also consider less frequent but expensive operations. A monthly report may deserve attention even though it runs far less often than a customer lookup.

Design Composite Indexes Around Access Patterns

A composite index is an ordered combination of columns:

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);

Entries are ordered by customer first, then by creation time within each customer.

For the previous query, this can help the database find a customer’s portion of the index and traverse it in the required direction. Whether it avoids a separate sort depends on the index capabilities and execution plan.

Column order matters:

Index

Access pattern it naturally supports

(customer_id, created_at)

Orders for a customer, organized by time

(created_at, customer_id)

Orders organized globally by time

These indexes are not interchangeable.

Equality conditions followed by range or ordering columns are often a useful starting point. However, the final design should account for the complete query and other queries that share the index.

Composite index recent orders

Composite index recent orders

Consider Selectivity and Data Distribution

Selectivity describes how much a condition narrows the data.

An order-ID lookup may match one record. A condition such as status = 'completed' might match most of the table.

An index on a low-selectivity column is not automatically useless. It may help when searching for a rare value or when combined with other columns.

For example:

SELECT order_id
FROM orders
WHERE status = 'pending'
AND created_at < '2026-01-01';

An index on (status, created_at) could help locate old pending orders.

Data distribution matters as much as the number of distinct values. If almost every order is completed, estimates based on an even distribution can be misleading. Keep optimizer statistics appropriately maintained so the database can make informed choices.

Use Covering Indexes Selectively

An index is covering for a query when it contains all the column values that query needs.

For example:

SELECT customer_id, created_at
FROM orders
WHERE customer_id = 101;

An index on (customer_id, created_at) contains both requested columns. This can reduce additional table access, although some databases may still need visibility or other checks.

Adding more columns can cover more queries, but wider indexes also require more storage, memory, and maintenance.

Some databases support included columns that store additional values without making them part of the search key. Use such features when appropriate, rather than automatically adding every selected column to the key.

Account for Write and Memory Costs

Every additional index creates work when rows are inserted or deleted. Updates also require index maintenance when indexed values change.

At scale, this can affect:

  • Write latency: More index entries must be maintained.

  • Storage: Each index occupies additional pages.

  • Memory: Index pages compete for buffer space.

  • Maintenance: Building and rebuilding indexes requires resources.

An index that saves a small amount of read time may be a poor trade if it substantially increases the cost of a busy write workload.

Small tables may also be faster to scan than to access through an index. An index scan is not automatically better than a table scan.

The indexing trade off

The indexing trade off

Review Redundant and Unused Indexes

Suppose a table has indexes on:

  • (customer_id)

  • (customer_id, created_at)

The second may support many queries served by the first. However, the narrower index may still have advantages because it is smaller.

Do not remove an index based only on overlapping column names. Check:

  1. Whether it supports a constraint.

  2. Which queries use it.

  3. Whether another index performs adequately in its place.

  4. Whether usage measurements cover reporting and seasonal workloads.

An index unused during one quiet afternoon may still support an important monthly operation.

Validate Changes with Execution Plans

Use an execution plan to inspect how the database accesses data.

Look for:

  • Estimated and actual rows, where available.

  • Rows examined compared with rows returned.

  • Additional sorting or repeated lookups.

  • Whether the proposed index reduces the expensive work.

Compare performance using representative data and concurrency. Measure both the queries you intend to improve and the writes that must maintain the index.

Large index builds can consume CPU, I/O, and temporary space or interfere with concurrent work. Available online or concurrent build options depend on the database.

Summary

Index strategy at scale begins with access patterns, data distribution, and workload priorities. Composite and covering indexes can improve important queries, but every index also consumes resources.

Treat indexing as an ongoing design decision. Validate changes with execution plans and realistic measurements, then review existing indexes as the application and its data evolve.

CS Core

Read Similar Blogs

Comments0