Search Engines in System Design: When and How to Add Search

The cluster left precise debts for this article. Database replication pointed here for “when search deserves a system of its own, and what that system must keep consistent.” Database sharding noted that search is where teams meet “its own sharded system” before they ever chose one. Database indexing called the inverted index “the same sorted-copy idea pointed at words”; this article is the design around that idea, and its internals are the deep dive’s to own. And the data-model article named this pipeline the most visible example of polyglot persistence’s tax: an added store, and the sync path that keeps it honest.

A search index is a derived, query-optimized copy of data the database already holds: organized by words instead of rows, ranked by relevance instead of filters. To add search is to add a second stateful system and a pipeline that keeps it in step with the first: the index answers text queries the database cannot answer well, and everything else in this article is the cost of that bargain.

Search vs database queries: the honest boundary

The boundary between the two is semantics before speed. A database query is a filter with a contract: exact predicates over structured columns (IDs, ranges, foreign keys) returning the rows that satisfy them, in an order the schema defines, served by the sorted copies database indexing builds. A search query is a ranking problem: which documents best match these words, in what order, scored by how the words are distributed; a question the database does not answer even when it is fast, because ranking by relevance is not a predicate. The two compose cleanly; the mistake is treating one as a degraded version of the other.

The standard attempt at degradation is LIKE '%term%', and its failure is structural, not incidental. A leading wildcard defeats the index’s sortedness (the leftmost-prefix logic from the indexing article) so the match runs as a scan. The results come back unranked: every match is equal, and the user sees insertion order instead of meaning. And the matching is literal: “running” does not match “ran,” “Wi-Fi” does not match “wifi,” and a typo ends the query. None of this is the database failing at its job; it is the database being asked a job it was never given; text is a different data shape, and it earns its own query engine.

Real systems run the two engines together, and the composite is a standard shape: structured filtering in the database, relevance ranking in the search engine. A catalog query is “in-stock items, category X, price under Y” (predicates) “sorted by best match for the user’s words”, ranking; the honest architecture answers the predicates where they are cheap and the ranking where it is native, and composes the two rather than duplicating either. The mistake at the boundary is the opposite one as well: indexing everything into the search engine and treating it as a second database (an unranked, untransactional one) which pays the pipeline cost for queries the first database already answered well.

When to add Elasticsearch and when not

The signals that earn a search system are user-shaped. Free-text queries, typed by humans, are the first: a search box is a promise of relevance. Ranking that must reflect meaning is the second; the words users type are rare or common in your corpus, and the ranking should know which. Typo tolerance, stemming, and prefix-as-you-type are the third: the features that make a search box feel alive rather than literal. Faceted navigation (counts by category, brand, price band) is the fourth, because it is text aggregation the database does at full-scan prices. When two or more of these are product requirements, the search system has earned its place; when zero of them are, it has not.

The costs are the reason this is a decision and not an upgrade. A search engine is a second stateful, distributed system: its own capacity to plan, its own failure modes to survive, and (the cost database sharding named) its own sharded system, with the fan-out and rebalancing that implies. Behind it stands the pipeline that keeps it synchronized, and behind the pipeline the consistency question this article keeps circling. When none of the signals are present, the honest answer is the one the ladder always gives: the structured lookups belong to the database and its indexes, and no second system is cheaper than the first one being correct.

The sequencing question deserves its own answer, because premature search infrastructure is a real failure mode. Most products start correctly without this system: a few indexed columns answer the early queries, and the search box ships against the database until users type enough words to feel the difference. The honest signal to watch is user behavior, not scale numbers, when relevance complaints appear in the support queue, the pipeline work is earned. And the migration path is well-trodden when it is: stand the engine up beside the database, backfill the index from the source of truth, run both behind a flag, and cut the search box over when the counts verify; the same blue-green pattern the reindex section uses for recovery, used once for adoption.

The search indexing pipeline

The pipeline is the design problem of this article, and there are three architectures for it. Application dual-write: the service writes the database, then indexes the document; one code path, no new components, and a failure between the two writes is a drift nobody notices until a user finds a search result that should not exist. Change data capture: an indexer tails the database’s own ordered log (the same stream replication consumes) and turns each committed change into an indexing operation; the pipeline decouples from the application, and the database’s commit becomes the pipeline’s trigger. Scheduled full reindex: the reset button: rebuild the index from the source of truth on a schedule, complete and expensive. The mature default is the second, with the third as a periodic audit that proves the pipeline never lied.

The pipeline’s guarantees are worth stating precisely, because they decide what the search box may promise. Delivery is at-least-once, so indexing must be idempotent: the document’s ID is the entity’s ID, and re-indexing the same change twice lands the same document twice. Ordering comes from the log; the newest version of an entity wins, applied in commit order. And the failure mode is time: an indexer that falls behind serves yesterday’s truth with full confidence, so pipeline lag is the primary metric on this stack’s dashboard; a cousin of the replica lag read/write separation manages on the database side, and the number that tells the team how stale the search box honestly is.

Failure handling is where the pipeline earns its architecture. An indexer that crashes mid-stream resumes from its checkpoint; the log’s offset makes replay safe, because idempotent indexing turns a re-delivered change into a no-op rather than a duplicate. A document the indexer cannot process is quarantined to a dead-letter queue with the reason attached, because a pipeline that stops for one malformed record is a pipeline one bad row away from an outage. And an indexer that simply cannot keep up (a corpus growing faster than the indexing rate) is a capacity problem, not a retry problem: the slow-consumer math is backpressure’s subject, and the honest dashboard shows the trend line, not just the current lag.

Analysis: what the index actually stores

Search engines do not store rows and do not quite store documents; they store what analysis makes of them. The analysis chain turns text into tokens: split on word boundaries, lowercase, drop stop words, reduce inflections, so “Running Wi-Fi Networks” and “run wifi network” land on the same terms. The inverted index then maps each token to the documents containing it: the sorted-copy idea database indexing applies to column values, pointed at words. Ranking falls out of term statistics; the classic intuition that rare terms count more is the core of the scoring families in use, and the reason a search engine can rank while a database can only filter.

Analysis is a schema, and the schema is expensive to change. A new stemming rule, a newly searchable field, a different tokenizer; each means reprocessing every document, which means reindexing, which is the full-rebuild cost the pipeline section kept in reserve. Index-time and query-time analysis must also agree: a query analyzed differently from its index finds nothing. The design review therefore treats the analysis chain like a database migration (versioned, deliberate, changed with the reindex cost written next to the change) and the structures underneath, the inverted index at depth, sharding and replicas, and the near-real-time refresh that makes writes searchable within a second, are the deep dive’s machinery.

The document the pipeline writes is itself a design decision, and the shape is worth naming: a flattened projection of the entity: the searchable text fields, the summary fields the results page renders, and the routing fields (entity ID, version, timestamps) the pipeline and the UI depend on. It is not the row, and it should not try to be: the index answers search queries and renders summaries, and the detail page still reads the source. Teams that index the whole row to “keep options open” pay the reindex cost on every schema change, for fields no query will ever touch; the projection discipline is the same one the caching series applied to cached shapes.

The consistency question and the reindex

Here is the debt replication’s article left for this one: what the system must keep consistent. The answer is a pair of copies and a promise the architecture cannot fully keep: the index is eventually consistent with the database, not because the search engine is sloppy, but because the pipeline is a distributed system with delivery semantics of its own. The drift is what users find: a search result that disagrees with the product page, a deleted item still surfacing, a new item invisible until the indexer catches up. The monitoring follows directly: pipeline lag on the dashboard, and a periodic full reindex as the audit that proves the incremental path never lied.

The reindex deserves its standing as the recovery of last and first resort, because of a principle this stack inherits from the caching series: a derived copy is disposable. Event-driven architecture named the same escape (a projection can always be dropped and rebuilt from the log) and a search index is exactly that kind of projection. The recovery pattern is a rebuild from the source of truth: index into a new index while the old one serves, then cut queries over when the counts verify; a blue-green deployment for indexes, routine because the index is a copy and never the truth. What makes it routine rather than heroic is the discipline the pipeline section built: idempotent indexing, versioned entities, and a rebuild that can run beside traffic.

Versioning closes the last race the pipeline can lose. An update indexed as a whole-document replacement carries the entity’s version; an indexer that receives changes out of order applies them guarded (the older version is discarded, the newer wins) and an unversioned index is one reordered delivery away from resurrecting a deleted field. The discipline is small and the failure without it is quiet: the search box disagrees with the product page, the counts verify, and the pipeline logs show nothing wrong, because the wrongness happened in the order, not the content.

The read path carries the last of the honesty. Search results are summaries that link to the source; the product page is the truth, and when the index and the database disagree, the database wins, so anything transactional renders from the source, never from the derived copy. Facet counts and aggregations are as-of-last-index, and the interfaces that use them say so where it matters. None of this is a defect of search; it is the price of a second copy, and the design that names the price is the design that gets to keep the feature.

FAQ

When should you add Elasticsearch to a system?

When users type words at you: free-text search, relevance ranking, typo tolerance, as-you-type suggestions, or faceted navigation are product requirements. Each is a text-shaped query a database answers poorly or not at all. If none of them is required, the database and its indexes are the honest answer; a search system is a second stateful, distributed service plus a pipeline, and it should be earned.

Why not just use SQL LIKE for search?

Because the failure is structural: a leading wildcard defeats the index, so the match scans; the results are unranked, so the user sees insertion order instead of relevance; and the matching is literal, so typos, inflections, and normalization end the query. LIKE is a corner-case tool; search is a product feature backed by tokenization and ranking.

How does a search index stay in sync with the database?

Three architectures: application dual-write (simplest, drift-prone), change data capture over the database’s log (decoupled, at-least-once, the mature default), and scheduled full reindex (the reset button and the audit). Because delivery is at-least-once, indexing is idempotent (the document ID is the entity ID) and because the log is ordered, the newest version wins.

What is a search indexing pipeline?

The machinery that turns committed database changes into searchable documents: a change source (the log), an indexer that transforms and writes documents, and the monitoring (lag above all) that tells the team how stale the search box honestly is. It is polyglot persistence’s most visible tax, and the reason adding search is a design decision rather than an install.

Is the search index consistent with the database?

Eventually, by design, not by accident. The index is a disposable derived copy, rebuilt from the source of truth when it drifts, monitored by pipeline lag, and always subordinated to the database on the read path: summaries from the index, transactions from the source. The system that names this contract gets a search box it can trust; the one that doesn’t gets results that argue with the product page.

What is a blue-green reindex?

Rebuilding the index into a new copy while the old one serves, then cutting queries over when the counts verify, the deployment pattern applied to an index. It turns a schema change or a drift recovery into a routine operation: both indexes exist, the old one is authoritative until the new one is proven, and the rollback is a pointer, not a restore.

Related articles

• Next read: Elasticsearch architecture, the internals this article kept pointing at: the inverted index at depth, shards and replicas, and the near-real-time refresh.
• database indexing; the sorted-copy idea the inverted index is built from.
• database replication, the ordered log both the replica and the indexing pipeline consume.
• event-driven architecture, the projection principle: derived copies rebuilt from events, never hand-repaired. Reindexing becomes replay, the search index becomes a subscriber, and the pipeline this article drew becomes an event stream with a purpose.
• read/write separation, the lag-management cousin on the database side of the stack. Both patterns buy read scale by accepting that a copy answers a moment behind its source, and both pay the same reconciliation bill.
• blob and object storage; the document tier this pipeline reads beside the database. The index wants the text in one place and the binaries somewhere cheap, and this tier is that somewhere.