System Design Day 7: Database Indexing

Database Indexing: Faster Reads Without Indexing Everything

An index gives a database an organized path to matching rows. By the end of Day 7, you will be able to identify a useful index for a frequent query, explain its read-versus-write trade-off, and validate the choice with a query plan.

This lesson is part of the System Design in 30 Days roadmap.

The mental model: use the back of the book

Imagine finding one topic in a long textbook. You could start at page one and check every page, or use the index at the back to jump close to the right location.

A database index serves a similar purpose. It stores indexed values in a structure designed for lookup, together with information that helps the database locate matching table rows. PostgreSQL, for example, uses B-tree as its default index type, and B-tree indexes can support common equality and range comparisons.

The index does not replace the table. The table still stores the complete row. The index is an additional structure that the database must store and maintain.

One order lookup, two possible access paths

Consider an orders table and this frequent query:

SELECT *
FROM orders
WHERE order_id = 742901;

Assume the following workload for this teaching example:

This is a read-heavy access pattern: 500 lookups per second divided by 100 writes per second equals a 5-to-1 read/write ratio. That does not prove an index will be fast, but it makes this frequent point lookup a strong candidate to test.

Without a suitable index, the optimizer may choose a sequential or full table scan and inspect many rows. With an index on order_id, the optimizer can consider an index-based path to matching entries and then retrieve the required row.

CREATE INDEX idx_orders_order_id
ON orders (order_id);

Do not create this secondary index if order_id is already a primary key or already has an equivalent useful index. Duplicate indexes add cost without adding a new access path.

What you gain and what you pay

An index is not a universal speed switch. The optimizer may still prefer a table scan when a query returns a large share of the table, when statistics are stale, or when the available index does not match the query pattern.

This is why the practical rule is: index for real access patterns, not for every column.

How to validate the index

Use the database query plan before and after the change.

EXPLAIN
SELECT *
FROM orders
WHERE order_id = 742901;

In a safe test environment:

1. Capture the plan before creating the index.

2. Create the candidate index.

3. Refresh statistics if your database and workflow require it.

4. Run the same EXPLAIN again.

5. Compare the chosen access path and the amount of work estimated or measured.

6. Test write performance and storage impact before production rollout.

For execution measurements, use the database-specific option designed to run the query, such as EXPLAIN ANALYZE, only where executing the statement is safe.

Try this today

Find one slow query in a non-production environment and answer these questions:

• Which columns appear in its filters, joins, and sort order?

• How many rows does the query usually return?

• Does an equivalent index already exist?

• What does the query plan show before the change?

• What happens to the plan after adding one candidate index?

Write your answer as: query pattern -> candidate index -> expected benefit -> write/storage cost -> measured plan change.

Completed example you can copy

Query pattern: Frequent point lookup by order_id.

Assumptions: 10 million rows, 500 lookups/second at peak, 100 relevant writes/second at peak, and one row expected per lookup. order_id is not already indexed.

Candidate: A single-column index on orders(order_id).

Expected benefit: The optimizer gains an organized lookup path for a selective equality query.

Cost: More storage and extra index maintenance for inserts, deletes, and updates that affect the indexed value.

Validation: Compare the plan before and after the candidate index, then measure representative read and write workloads. Keep the index only if the observed trade-off supports the real workload.

Common indexing mistakes

Indexing every column

Unused indexes consume space and increase maintenance work. Start with frequent, important queries.

Ignoring the query shape

An index on one column may not help a query that filters and sorts by a different combination. Match indexes to access patterns.

Duplicating an existing index

Primary keys and unique constraints often create indexes. Inspect the schema before adding another one.

Assuming the optimizer must use your index

The query planner chooses an access path using costs and statistics. Verify the plan instead of guessing.

Measuring reads but not writes

A faster lookup can still be a poor system-level trade-off if write latency, storage, or maintenance becomes unacceptable.

Quick knowledge check

1. Why can an index slow writes?
Because the database may need to update the index when indexed data is inserted, deleted, or changed.

2. Should every filtered column receive an index?
No. Frequency, selectivity, query shape, storage, and write cost all matter.

3. What should you inspect before and after creating an index?
The query plan, followed by representative read and write measurements.

4. What if order_id is already the primary key?
Check the database metadata first; an equivalent lookup index may already exist, making another one redundant.

Keep learning with Korshub

Explore Rocking System Design to connect indexing decisions with capacity, caching, replication, sharding, and other system-design trade-offs.

Continue the series

• Previous: Day 6 - SQL vs NoSQL

• Roadmap: System Design in 30 Days

• Next: Day 8 - Database Replication