Search & Autocomplete — Trade-offs & Decisions

Relational databases are not limited to plain B-tree prefix lookups for text — many support full-text indexes, trigram indexes, and specialized extensions, alongside prefix indexes for suitable access patterns. For smaller corpora, modest search QPS, and simple relevance needs, database-native full-text search can be entirely reasonable — no separate system to run, no index-lag consistency model to reason about.

Becomes justified once requirements grow: sophisticated relevance ranking (BM25-style scoring, multi-signal ranking), high search QPS that would compete with transactional workload, fuzzy/typo matching, faceted aggregation, and the ability to scale search capacity independently of the transactional database.

How often buffered documents become a new searchable segment (see Architecture Deep Dive → Refresh, Segments and Visibility) — more frequent refresh means fresher results and more background merge overhead.

Processing indexing events in larger batches is more throughput-efficient per event but delays when any individual event's effect becomes visible.

The indexing pipeline's own throughput ceiling — a pipeline sized for steady-state load will fall behind during bulk imports or event bursts, regardless of refresh interval.

More aggressive refresh creates more, smaller segments, increasing background merge work — this competes for the same CPU/I/O capacity serving live queries.

Query repetition/cardinality (how often the exact same cache key recurs), how heavily the workload is personalized or filtered, how fresh the underlying ranking/index needs to feel, and product tolerance for slightly stale results. A high-repetition, low-personalization workload can tolerate a much longer TTL than a highly filtered, highly personalized one.

Fresher results, generally lower hit rate, more load reaching the index — reasonable for rapidly changing corpora or where staleness has real product cost.

Higher hit rate, staler results, less index load — reasonable for stable corpora and generic, highly-repeated queries.

Less operational surface — one system to run and keep synchronized. Whether it reliably meets an aggressive autocomplete latency budget under load depends on the specific engine, its prefix-query implementation, and how much concurrent full-text query load it's also absorbing — not something to assume either way without knowing the real numbers.

Can be purpose-built for consistent low-latency prefix lookup, isolated from full-text query load entirely. Costs: a second system/index to maintain and keep synchronized from the same underlying data.

Efficient prefix traversal, and can be compact and purpose-built for this exact access pattern if implemented well. Update cost (inserting new terms, adjusting precomputed top-k completions) and memory/operational complexity vary meaningfully by the specific implementation chosen — it is not automatically the cheapest or simplest option.

Simpler to reason about — prefixes are just indexed terms, reusable with standard inverted-index query machinery — at the cost of a larger index (every prefix of every term is a separate indexed entry) and correspondingly higher index-time write cost.

Reads committed database changes (via WAL/binlog or an equivalent mechanism) with low coupling to application code — the application doesn't need to know indexing exists. Adds real operational complexity: a CDC connector, a broker/log to buffer events, and ordering/offset-management semantics that depend on the specific connector and broker chosen — not an automatically zero-cost, zero-latency mechanism.

The application explicitly publishes a domain event ("ProductUpdated") when it makes a change, carrying rich domain semantics beyond a raw row diff. Requires the application to reliably publish these events (including under partial failure — the classic dual-write problem between the database write and the event publish) — producer correctness becomes a real dependency.

Periodically querying the source for changes, or re-importing a full snapshot on a schedule. Operationally simple for some datasets, but generally has higher freshness lag and adds direct read load to the source on each poll/import cycle.

Produces balanced document distribution across shards with a simple, predictable assignment rule. The cost: since any document could be on any shard, a general keyword query typically must fan out to every shard to be sure it's found all matches.

Routes documents (and matching queries) by a meaningful key — tenant, category, region. Queries that can supply that key touch only the relevant shard(s), reducing fan-out and coordination cost meaningfully. The cost: risk of imbalance/hot shards if some tenants/categories are much larger than others, and added complexity for any query that must search across the routing dimension.

Simple to implement and reason about for shallow pages. Cost grows with page depth in a scatter-gather system (every shard must compute results up to the requested offset, not just the page size), and results can shift or duplicate across pages if the underlying document set changes mid-pagination.

Carries forward the last result's stable sort values plus a deterministic tiebreaker, letting each shard resume from that position without recomputing from the start — cost stays roughly constant regardless of how deep the user pages. Cost: slightly more complex API contract (opaque cursor rather than a page number), and it doesn't by itself solve result-set changes mid-pagination without added snapshot/point-in-time semantics.

Caches the complete, final response for a specific cache key (query + filters + sort + locale + page + ranking version). Highest potential latency win on a hit, but only useful when that exact combination recurs — poor fit for highly personalized or highly filtered traffic.

Caches the retrieval-stage candidate set (before final ranking/personalization), reusable across requests that share retrieval parameters even if final ranking differs — useful when personalization/re-ranking varies but the underlying candidate pool doesn't.

Caches facet counts or filter-eligible document sets independently of the keyword query, since these can be expensive to recompute and may be reusable across many different keyword queries sharing the same filter.

Caches the (potentially non-trivial) result of analyzing/normalizing a raw query string, reusable regardless of what filters or personalization apply on top.

For traffic where the effective cache key is nearly unique per request (heavy personalization combined with many filter combinations), a cache may add complexity without a meaningful hit rate — sometimes the right answer is not caching that layer at all.

Guarantees the user never sees an incomplete result silently presented as complete. Costs full availability during any partial shard outage — a single unreachable shard takes down the entire query, even though most of the corpus was reachable.

Preserves availability — users still get useful results from the shards that responded. Requires explicitly marking the response as partial/incomplete (a flag, a UI indicator, a monitored signal) so it is never silently mistaken for a complete result set.