Search Systems

What we're doing: Show why a leading-wildcard LIKE query degrades as a table grows, and what it still can't do even when it finishes.

-- Table: articles(id, title, body, published_at)
-- Index: a plain B-tree on articles(id) and articles(published_at)

-- Fast: uses the B-tree index on published_at
SELECT id, title FROM articles
WHERE published_at > '2026-01-01';

-- Slow: leading wildcard defeats any B-tree index on body
SELECT id, title FROM articles
WHERE body LIKE '%distributed consensus%';
-- Postgres/MySQL scan all rows, string-match each one.

-- Even if it returns in time, it cannot do this:
-- "rank results by how relevant they are to
--  'distributed consensus algorithms', tolerating
--  the misspelling 'consensis', and also match
--  articles that say 'Raft' or 'Paxos' as related terms"

6

The range predicate on published_at can use a normal B-tree index — this is what relational indexes are built for.

10

The leading % in the LIKE pattern means no B-tree can be used; the database must scan and string-match every row.

15

Even a successful full-table scan still cannot rank, fuzzy-match, or understand related terms — that requires a different indexing structure entirely, not just a faster scan.

Why this works: The failure mode isn't just "slow" — it's "the wrong tool," because even an infinitely fast substring scan still can't produce ranked, typo-tolerant, semantically related results the way a search engine's inverted index and scoring model can.

What we're doing: Trace two short documents from raw text through tokenization and analysis into the inverted index they produce, then show a query hitting that index.

Doc 1: "Running distributed systems is hard"
Doc 2: "Distributed databases replicate data"

Step 1 — tokenize (split on whitespace/punctuation):
  Doc 1: [Running, distributed, systems, is, hard]
  Doc 2: [Distributed, databases, replicate, data]

Step 2 — analyze (lowercase, drop stopwords, stem):
  Doc 1: [run, distribut, system, hard]
  Doc 2: [distribut, databas, replic, data]

Step 3 — build the inverted index (term -> doc list):
  run       -> [1]
  distribut -> [1, 2]
  system    -> [1]
  hard      -> [1]
  databas   -> [2]
  replic    -> [2]
  data      -> [2]

Step 4 — query "distributed systems", analyzed the
same way -> terms [distribut, system]
  distribut -> docs [1, 2]
  system    -> docs [1]
  Candidates: {1, 2}. Doc 1 matches both terms,
  doc 2 matches only one -> doc 1 ranks higher.

8

Both "Running" and "distributed" are stemmed to their root form (run, distribut) — this is what lets a search for "run" later match a document that only contains "Running."

14

The inverted index stores "distribut" once, pointing at both documents — this is the core structure: term to document list, not document to term list.

21

The query goes through the exact same analyzer as indexing, producing terms that can actually be looked up in the index built in step 3.

25

Ranking uses how many query terms each document matched (and would also weigh term rarity in a real TF-IDF/BM25 score) — doc 1 outranks doc 2 because it matches both query terms, not just one.

Why this works: Showing the same two words ("distributed", "systems") reduced to the same stems in different documents makes concrete why tokenization and analysis have to happen — and be consistent — before the inverted index or ranking can work at all.

What we're doing: Show what breaks when a team treats the search engine as the primary store for order data, and how the standard two-store pattern avoids it.

Anti-pattern: order records written directly and
only into Elasticsearch, no relational store at all.

1. Two requests update the same order's status
   concurrently -> Elasticsearch has no multi-document
   transaction to make this atomic the way a relational
   UPDATE ... WHERE with a transaction would.
2. A mapping change requires reindexing -> since there
   is no other copy of the data, the reindex source
   IS the search engine, and a mistake here has no
   independent backup to recover from.
3. Finance asks for a strongly consistent read of
   "current balance after this order" -> the search
   engine's near-real-time indexing (a short delay
   between write and searchable) makes this the wrong
   tool for a read that must reflect the latest write.

Standard pattern instead:
  Orders table in Postgres (source of truth, ACID
  transactions) -> CDC stream or batch job ->
  Elasticsearch index of orders (searchable copy).
  Balance reads go to Postgres; free-text/faceted
  order search goes to Elasticsearch.

6

Concurrent updates to the same document have no cross-document transaction guarantee in a search engine the way a relational transaction provides — a real gap, not a hypothetical one.

9

With no independent primary store, the search engine's own copy is the only copy — there is nothing to reindex from if something goes wrong.

13

Search engines index in near real time (a short, deliberate delay), which is the wrong consistency model for a query that must reflect the very latest write, like a financial balance.

Why this works: The failure modes are concrete and different in kind (concurrency, recoverability, consistency) — showing all three makes clear why the fix is architectural (keep a primary store) rather than a matter of using Elasticsearch more carefully.