Search Systems

Key features

• A LIKE '%term%' predicate (leading wildcard) cannot use a standard B-tree index — the database scans every row and does a substring match inside it, so cost grows linearly with table size.
• Relational indexes answer "equals this value" or "falls in this range" on one or a few columns — they have no built-in concept of relevance ranking across many text fields.
• Full-text search needs typo tolerance (fuzzy matching), stemming (run/running/ran treated as related), and synonym handling — none of which a plain B-tree provides.
• Faceted filtering — narrowing results by category, price range, and tags simultaneously while keeping counts per facet — is a search-engine feature, not a SQL index feature.
• PostgreSQL's own full-text search (tsvector/GIN index) and MySQL's FULLTEXT index close part of this gap for a single database — the decision point is when ranking quality, query volume, or multi-field relevance outgrows what those built-ins handle well.

Command

-- This predicate cannot use a standard B-tree index:
SELECT * FROM articles WHERE body LIKE '%distributed consensus%';
-- Postgres/MySQL will scan every row and string-match inside it.

Key features

• Tokenization splits raw text into individual terms (roughly: words) — "Distributed Systems!" becomes tokens like "Distributed" and "Systems".
• An analyzer normalizes tokens after splitting: lowercasing, stripping punctuation, stemming word forms to a root (running/ran → run), and often dropping stopwords (the, a, of).
• The inverted index maps each normalized term to the list of documents (and often positions within them) that contain it — the structure that makes "find all docs with this term" an index lookup instead of a full scan.
• The same analyzer must run on both indexing and query text, or terms won't match — indexing "running" as "run" but searching for the literal string "running" would miss it.
• Ranking scores matches by relevance, commonly via a TF-IDF-family formula: how often a term appears in a document (term frequency) weighed against how rare that term is across all documents (inverse document frequency) — Elasticsearch and OpenSearch use BM25 by default.
• Filtering narrows the candidate set by exact-match criteria (category = "books", price < 50) and, unlike ranking, is typically binary — a document either passes a filter or it doesn't, with no relevance score involved.

Command

Indexing pipeline (build time):
  raw text -> tokenizer -> token filters (lowercase,
  stem, stopwords) -> terms -> written into the
  inverted index as term -> [doc IDs, positions]

Query pipeline (search time):
  raw query -> SAME analyzer -> terms -> look up each
  term in the inverted index -> candidate docs
  -> apply filters (exact match) -> score remaining
  docs (e.g. BM25) -> return ranked results

Key features

• Search engines optimize for read-heavy, relevance-ranked query patterns — not for the multi-row ACID transactions and foreign-key integrity a relational primary store provides.
• The standard pattern is: primary database holds the authoritative record, an indexing pipeline (batch job, change-data-capture stream, or dual write) copies searchable fields into the search engine.
• Because the search engine's data is derived, its index should be treated as rebuildable — a disaster-recovery plan should be able to reindex from the primary store rather than depend solely on the search engine's own durability.
• Keeping the two stores in sync (search index following primary-store writes) is itself a design problem — with real, bounded lag between a write landing in the primary store and becoming searchable, not instant consistency.
• Using a search engine as the only store for data that also needs strict transactional consistency (e.g. financial balances) is a common architecture mistake — those guarantees belong in the primary relational/transactional store, not the search index.

Command

Typical architecture:

  writes ---> [ Primary store: Postgres/MySQL/Mongo ]
                        |
                (indexing pipeline: CDC stream,
                 batch job, or dual write)
                        v
              [ Search engine: Elastic/OpenSearch ]
                        ^
  reads (search) -------'

  The primary store is authoritative. The search
  engine is a derived, rebuildable index of it.