Data warehouse vs index server

Data warehouse

• Analysts, data teams, finance
• History across many systems, joined
• Loaded nightly or in batches
• SQL; seconds to minutes per query
• Too slow and too heavy behind an application screen

BIG QUESTIONS, FEW READERS

Index server

• Application screens, dashboards, assistants
• Current records, one collection per entity
• Refreshed hourly to weekly, per collection
• API; milliseconds per request, facets included
• Not built for ad hoc joins across years of history

FAST QUESTIONS, MANY READERS

Both are copies of your data. Different readers, different clocks.

Two copies, two purposes

Both a warehouse and an index server start from the same observation: the production database should not be the thing people read from when they want to analyse, and a copy organized for reading is the fix. From there they diverge, because they are organized for different readers. A warehouse is organized for a person with a question nobody anticipated. An index server is organized for a screen whose questions were designed in. That one difference sets everything else: the clock, the interface, the latency, the data model.

It helps to say what neither of them is. Neither is the production database, and neither should be: both exist so that reads of any size leave the system that takes the orders. Neither is a cache, because both reorganize the data rather than remembering answers to it. They are two different reorganizations of the same records, and the question is only which reorganization a given reader needs.

What a data warehouse is for

Analysis across time and across systems. Five years of orders joined to the customer master, the product hierarchy and the marketing spend, in a model somebody designed, queried in SQL by someone who knows what they are looking for and can wait a minute for it. Loads run nightly or in batches because the questions are about history, not about the last hour. The readers are few and skilled. The warehouse is the right home for the quarterly analysis, the cohort study, the finance reconciliation.

A warehouse also carries the organization's memory. Records that the operational systems have archived or overwritten stay in the warehouse as they were, which is what makes year-on-year analysis possible. That history is a strength, and it is also why a warehouse is heavy: it grows without bound, and the model that joins it all is a project in its own right, maintained by a data team.

What an index server is for

Screens, and the people and assistants that use them. A catalog search that forgives jaket and returns 531 results with brands and sizes counted beside them. An orders listing filtered six ways with a running total. A dashboard that shows North at 395 orders and updates every hour. The questions are known in advance, because screens are designed, and the index is built to answer exactly those in milliseconds through an API, for thousands of concurrent users who will never see a query. What is an index server? describes the structure.

The index carries the present, not the past. Each collection holds the current state of its records, refreshed from the source, so a product that changed price yesterday shows today's price, and the orders listing shows this month's orders with their current status. If a screen needs last year's view of a record, that is a warehouse question. If it needs today's, it is an index question, and the index answers it in a millisecond for everyone who asks.

Side by side

Freshness and cost

The clocks differ because the questions do. A warehouse that is a day behind is fine for last quarter's analysis; an operations grid that is a day behind is useless. So the index refreshes hourly for the busy collections and daily for the quiet ones, per collection, while the warehouse loads overnight. The cost profile differs too: a warehouse charges by the scan, which is fine for a few analysts and ruinous behind a public screen; an index answers a bounded set of question shapes at a flat, predictable cost per request.

There is a tempting shortcut: put a dashboard tool on the warehouse and call it done. It works for an executive summary refreshed once a day and read by a dozen people. It fails the moment the dashboard needs filters that recount on every click, a search box, or a thousand users at nine in the morning, because each interaction becomes a warehouse scan with a warehouse latency and a warehouse bill. The index exists for that interaction pattern, and it is a different pattern from analysis.

Both, usually

Companies that grow end up with both, fed one way from the same database. The warehouse answers the analysts, who need breadth and history. The index answers the screens, which need speed and currency. Neither replaces the other: pointing a dashboard for a thousand users at the warehouse makes it slow and expensive, and asking an index server for a five-year cohort join is asking it for something it was not built to do. Feed both from the same source, one way, and let each refresh on its own clock; the two never need to talk to each other. Pick by reader. If the reader is a person with SQL and time, warehouse. If the reader is a screen, an assistant, or a person with a filter sidebar, index.

Related

COMPARISON OLTP vs OLAP: two kinds of database work Transactions and analysis want opposite things from a database. The split explains most slow reporting screens, and where an index server fits. PILLAR What is an index server? A copy of your data organized for finding and counting, not for storing and updating - and the reason the fast screens in large applications are fast. EXPLAINER Why not query the production database directly? Because it has a job already, and the reports compete with it.