ahmet gedik

Posted on Aug 19

Rebuilding a Video Metadata Service on Litestar Without Losing CJK Search

#python #litestar #sqlite #performance

240 ms to return 4 KB of JSON

TopVideoHub aggregates trending video metadata across nine Asia-Pacific regions, and the HTML side of it has never been the problem. PHP 8.4 renders a page, the result lands in a file page cache, LiteSpeed serves it, Cloudflare fronts it. That part is boring in the good way. The endpoints that hurt on TopVideoHub are the ones the browser calls after the page paints:

/api/suggest?q=... — search-as-you-type, mostly Japanese, Korean and Traditional Chinese queries
/api/related?id=... — the strip under the player
/api/trending/{region} — the region switcher on the home page

None of these can live in the HTML page cache. Suggest fires per keystroke with a query I have never seen before. Related is per-video across roughly 180k rows. The region switcher is cacheable at the edge but still needs a cold origin response every few minutes per region.

Each call was a fresh LSAPI request: interpreter bootstrap, autoloader, config parse, a new PDO handle to the SQLite file, PRAGMA setup, and then about 2 ms of actual query work. Sampled from a box in Singapore at 32 concurrent connections: p50 41 ms, p95 180 ms, occasional 240 ms outliers when the LSAPI worker pool churned. Roughly 90% of that number was everything except the query.

So I carved the metadata endpoints out into a separate Python process running Litestar, kept SQLite as the single source of truth, and left PHP owning every byte of HTML. This is the part I got right: it is not a rewrite. It is one process that holds a warm connection and an in-memory cache, sitting behind the same LiteSpeed instance.

Why Litestar and not FastAPI or plain ASGI

I evaluated three options and the deciding factors were unglamorous.

msgspec instead of Pydantic for serialization. Litestar uses msgspec internally for encoding and decoding. For a suggest response of 10 rows with Japanese titles, msgspec's encoder writes raw UTF-8 by default. json_encode() in PHP escapes non-ASCII into six-character sequences unless you pass JSON_UNESCAPED_UNICODE, which turns a 3-byte kana character into 6 bytes on the wire. My suggest payload dropped about 38% just from that, and I had genuinely never noticed the flag was missing in the old endpoint.

Explicit sync_to_thread. SQLite's Python driver is synchronous. Litestar refuses to let you register a sync handler without saying sync_to_thread=True or False — it raises a warning at import time. FastAPI silently offloads def handlers to a threadpool, which is the right default until the day you accidentally put a blocking call in an async def and stall the whole loop. I wanted the failure mode to be loud.

Layered configuration. Cache TTL, cache-control headers and dependencies can be declared on the app, the router, the controller or the handler, and the most specific one wins. That maps directly onto how the site's caching already works (per-route TTLs in PHP config), so I could port the table of TTLs rather than reinvent it.

What I did not pick it for: benchmark charts. On this workload the framework overhead is a rounding error next to the SQLite query. The win came from process reuse, not from routing speed.

The endpoint I was replacing

For reference, here is roughly what the PHP version looked like. It is fine code. It just pays full startup cost on every single keystroke.

<?php
// public/api/suggest.php - the endpoint this post replaces
declare(strict_types=1);

$q = trim((string)($_GET['q'] ?? ''));
if ($q === '' || mb_strlen($q) > 64) {
    http_response_code(400);
    echo '[]';
    exit;
}

$pdo = new PDO('sqlite:' . __DIR__ . '/../../data/videos.db', null, null, [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$pdo->exec('PRAGMA query_only = ON');

// NFKC + lowercase, same normalisation the indexer applies
$norm = mb_strtolower(Normalizer::normalize($q, Normalizer::FORM_KC));
$expr = '"' . str_replace('"', '""', $norm) . '"';

$stmt = $pdo->prepare(
    'SELECT v.youtube_id, v.title, v.channel_title
       FROM videos_fts f
       JOIN videos v ON v.id = f.rowid
      WHERE videos_fts MATCH :q
      ORDER BY bm25(videos_fts, 4.0, 1.0), v.view_count DESC
      LIMIT 10'
);
$stmt->execute([':q' => $expr]);

header('Content-Type: application/json; charset=utf-8');
header('Cache-Control: public, max-age=600');
echo json_encode($stmt->fetchAll(), JSON_UNESCAPED_UNICODE);

The schema stays exactly where it is

The Python service opens the same file the PHP cron writer owns, in read-only mode. WAL means readers never block the writer and the writer never blocks readers, which is the only reason this arrangement is safe at all.

PRAGMA journal_mode = WAL;

CREATE TABLE IF NOT EXISTS videos (
  id            INTEGER PRIMARY KEY,
  youtube_id    TEXT NOT NULL UNIQUE,
  title         TEXT NOT NULL,
  title_norm    TEXT NOT NULL,          -- NFKC + lowercased, written by the fetcher
  channel_title TEXT NOT NULL,
  region        TEXT NOT NULL,
  lang          TEXT NOT NULL DEFAULT 'und',
  view_count    INTEGER NOT NULL DEFAULT 0,
  published_at  TEXT NOT NULL,
  fetched_at    TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_videos_region_pub
  ON videos(region, published_at DESC);
CREATE INDEX IF NOT EXISTS idx_videos_recent
  ON videos(published_at DESC, view_count DESC);

-- trigram: the only built-in tokenizer that behaves sanely on CJK
CREATE VIRTUAL TABLE IF NOT EXISTS videos_fts USING fts5(
  title_norm,
  channel_title,
  content='videos',
  content_rowid='id',
  tokenize="trigram remove_diacritics 1"
);

CREATE TRIGGER IF NOT EXISTS videos_ai AFTER INSERT ON videos BEGIN
  INSERT INTO videos_fts(rowid, title_norm, channel_title)
  VALUES (new.id, new.title_norm, new.channel_title);
END;

CREATE TRIGGER IF NOT EXISTS videos_ad AFTER DELETE ON videos BEGIN
  INSERT INTO videos_fts(videos_fts, rowid, title_norm, channel_title)
  VALUES ('delete', old.id, old.title_norm, old.channel_title);
END;

CREATE TRIGGER IF NOT EXISTS videos_au AFTER UPDATE ON videos BEGIN
  INSERT INTO videos_fts(videos_fts, rowid, title_norm, channel_title)
  VALUES ('delete', old.id, old.title_norm, old.channel_title);
  INSERT INTO videos_fts(rowid, title_norm, channel_title)
  VALUES (new.id, new.title_norm, new.channel_title);
END;

Why trigram and not unicode61 or ICU

This is the part that costs people weeks, so it is worth being precise.

unicode61, the FTS5 default, splits on non-alphanumeric codepoints. Japanese and Chinese do not put spaces between words, so a title like a six-character Japanese phrase becomes one token. Searching for a two-character substring of it matches nothing. You can add a prefix index and get prefix matches, which helps only if the user types from the beginning of the whole title. In practice suggest returned zero results for most Japanese queries and I did not notice for a month because the fallback rendered an empty dropdown rather than an error.

The ICU tokenizer does real dictionary-based segmentation and is the correct answer — if you can compile SQLite with ICU. On shared LiteSpeed hosting I cannot, and I am not going to run a custom SQLite build to serve a video site.

The trigram tokenizer indexes every overlapping 3-character window and never tries to find word boundaries at all. That makes it script-agnostic: it works identically for Korean, Japanese, Chinese and Latin text, and it gives you substring matching for free. The costs are real and you should know them going in:

The index is roughly 3–4× larger than a unicode61 index over the same text. On 180k titles that took the FTS table from about 22 MB to about 78 MB. Acceptable.
You cannot set detail='none'. Trigram matching relies on position data to reassemble phrases; strip it and phrase queries stop working.
Queries shorter than 3 characters cannot use the index at all. MATCH on a 2-character string returns nothing, silently.
bm25() over trigrams is noisy — it is ranking character windows, not words. I always add a secondary sort key (view_count DESC) so the ordering is stable and defensible.

That last point matters more than it sounds. Two-character queries are extremely common in Japanese and Chinese search boxes. I needed an explicit fallback path, not a shrug.

The service

One file, no ORM, no migration framework. The writer is PHP; Python only reads.

from __future__ import annotations

import sqlite3
import threading
from collections.abc import AsyncGenerator
from contextlib import asynccontextmanager

import msgspec
from litestar import Litestar, get
from litestar.config.response_cache import ResponseCacheConfig
from litestar.datastructures import CacheControlHeader, State
from litestar.di import Provide
from litestar.exceptions import NotFoundException
from litestar.params import Parameter

DB_PATH = "/home/tvh/data/videos.db"


class VideoOut(msgspec.Struct, frozen=True):
    id: str
    title: str
    channel: str
    region: str
    views: int
    published_at: str


def _to_video(r: sqlite3.Row) -> VideoOut:
    return VideoOut(
        id=r["youtube_id"],
        title=r["title"],
        channel=r["channel_title"],
        region=r["region"],
        views=r["view_count"],
        published_at=r["published_at"],
    )


class Repo:
    """One read-only connection per worker thread, opened lazily."""

    def __init__(self, path: str) -> None:
        self._path = path
        self._local = threading.local()
        self._opened: list[sqlite3.Connection] = []
        self._lock = threading.Lock()

    def conn(self) -> sqlite3.Connection:
        c = getattr(self._local, "c", None)
        if c is None:
            c = sqlite3.connect(
                f"file:{self._path}?mode=ro", uri=True, check_same_thread=False
            )
            c.row_factory = sqlite3.Row
            c.execute("PRAGMA query_only = ON")
            c.execute("PRAGMA mmap_size = 268435456")
            c.execute("PRAGMA cache_size = -32000")
            self._local.c = c
            with self._lock:
                self._opened.append(c)
        return c

    def close_all(self) -> None:
        with self._lock:
            for c in self._opened:
                c.close()
            self._opened.clear()

    def trending(self, region: str, limit: int) -> list[VideoOut]:
        rows = self.conn().execute(
            """
            SELECT youtube_id, title, channel_title, region, view_count, published_at
              FROM videos
             WHERE region = ?
             ORDER BY published_at DESC
             LIMIT ?
            """,
            (region.upper(), limit),
        ).fetchall()
        return [_to_video(r) for r in rows]

    def suggest(self, match_expr: str, limit: int) -> list[VideoOut]:
        rows = self.conn().execute(
            """
            SELECT v.youtube_id, v.title, v.channel_title, v.region,
                   v.view_count, v.published_at
              FROM videos_fts f
              JOIN videos v ON v.id = f.rowid
             WHERE videos_fts MATCH ?
             ORDER BY bm25(videos_fts, 4.0, 1.0), v.view_count DESC
             LIMIT ?
            """,
            (match_expr, limit),
        ).fetchall()
        return [_to_video(r) for r in rows]


def provide_repo(state: State) -> Repo:
    return state.repo


@get(
    "/api/v1/trending/{region:str}",
    sync_to_thread=True,
    cache=300,
    cache_control=CacheControlHeader(max_age=300, public=True),
)
def trending(
    region: str,
    repo: Repo,
    limit: int = Parameter(default=24, ge=1, le=60),
) -> list[VideoOut]:
    items = repo.trending(region, limit)
    if not items:
        raise NotFoundException(f"no videos indexed for region {region}")
    return items


@get(
    "/api/v1/suggest",
    sync_to_thread=True,
    cache=60,
    cache_control=CacheControlHeader(max_age=60, public=True),
)
def suggest(
    repo: Repo,
    q: str = Parameter(min_length=1, max_length=64),
    limit: int = Parameter(default=10, ge=1, le=25),
) -> list[VideoOut]:
    expr = to_match_expr(q)
    if expr is None:
        return repo.suggest_short(normalize(q), limit)
    return repo.suggest(expr, limit)


@asynccontextmanager
async def db(app: Litestar) -> AsyncGenerator[None, None]:
    app.state.repo = Repo(DB_PATH)
    try:
        yield
    finally:
        app.state.repo.close_all()


app = Litestar(
    route_handlers=[trending, suggest],
    dependencies={"repo": Provide(provide_repo, sync_to_thread=False)},
    lifespan=[db],
    response_cache_config=ResponseCacheConfig(default_expiration=120),
)

Two details in there that cost me time. Provide(provide_repo, sync_to_thread=False) is mandatory for a synchronous dependency — the provider does nothing but return an attribute, so pushing it to a thread would be pure overhead, but Litestar still wants you to say which you meant. And app.state set inside the lifespan context is the same State object injected into provide_repo, so there is no global and no import-time connection.

Run it with litestar --app app:app run --port 8081 while developing, and with a production ASGI server behind that in deployment.

Normalizing the query the same way you normalized the document

The single largest source of "why does this return nothing" bugs in CJK search is asymmetric normalization. Half-width katakana and full-width Latin are different codepoints from their normal-width equivalents, and users type both — Japanese IMEs emit full-width digits and Latin letters constantly. If the indexer applies NFKC and the query path does not, every query containing a full-width character misses.

import re
import unicodedata

_WS = re.compile(r"\s+")


def normalize(q: str) -> str:
    """Must stay byte-identical in behaviour to the PHP indexer."""
    q = unicodedata.normalize("NFKC", q)
    q = q.casefold().strip()
    return _WS.sub(" ", q)


def to_match_expr(q: str) -> str | None:
    """Wrap as an FTS5 string literal, or None if trigram can't serve it."""
    q = normalize(q)
    if len(q) < 3:
        return None
    return '"' + q.replace('"', '""') + '"'

Quoting the whole query as a single FTS5 string literal is not optional. An unquoted MATCH argument is parsed as a query expression, so a user typing AND, *, ^, : or a stray " either changes the semantics or throws a syntax error straight into a 500. Doubling embedded quotes inside the literal is the FTS5 escape rule.

The short-query fallback lives on the repo:

    def suggest_short(self, q: str, limit: int) -> list[VideoOut]:
        """Under 3 chars trigram is useless. Scan the recent partition only."""
        if not q:
            return []
        rows = self.conn().execute(
            """
            SELECT youtube_id, title, channel_title, region, view_count, published_at
              FROM videos
             WHERE published_at > date('now', '-30 day')
               AND title_norm LIKE '%' || ? || '%'
             ORDER BY view_count DESC
             LIMIT ?
            """,
            (q, limit),
        ).fetchall()
        return [_to_video(r) for r in rows]

A leading-wildcard LIKE cannot use an index; this is a scan and I am not pretending otherwise. What makes it acceptable is the 30-day bound, which the published_at DESC index turns into a range of about 41k rows on my dataset — roughly 6 ms warm. If the corpus triples I will bound it harder rather than pretend the query plan improved.

One cross-language wrinkle: PHP's mb_strtolower and Python's str.casefold() are not identical (casefold maps the German sharp s to ss, lowercasing does not). For CJK it makes no difference, and I documented the divergence rather than papering over it.

Sync SQLite inside an async framework

sync_to_thread=True hands the handler to AnyIO's worker thread pool. The default capacity is 40 threads, which is far more concurrency than a single SQLite file wants — 40 threads all mmap-ing and page-faulting is a good way to make p99 worse, not better. I cap it at startup:

import anyio.to_thread

@asynccontextmanager
async def db(app: Litestar) -> AsyncGenerator[None, None]:
    anyio.to_thread.current_default_thread_limiter().total_tokens = 12
    app.state.repo = Repo(DB_PATH)
    try:
        yield
    finally:
        app.state.repo.close_all()

Twelve threads times one connection each is twelve open read handles. Requests beyond that queue in the event loop instead of thrashing the page cache, and the tail latency curve got noticeably flatter.

Caching in three places, not four

The site already had three cache layers (LiteSpeed page cache, a PHP file cache, a PHP data cache). I refused to add a fourth conceptual layer, so the Python service reuses the same TTLs the PHP config already declares:

Litestar response cache (cache=300 on trending, cache=60 on suggest) — in-process, keyed by path plus query string. This is what makes the region switcher effectively free.
Cache-Control on the response — so Cloudflare can hold trending at the edge. Suggest gets a short TTL because query diversity makes edge caching mostly useless anyway.
The SQLite page cache in each connection, which is the layer that actually pays for keeping the process alive.

The in-process cache does mean each ASGI worker holds its own copy. With two workers that is two cold misses per TTL window instead of one. Fine at this scale; if it stops being fine, Litestar's response cache accepts a shared store rather than the default in-memory one, and that is a config change, not a rewrite.

Measuring it honestly

I wrote a small sampler rather than reaching for a load-testing suite, because I wanted latency percentiles against one endpoint at a fixed concurrency, not a report.

package main

import (
    "fmt"
    "io"
    "net/http"
    "os"
    "sort"
    "sync"
    "time"
)

func main() {
    if len(os.Args) < 2 {
        fmt.Println("usage: sampler <url>")
        return
    }
    url := os.Args[1]
    const n, workers = 2000, 32

    lat := make([]time.Duration, n)
    jobs := make(chan int, n)
    client := &http.Client{Timeout: 10 * time.Second}

    var wg sync.WaitGroup
    for w := 0; w < workers; w++ {
        wg.Add(1)
        go func() {
            defer wg.Done()
            for i := range jobs {
                start := time.Now()
                resp, err := client.Get(url)
                if err != nil {
                    lat[i] = 10 * time.Second
                    continue
                }
                io.Copy(io.Discard, resp.Body)
                resp.Body.Close()
                lat[i] = time.Since(start)
            }
        }()
    }
    for i := 0; i < n; i++ {
        jobs <- i
    }
    close(jobs)
    wg.Wait()

    sort.Slice(lat, func(a, b int) bool { return lat[a] < lat[b] })
    fmt.Printf("p50=%v p95=%v p99=%v\n", lat[n/2], lat[n*95/100], lat[n*99/100])
}

On a 2-vCPU box, same machine, same database file, /api/suggest with a rotating set of Japanese and Korean queries:

PHP over LSAPI: p50 41 ms, p95 180 ms, p99 244 ms
Litestar, two workers, cache warm: p50 6 ms, p95 19 ms, p99 34 ms

Read that as "process reuse is worth about 35 ms per request here," not "Python beat PHP." The same PHP code behind a persistent worker with a pooled handle would close most of the gap. What I actually bought was a place to keep warm state, plus a search path I can unit-test without booting a web server.

Where it sits in the stack

LiteSpeed proxies /api/v1/* to 127.0.0.1:8081 and serves everything else from PHP. Cloudflare sees one origin and one hostname, so no CORS and no preflight. The old PHP endpoints are still on disk behind a config flag; flipping it back is a one-line change, and I have used that escape hatch twice — once when a botched deploy left the Python process down, once when I broke the normalization function and did not catch it in tests.

What I would keep and what I would skip

Keep: the trigram tokenizer, the shared normalization contract between the indexer and the query path, the explicit thread limiter, and the read-only URI connection flag. Those four things are the whole substance of the migration.

Skip: the instinct to move more endpoints across just because the service exists. Rendering stayed in PHP and should stay there — the page cache already makes it a non-problem, and running two languages against one page is a debugging tax I have no reason to pay. The metadata service earns its keep precisely because it is small, read-only, and has exactly one job.

If you are running SQLite full-text search against CJK content and getting empty result sets, check your tokenizer before you check anything else. It is almost always that.

DEV Community