Last month our search logs at TopVideoHub surfaced a query I couldn't ignore: blackpink jenny solo stag. The user meant BLACKPINK Jennie SOLO stage, but our SQLite FTS5 index returned zero rows. FTS5 with a CJK trigram tokenizer is excellent at what it does — it matches substrings across Korean, Japanese, and Chinese titles without word boundaries — but it is a substring engine, not a fuzzy one. One transposed letter in a romanized idol name and the whole query collapses. We aggregate trending video metadata across the Asia-Pacific region, and a huge fraction of our traffic is people typing artist and drama names phonetically, in a hurry, on a phone keyboard. "Zero results" for a real, popular video is a bug, not an edge case.
This post is the write-up of how we bolted OpenSearch onto the read path specifically for typo tolerance, kept SQLite FTS5 as the source of truth, and did it without breaking CJK matching. I'll show the analyzer configuration, the actual query shape we run, the reindex job, and the fallback logic that keeps the site up when the cluster hiccups.
Why FTS5 alone wasn't enough
Our primary store is SQLite with an FTS5 virtual table using a custom trigram tokenizer for CJK. That combination gives us:
- Fast substring matching for Han/Hangul/Kana with no dictionary.
- Zero external dependencies — it ships inside the same file we back up.
- Sub-millisecond lookups on a few hundred thousand rows.
What it does not give us is edit-distance tolerance. FTS5's MATCH is boolean over tokens. If the token isn't there, the row isn't there. There is no built-in fuzzy operator, no Levenshtein, no phonetic folding. You can fake prefix matching with term*, but that doesn't help when the typo is in the middle of the word (jenny vs jennie) or when two characters are swapped (stag vs stage needs an insertion; teh vs the needs a transposition).
We considered doing edit distance in PHP over a candidate set, but the candidate generation is the hard part — you can't cheaply enumerate "all titles within edit distance 2 of this query" from a substring index. OpenSearch (the Apache-2.0 fork of Elasticsearch) solves candidate generation and scoring in one query with its fuzziness parameter, built on a Levenshtein automaton. So the plan became: FTS5 stays authoritative for storage and for exact CJK substring matching; OpenSearch becomes a secondary read index tuned for fuzzy Latin-script queries and multi-language analysis.
The analyzer problem for a multi-language index
The naive move is to throw every title into a text field with the standard analyzer and call it done. That fails badly for us because a single video title routinely mixes scripts:
【4K】NewJeans (뉴진스) 'Super Shy' Official MV
That one title contains Latin, Hangul, and CJK punctuation. The standard analyzer will tokenize the Latin words fine but treat the Korean as one blob. We need per-script handling, and crucially we need fuzziness to apply to the romanized tokens while CJK gets n-gram treatment where edit distance is meaningless.
Our solution is a multi-field mapping. The same title is indexed three ways:
-
title.std— standard analyzer, lowercased and ASCII-folded, this is the fuzzy target for Latin queries. -
title.cjk— the built-incjkanalyzer, which produces bigrams for Han/Kana/Hangul. This handles CJK the way our FTS5 trigram tokenizer does. -
title.keyword— raw, for exact-match boosting and sorting.
Here's the index definition we PUT at bootstrap. Note the asciifolding filter — that's what makes Pokémon match pokemon and Blackpink match bláckpink, and it's separate from fuzziness (folding is deterministic normalization; fuzziness is edit distance). Running both is what covers the long tail.
PUT /videos
{
"settings": {
"index": { "number_of_shards": 1, "number_of_replicas": 1 },
"analysis": {
"filter": {
"latin_fold": { "type": "asciifolding", "preserve_original": true }
},
"analyzer": {
"latin_std": {
"type": "custom",
"tokenizer": "standard",
"filter": ["lowercase", "latin_fold"]
}
}
}
},
"mappings": {
"properties": {
"video_id": { "type": "keyword" },
"title": {
"type": "text",
"analyzer": "latin_std",
"fields": {
"cjk": { "type": "text", "analyzer": "cjk" },
"keyword": { "type": "keyword", "ignore_above": 512 }
}
},
"channel": { "type": "text", "analyzer": "latin_std" },
"region": { "type": "keyword" },
"lang": { "type": "keyword" },
"view_count": { "type": "long" },
"published_at":{ "type": "date" }
}
}
}
One subtlety worth stating out loud: do not apply fuzziness to the cjk bigram field. Edit distance on CJK bigrams produces garbage matches — a fuzzy CJK query treats a one-character difference as huge because each character carries a whole word's meaning. Fuzziness belongs on the Latin field only. The query below enforces that split.
The query that actually runs
When a search comes in, we build a single bool query with several should clauses at different precision tiers. Highest-precision clauses carry the biggest boost, so an exact phrase match always outranks a fuzzy one. fuzziness: AUTO is the important bit: OpenSearch picks edit distance 0 for terms ≤2 chars, distance 1 for 3–5 chars, and distance 2 for longer terms — which is exactly right, because you don't want bts fuzzy-matching abs but you do want jenny reaching jennie.
Here is the PHP that builds and issues the query. We're on PHP 8.4 and talk to the cluster over plain HTTP with the official opensearch-project/opensearch-php client behind a small wrapper.
<?php
declare(strict_types=1);
final class VideoSearch
{
public function __construct(
private readonly \OpenSearch\Client $client,
private readonly string $index = 'videos',
) {}
/** @return list<array{video_id:string,title:string,score:float}> */
public function search(string $q, ?string $region = null, int $size = 20): array
{
$q = trim($q);
if ($q === '') {
return [];
}
$filter = $region !== null
? [['term' => ['region' => $region]]]
: [];
$body = [
'size' => $size,
'query' => [
'bool' => [
'filter' => $filter,
'minimum_should_match' => 1,
'should' => [
// Tier 1: exact phrase, highest boost
['match_phrase' => ['title' => ['query' => $q, 'boost' => 6.0]]],
// Tier 2: CJK bigram match, NO fuzziness
['match' => ['title.cjk' => ['query' => $q, 'boost' => 4.0]]],
// Tier 3: typo-tolerant Latin match
['match' => ['title' => [
'query' => $q,
'fuzziness' => 'AUTO',
'operator' => 'and',
'boost' => 2.0,
]]],
// Tier 4: channel name, also fuzzy
['match' => ['channel' => [
'query' => $q,
'fuzziness' => 'AUTO',
'boost' => 1.0,
]]],
],
],
],
// Freshness/popularity tie-break without overriding relevance
'sort' => ['_score', ['view_count' => 'desc']],
];
$resp = $this->client->search(['index' => $this->index, 'body' => $body]);
return array_map(
static fn(array $hit): array => [
'video_id' => (string) $hit['_source']['video_id'],
'title' => (string) $hit['_source']['title'],
'score' => (float) $hit['_score'],
],
$resp['hits']['hits'],
);
}
}
A few decisions in there earned their place through trial and error:
-
operator: andon the fuzzy Latin clause. Without it,jennie solo stagematches any video containing juststage, and the popularity sort then floods the results with unrelated high-view videos. Requiring all terms (each still allowed one or two edits) keeps precision high. -
match_phraseat boost 6 guarantees that when someone types a title correctly, the exact match wins outright and fuzziness never reorders it. - Sorting by
_scorefirst, thenview_count, means fuzziness decides what is relevant and popularity only breaks ties among equally-relevant hits. Sorting by views first would resurrect the original "popular but wrong" problem.
Keeping OpenSearch in sync with SQLite
OpenSearch is a derived index, so it must never be the source of truth — if the cluster is wiped, one command rebuilds it from SQLite. Our ingestion cron writes new videos to SQLite first, then enqueues their IDs for indexing. A small Python worker drains that queue with the bulk API. We chose Python here only because the worker runs on the ingestion box alongside our other data scripts; the logic is identical in any language.
import json
import sqlite3
from opensearchpy import OpenSearch, helpers
client = OpenSearch(
hosts=[{"host": "127.0.0.1", "port": 9200}],
http_compress=True,
)
def fetch_batch(db: sqlite3.Connection, limit: int = 1000):
db.row_factory = sqlite3.Row
rows = db.execute(
"""
SELECT v.video_id, v.title, v.channel, v.region,
v.lang, v.view_count, v.published_at
FROM videos v
JOIN index_queue q ON q.video_id = v.video_id
LIMIT ?
""",
(limit,),
).fetchall()
return rows
def to_actions(rows):
for r in rows:
yield {
"_op_type": "index",
"_index": "videos",
"_id": r["video_id"], # id = video_id makes writes idempotent
"_source": {
"video_id": r["video_id"],
"title": r["title"],
"channel": r["channel"],
"region": r["region"],
"lang": r["lang"],
"view_count": r["view_count"],
"published_at": r["published_at"],
},
}
def run():
db = sqlite3.connect("data/videos.db")
total = 0
while True:
rows = fetch_batch(db)
if not rows:
break
ok, errors = helpers.bulk(client, to_actions(rows), raise_on_error=False)
total += ok
if errors:
# Log and keep going; a poison row must not stall the queue
print(json.dumps({"bulk_errors": errors[:5]}))
ids = [(r["video_id"],) for r in rows]
db.executemany("DELETE FROM index_queue WHERE video_id = ?", ids)
db.commit()
print(f"indexed {total} documents")
if __name__ == "__main__":
run()
Setting _id to video_id makes the whole pipeline idempotent — reindexing the same video overwrites rather than duplicating, so a crashed worker just reprocesses its batch harmlessly. raise_on_error=False plus logging means one malformed title (bad UTF-8 from a scraped source, usually) doesn't wedge the queue. For a full rebuild we simply enqueue every video_id and let the same worker drain it; on our dataset a from-scratch reindex finishes in a couple of minutes.
Failing open: the fallback that keeps search alive
The moment you add a network dependency to a hot path, you inherit its failure modes. OpenSearch can be mid-restart, out of heap, or unreachable because a firewall rule changed. Search is one of our highest-intent surfaces, so "search is down" is unacceptable. Our rule: OpenSearch is an enhancement, not a dependency. If it doesn't answer fast, we fall back to the FTS5 path that predates it.
<?php
declare(strict_types=1);
final class SearchService
{
public function __construct(
private readonly VideoSearch $fuzzy, // OpenSearch
private readonly Fts5Search $exact, // SQLite FTS5
) {}
/** @return list<array<string,mixed>> */
public function query(string $q, ?string $region = null): array
{
try {
$hits = $this->fuzzy->search($q, $region);
if ($hits !== []) {
return $hits;
}
// Empty is a valid answer, but double-check FTS5 for CJK edge cases
return $this->exact->search($q, $region);
} catch (\Throwable $e) {
error_log('opensearch fallback: ' . $e->getMessage());
return $this->exact->search($q, $region);
}
}
}
We also set a hard client-side timeout of 400 ms on the OpenSearch call. If the cluster is slow, we'd rather serve exact FTS5 results immediately than make the user wait. Behind LiteSpeed and Cloudflare, search result pages for common queries are cached at the edge anyway, so the origin only sees the long tail — which is exactly the traffic that benefits most from fuzziness and can most afford the occasional fallback. That layering matters: the fuzzy path handles the queries the cache misses, and the cache absorbs the repeated hits so the cluster never has to be sized for peak.
What changed after we shipped it
We measured this with a simple before/after on the same two weeks of query logs, replayed against both engines:
- Zero-result rate for Latin-script queries dropped from 11.4% to 2.1%. Most of the remaining zeros are genuinely nonsensical queries or videos we don't carry.
-
CJK query behavior was unchanged, which was the goal — the
title.cjkclause mirrors what FTS5 already did, so we didn't regress our core Asia-Pacific traffic. - p95 search latency at the origin rose from 3 ms to 18 ms. That sounds like a lot proportionally, but 18 ms is invisible to users and most of it is HTTP round-trip to a co-located node, not query time.
The operational cost is one small single-node cluster with a replica, plus the Python worker on a box we already run. For a search quality jump that large, it's an easy trade.
Things I'd tell my past self
-
Don't fuzzy-match short tokens.
AUTOfuzziness handles this, but if you hardcodefuzziness: 2you'll matchbtstoabs,ost, andbus. Let the automaton scale edit distance with term length. -
Keep folding and fuzziness separate in your head.
asciifoldingis deterministic normalization for accents and full-width characters; fuzziness is edit distance for typos. You want both, and they solve different problems. - Never let the derived index become authoritative. Every document must be reconstructable from SQLite with one command. The day you can't rebuild your search index from your source of truth is the day an ops mistake becomes data loss.
-
Boost tiers beat a single clever query. A stack of
shouldclauses from exact to fuzzy, each with a boost, is easier to reason about and tune than one query trying to do everything.
FTS5 with a CJK tokenizer is still the heart of our storage and still the right tool for exact multilingual substring matching. OpenSearch didn't replace it — it sits beside it, catches the typos FTS5 was never designed to catch, and fails back to it the instant anything goes wrong. That division of labor is the whole design, and it's why a stag-instead-of-stage query now finds the video the user actually wanted.
Top comments (0)