Search engines and analytics stores
Two systems that are never the source of truth, and how to keep them from drifting.
What you will be able to do
- Explain why full-text search and analytical scans are separate systems
- Design an indexing pipeline with a defined freshness target
- Describe how to rebuild an index without taking the site down
- Choose between a search engine, a columnar warehouse, and a stream processor for a query need
Why not just query the database
LIKE '%phone case%' on a million-row table reads every row and cannot rank by relevance. A search engine builds an inverted index: for each term, the list of documents containing it. Matching becomes a lookup of sorted lists, ranking becomes scoring, and facets become counting per value.
| Query need | Engine | Why |
|---|---|---|
| Exact lookup by id | Primary database | Already indexed, strong consistency |
| Prefix and fuzzy text match | Search engine | Inverted index plus analysers and typo tolerance |
| Relevance ranking, boosting, facets | Search engine | Scoring model built for it |
| Sum revenue by region over a year | Columnar warehouse | Reads only needed columns, compresses, parallel scans |
| Count events per minute, live | Stream processor plus time-series store | Incremental aggregation, not repeated scans |
The indexing pipeline
Change data capture straight from the database log is the most common production shape, because it cannot miss a write made by another service. The outbox pattern gets you the same reliability without log-level access. Either way, the message must carry enough state to rebuild the document without reading the source again.
Reindexing without an outage
- Build the new index under a new name while the old one keeps serving.
- Dual-write to both indexes during the build, or replay events from a point in time.
- Switch the alias or pointer atomically once counts and spot checks agree.
- Keep the old index for a rollback window, then delete it.
Mapping changes are the usual reason for this drill: renaming a field or changing an analyser requires rebuilding, because an inverted index cannot be edited in place.
Analytics: the shape of the question
Operational databases store rows for one entity at a time. Analytical questions scan many rows and few columns, which is why columnar stores exist: reading one column out of fifty is a fifth of the I/O, and similar values compress heavily. Pre-aggregating into hourly or daily rollups turns a billion-row scan into a few thousand rows.
Worth remembering
- An inverted index maps terms to documents; a B-tree cannot do relevance ranking or tokenised matching.
- The search index is derived data. The primary database stays the truth and the index can be rebuilt.
- Freshness is a product decision: seconds for marketplace search, hours for an internal report.
- Columnar storage and pre-aggregation are what make scanning billions of rows feasible.