Database Indexing and Read Replicas — A Practical Guide
How to choose the right database indexes, avoid the ones that quietly hurt write performance, and scale reads with replicas without introducing replication lag bugs.
Two techniques do most of the heavy lifting when a database starts to strain under load: indexing, which makes individual queries faster, and read replicas, which spread read traffic across more hardware. Both are simple in concept and easy to misuse — over-indexing quietly kills write throughput, and replicas introduce a consistency gap that shows up as confusing, hard-to-reproduce bugs if you don't design around it.
How Indexes Actually Work
An index is a separate, sorted data structure (typically a B-tree) that lets the database find rows matching a condition without scanning the whole table.
Without this index, SELECT * FROM orders WHERE user_id = 123 has to scan every row in the table. With it, the database can jump directly to the matching rows — the difference between O(n) and roughly O(log n) lookup time. On a table with a few hundred rows this doesn't matter; on a table with tens of millions, it's the difference between a 5ms query and a 5-second one.
The Cost Side: Why You Can't Index Everything
Every index has to be maintained on every write to that table.
This is the part teams miss: an index that speeds up one report query might be silently adding latency to every single write in the system, forever, whether or not that report ever runs. Before adding an index, check whether the query it would help actually runs often enough, and on a large enough table, to be worth that ongoing write cost. A good rule of thumb — index for your top 5-10 slowest or most frequent queries, not for every column that theoretically could be filtered on.
Composite Indexes and the Leftmost Prefix Rule
A composite (multi-column) index only helps queries that filter using its columns in order, starting from the left.
If you regularly query by status alone as well, you need a separate index on status — the composite index above won't serve that query efficiently. Order the columns in a composite index by how they're actually queried, not alphabetically or by table definition order.
Verifying an Index Is Actually Used
Adding an index doesn't guarantee the query planner will use it — always verify with EXPLAIN ANALYZE.
If you instead see Seq Scan on a large table where you expected an index to be used, the planner has decided a full scan is cheaper — often because the table is small enough that the difference doesn't matter, or the column has low cardinality (few distinct values), where an index provides little benefit over a scan. Don't assume an index is helping; confirm it.
Scaling Reads with Read Replicas
Once indexing has been exhausted, the next lever is spreading read traffic across replicas — copies of the primary database that stay in sync via replication and serve read-only queries.
This lets you scale read throughput horizontally by adding more replicas, independent of the primary — useful because most applications are read-heavy by a wide margin.
The Replication Lag Problem
Replication is asynchronous by default in most setups, which means there's a real, if usually small, delay between a write landing on the primary and that write appearing on a replica.
This is the single most common bug introduced by adding read replicas: a user submits something, gets redirected to a page that reads it back, and sees it missing or outdated because the read landed on a replica that's a few milliseconds (or, under load, seconds) behind. The fix is to route read-your-own-write scenarios back to the primary explicitly.
A practical rule: default reads to replicas for anything that tolerates slight staleness (dashboards, public listings, search), and explicitly route to the primary for anything that must reflect a write the current request or user just made.
Key Takeaways
Index the queries that are actually slow and frequent, not every column — each index adds real write overhead that persists whether or not the query it supports ever runs. Composite indexes only help queries that filter using their columns from the leftmost one inward, and EXPLAIN ANALYZE is how you confirm an index is actually being used rather than assumed to help. Read replicas scale read throughput well, but asynchronous replication means read-your-own-write reads need to be routed to the primary explicitly, or users will intermittently see their own just-submitted data as missing or stale.
