DATA & INDEXING
Why not query the production database directly?
Because it has a job already, and the reports compete with it.
Two workloads, one machine
Transactions are small, exact and constant: insert an order, update a status, read one customer by id. Each touches a few rows, holds a lock for a moment and commits. The database is tuned for thousands of these a minute, and when it is doing only this, it is very good at it.
Reports and search are the opposite shape. A month-end report scans every order in the period, joins it to customers and products, and groups the result by region and category. A catalog search reads every product row looking for a substring. A listing with filter counts runs one GROUP BY per filter over the same filtered set. These are scans, joins and group-bys over everything, and they are heavy by nature, not by mistake.
Put both on one machine and they compete for the same CPU, the same memory and the same locks. The report does not know it is slowing order entry, and order entry does not know why it is slow.
What goes wrong
Lock waits: a long scan holds shared locks that the writes queue behind, or the writes hold locks the scan waits on, and either way the screen that should take a second takes ten. Cache eviction: the database keeps the hot transactional pages in memory, and a full scan pushes them out, so the transactions that follow go to disk until the cache warms up again. Month-end slowdowns, because the heaviest reports and the heaviest transaction volume land in the same week. And the unofficial rule, written nowhere and known to everyone: no big reports before 6pm.
Each of these is the same thing seen from a different seat. The database is doing two jobs, and the second is the one that hurts.
The usual fixes and what each is for
Teams reach for three things, in roughly this order, and each one is a good tool for a problem other than this one.
- Takes read load off the primary
- Survives a primary outage
- Same query plans, same slow scans
- Adds replication lag without adding speed
- Repeated identical queries become instant
- Cheap to add in front of an API
- Free-form filters rarely repeat
- Stale until invalidated; no counts, no search
- Built for analysts and long-running batch
- Joins history across many systems
- Seconds per query, not milliseconds per screen
- Too heavy to sit behind an application page
A read replica runs the same engine with the same query plans over the same data. A scan that takes four seconds on the primary takes four seconds on the replica, now with replication lag on top. It protects the primary from the load, which matters for availability, and it does nothing for the speed of the screen.
A cache remembers answers. The second person to run an identical query gets it instantly; everyone with a slightly different filter, date range or search term gets a miss. Listing screens with free filters are almost all misses, and a cache cannot count, search or rank anything it has not seen.
A data warehouse is built for analysts: wide history, many systems joined, long-running queries that nobody is waiting on a page for. Pointed at an application screen it is too slow - seconds, not milliseconds - and too heavy, and it is usually loaded nightly, which is the wrong freshness for an operations console.
And more indexes, which help the first report and slow every write, and which run into the limits that Database index vs search index describes.
The indexed-copy approach
Keep a copy of the data those screens ask about, organized for the questions they ask. The copy lives in an index server: one collection per entity - orders, customers, products, invoices - each with its own index, refreshed from the database on a schedule. A search is a lookup rather than a scan. Counts and totals are facets, computed beside the results on the same filtered set. The six-field search with three counts and a sort is one request, and it stays one request as the tables grow.
The database is not consulted for those screens any more. It keeps the transactions, and only the transactions, which is what it was sized for in the first place.
What stays on the database
Writes, all of them: nothing an application does through the index changes the source. Record detail, where one row is read by its id and the B-tree is the right tool. Anything transactional, where several rows must change together or not at all. And anything that must be exact to the second: a stock check at the point of sale, an account balance before a withdrawal. Those screens keep reading the database, and they get faster, because the scans have left.
The trade-off, stated plainly
The index is as fresh as its last import. Orders refreshed hourly are up to an hour old; a product master refreshed nightly can be a day old. That is a real limit and the honest way to handle it is to show it: as of 09:00 on the screen, with the schedule chosen per collection to match what each screen needs. For reporting and listing screens that trade - a stated lag for a response in milliseconds and a database left alone - is the right one almost every time. For the screens where it is not, keep them where they are. Incremental indexing covers how the refresh works and how to choose the schedule.