RDBMS vs index server: what's the difference?

-- 1 · the results
SELECT * FROM products
WHERE category = 'Clothing'
  AND name LIKE '%jaket%'
ORDER BY ? LIMIT 20;

-- 2 · the count
SELECT COUNT(*) FROM products
WHERE … same filters …;

-- 3 · one per facet (brand, size, price band)
SELECT brand, COUNT(*) FROM products
WHERE … same filters …
GROUP BY brand;

-- 4 · the total
SELECT AVG(price) FROM products
WHERE … same filters …;

Two different jobs

A relational database exists to record what happened and keep it correct. An order is inserted, its lines with it, the stock count is decremented, and either all of that commits or none of it does. Every query sees a consistent state. The structure - tables, keys, constraints - is there so that the data cannot drift into contradiction, and the engine is tuned for many small, exact reads and writes.

An index server exists to answer questions about what happened, fast, across a lot of it. Which products mention jacket, how many per brand, what the average price is of the ones under 100, sorted by relevance, page three. It holds a copy of the records, flattened and organized by field and by word, and it answers those questions in milliseconds because that is the only kind of question it was built for.

These are not two solutions to one problem. They are two problems. Most systems start with only the first and discover the second as they grow.

Side by side

Read the rows about writes, consistency and joins and the database wins every one. Read the rows about search, counts, totals and pagination and the index wins every one. That is the whole comparison: each is built for its column.

One listing screen, done both ways

Take the products request used across this site: search text jaket in category Clothing, with counts by brand and size, price bands, an average price and typo tolerance of one edit. The lead visual shows both implementations.

In SQL that screen is at least four round-trips. One query returns the page of results, except that LIKE '%jaket%' matches nothing because the typo is not a substring, and if it did match, ORDER BY has no notion of relevance to sort by. A second query counts the matches. A third runs GROUP BY brand for the sidebar, and the same again for size and for price band, each one scanning the filtered set from scratch. A fourth computes the average. Every one of those scans competes with the transactions the database is also running.

In an index it is one request and one response: 531 records, Polo 302 · Levis 144 · Nike 85, Small 412 · Medium 88 · Large 31, three price bands - 0–25, 25–50 and 50–100 - holding 120, 245 and 166 records, an average price of 42.60, and Denim jacket and Rain jacket as suggestions, because jaket is one edit from jacket. The brands, the sizes and the price bands each total 531, because every facet was computed on the same filtered set as the results. 624 ms for the whole screen, from a copy that the transactions never touch.

When the database alone is enough

Small data: a few hundred thousand rows will survive almost any query pattern with a sensible index or two. Simple lookups: find by id, by key, by one indexed column. Screens without search or facets: a record detail page, an edit form, a short list filtered by one status. And anything transactional, which is to say anything that writes or that must be exact to the second. For all of these, adding an index server would be a second system to run with nothing to show for it.

When you need the index

Three tells, any one of which is enough. A search box: users expect it to forgive typos, rank results and suggest completions, none of which LIKE can do. Filter counts: a sidebar that says Polo (302) before the user clicks is a facet, and facets are one scan per filter in SQL. Totals over filters: a listing with a running sum, an average or a count that changes as the filters change is a report wearing a listing's clothes.

Add a fourth: data past a few million rows, where even the queries that used to be fine start competing with the writes. The index does not make the database faster. It takes those reads away from it.

How they work together

One-way flow. The database stays the source of truth and keeps every write. Records flow from it into the index on a schedule - pushed by your systems or pulled by the index - and nothing flows back. If the two ever disagree, the database is right and the index is refreshed.

Integrate the slow screens first. The catalog search, the orders listing with its filters and totals, the customer console that searches six fields. Each one is routed to the index and stops hitting the database. The checkout, the edit forms and the stock adjustment stay exactly where they are. Nothing is rewritten; a few screens change where they read from. Why not query the production database directly? goes into what the database gains from that.