START WITH A PROBLEM
A database that keeps growing, screens that keep slowing
The screens were fine at 200,000 rows. At five million, the same queries crawl, and the next index will not save them.
- More indexes helped once
- Read replica same plans, plus lag
- Cache only repeats
The symptom
Everything was fine at 200,000 rows. The listing screens - orders with filters, sorting and a running total; customers searchable by six fields; a product console - were quick, and nobody thought about them. At five million rows the same screens crawl, and they degrade a little more every month.
The usual moves have been made. A read replica was added, and it is exactly as slow as the primary, now with replication lag. A cache was added, and it helps the second person who runs the identical query and nobody else. More indexes were added, and each one helped less than the one before while writes got slower.
Why it happens
A database index is a B-tree. It answers find the rows where this column equals this value and find the rows in this range very well, which is what keys and foreign keys need. It answers search these six fields for this text, count the matches by three of them and sort by relevance badly, because that is not one lookup, it is a scan dressed up as several.
Composite indexes multiply. A screen that lets people filter by any combination of six fields would need a composite index for each combination to make every path fast, and the planner gives up on wide predicates long before that. A read replica copies the query plan along with the data, so it moves the load without reducing it. A cache stores answers, and listing screens with free filters rarely ask the same question twice.
None of these are mistakes. They are the right tools for other problems. The problem here is that the screens ask index-shaped questions of a store that is shaped for transactions.
What changes with an indexed layer
Route the heavy screens through an index built for them. A search index holds a copy of each collection organized by field and by word, with counts and totals as facets, so the six-field search with three counts and a sort is one request, and it stays one request at five million rows or fifty.
The database stays the source of truth and keeps every transaction. It just stops carrying reads it was never built for. Integration is screen by screen: the orders listing moves first, then the customer search, and the checkout stays where it is. Nothing else in the application changes, and the writes get faster, because the reporting indexes can finally come off.