Database Index Trade-offs

Database indexes can make read queries much faster by avoiding full table scans, but they consume storage and add work to inserts, updates, deletes, and index maintenance. Good indexing balances query performance against write cost, storage, and operational complexity.

An index is a separate data structure that helps the database locate rows without examining every row in a table. A common structure is a B-tree, which keeps indexed values organized so equality and range lookups can be performed efficiently.

Indexes improve reads only when the query can use them effectively. For example, an index on `email` can help a query that searches for a particular email, while an index on `created_at` can help retrieve rows within a time range. The database optimizer decides whether using an index is cheaper than another access path such as a table scan.

Indexes are not free. An indexed column generally requires additional disk space, and every change to indexed data may require corresponding changes to the index. A table with many indexes can therefore have slower writes and greater maintenance overhead.

The useful index depends on the query pattern. Composite indexes can support multiple predicates, but column order matters because an index is organized according to its leading columns. An index that is rarely used or duplicates another index can add cost without providing meaningful benefit.

For a Java backend engineer, index decisions should be based on actual access patterns: inspect slow queries and execution plans, identify selective predicates and common sort or join operations, then add or adjust indexes and measure the result.

Key points

• Indexes can reduce the amount of data the database must examine for suitable queries.
• Every additional index consumes storage and can increase insert, update, and delete costs.
• Composite index column order affects which query patterns can efficiently use the index.
• An index is not automatically beneficial; the optimizer may choose a table scan instead.
• Use query patterns and execution plans to guide indexing decisions.

Interview questions

Why does column order matter in a composite index?

A composite index is ordered using its first column, then subsequent columns within matching values of earlier columns. Queries that constrain the leading column can generally make effective use of the index. A query that only constrains a later column may not receive the same benefit. The best order depends on the workload and the database optimizer.

Why do databases use indexes?

Indexes provide an additional structure that can help the database find matching rows without scanning the entire table. This can substantially reduce the amount of data examined for selective queries. The trade-off is additional storage and maintenance work when data changes. The database can still choose a table scan when it estimates that scanning is cheaper.

What are the main trade-offs of adding an index?

The main benefit is faster execution for queries that can use the index. The costs include additional disk space and extra work for inserts, updates, and deletes involving indexed data. Indexes can also increase maintenance and operational complexity. Therefore, an index should be justified by useful query patterns rather than added automatically.

Why might a database choose a table scan even when an index exists?

An index is useful only if its estimated cost is lower than the alternatives. If a query returns a large fraction of the table, scanning the table can be cheaper than finding many index entries and then fetching many rows. Statistics, data distribution, query predicates, and available indexes can all affect the optimizer's estimate. An execution plan is the practical way to determine what the database chose.

How would you investigate whether a new index actually improved a slow endpoint?

I would first identify the SQL responsible for the endpoint's database time and inspect its execution plan and workload characteristics. I would add the smallest index that matches the query pattern, then compare latency, rows examined, resource consumption, and write overhead before and after the change. I would also check whether existing indexes overlap with the proposed one. Finally, I would validate the result under representative data and traffic rather than relying on a single development query.