Serving Video Metadata Across Eight Regions With Litestar And SQLite

The 41-minute cron that had to fit inside 180 seconds

The /watch/{id} page on our site renders in about 38 ms. The job that keeps the data behind it fresh took 41 minutes, and the shared LiteSpeed host it ran on kills any PHP process at max_execution_time = 180. So the refresh was chopped into fourteen cron slots that each did a slice of the work, wrote a checkpoint row into SQLite, and hoped the next slot picked up where the last one died. When a slot failed — a 403 from a burned API key, a DNS hiccup, a slow region — the checkpoint drifted and one of our eight regions would silently serve day-old view counts until somebody noticed.

The fix was not a faster PHP loop. The problem was structural: refreshing metadata for eight regions is eight independent network round trips, and curl_exec in a foreach does them one after another. At roughly 300 ms per YouTube Data API call and ~2,400 videos in rotation, the arithmetic is brutal and no amount of micro-optimization moves it. I pulled that one job out of the PHP monolith that runs TrendVidStream and rebuilt it as a small Litestar service. This is what that looked like in practice, including the parts that went badly.

Why not just fix the PHP

I want to be honest that this was not obviously the right call. PHP 8.4 with Fibers can do concurrent I/O now, and curl_multi_exec has existed forever. I tried both first.

curl_multi_exec works fine and cut the fanout to roughly 4 minutes. What it did not solve:

• The 180-second ceiling was still there. Concurrency shrank the wall clock but the job still had no ability to run longer than the SAPI allowed, so the checkpoint machinery had to stay.
• No process to hold state. Rate-limit budgets, per-key backoff, and in-flight dedup all had to be persisted to SQLite and re-read every invocation, because the process dies after every run.
• Retries were a cron concern. A failed region waited two hours for the next slot instead of 30 seconds.

A long-lived process solves all three, and once you want a long-lived process, PHP stops being the path of least resistance on shared hosting. The metadata fetcher is also the one component that does not need to be on the web host at all — it writes a SQLite file, and the web tier only reads it.

Why Litestar and not FastAPI

I have shipped FastAPI services and would again. Three things pushed me to Litestar here:

• msgspec instead of Pydantic for the hot path. Our largest response is a 200-item region feed. Serializing that with msgspec structs measured about 4x faster than the equivalent Pydantic v2 model dump in my benchmark, and msgspec structs are plain frozen objects with __slots__, so the memory profile on a 512 MB box is much flatter.
• Dependency injection that composes at the router level. Read-only DB handles, the API key pool, and the region allowlist are declared once on the app and inherited. No module-level globals, no Depends() repeated on 20 handlers.
• sync_to_thread is explicit. SQLite's driver is synchronous. Litestar makes you say so per handler and warns loudly if you forget, instead of quietly blocking the event loop. That single warning caught a real bug for me on day two.

The framework name still shows up as "Starlite" in older blog posts and in a few stale PyPI mirrors — it was renamed in 2023. If you are copying snippets off the internet, check which era they came from, because the import paths and the DTO API both changed.

The read path

The read store stayed SQLite with FTS5, exactly as the PHP side had it. There was no reason to change it: the file is 340 MB, it lives on the same disk as the process, and FTS5 answers a prefix query over 2.4 million title tokens in single-digit milliseconds. Litestar just puts an HTTP surface on it.

The service opens the database read-only, in WAL mode, with one connection per worker thread. Writes go through a completely separate process, which is the only sane way to use SQLite from an async framework.

# app/main.py
from __future__ import annotations

import sqlite3
from typing import Annotated

import msgspec
from litestar import Litestar, get
from litestar.di import Provide
from litestar.exceptions import NotFoundException
from litestar.params import Parameter

REGIONS = ('US', 'GB', 'DE', 'FR', 'IN', 'BR', 'JP', 'AU')
DB_PATH = 'file:data/metadata.db?mode=ro'


class VideoOut(msgspec.Struct, frozen=True, omit_defaults=True):
    video_id: str
    title: str
    channel: str
    duration_s: int
    views: int
    region: str
    fetched_at: int


def provide_db() -> sqlite3.Connection:
    conn = sqlite3.connect(DB_PATH, uri=True, check_same_thread=False)
    conn.row_factory = sqlite3.Row
    conn.execute('PRAGMA journal_mode = WAL')
    conn.execute('PRAGMA query_only = ON')
    conn.execute('PRAGMA mmap_size = 268435456')
    return conn


@get('/v1/videos/{video_id:str}', sync_to_thread=True, cache=300)
def get_video(video_id: str, db: sqlite3.Connection) -> VideoOut:
    row = db.execute(
        'SELECT video_id, title, channel, duration_s, views, region, fetched_at '
        'FROM videos WHERE video_id = ?',
        (video_id,),
    ).fetchone()
    if row is None:
        raise NotFoundException(detail=f'unknown video {video_id}')
    return VideoOut(**dict(row))


@get('/v1/search', sync_to_thread=True, cache=60)
def search(
    q: Annotated[str, Parameter(min_length=2, max_length=64)],
    region: Annotated[str, Parameter(default='US')],
    limit: Annotated[int, Parameter(default=24, ge=1, le=100)],
) -> list[VideoOut]:
    if region not in REGIONS:
        region = 'US'
    # FTS5 prefix match; the trailing * is why we build the term by hand.
    term = ' '.join(f'{tok}*' for tok in q.split() if tok.isalnum())
    if not term:
        return []
    db = provide_db()
    rows = db.execute(
        'SELECT v.video_id, v.title, v.channel, v.duration_s, v.views, '
        '       v.region, v.fetched_at '
        'FROM videos_fts f JOIN videos v ON v.rowid = f.rowid '
        'WHERE videos_fts MATCH ? AND v.region = ? '
        'ORDER BY bm25(videos_fts), v.views DESC LIMIT ?',
        (term, region, limit),
    ).fetchall()
    return [VideoOut(**dict(r)) for r in rows]


app = Litestar(
    route_handlers=[get_video, search],
    dependencies={'db': Provide(provide_db, sync_to_thread=True, use_cache=True)},
)

Two details worth calling out. bm25(videos_fts) sorts ascending because FTS5 returns negative relevance scores — lower is better — which is the opposite of what everyone assumes on first read, and it is the single most common FTS5 bug I have written. And building the MATCH term by hand with isalnum() filtering is not paranoia; FTS5 query syntax will happily interpret a user-supplied " or NEAR as an operator and throw a sqlite3.OperationalError straight into a 500.

The write path is where the time went

The read side was an afternoon. The fetcher was the real work. Eight regions, three API keys, a per-key daily quota, and a hard requirement that one bad region never blocks the other seven.

asyncio.TaskGroup handles the fanout, a Semaphore caps in-flight requests so we do not open 200 sockets against one host, and each region's failure is caught inside its own task so the group does not cancel its siblings. That last part matters: TaskGroup cancels every sibling task when one raises, which is exactly the wrong behavior for independent regions. If you want partial success, you catch inside.

# app/fetcher.py
import asyncio
import time
from dataclasses import dataclass, field

import httpx

API = 'https://www.googleapis.com/youtube/v3/videos'
MAX_INFLIGHT = 6


@dataclass
class RegionResult:
    region: str
    items: list[dict] = field(default_factory=list)
    error: str | None = None
    elapsed_ms: int = 0


async def fetch_region(
    client: httpx.AsyncClient,
    sem: asyncio.Semaphore,
    region: str,
    api_key: str,
) -> RegionResult:
    started = time.monotonic()
    params = {
        'part': 'snippet,statistics,contentDetails',
        'chart': 'mostPopular',
        'regionCode': region,
        'maxResults': 50,
        'key': api_key,
    }
    async with sem:
        for attempt in range(3):
            try:
                resp = await client.get(API, params=params, timeout=8.0)
                if resp.status_code == 403:
                    return RegionResult(region, error='quota', 
                                        elapsed_ms=_ms(started))
                if resp.status_code >= 500:
                    await asyncio.sleep(0.5 * (2 ** attempt))
                    continue
                resp.raise_for_status()
                data = resp.json()
                return RegionResult(region, data.get('items', []),
                                    elapsed_ms=_ms(started))
            except (httpx.TimeoutException, httpx.TransportError) as exc:
                if attempt == 2:
                    return RegionResult(region, error=repr(exc),
                                        elapsed_ms=_ms(started))
                await asyncio.sleep(0.5 * (2 ** attempt))
    return RegionResult(region, error='exhausted', elapsed_ms=_ms(started))


def _ms(started: float) -> int:
    return int((time.monotonic() - started) * 1000)


async def fetch_all(regions: list[str], keys: list[str]) -> list[RegionResult]:
    sem = asyncio.Semaphore(MAX_INFLIGHT)
    limits = httpx.Limits(max_connections=MAX_INFLIGHT, 
                          max_keepalive_connections=MAX_INFLIGHT)
    results: list[RegionResult] = []
    async with httpx.AsyncClient(limits=limits, http2=True) as client:
        async with asyncio.TaskGroup() as tg:
            tasks = [
                tg.create_task(
                    fetch_region(client, sem, region, keys[i % len(keys)])
                )
                for i, region in enumerate(regions)
            ]
        results = [t.result() for t in tasks]
    return results

The eight-region sweep now finishes in about 2.9 seconds, dominated by the slowest single region rather than the sum of all of them. The full refresh — 2,400 videos in batches of 50 — went from 41 minutes to 94 seconds, which fits comfortably inside a single cron slot with room for retries.

A few things I would tell my past self:

• http2=True on the httpx client is worth it here because every request goes to one host. Multiplexing over a single connection removed roughly 200 ms of TLS handshake per region on cold starts.
• Round-robin the keys by index, not randomly. Random selection clustered onto one key often enough to trip a per-key rate limit while the other two sat idle.
• Treat 403 as terminal, not retryable. Quota exhaustion does not get better in 500 ms, and retrying it three times just burns the remaining budget faster.

Calling it from PHP 8.4

The web tier still renders everything. It talks to the metadata service over HTTP on a private interface, with a hard timeout and a fallback to the last-known-good SQLite copy that gets rsynced alongside. If the service is down, pages render slightly stale instead of not at all — which is the only acceptable failure mode for a page that Google crawls.

PHP 8.4's property hooks made the client noticeably tidier: the available flag is derived, not stored, so there is no way for it to go stale.

<?php
declare(strict_types=1);

final class MetadataClient
{
    private ?int $lastFailureAt = null;

    public bool $available {
        get => $this->lastFailureAt === null
            || (time() - $this->lastFailureAt) > self::COOLDOWN;
    }

    private const int COOLDOWN = 30;

    public function __construct(
        private readonly string $baseUrl,
        private readonly float $timeout = 0.75,
    ) {}

    /** @return array<int, array<string, mixed>> */
    public function search(string $q, string $region, int $limit = 24): array
    {
        if (!$this->available) {
            return $this->fallbackSearch($q, $region, $limit);
        }

        $url = $this->baseUrl . '/v1/search?' . http_build_query([
            'q' => $q, 'region' => $region, 'limit' => $limit,
        ]);

        $ch = curl_init($url);
        curl_setopt_array($ch, [
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_TIMEOUT_MS     => (int) ($this->timeout * 1000),
            CURLOPT_CONNECTTIMEOUT_MS => 200,
            CURLOPT_HTTPHEADER     => ['Accept: application/json'],
        ]);
        $body = curl_exec($ch);
        $code = curl_getinfo($ch, CURLINFO_RESPONSE_CODE);
        curl_close($ch);

        if ($body === false || $code !== 200) {
            $this->lastFailureAt = time();
            return $this->fallbackSearch($q, $region, $limit);
        }

        $this->lastFailureAt = null;

        try {
            return json_decode($body, true, 8, JSON_THROW_ON_ERROR);
        } catch (JsonException) {
            $this->lastFailureAt = time();
            return $this->fallbackSearch($q, $region, $limit);
        }
    }

    /** @return array<int, array<string, mixed>> */
    private function fallbackSearch(string $q, string $region, int $limit): array
    {
        $db = new PDO('sqlite:' . __DIR__ . '/../data/metadata.db');
        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $stmt = $db->prepare(
            'SELECT v.video_id, v.title, v.channel, v.views '
            . 'FROM videos_fts f JOIN videos v ON v.rowid = f.rowid '
            . 'WHERE videos_fts MATCH :q AND v.region = :r '
            . 'ORDER BY bm25(videos_fts) LIMIT :n'
        );
        $term = implode(' ', array_map(
            static fn(string $t): string => $t . '*',
            array_filter(explode(' ', $q), ctype_alnum(...))
        ));
        $stmt->bindValue(':q', $term === '' ? 'zzzz' : $term);
        $stmt->bindValue(':r', $region);
        $stmt->bindValue(':n', $limit, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}

The 30-second circuit-breaker cooldown is not sophisticated, but it prevents the failure mode where every page view pays a 750 ms timeout because the service is restarting. Under load, that difference is the whole ballgame.

Deploying async Python when your pipeline is FTP

Our deploy story is lftp mirroring a directory tree to shared hosts. That works fine for PHP, where uploading a file is the deploy. It does not work for a process that has to be restarted, and shared LiteSpeed hosting will not run a long-lived Python process for you anyway.

So the metadata service does not live on the web hosts. It runs on one small VPS under systemd, and the artifact it produces — a rebuilt metadata.db — is what gets pushed out. The FTP pipeline stayed exactly as it was; it just gained one more file to mirror.

The sequence per cycle:

• The fetcher writes into metadata.build.db, a fresh file, never the live one.
• PRAGMA wal_checkpoint(TRUNCATE) then VACUUM runs, so what ships is compact and has no sidecar WAL.
• An integrity gate runs: row counts per region must be within 20% of the previous build, or the build is discarded and the old file ships again.
• lftp mirrors the file to each web host under a temp name, then a single rename swaps it atomically on the remote side.
• The service on the VPS reopens its read handle against the new file.

That integrity gate has saved us twice. Once when a quota exhaustion produced a technically valid database containing 340 videos instead of 2,400, and once when a region code typo silently produced an empty JP partition. Both would have shipped. A build that fails the gate is not an outage; a build that ships empty is.

Measuring it honestly

I do not trust a latency number I did not generate under concurrency, and I wanted the load generator to be a single static binary I could drop on any host without a Python environment. Small Go program, 40 lines, no dependencies:

package main

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

func main() {
    url := os.Args[1]
    workers, reqs := 32, 2000

    client := &http.Client{Timeout: 5 * time.Second}
    samples := make([]time.Duration, 0, reqs)
    var mu sync.Mutex
    var wg sync.WaitGroup
    jobs := make(chan int, reqs)

    for w := 0; w < workers; w++ {
        wg.Add(1)
        go func() {
            defer wg.Done()
            for range jobs {
                start := time.Now()
                resp, err := client.Get(url)
                if err != nil {
                    continue
                }
                resp.Body.Close()
                mu.Lock()
                samples = append(samples, time.Since(start))
                mu.Unlock()
            }
        }()
    }

    for i := 0; i < reqs; i++ {
        jobs <- i
    }
    close(jobs)
    wg.Wait()

    sort.Slice(samples, func(i, j int) bool { return samples[i] < samples[j] })
    p := func(q float64) time.Duration {
        return samples[int(float64(len(samples)-1)*q)]
    }
    fmt.Printf("n=%d p50=%v p95=%v p99=%v\n",
        len(samples), p(0.50), p(0.95), p(0.99))
}

Numbers from the production box, 32 concurrent workers, 2,000 requests against /v1/search?q=trailer&region=US:

• p50 6.4 ms, p95 14.1 ms, p99 31.8 ms. The p99 tail is SQLite page cache misses, not framework overhead.
• Single-region /v1/videos/{id} lookups: p50 1.9 ms with the 300-second response cache warm.
• Memory: 84 MB RSS steady state with four Granian workers. The mmap'd database does not count against that in any way that matters, since it is shared page cache.

For comparison, the same search served directly from PHP against the same file was p50 8.9 ms — barely different. That is the honest result: the read path was never the bottleneck and moving it to Python did not make it meaningfully faster. The win was entirely on the write path, and I would not have bothered building the read endpoints at all except that having them made the fallback path testable.

What broke in the first two weeks

• A sync handler without sync_to_thread blocked the loop. Litestar warned in the logs; I did not read the logs. p99 went to 900 ms under concurrency and I spent an hour blaming SQLite.
• check_same_thread=False plus a cached DI provider meant one connection shared across the threadpool. SQLite tolerates this for reads in WAL mode but serializes them. Per-thread connections via threading.local fixed a real throughput ceiling.
• msgspec Struct with frozen=True cannot be mutated in a post-processing step. Obvious in hindsight; I had a view-count normalization pass that needed rewriting into the constructor.
• The atomic remote rename is not atomic on every FTP server. Two of our hosts implement RNFR/RNTO non-atomically across filesystems. Uploading to the same directory as the target, not /tmp, fixed it.

Conclusion

The part worth generalizing is not "use Litestar." It is that the job which needs concurrency and the job which needs to render HTML in 38 ms are different jobs with different runtime requirements, and shared hosting is genuinely fine for the second one. Splitting them let each stay simple: PHP kept doing what it is good at, the fetcher got an event loop and a process that lives longer than 180 seconds, and SQLite stayed the boundary between them because a file is the easiest interface two languages can agree on.

If you are staring at a chunked cron job with checkpoint rows in it, that is the smell. The checkpoints exist because the process cannot live long enough to finish. Give it a process that can, and most of the machinery around it disappears.