WHERE title ILIKE '%wireless%' works in a demo and dies in production. Leading wildcards skip B-trees, faceted navigation turns into a pile of GROUP BYs, typo tolerance becomes a science project, and multi-tenant filters fight the query planner. Product search, log analytics, and knowledge bases need a different data structure than OLTP tables.
This post covers the mechanism: inverted indexes, distributed scatter-gather, relevance (BM25 and friends), engine choices, SaaS tenancy of indexes, and why async indexing via outbox/CDC beats dual-writing your search cluster. Vectors and hybrid retrieval close the loop as a section, not a rewrite of the whole article.
What SQL struggles with
- Full-text across large corpora with ranking, not boolean presence.
- Fuzzy / typo-tolerant matching ("iphne case").
- Facets: counts per brand/size/price bucket over the current result set.
- Multi-language analysis, stemming, and ranking.
- Relevance tuning: field boosts, freshness, personalization.
Postgres tsvector / pg_trgm are excellent under roughly a million documents for many apps. Past that, or when you need distributed facets and relevance labs, you want a search engine.
Inverted index: the core idea
Instead of scanning documents for a term, store term → list of documents (a posting list). Multi-term queries intersect lists.
Term lookup is effectively dictionary-cheap; cost shifts to intersecting and scoring postings. That is why search engines feel instant on corpora that would melt a sequential LIKE.
Distributed architecture: scatter-gather
Indices shard across nodes. A coordinator receives the query, fans it out to relevant shards, merges top-k results, and returns.
Tail latency is dominated by the slowest shard. Stragglers matter more than average shard speed. Shard count is a one-way door in many engines (primary shard count fixed at index creation): overshard and you pay coordination overhead forever; undershard and reindexing is a project. Plan capacity with headroom, then use replicas for read scale and failover.
Routing is typically hash(routing_key) % primary_shards. For SaaS, routing on tenant_id can colocate a tenant's docs (better filters, noisier neighbors) or you keep global hashing and enforce tenant filters on every query. Either way, mandatory filters are required to avoid cross-tenant leakage.
Engine choices (short map)
Pick based on ops appetite and query shape, not blog-post fashion. Moving off a wrong search cluster is more painful than migrating a cache.
Relevance: BM25 and then some
BM25 (Best Matching 25) is still the workhorse lexical scorer, an evolution of TF-IDF that saturates term frequency (the tenth "running" helps less than the second) and normalizes for document length:
score(q,d) ≈ Σ IDF(t) · (tf · (k1+1)) / (tf + k1 · (1-b + b·|d|/avgdl))
Defaults are fine to start. Then add:
- Field boosts: title matches beat body matches.
- Function score: blend BM25 with recency, popularity, stock, margin.
- Learning-to-rank: optimize from click/order signals when you have volume.
- Hybrid: BM25 candidates ∪ vector ANN, then re-rank (see below).
Indexing path: do not dual-write the search cluster
Sync dual-write (OLTP transaction + immediate index update) recreates the dual-write problem: one side succeeds, the other fails, search lies. Prefer async:
Seconds of lag are usually an acceptable SLO for product search ("new SKU visible within 5s"). You gain a single source of truth and the same reliability story as domain events. (See the outbox/CDC post for the relay mechanics.)
Ops that keep search boring
- Reindex via aliases: build
products_v2, atomically swap aliasproducts; never reindex in place as the live read target. - Hot / warm / cold: recent indices on fast nodes; old logs on cheap storage.
- Per-tenant index vs shared index + filter: dedicated indices isolate noisy tenants and simplify deletes; shared indices maximize density. Enterprise silos often get dedicated indices; the long tail shares with a mandatory
tenant_idfilter (and document-level security where available). - Mapping discipline: accidental
texton high-cardinality IDs explodes memory; explicit mappings beat dynamic surprises.
The vector wave
Dense embeddings let "budget-friendly transportation" match "affordable cars" without shared keywords. Pinecone, Qdrant, pgvector, and ES/OpenSearch dense_vector fields all ship ANN (HNSW/IVF). RAG patterns for SaaS-AI sit on the same rails: retrieve, then generate, still with tenant filters on retrieval.
Hybrid is the pragmatic default in 2026: lexical precision for SKUs and error codes, vectors for natural language, re-rank for the blend. Do not throw away BM25 because embeddings are trendy. The failure modes differ, and users still type exact model numbers.
Analysis pipelines (why "search" is not just storage)
Before indexing, text passes through analyzers: tokenize, lowercase, strip stopwords, stem or lemmatize, maybe synonym expansion. E-commerce may treat iPhone-15 and iPhone 15 as one token; legal search may preserve punctuation. Getting analysis wrong is a relevance bug that looks like "Elasticsearch is bad." Invest in a small golden-query suite per locale before tuning BM25 knobs in production.
Queries deserve the same care: intent classification, locale detection, and filters (tenant, ACL, in-stock) applied before or as part of retrieval so you never re-rank documents the caller must not see.
Capacity and failure modes
- Heap pressure from fielddata / high-cardinality aggs: facet carefully; prefer doc values / keyword fields.
- Mapping explosions: user-defined fields without limits.
- Reindex storms: changing analyzers requires a new index; schedule and throttle.
- Split-brain ops: treat search as a system with runbooks, not a library you "just call."
For multi-tenant SaaS, delete-by-tenant must be complete: index documents, snapshots, and any embedding store. Partial deletes are GDPR incidents with extra steps.
One more operational habit: expose search freshness as a first-class metric (max indexing lag by tenant or index). Product and support should know whether "I can't find the SKU I just created" is expected eventual consistency or a stuck CDC pipeline.
Key takeaways
LIKE/ leading-wildcard SQL is not a search architecture.- Inverted indexes make term lookup cheap; ranking and facets do the real work.
- Scatter-gather latency tracks the slowest shard; shard count is hard to undo.
- BM25 + boosts/function score covers most lexical needs; add LTR/hybrid when data justifies it.
- Index asynchronously via outbox/CDC; treat search lag as an SLO.
- Enforce tenant isolation with dedicated indices or mandatory filters. Never trust the client.
- Hybrid BM25 ∪ vectors is complementary, not a replacement.
Search is a serving system with its own consistency, capacity, and tenancy story. Design it that way, not as an afterthought ILIKE on the primary.