Migrating Video Title Search From SQLite FTS5 to OpenSearch Fuzzy Queries

Our search logs told an ugly story. Roughly 12% of queries on our video platform returned zero results, and when I sampled a few hundred of them by hand, the pattern was obvious: intersteller, avengrs endgame, mr beast challange, godzila. Real people looking for real videos, typing the way people actually type on phones, and our search returning a blank page. Every one of those empty results is a bounce, and on a free discovery site where the only product is helping people find something to watch, a bounce is a failure.

The search backend at the time was SQLite FTS5. It is genuinely excellent — fast, embedded, zero operational cost — and it powered DailyWatch search for a long time. But FTS5 matches tokens, and a token either matches or it doesn't. intersteller is not interstellar, so FTS5 sees no match and moves on. This post is about how I diagnosed the limits of the FTS5 approach, why prefix and trigram hacks weren't enough, and how I moved title search to OpenSearch with fuzzy matching — including the parts nobody warns you about, like relevance tuning and keeping the two systems in sync during migration.

Why FTS5 Runs Out of Road on Typos

FTS5 is a token-based inverted index. When you insert Interstellar Official Trailer, it stores the tokens interstellar, official, trailer. A query for interstellar looks up that exact token and returns the row. This is O(1)-ish per token and stupidly fast.

The problem is that fuzzy matching is not a token lookup problem. intersteller needs an edit distance comparison against every candidate token, and an inverted index has no native concept of "tokens that are one character off." People reach for two workarounds:

• Prefix queries (interstell*). This catches truncations and misspellings after the typo point, but does nothing for a typo in the first few characters. nterstellar or imterstellar both fail.
• Trigram tokenizers. FTS5 supports a trigram tokenizer that indexes 3-character shingles. This genuinely helps with substring and some fuzzy matching, but it explodes index size, breaks relevance ranking (BM25 over trigrams is close to meaningless), and still can't express "within 2 edits of this word."

I actually shipped the trigram approach first because it required no new infrastructure. Here is roughly what that looked like:

<?php
declare(strict_types=1);

// FTS5 trigram table — the approach I tried BEFORE OpenSearch.
// Works for substrings, but ranking is poor and true fuzzy is impossible.
final class Fts5TrigramSearch
{
    public function __construct(private readonly \PDO $db) {}

    public function migrate(): void
    {
        $this->db->exec(
            "CREATE VIRTUAL TABLE IF NOT EXISTS video_search
             USING fts5(title, channel, tokenize = 'trigram')"
        );
    }

    /** @return array<int, array{id:int,title:string}> */
    public function search(string $query): array
    {
        // Trigram FTS5 needs LIKE-style matching to be useful.
        $stmt = $this->db->prepare(
            "SELECT rowid AS id, title
             FROM video_search
             WHERE video_search MATCH :q
             ORDER BY rank
             LIMIT 40"
        );
        // Escape FTS5 special chars, wrap for substring intent.
        $safe = '"' . str_replace('"', '""', $query) . '"';
        $stmt->execute([':q' => $safe]);

        return $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
    }
}

This got zero-result queries down from 12% to about 7%. Better, but the wins came with a cost: the trigram index was 3.4x larger than the standard FTS5 index, and ranking quality dropped noticeably. Users searching trailer now got a soup of results because tra, rai, ail match half the catalog. I was trading precision for recall with no dial to balance them. That's when I decided title search — specifically title search, not the whole app — needed a real search engine.

Why OpenSearch and Not a Managed Alternative

A few constraints drove the choice:

• It had to be self-hostable. The whole platform runs on a modest LiteSpeed + PHP 8.4 stack fronted by Cloudflare. Paying per-query for a hosted search service didn't fit a free product's economics.
• It had to speak fuzzy natively. OpenSearch (the Apache-licensed fork of Elasticsearch) has fuzziness built into its match queries, backed by Levenshtein automata. No trigram hacks.
• The Apache 2.0 license mattered. I didn't want to build on something that might change licensing terms later.

The mental model shift is important: I am not replacing SQLite. SQLite remains the source of truth for every video record, view count, and category mapping. OpenSearch becomes a derived index — a denormalized, disposable projection of the title data optimized purely for retrieval. If OpenSearch dies, search degrades to the FTS5 fallback, but no data is lost. Treating the search engine as a rebuildable cache rather than a database of record is what keeps the operational risk low.

Designing the Index and Analyzer

The default analyzer is fine, but video titles have quirks worth handling: they're full of years (2024), symbols (|, -, feat.), and mixed casing. I wanted an analyzer that lowercases, folds accents (so Pokémon matches pokemon), and keeps things simple enough that fuzzy matching has clean tokens to work with.

Here's the index creation, which I run once from a small Python bootstrap script:

#!/usr/bin/env python3
"""Create the videos index with a typo-friendly analyzer."""
from opensearchpy import OpenSearch

client = OpenSearch(
    hosts=[{"host": "127.0.0.1", "port": 9200}],
    http_auth=("admin", "admin"),
    use_ssl=False,
)

INDEX = "videos"

body = {
    "settings": {
        "number_of_shards": 1,          # small catalog, one shard is plenty
        "number_of_replicas": 0,
        "analysis": {
            "analyzer": {
                "title_analyzer": {
                    "type": "custom",
                    "tokenizer": "standard",
                    "filter": ["lowercase", "asciifolding"],
                }
            }
        },
    },
    "mappings": {
        "properties": {
            "video_id": {"type": "keyword"},
            "title": {
                "type": "text",
                "analyzer": "title_analyzer",
                # A raw sub-field lets us boost exact phrase matches later.
                "fields": {"raw": {"type": "keyword"}},
            },
            "channel": {"type": "text", "analyzer": "title_analyzer"},
            "views": {"type": "long"},
            "published_at": {"type": "date"},
        }
    },
}

if client.indices.exists(INDEX):
    client.indices.delete(INDEX)
client.indices.create(INDEX, body=body)
print(f"Created index '{INDEX}'")

A couple of decisions worth calling out:

• One shard, zero replicas. The catalog is in the low millions of documents. A single shard keeps fuzzy scoring consistent (distributed IDF across shards can skew relevance on small indices) and simplifies everything. I'll add replicas when I add a second node, not before.
• asciifolding before fuzziness. Folding accents at index and query time means pokemon and Pokémon collapse to the same token, so fuzzy matching never has to "spend" an edit on an accent. Edits are a scarce budget; don't waste them on things a normalizer can handle for free.
• The title.raw keyword sub-field. This is what lets me boost exact matches above fuzzy ones in the query, which turns out to be the whole game for relevance.

Bulk Loading From SQLite

With the index defined, I need to project every video title into it. The initial backfill uses the bulk API, streamed in batches so memory stays flat regardless of catalog size:

#!/usr/bin/env python3
"""Backfill OpenSearch from the SQLite source of truth."""
import sqlite3
from opensearchpy import OpenSearch, helpers

client = OpenSearch(
    hosts=[{"host": "127.0.0.1", "port": 9200}],
    http_auth=("admin", "admin"),
    use_ssl=False,
)

def rows(db_path: str):
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    cur = conn.execute(
        "SELECT id, title, channel_title, view_count, published_at FROM videos"
    )
    for r in cur:
        yield {
            "_index": "videos",
            "_id": r["id"],            # dedupe: re-running updates, never duplicates
            "_source": {
                "video_id": r["id"],
                "title": r["title"],
                "channel": r["channel_title"] or "",
                "views": r["view_count"] or 0,
                "published_at": r["published_at"],
            },
        }
    conn.close()

ok, errors = helpers.bulk(
    client,
    rows("/var/www/data/videos.db"),
    chunk_size=1000,
    raise_on_error=False,
)
print(f"Indexed {ok} docs, {len(errors)} errors")

Using the SQLite primary key as the OpenSearch _id is the single most important line here. It makes the whole operation idempotent: re-running the backfill upserts rather than duplicates, so a crashed load is safe to just run again. Incremental updates after a cron fetch reuse the exact same code path — I simply pass a WHERE updated_at > ? filter instead of selecting everything.

The Query That Actually Balances Typos and Precision

This is where most tutorials stop too early. If you just fire a single match query with fuzziness: AUTO, you get typo tolerance but you also get garbage relevance — a fuzzy match on a short common word outranks an exact match on the full title. The fix is a bool query with several should clauses at different precision levels, letting the scorer prefer exact matches while still catching typos.

Here's the search endpoint, in PHP since that's what the front end calls:

<?php
declare(strict_types=1);

final class OpenSearchVideoQuery
{
    public function __construct(
        private readonly string $baseUrl = 'http://127.0.0.1:9200'
    ) {}

    /** @return array<int, array<string,mixed>> */
    public function search(string $q): array
    {
        $q = trim($q);
        if ($q === '') {
            return [];
        }

        $body = [
            'size' => 40,
            'query' => [
                'bool' => [
                    'should' => [
                        // 1. Exact phrase — highest boost, wins when it matches.
                        ['match_phrase' => [
                            'title' => ['query' => $q, 'boost' => 10],
                        ]],
                        // 2. All terms present, no typos — strong signal.
                        ['match' => [
                            'title' => [
                                'query' => $q,
                                'operator' => 'and',
                                'boost' => 4,
                            ],
                        ]],
                        // 3. Fuzzy fallback — catches the typos.
                        ['match' => [
                            'title' => [
                                'query' => $q,
                                'fuzziness' => 'AUTO',
                                'prefix_length' => 1,
                                'max_expansions' => 40,
                                'boost' => 1,
                            ],
                        ]],
                    ],
                    'minimum_should_match' => 1,
                ],
            ],
            // Nudge popular videos up when relevance scores tie.
            'sort' => ['_score', ['views' => 'desc']],
        ];

        $ch = curl_init("{$this->baseUrl}/videos/_search");
        curl_setopt_array($ch, [
            CURLOPT_POST => true,
            CURLOPT_HTTPHEADER => ['Content-Type: application/json'],
            CURLOPT_POSTFIELDS => json_encode($body, JSON_THROW_ON_ERROR),
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_TIMEOUT => 2, // fail fast; fall back to FTS5 on timeout
        ]);

        $raw = curl_exec($ch);
        if ($raw === false) {
            throw new \RuntimeException('search backend unavailable');
        }
        curl_close($ch);

        $data = json_decode($raw, true, 512, JSON_THROW_ON_ERROR);
        return array_map(
            static fn(array $hit): array => $hit['_source'],
            $data['hits']['hits'] ?? []
        );
    }
}

The three should clauses form a precision ladder:

• match_phrase (boost 10) fires only when the exact phrase appears. Someone typing interstellar official trailer correctly gets that video pinned to the top, every time.
• match with operator: and (boost 4) requires all terms but ignores order. This handles correct-but-reordered queries.
• match with fuzziness: AUTO (boost 1) is the safety net. It only wins when the higher clauses find nothing, which is exactly when we want typo tolerance to kick in.

The two fuzzy parameters that matter most:

• prefix_length: 1 means the first character must match exactly. This is a huge performance win — fuzzy matching is expensive, and requiring one fixed prefix character prunes the automaton search space dramatically. It costs almost nothing in recall, because first-character typos are rare (people rarely fat-finger the leading letter). If your logs say otherwise, set it to 0.
• max_expansions: 40 caps how many index terms each fuzzy term can expand to. Without a cap, a fuzzy query on a common short word can expand to thousands of terms and tank latency. 40 is a sane default; raise it only if you measure missed matches.

fuzziness: AUTO is smarter than a fixed number: it allows 0 edits for terms of 1-2 chars, 1 edit for 3-5 chars, and 2 edits for anything longer. That scaling matches how typos actually distribute — you don't want two edits allowed on a three-letter word, or cat starts matching bat and hat.

Keeping Two Systems in Sync Without a Message Queue

The scary part of any dual-store setup is drift: the index quietly disagreeing with the source of truth. A full message-queue-plus-change-data-capture pipeline would be over-engineering for this workload, so I use three layers instead:

• Write-through on the cron fetch. The same job that pulls new videos into SQLite indexes them into OpenSearch in the same run, using the idempotent upsert from the backfill script. This handles the 99% case in near real time.
• A nightly reconciliation pass. A small job counts documents per side and re-indexes any SQLite rows whose updated_at is newer than what's in OpenSearch. Because indexing is idempotent, over-indexing is harmless — worst case, a few documents get rewritten with identical content.
• A weekly full rebuild. OpenSearch is a disposable projection, so once a week I rebuild the index from scratch into a new index name and swap an alias. This erases any accumulated drift permanently and doubles as a tested disaster-recovery drill.

The alias swap is the trick that makes rebuilds invisible to production. The application always queries the alias videos, never a concrete index. I build into videos_v2, verify the document count, then atomically repoint the alias:

curl -X POST "127.0.0.1:9200/_aliases" -H 'Content-Type: application/json' -d '{
  "actions": [
    { "remove": { "index": "videos_v1", "alias": "videos" } },
    { "add":    { "index": "videos_v2", "alias": "videos" } }
  ]
}'

Zero-downtime, fully reversible, and no query ever sees a half-built index.

Failing Gracefully Back to FTS5

Adding a network hop to search adds a failure mode: OpenSearch can be down, slow, or restarting. On a free product I refuse to let that turn into a blank search page, so the front-end query object wraps OpenSearch and catches any failure, falling back to the old FTS5 path:

<?php
declare(strict_types=1);

final class VideoSearch
{
    public function __construct(
        private readonly OpenSearchVideoQuery $primary,
        private readonly Fts5TrigramSearch $fallback,
    ) {}

    /** @return array<int, array<string,mixed>> */
    public function query(string $q): array
    {
        try {
            return $this->primary->search($q);
        } catch (\Throwable $e) {
            error_log('opensearch down, using fts5 fallback: ' . $e->getMessage());
            return $this->fallback->search($q);
        }
    }
}

The 2-second curl timeout in the OpenSearch client is what makes this fallback actually protective. Without a tight timeout, a hung OpenSearch node would hang every PHP worker waiting on it, and the fallback would never fire because the request never fails — it just stalls. Fail fast, then degrade. Cloudflare sits in front of everything and caches the hottest search result pages, so even during a full OpenSearch outage most users hit cache and never notice the degraded backend at all.

Results

After the migration, measured over two weeks against the prior baseline:

• Zero-result queries dropped from 12% to 1.4%. The remaining 1.4% are mostly genuinely-not-in-catalog searches, which is correct behavior — I don't want to invent matches.
• Search-to-click rate rose about 9%, the clearest signal that people were finding what they meant, not just getting some result.
• p95 search latency landed around 28ms on a single modest node, well under the FTS5-plus-trigram path it replaced, and the fuzzy tier adds no measurable overhead thanks to prefix_length and max_expansions.
• Index size on OpenSearch is larger than plain FTS5, but roughly on par with the FTS5 trigram index it replaced — and with vastly better ranking.

What I'd Tell Myself Before Starting

• Keep SQLite as the source of truth. Treating OpenSearch as a rebuildable projection removed almost all the operational fear. Nothing you can't _reindex in five minutes is worth losing sleep over.
• The bool/should precision ladder matters more than the fuzzy setting. Naive fuzziness: AUTO alone will wreck your relevance. Boost exact and phrase matches above fuzzy ones and everything falls into place.
• prefix_length and max_expansions are not optional. They are the difference between fuzzy search that's fast and fuzzy search that melts under load.
• Always build the fallback path first. A search box that fails closed to a blank page is worse than one that never got the upgrade. The FTS5 fallback made the whole migration safe to ship incrementally.

Typo tolerance isn't a nice-to-have on a discovery product — it's the difference between a user finding something to watch and closing the tab. FTS5 got us impressively far for zero infrastructure, and it still backstops the whole thing today. But when the core job is understanding what people meant to type, an engine that speaks edit distance natively is worth the extra moving part. Just wire the fallback first, keep your source of truth boring, and treat the fancy index as the disposable cache it should be.