DATA & INDEXING
Cache vs read replica vs index
Three ways to take load off a database, and they are not interchangeable. Each one answers a different kind of read.
- App
- Cacheanswers it saw
- Database
- Identical repeated queries become instant
- Free filters rarely repeat
- Cannot search, count or rank anything unseen
- Database
- Replicasame engine, same plans
- Reports
- Takes read load off the primary
- Covers a primary outage
- A slow scan is just as slow, now with lag
- Database
- Indexorganized for reads
- Screens
- Search, filters, counts and totals in one request
- Stays fast as the table grows
- As fresh as its last import
Three fixes, three problems
When a database gets slow under reads, teams reach for one of three things, usually in this order: a cache, because it is cheap; a read replica, because it is familiar; an index, because the first two did not help. All three are sound. The mistake is treating them as interchangeable, when each one fixes a different kind of slow read and does nothing for the other two.
Cache
A cache stores the answer to a query so the next identical query gets it without touching the database. For a product page that thousands of people open, or a home-page count that changes once an hour, that is exactly right and nearly free. Its limit is the word identical. A listing screen where people combine filters, change date ranges and type search terms almost never asks the same question twice, so almost every request is a miss, and a miss costs the cache lookup plus the full query. A cache also knows nothing about the data: it cannot search, count or rank anything it has not seen verbatim, and it serves stale answers until something tells it not to.
Read replica
A replica is a second copy of the database, kept in step by replication, that serves reads so the primary can concentrate on writes. It is the right answer when the primary is simply saturated, and it gives you a standby for an outage. What it does not change is the query. The same scan that takes four seconds on the primary takes four seconds on the replica, because it is the same engine running the same plan over the same layout. The slow report is now slow somewhere else, with replication lag on top, and the writes still cost the same.
Index
An index server keeps a copy of the data reorganized for the questions screens ask: text tokenized into an inverted index, fields in column layouts for sorting and counting, one collection per entity. A six-field search with three counts and a sort is one request, and it stays one request at five million records. Those reads leave the database entirely, which is the difference from the replica, and the index answers questions it has never seen, which is the difference from the cache. Its cost is that it is as fresh as its last import, and it is a second system with its own schedule to operate. What is an index server? covers what it is and is not.
One more difference is worth naming: what each one knows. A cache knows strings. A replica knows the same schema as the primary. An index knows the data as data - that jacket and Jackets are the same word, that price is a number with ranges, that brand is a field whose values can be counted. That knowledge is why it can answer the 531-record search with Polo 302 · Levis 144 · Nike 85 beside it, and why neither of the others can.
Side by side
| Criterion | Cache | Read replica | Index |
|---|---|---|---|
| What it stores | Answers to queries already run | A full copy of the database | A copy of each collection, organized by field and word |
| Helps when | The same query repeats | The primary is saturated by reads | Screens search, filter, count and sort across many records |
| Fails when | Queries varyFree filters rarely repeat | The query itself is slowSame engine, same plan | A screen needs the exact current row |
| Freshness | Stale until invalidated | Seconds behind the primary | As of the last import: minutes to hours, per collection |
| Search and counts | Only if cached verbatim | Scans, as on the primary | Full-text, facets, totals in one request |
| Effect on production | Fewer repeated hits | Writes still replicate; reads move | Those reads leave the database entirely |
| Operational cost | Low; invalidation logic | Medium; a second database | Medium; a second system with its own schedule |
Choosing by the shape of the slow read
Ask what the slow read looks like. The same query, over and over, from many users: cache it. Many different cheap queries that together overwhelm the primary: replicate. Queries that search text, combine filters, count per value or total over a filtered set, and that get slower as the tables grow: index. The third is the shape of a catalog search, an orders listing with a sidebar, a report with totals, and it is the one the first two fixes cannot touch. Database index vs search index explains why adding more database indexes does not turn the replica into one.
A quick way to tell which you need: look at the slow query's plan and its log. If the same statement appears a thousand times, cache. If different statements, each fast, appear a hundred thousand times, replicate. If the statements are few, slow, and full of LIKE, GROUP BY and six-way WHERE clauses, index. The log usually settles it in ten minutes.
Using them together
They stack. A replica for the record-detail reads that must be exact and for standby. An index for every screen that searches, filters and counts. A cache in front of either for the handful of answers that really do repeat. Each one carries the reads it is shaped for, and the production database is left with the writes, which is the arrangement it was sized for in the first place.