Database indexes: faster reads, slower writes, better decisions

5 min read

databasesperformancebackend

On this page

An index is not a performance button. It is a data structure the database must maintain so it can find some rows without reading every row.

That sounds simple, but it explains nearly every indexing decision: an index can make the right read dramatically cheaper while making every write a little more expensive.

Start with the query, not the column

Suppose an orders table has millions of rows and a customer wants to see their recent orders:

SELECT id, status, total_amount, created_at
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT 20;

Without a useful index, the database may need to inspect a large portion of orders, keep only rows for that customer, sort them, and then return 20. That is a lot of work for a small answer.

The question is not “should customer_id have an index?” The better question is:

What work does this query repeat, and can an index remove it?

How the database sees it

Ask the database before changing anything. In PostgreSQL, use EXPLAIN to see the planned work and EXPLAIN ANALYZE to run the query and report what actually happened.

EXPLAIN ANALYZE
SELECT id, status, total_amount, created_at
FROM orders
WHERE customer_id = 'cus_123'
ORDER BY created_at DESC
LIMIT 20;

For an unindexed query, the interesting parts often look conceptually like this:

Limit
  -> Sort
       Sort Key: created_at DESC
       -> Seq Scan on orders
            Filter: (customer_id = 'cus_123')

Seq Scan means a sequential scan. The database is reading the table row by row, applying the filter, then sorting the matches. A sequential scan is not automatically bad. It can be the fastest option when a query needs a large share of a small table.

For this query, though, the database has a more direct route available.

Give the query the shape it needs

This PostgreSQL index matches both the filter and the sort order:

CREATE INDEX CONCURRENTLY idx_orders_customer_created_at
ON orders (customer_id, created_at DESC);

Now the database can find one customer’s portion of the index, already ordered by newest first, and stop after 20 rows. The plan may look more like this:

Limit
  -> Index Scan using idx_orders_customer_created_at on orders
       Index Cond: (customer_id = 'cus_123')

The important improvement is not just that the word changed from Seq Scan to Index Scan. The filter, sort, and limit now work together. The database can avoid scanning unrelated orders and avoid sorting the full result set.

Composite indexes have an order

An index on (customer_id, created_at DESC) is not the same as one on (created_at DESC, customer_id).

The first version is a good fit for “one customer’s newest orders.” The database can first narrow to one customer, then read rows in the required order.

The second version is more useful for a query such as “the newest orders across all customers.” It is much less helpful for finding one customer’s history because the customer values are spread throughout the time-ordered index.

Think of a composite index like a phone book sorted by last name and then first name. It is excellent when you know the last name. It is not an efficient way to find everyone named Kushal.

When an index usually helps

Indexes are strongest when they support a repeated, selective access pattern:

  • A WHERE clause that narrows a large table to a small group of rows.
  • A join key used repeatedly, such as orders.customer_id.
  • An ORDER BY that is paired with a filter and a small LIMIT.
  • A uniqueness rule, such as an email address or external payment reference.

Primary keys and unique constraints already create indexes in most relational databases. Check what exists before adding another one.

The tradeoffs are real

Every extra index adds work. When a row is inserted, updated, or deleted, the database must also update each affected index.

Benefit Cost
Faster targeted reads Slower inserts and updates
Less work during filtering and sorting More storage space
Faster joins on indexed keys More maintenance during vacuuming or cleanup
Enforced uniqueness when needed More objects to monitor and reason about

An index on a frequently updated column can be especially expensive. An index on a low-cardinality value such as is_active may not help much when most rows share the same value. The planner may correctly choose a sequential scan instead.

Common indexing mistakes

Indexing every column

This is the database version of installing every browser extension. It feels proactive until writes slow down, storage grows, and nobody remembers which indexes are useful.

Add indexes for real query patterns, not hypothetical future queries.

Ignoring the leftmost columns

For a composite B-tree index, the leading columns matter most. An index on (account_id, created_at) naturally supports queries that constrain account_id. It usually will not be the best answer for a query that filters only by created_at.

Forgetting the expression in the query

This query may not use a plain index on email:

SELECT id
FROM users
WHERE LOWER(email) = LOWER($1);

The query asks for LOWER(email), not email. If case-insensitive lookup is intentional and frequent, consider an expression index or a database type designed for that behavior. Measure first.

Looking only at development data

An index that changes nothing on 500 local rows may be essential on 5 million production rows. Conversely, a plan that looks clever locally may be slower against production data distribution.

Use realistic data volume and inspect production safely when possible.

A practical workflow

  1. Find a slow or frequent query.
  2. Record its current plan with EXPLAIN ANALYZE.
  3. Identify the filter, join, ordering, and limit that dominate the work.
  4. Add the smallest index that supports that specific pattern.
  5. Compare the new plan and actual execution time.
  6. Watch write latency, storage, and index usage after deployment.

For a busy PostgreSQL table, CREATE INDEX CONCURRENTLY avoids blocking ordinary writes while the index is built. It has operational rules of its own, so read the PostgreSQL documentation before running it in production.

The rule worth remembering

The best index is not the one that makes a benchmark look impressive. It is the one that removes repeated work from an important query while costing less than it saves.

Measure the query, understand the plan, make one deliberate change, and measure again.