Database indexing: the cost-performance trade you control with SQL

Quick answer

A well-chosen index turns a full table scan into a fast lookup, cutting query latency and the compute or I/O you pay for, often dramatically. But each index adds write overhead (every insert and update maintains it) and storage. So index the queries that matter, remove unused indexes, and treat indexing as the cheapest performance lever before scaling the database up.

Before you add a read replica or a bigger instance, look at the indexes. Indexing is the highest-leverage, lowest-cost database performance tool there is: a single well-placed index can turn a query that scans millions of rows into one that reads a handful, cutting latency and the resources you pay for at once. It is not free, but it is far cheaper than scaling hardware.

What an index buys

Without an index, a query filtering on a column may scan the whole table, consuming CPU and I/O proportional to the table size, which on a pay-per-I/O database like DocumentDB or Aurora is direct cost. An index lets the database jump to matching rows, so the query reads far less and returns faster. On a busy query path, that is both a latency win and a cost win.

What an index costs

Index cost	Impact
Write overhead	Every insert/update/delete maintains the index
Storage	The index occupies disk
Planning	Too many indexes can confuse the optimizer

Each index must be updated on every write to the table, so a write-heavy table with many indexes pays overhead on every insert and update. Indexes also consume storage and, beyond a point, complicate query planning. So more indexes is not better; the right indexes are.

Getting indexing right

Index the columns your frequent, expensive queries filter and join on, use composite indexes that match query patterns, and remove indexes nothing uses, since they cost writes and storage for no benefit. Check the query planner to confirm indexes are actually used. Do this before reaching for a read replica or a bigger instance, because fixing a missing index is far cheaper than adding hardware to brute-force a scan, the core of cost-performance optimization.

FAQ

How does indexing affect database cost?

A good index turns a full table scan into a fast lookup, cutting the CPU and I/O a query consumes, which on pay-per-I/O databases is direct cost, and reducing latency. But each index adds write overhead and storage, so the trade is read speed and cost versus write overhead.

Do indexes slow down writes?

Yes. Every index on a table must be updated on each insert, update, or delete, so a write-heavy table with many indexes pays maintenance overhead on every write. This is why you index the queries that matter and remove indexes nothing uses, rather than indexing everything.

Is indexing cheaper than scaling the database?

Almost always. A single well-placed index can turn a query scanning millions of rows into one reading a handful, cutting cost and latency for free relative to hardware. Fix missing indexes before adding a read replica or a bigger instance to brute-force a scan.

How do I know which indexes to add?

Index the columns your frequent, expensive queries filter and join on, using composite indexes that match query patterns. Use the database's query planner to find full scans and confirm indexes are actually used. Add indexes for real query patterns, not speculatively.

Can too many indexes hurt?

Yes. Beyond the write overhead and storage each index adds, too many indexes can complicate query planning and provide little benefit if queries do not use them. Remove unused indexes, since they cost writes and storage for no return. The right indexes matter more than many.

Does C3X help with database cost?

C3X prices your database instances from Terraform, so you can compare the cost of scaling up against keeping a smaller instance. Indexing reduces the load that would otherwise push you to a larger instance, and the cheapest instance is the one good indexing lets you keep.

What to do next

Price the database you can keep with good indexing. C3X reads your Terraform and prices your resources against a live catalog. Start with the quickstart.