Search Engines

The Problem With LIKE ‘%query%‘

 SQL text search:

 WHERE description ILIKE '%wireless headphones%'
 → full table scan, no relevance ranking, no typos,
   no stemming ("headphone" misses), no phrase awareness

 search engines invert the model entirely:
 pre-compute WHICH documents contain WHICH terms.

The Inverted Index

 forward index (normal):  doc → its words
 INVERTED index:          word → docs containing it

 "wireless"  → [doc3, doc17, doc88]
 "headphones"→ [doc3, doc42]
 
 term dictionary + postings lists per field;
 lookup = dictionary hit + list walk ≈ microseconds
 regardless of corpus size.

 enriched with linguistic processing at INDEX time:
   tokenization    split text into terms
   normalization   lowercase, accent-folding  
   stemming        running/runs/ran → "run"
   synonyms        earbuds ↔ headphones (configurable)

Relevance Ranking

 results ordered by SCORING, not just matching:

 TF-IDF/BM25 core ideas:
 - TERM FREQUENCY: doc mentions term often → more relevant
 - INVERSE DOCUMENT FREQUENCY: rare terms weigh more than "the"
 - field boosts: title match > description match
 - recency/business signals layered on top

 "wireless headphones":
   doc mentioning both in TITLE beats one mention in body —
   computed automatically from index statistics.

The Document Store Shape

 JSON documents in, indexed across ALL fields:

 POST /products/_doc/p77
 { "name": "Trail Shoe", "brand": "Ridge",
   "tags": ["outdoor"], "price": 129.99 }

 each field gets indexing strategy:
   text     → analyzed, searchable, ranked
   keyword  → exact-match/filterable (facets, aggregations!)
   numeric  → range queries
   
 aggregations = the hidden superpower:
   facet counts, price histograms, category rollups —
   analytical groupings served from indexes in milliseconds.

The Sync Problem

 search index is a DERIVED store; source of truth lives elsewhere:

 [postgres] ──CDC/dual-write──► [elasticsearch]
                │
 near-real-time lag (seconds typical)

 rules learned in production:
 - prefer CDC (change-data-capture) over dual-writes
   (dual-write = consistency bugs, own lessons cover why)
 - design for reindex-from-scratch: aliases let you build
   index-v2 fully, then atomically flip traffic
 - never treat search as authoritative storage

When Search Engines Fit

 ✓ product/catalog search with facets+ranking
 ✓ log analytics (the ELK stack use case)  
 ✓ autocomplete/typeahead
 ✓ geospatial + text combined queries

 skip when:
 ✗ simple exact lookups (KV/indexed SQL cheaper)
 ✗ transactional truth needed (they're not databases of record)
 ✗ small data (<1M rows: Postgres FTS is plenty)

Interview Framing

“Add product search” scored shape: justify beyond LIKE (relevance/stemming/facets), sketch the inverted-index concept, define sync pipeline as CDC-based with alias-reindex strategy, and place it correctly architecturally (“derived read store; Postgres stays source of truth”). Mentioning aggregations for faceted filtering shows you’ve built real catalog search.