When not to use an index server

What does the screen do?

1. Reads one record by idDatabase
2. Writes, or must be exact to the secondDatabase
3. Small table, simple lookupsDatabase
4. Searches, filters and counts across many recordsIndex
5. Totals over a filtered setIndex

The copy and the original

Everything an index server is good at follows from one fact: it is a copy, reorganized for reading and refreshed on a schedule. Everything it is bad at follows from the same fact. A copy cannot be the place a write goes. A copy is as fresh as its last import. A copy organized for finding and counting is not organized for reading one row cheaply, which the original already does perfectly well. So the question for every screen is simple: does this screen need the copy's strengths, or the original's?

Put differently: the index is for questions, the database is for facts. A question - which, how many, how much, in what order - is cheap on the copy and expensive on the original. A fact - this record, right now, exactly - is cheap on the original and, at best, slightly stale on the copy. Screens tend to be mostly one or mostly the other, which is what makes the sorting possible.

Keep on the database

Writes. Placing an order, changing a status, adjusting stock. Nothing a reader does through an index should change a record, and nothing should. The index is the read side; the write side has a home.

Record detail. Opening one order by its id is a B-tree lookup, which the database does in a millisecond. Routing it through an index gains nothing and adds a hop.

Anything exact to the second. A stock check at the point of sale, a balance before a withdrawal, an approval that depends on the state right now. A copy that is ten minutes old is wrong for these by definition, however small the lag.

Small tables with simple lookups. A few hundred thousand rows filtered by one indexed column will be fast on any database. A second system to operate is a cost, and here it buys nothing.

Transactions. Anything that must change several rows together or not at all. An index has no transactions and should not pretend to.

Move to the index

Three tells, any one of which is enough. A search box, because users expect it to forgive jaket, rank results and suggest completions. Filters with counts, because a sidebar that says Polo (302) before the click is one scan per filter in SQL. Totals over a filtered set, because a listing with a running sum or an average that changes with the filters is a report. A fourth, quieter tell: the table is past a few million rows and the screen slows every month. Those screens want the copy, and they are usually the ones the business complains about first, because they are the ones people use all day. RDBMS vs index server puts the two side by side.

A tell that is easy to miss: the screen already has a workaround. A nightly materialized view, a reporting replica, a cache with a long expiry, a rule that says not to run it during the day. Each of those is a sign that someone has already discovered the screen wants a copy, and built a small, fragile one. The index is the general version of the workaround, and replacing the workarounds is usually where the first integration pays for itself.

The screens in between

Most screens are mixed. An orders listing is index-shaped - search, filters, counts, a total - but clicking a row opens the record detail, which is database-shaped. The right design is the obvious one: the listing reads the index, the detail reads the database, and the user never knows two systems were involved. A dashboard is the same: the widgets read the index; the "edit" link under a widget goes to the application and the database.

The honest edge case is a screen that needs both search and exactness, such as an inventory console that must show live stock. Search it on the index, then fetch the live quantity for the handful of rows on screen from the database. Two requests, each to the system built for it.

A worked example

An order-management system. The checkout: writes, exact, transactional - database. The order-detail page: one id - database. The orders listing with its six filters, text search and running total - index, and it is the screen that was slow. The Urgent orders saved grid at 14 and the regions leaderboard with North at 395 - index, through a dashboard. The warehouse pick screen that must show what is on the shelf right now - database for the quantity, index for the search. Nothing was rewritten; four screens changed where they read from.

The honest trade

Adding an index server means a second system, a refresh schedule and a stated lag. For a screen that searches and counts, that trade is overwhelmingly worth it. For a screen that reads one exact row, it is not worth it at all. The skill is not in choosing the index; it is in choosing where not to, and the chooser at the top of this page is most of it. Incremental indexing covers the lag side of the trade.

Related

COMPARISON RDBMS vs index server: what's the difference? They are not rivals. One is built for correct writes and relationships, the other for fast reads that search, filter and count. Most systems that grow end up needing both. EXPLAINER Why not query the production database directly? Because it has a job already, and the reports compete with it. EXPLAINER Incremental indexing: keeping an index fresh An index is a copy, so it has to be refreshed. The question is how, how often, and what to tell users.