The engineering team behind a video-hosting service swapped its SQLite FTS5 index for an OpenSearch cluster, cutting zero-result queries from 12 % down to 1.4 % and lifting the search-to-click rate by 9 % while keeping latency under 28 ms.
Why the switch became urgent
SQLite’s full-text search extension (FTS5) is attractive: it lives in the same file as the rest of the data, carries no licensing cost, and returns matches instantly for exact token matches. The platform’s logs, however, showed that a dozen percent of user searches returned nothing at all. Misspellings such as “intersteller” or “avengrs endgame” – the kind of typos people make on mobile keyboards – were the primary culprits.
A quick hack using trigrams (three-character fragments) lowered the blank-search rate to 7 % but introduced two problems. First, the index ballooned to more than three times its original size, inflating storage costs and slowing updates. Second, relevance suffered; the fuzzy matching returned a noisy mix of unrelated videos, confusing users instead of guiding them.
The team concluded that a purpose-built search engine, with native typo-tolerance and sophisticated relevance scoring, was required.
Building the OpenSearch pipeline
Keep SQLite as the source of truth
OpenSearch served as a disposable, read-only replica. All video metadata stayed in SQLite; the search index could be rebuilt without risking data loss. When the OpenSearch cluster went down, the application fell back to the original FTS5 engine automatically.
Layered relevance with a “should” query
Instead of relying on fuzzy matching alone, the query combined three clauses:
- Exact phrase match – highest boost, rewarding users who typed the title correctly.
- All terms present – medium boost, catching queries where every word appears but not necessarily in order.
- Fuzzy match – low boost, acting as a safety net for misspelled tokens.
This hierarchy preserved precision for clean queries while still offering a forgiving fallback for typos.
Tuning fuzzy settings
A prefix length of 1 forced the first character of each term to match before fuzzy logic kicked in. This rule kept the search fast and prevented the explosion of candidate terms that can overload memory. The team also capped the maximum number of term expansions, another guardrail against runaway resource use.
Synchronisation strategy
Three complementary processes keep the OpenSearch index aligned with SQLite:
- A cron job to sync new data.
- Nightly diff pass – scans for mismatches that slipped through the incremental updates.
- Weekly full rebuild – runs behind an index alias, then swaps the alias in a single operation, guaranteeing zero downtime.
Measurable impact after two weeks
- Zero-result queries fell from 12 % to 1.4 %.
- Search-to-click conversion rose by 9 %.
- Median latency stayed below 28 ms, well within the platform’s user-experience target.
Caveats and counter-points
The migration is not a plug-and-play upgrade. The team stresses that the primary database should never be replaced by a search engine; SQLite remains the authoritative store for all video metadata.
Bottom line
Adding typo tolerance through a purpose-built search engine turned a noticeable dead-end in the user journey into a smooth, fast experience. The case study shows that a disciplined architecture – keeping the relational store as the source of truth, layering relevance, and safeguarding fuzzy logic – can deliver measurable gains without sacrificing stability.