← All posts

Why LIKE Doesn't Scale: Inverted Indexes, Scatter-Gather, and How SaaS Should Index Search

September 25, 2026

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

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.

Inverted index mapping terms to document postings

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.

Scatter-gather search across shards with merge top-k

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)

When to choose each search technology

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:

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:

Bad dual-write to search vs good outbox/CDC indexing

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

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 BM25 and vector ANN search with merge and filters

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

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

  1. LIKE / leading-wildcard SQL is not a search architecture.
  2. Inverted indexes make term lookup cheap; ranking and facets do the real work.
  3. Scatter-gather latency tracks the slowest shard; shard count is hard to undo.
  4. BM25 + boosts/function score covers most lexical needs; add LTR/hybrid when data justifies it.
  5. Index asynchronously via outbox/CDC; treat search lag as an SLO.
  6. Enforce tenant isolation with dedicated indices or mandatory filters. Never trust the client.
  7. 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.