DEV Community: ahmet gedik

Real-Time Video Metadata Invalidation With Postgres LISTEN and NOTIFY

ahmet gedik — Fri, 12 Jun 2026 18:00:02 +0000

A viewer hits a watch page on DailyWatch and sees a view count that is six hours stale, a title that was corrected last night, and a thumbnail that points at a video we already pulled for a copyright strike. Every one of those is a caching bug, and every one of them traces back to the same root cause: we had no fast, reliable way to tell the edge that a single video's metadata had changed. We cache aggressively — LiteSpeed page cache in front of PHP 8.4, a SQLite FTS5 index for discovery, and Cloudflare in front of all of it. Aggressive caching is what lets a free video discovery platform serve millions of requests on a small budget. The price is invalidation. When the canonical metadata for one video changes, that change has to ripple outward in seconds, not on the next cron tick.

The naive fixes all fail in instructive ways. Short TTLs throw away the cache hit rate we depend on. A cron job that scans for updated_at > last_run is coarse, polls a hot table constantly, and still leaves a window of staleness equal to its interval. Pushing every edit through an application-level message bus means the bus and the database can disagree the moment a transaction rolls back. What I actually wanted was for the database — the single source of truth for metadata — to be the thing that announces changes, transactionally, the instant a row is committed. That is exactly what Postgres LISTEN/NOTIFY gives you. This post walks through how we wired it up: the trigger that fires on commit, a long-lived Go listener that fans changes out, and the PHP and Python pieces that turn a notification into a surgical cache purge.

Why the source of truth should announce its own changes

Our metadata pipeline is Postgres, even though the read path that serves pages is SQLite FTS5. That split is deliberate. Ingestion workers in Python pull from upstream APIs, normalize, dedupe, and write canonical rows into Postgres. A separate process projects the relevant columns into the per-edge SQLite databases that LiteSpeed reads from. Postgres is where truth lives and where mutations happen, so Postgres is the natural place to emit "this changed" events.

NOTIFY is a Postgres command that sends a payload on a named channel. LISTEN subscribes a connection to that channel. The property that makes it useful for invalidation — and that an external broker cannot give you for free — is that notifications are delivered as part of the transaction. If you NOTIFY inside a transaction and the transaction commits, the notification is delivered. If it rolls back, the notification is discarded. You can never tell a downstream cache to purge a change that never actually landed. That single guarantee eliminates an entire category of phantom-invalidation bugs.

There are real limits worth stating up front, because they shape the design:

The payload is capped at 8000 bytes. You send identifiers and a reason code, never the full row.
Delivery is at-most-once to currently connected listeners. If your listener is down when the NOTIFY fires, that event is gone. You need a catch-up path.
Notifications are not queued durably. This is a signaling mechanism, not Kafka. Treat it as a hint that says "go look," not as the system of record.

Those constraints push you toward a specific shape: send tiny payloads, design the consumer to be idempotent, and keep a reconciliation sweep as a safety net.

The trigger that fires on commit

The cleanest place to emit the event is a row-level trigger on the videos table. It fires for every insert, update, and delete, builds a compact JSON payload, and calls pg_notify. Because the trigger runs inside the same transaction as the write, the notification inherits the transaction's commit/rollback fate automatically.

CREATE OR REPLACE FUNCTION notify_video_change()
RETURNS trigger AS $$
DECLARE
  payload json;
  changed_id text;
BEGIN
  changed_id := COALESCE(NEW.video_id, OLD.video_id);

  -- For updates, only emit when a cache-relevant column actually changed.
  IF (TG_OP = 'UPDATE') THEN
    IF NEW.title IS NOT DISTINCT FROM OLD.title
       AND NEW.description IS NOT DISTINCT FROM OLD.description
       AND NEW.thumbnail_url IS NOT DISTINCT FROM OLD.thumbnail_url
       AND NEW.status IS NOT DISTINCT FROM OLD.status
       AND NEW.category_id IS NOT DISTINCT FROM OLD.category_id THEN
      RETURN NEW;  -- view-count-only update, not worth a purge
    END IF;
  END IF;

  payload := json_build_object(
    'video_id', changed_id,
    'op', TG_OP,
    'category_id', COALESCE(NEW.category_id, OLD.category_id),
    'ts', extract(epoch FROM clock_timestamp())
  );

  PERFORM pg_notify('video_metadata', payload::text);
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_video_metadata_change
AFTER INSERT OR UPDATE OR DELETE ON videos
FOR EACH ROW EXECUTE FUNCTION notify_video_change();

The IS NOT DISTINCT FROM guard matters more than it looks. View counts and watch timestamps churn constantly; if every increment triggered an edge purge we would be invalidating the same hot pages thousands of times an hour and defeating the cache entirely. We only signal when a column that actually appears in rendered HTML or the search index changes. View counts get reconciled lazily on a slower path because nobody files a bug report over a count that is two minutes behind.

One caution about firing pg_notify in AFTER triggers under heavy write load: notifications on a given channel are coalesced and ordered, and a flood of distinct payloads can add commit latency. We measured it at our write volume and it is negligible, but if you ingest tens of thousands of rows in a single transaction, batch the notify into one statement-level call instead of one per row.

A long-lived Go listener that fans out

The consumer is a separate daemon. It holds one dedicated Postgres connection open, blocks on incoming notifications, and translates each into concrete invalidation actions. I wrote ours in Go because the listener needs to be a rock-solid long-running process with good connection handling and cheap concurrency for the fan-out. pgx exposes the LISTEN/NOTIFY primitives directly through WaitForNotification.

package main

import (
    "context"
    "encoding/json"
    "log"
    "time"

    "github.com/jackc/pgx/v5"
)

type VideoChange struct {
    VideoID    string  `json:"video_id"`
    Op         string  `json:"op"`
    CategoryID *int    `json:"category_id"`
    TS         float64 `json:"ts"`
}

func main() {
    ctx := context.Background()

    for {
        if err := listen(ctx); err != nil {
            log.Printf("listener dropped: %v; reconnecting in 2s", err)
            time.Sleep(2 * time.Second)
            // On reconnect we may have missed events: trigger a catch-up sweep.
            go reconcileSince(ctx, time.Now().Add(-5*time.Minute))
        }
    }
}

func listen(ctx context.Context) error {
    conn, err := pgx.Connect(ctx, "postgres://ingest@localhost/dailywatch")
    if err != nil {
        return err
    }
    defer conn.Close(ctx)

    if _, err := conn.Exec(ctx, "LISTEN video_metadata"); err != nil {
        return err
    }
    log.Println("listening on channel video_metadata")

    for {
        n, err := conn.WaitForNotification(ctx)
        if err != nil {
            return err // surface to the reconnect loop
        }

        var c VideoChange
        if err := json.Unmarshal([]byte(n.Payload), &c); err != nil {
            log.Printf("bad payload %q: %v", n.Payload, err)
            continue
        }

        // Fan out concurrently; each action is idempotent.
        go handleChange(ctx, c)
    }
}

func handleChange(ctx context.Context, c VideoChange) {
    projectToSQLite(c.VideoID, c.Op)        // refresh the read-path FTS5 row
    purgeCloudflare("/watch/" + c.VideoID)  // edge purge for the watch page
    if c.CategoryID != nil {
        purgeLocalPageCache(c.VideoID, *c.CategoryID)
    }
    log.Printf("invalidated %s (%s)", c.VideoID, c.Op)
}

The reconnect loop is the whole point of using a dedicated daemon. WaitForNotification returns an error when the connection drops, we log it, sleep, reconnect, and — critically — kick off a reconciliation sweep for the window we might have missed. Because NOTIFY is at-most-once to live listeners, every reconnect is a potential gap, and the catch-up sweep is what makes the system eventually consistent rather than silently lossy.

Turning a notification into a surgical purge

The handleChange fan-out does three things, and each deserves a note.

Project to SQLite FTS5. The read path queries SQLite, so the listener pushes the changed columns into the per-edge database and runs an INSERT OR REPLACE plus an FTS5 index update. This is the step that makes search results and listing pages reflect the edit.
Purge the edge. A single targeted Cloudflare cache purge for the exact watch URL, not a zone-wide flush. Zone purges are a blunt instrument that tank your hit rate for minutes afterward.
Purge the local page cache. LiteSpeed and our PHP file cache hold rendered HTML for the watch page and any category listing the video appears on, so we delete those specific files.

The local PHP side is where the LiteSpeed and file-cache invalidation actually happens. This runs as a tiny endpoint the Go listener calls, or you can have PHP itself hold the listener connection on smaller deployments. Here is the file-cache purge in PHP 8.4:

<?php
declare(strict_types=1);

final class CacheInvalidator
{
    public function __construct(
        private readonly string $pageCacheDir = '/var/cache/dailywatch/pages',
    ) {}

    public function purgeVideo(string $videoId, int $categoryId): void
    {
        // Watch page and the category listing the video appears on.
        $keys = [
            "watch:{$videoId}",
            "category:{$categoryId}",
            'home:index',
        ];

        foreach ($keys as $key) {
            $path = $this->pageCacheDir . '/' . hash('xxh128', $key) . '.html';
            if (is_file($path)) {
                @unlink($path);
            }
        }

        // Tell LiteSpeed to drop its public cache entry for this tag.
        $this->purgeLiteSpeed("watch_{$videoId}");
    }

    private function purgeLiteSpeed(string $tag): void
    {
        // LiteSpeed honours this response header for tag-based purges.
        if (!headers_sent()) {
            header('X-LiteSpeed-Purge: tag=' . $tag, false);
        }
    }
}

// Invoked by the listener with a small JSON POST.
$body = json_decode(file_get_contents('php://input') ?: '{}', true);
if (isset($body['video_id'], $body['category_id'])) {
    (new CacheInvalidator())->purgeVideo(
        (string) $body['video_id'],
        (int) $body['category_id'],
    );
    http_response_code(204);
}

The X-LiteSpeed-Purge header with a tag is the clean way to evict LiteSpeed's public cache for a specific entity, assuming you tagged the page when you cached it. Combined with deleting the PHP file-cache artifacts and the targeted Cloudflare purge, an edit propagates through all three cache layers within a second or two of the database commit.

The ingestion side and the reconciliation safety net

The writers stay blissfully ignorant of all this. Python ingestion workers just write to Postgres inside a transaction; the trigger handles notification. That separation is the elegance of doing it in the database — no application code has to remember to publish an event, and there is no way to commit a metadata change without the notification going out.

import psycopg

def upsert_video(conn: psycopg.Connection, video: dict) -> None:
    """Writers do nothing special; the AFTER trigger emits the NOTIFY."""
    with conn.transaction():
        conn.execute(
            """
            INSERT INTO videos (video_id, title, description,
                                thumbnail_url, category_id, status, updated_at)
            VALUES (%(video_id)s, %(title)s, %(description)s,
                    %(thumbnail_url)s, %(category_id)s, %(status)s, now())
            ON CONFLICT (video_id) DO UPDATE SET
                title         = EXCLUDED.title,
                description   = EXCLUDED.description,
                thumbnail_url = EXCLUDED.thumbnail_url,
                category_id   = EXCLUDED.category_id,
                status        = EXCLUDED.status,
                updated_at    = now()
            """,
            video,
        )
    # On commit, trg_video_metadata_change fires pg_notify('video_metadata', ...).

The reconciliation sweep is the other half of correctness. Because notifications can be missed during a listener restart or a network blip, a periodic job re-projects everything touched recently. It is cheap, it is idempotent, and it closes the at-most-once gap.

import datetime

def reconcile_since(conn: psycopg.Connection, since: datetime.datetime) -> int:
    rows = conn.execute(
        """
        SELECT video_id, category_id
        FROM videos
        WHERE updated_at >= %s
        ORDER BY updated_at
        """,
        (since,),
    ).fetchall()

    for video_id, category_id in rows:
        project_to_sqlite(video_id)
        purge_caches(video_id, category_id)  # same idempotent purge path
    return len(rows)

We run reconcile_since on a five-minute overlap window so consecutive sweeps always cover any gap, and the Go listener calls the same logic immediately after every reconnect. The result is a system that is fast in the common case — sub-second propagation driven by NOTIFY — and self-healing in the failure case, because the sweep guarantees that anything the live channel dropped still gets picked up.

A few operational lessons from running this in production:

Make every consumer action idempotent. Re-projecting a row and re-purging a URL must be safe to do twice, because the live path and the reconciliation path will sometimes both handle the same change.
Keep payloads to identifiers. Sending the full row tempts you past the 8000-byte limit and makes the listener trust stale data. Send the id, fetch fresh on the consumer side if you need detail.
Don't NOTIFY on view-count churn. Separate the columns that affect rendered output from the columns that are pure analytics, and only signal on the former.
One dedicated connection per listener. Don't run LISTEN on a pooled connection that gets handed back; you will miss notifications and not know it.

Conclusion

The shape that worked for us is simple to state: let Postgres announce its own changes through a commit-bound trigger, consume those announcements in one resilient long-lived listener, fan each event out into surgical purges across SQLite FTS5, the PHP file cache, LiteSpeed, and Cloudflare, and back the whole thing with an idempotent reconciliation sweep that catches anything the at-most-once channel drops. LISTEN/NOTIFY is not a durable message queue and you will hurt yourself if you treat it like one. Treated as what it actually is — a transactional, low-latency signal that says "this id changed, go look" — it lets a free discovery platform keep an aggressive multi-layer cache and still reflect a corrected title or a pulled video within seconds. That combination, high hit rates plus fast invalidation, is exactly the thing that used to feel mutually exclusive, and it is the difference between a viewer seeing six-hour-stale metadata and seeing the truth.

Deduplicating Video URLs at Scale with a PHP Canonicalization Pipeline

ahmet gedik — Fri, 12 Jun 2026 11:00:01 +0000

Last month I opened our admin dashboard and found the trailer for one Korean drama sitting in the discovery feed eleven separate times. Eleven rows, eleven thumbnails, eleven primary keys, all pointing at the exact same video. At TrendVidStream we fan out discovery crons across eight regions, and each region had ingested that clip with its own mix of tracking parameters, a different host alias, and in two cases a shortened link. To a person they are obviously the same video. To a videos table they were eleven unrelated facts. This is the URL canonicalization problem, and if you run any kind of content aggregator it will quietly wreck your data quality before you ever notice it.

Why one video becomes eleven rows

The variation comes from every layer of the stack at once. The same upstream video reaches us as all of these:

https://www.youtube.com/watch?v=dQw4w9WgXcQ
https://youtu.be/dQw4w9WgXcQ?si=8fJ2kLm
https://m.youtube.com/watch?v=dQw4w9WgXcQ&feature=share
https://www.youtube.com/embed/dQw4w9WgXcQ
https://www.youtube.com/shorts/dQw4w9WgXcQ

Region crons receive tracking parameters that the upstream API hands back. Mobile endpoints return m. hosts. Share buttons append si and feature. Embed and shorts surfaces use entirely different path shapes for the identical video. If your dedup strategy is INSERT OR IGNORE keyed on the raw URL string, which is where almost everyone starts, every one of those becomes a brand new row, because as strings they genuinely are all different.

The damage is not just cosmetic. A duplicated catalogue poisons everything downstream: your trending ranker counts the same video five times and over-promotes it, your FTS5 search returns five identical hits on the first page, your region-availability badges are wrong, and your sitemap ships five URLs that search engines treat as duplicate content. We were paying that tax on every one of eight regions, every cron cycle.

What canonical actually means here

Canonicalization is the process of reducing many equivalent representations of a thing down to one chosen representative. For video URLs I split it into two layers, because each layer solves a different failure mode.

The first layer is string normalization: take a URL and produce a deterministic, cleaned-up version of the same URL. Same scheme, lowercased host, no tracking junk, sorted query string. This collapses ?v=X&utm_source=... and ?utm_source=...&v=X into one.

The second layer is identity extraction: pull the platform-stable video ID out of the normalized URL, because two completely different URL shapes such as youtu.be/X and youtube.com/embed/X still point at the same video, and string normalization alone will never make them equal. The identity is what we actually deduplicate on. The normalized URL is what we store and serve.

Treat these as separate steps. Bolting identity logic into your normalizer produces a tangled function that is impossible to test, and you will be testing this code constantly as platforms keep changing their URL formats.

Layer one normalizing the URL string

Here is the normalizer we run on PHP 8.4. It is deliberately boring: parse, rewrite the parts we care about, drop the rest.

<?php
declare(strict_types=1);

final class UrlNormalizer
{
    private const STRIP_PARAMS = [
        'utm_source', 'utm_medium', 'utm_campaign', 'utm_term', 'utm_content',
        'feature', 'gclid', 'fbclid', 'si', 'pp', 'ab_channel',
    ];

    private const HOST_ALIASES = [
        'm.youtube.com'     => 'www.youtube.com',
        'youtube.com'       => 'www.youtube.com',
        'youtu.be'          => 'www.youtube.com',
        'm.dailymotion.com' => 'www.dailymotion.com',
    ];

    public function normalize(string $raw): string
    {
        $parts = parse_url(trim($raw));
        if ($parts === false || empty($parts['host'])) {
            throw new InvalidArgumentException('unparseable URL: ' . $raw);
        }

        $host = strtolower($parts['host']);
        $host = self::HOST_ALIASES[$host] ?? $host;
        $path = rtrim($parts['path'] ?? '/', '/') ?: '/';

        $query = [];
        if (!empty($parts['query'])) {
            parse_str($parts['query'], $query);
            foreach (self::STRIP_PARAMS as $junk) {
                unset($query[$junk]);
            }
            ksort($query);
        }

        $rebuilt = 'https://' . $host . $path;
        if ($query !== []) {
            $rebuilt .= '?' . http_build_query($query);
        }
        return $rebuilt;
    }
}

A few decisions worth calling out:

Host aliasing is explicit, not clever. youtu.be, m.youtube.com and bare youtube.com all map to www.youtube.com. A lookup table you can read beats a regex you cannot.
Tracking parameters are an allowlist-to-strip, not a denylist-to-keep. We strip known junk and keep everything else, because dropping an unknown parameter risks dropping something semantically meaningful like a playlist index.
The query string is sorted. ksort($query) is the single line that makes ?a=1&b=2 and ?b=2&a=1 produce identical output. Without it your normalization is non-deterministic against input ordering.
Trailing slashes are removed so /watch/ and /watch agree.

The function throws on unparseable input instead of returning a best-effort string. In a pipeline you want a bad record to stop loudly at the boundary, not flow downstream as a silently malformed canonical key.

Layer two extracting a platform-stable identity

Normalization gets us a clean string, but www.youtube.com/embed/X and www.youtube.com/watch?v=X are both already normalized and still describe the same video. That is what identity extraction is for.

<?php
declare(strict_types=1);

final class VideoIdentity
{
    /** @return array{platform: string, id: string} */
    public function resolve(string $normalizedUrl): array
    {
        $u = parse_url($normalizedUrl);
        $host = $u['host'] ?? '';
        $path = $u['path'] ?? '';
        parse_str($u['query'] ?? '', $q);

        return match (true) {
            str_contains($host, 'youtube.com') => [
                'platform' => 'youtube',
                'id'       => $this->youtubeId($path, $q),
            ],
            str_contains($host, 'dailymotion.com') => [
                'platform' => 'dailymotion',
                'id'       => $this->lastSegment($path),
            ],
            str_contains($host, 'vimeo.com') => [
                'platform' => 'vimeo',
                'id'       => $this->lastSegment($path),
            ],
            default => [
                'platform' => 'generic',
                'id'       => hash('xxh128', $normalizedUrl),
            ],
        };
    }

    private function youtubeId(string $path, array $q): string
    {
        if (isset($q['v']) && preg_match('/^[A-Za-z0-9_-]{11}$/', $q['v'])) {
            return $q['v'];
        }
        if (preg_match('#/(?:embed|shorts|v)/([A-Za-z0-9_-]{11})#', $path, $m)) {
            return $m[1];
        }
        $seg = $this->lastSegment($path);
        if (preg_match('/^[A-Za-z0-9_-]{11}$/', $seg)) {
            return $seg;
        }
        throw new RuntimeException('no YouTube id in ' . $path);
    }

    private function lastSegment(string $path): string
    {
        $segments = array_values(array_filter(explode('/', $path)));
        return $segments ? end($segments) : '';
    }
}

The match (true) block dispatches on host and each platform gets a small handler. YouTube is the messy one: the ID can live in the v query parameter, or in an /embed/, /shorts/, or /v/ path segment, or, once youtu.be has been aliased and its path reduced to /<id>, as the last path segment. Every YouTube ID is validated against the canonical eleven-character [A-Za-z0-9_-] pattern, so a malformed path raises instead of producing a junk identity.

Anything we do not recognize falls through to a generic platform whose identity is just a hash of the normalized URL. That is a deliberate floor: an unknown source still gets a stable key, it just cannot be deduplicated across URL shapes the way a known platform can. When we add a new source we add a handler; until then nothing crashes.

The canonical key and an idempotent write

Now we can define the canonical key: platform:id, for example youtube:dQw4w9WgXcQ. It is human-readable in logs, collision-resistant across platforms, and makes a perfect primary key. Here is the schema:

CREATE TABLE videos (
    canonical_key TEXT PRIMARY KEY,   -- e.g. youtube:dQw4w9WgXcQ
    platform      TEXT NOT NULL,
    video_id      TEXT NOT NULL,
    title         TEXT NOT NULL,
    first_seen    INTEGER NOT NULL,
    last_seen     INTEGER NOT NULL
);

CREATE TABLE video_regions (
    canonical_key TEXT NOT NULL REFERENCES videos(canonical_key) ON DELETE CASCADE,
    region        TEXT NOT NULL,
    PRIMARY KEY (canonical_key, region)
);

-- Full-text discovery index, kept in sync with triggers on videos.
CREATE VIRTUAL TABLE videos_fts USING fts5(
    title,
    content='videos',
    content_rowid='rowid'
);

The videos row is the canonical record. Region membership lives in a separate video_regions junction table, because a single canonical video legitimately appears in many regions and that is a many-to-many relationship, not a comma-joined string you have to parse and re-serialize on every write. The FTS5 virtual table is kept in sync with AFTER INSERT, AFTER UPDATE and AFTER DELETE triggers on videos, so search always sees exactly one row per real video.

The write itself is a single idempotent upsert:

<?php
declare(strict_types=1);

final class CanonicalStore
{
    public function __construct(private PDO $db) {}

    public function canonicalKey(string $platform, string $id): string
    {
        return $platform . ':' . $id;
    }

    public function upsert(string $platform, string $id, string $title, string $region): void
    {
        $key = $this->canonicalKey($platform, $id);
        $this->db->beginTransaction();

        $video = $this->db->prepare(
            'INSERT INTO videos (canonical_key, platform, video_id, title, first_seen, last_seen)
             VALUES (:key, :platform, :vid, :title, unixepoch(), unixepoch())
             ON CONFLICT(canonical_key) DO UPDATE SET
                 last_seen = unixepoch(),
                 title     = excluded.title'
        );
        $video->execute([
            ':key'      => $key,
            ':platform' => $platform,
            ':vid'      => $id,
            ':title'    => $title,
        ]);

        $this->db->prepare(
            'INSERT OR IGNORE INTO video_regions (canonical_key, region) VALUES (:key, :region)'
        )->execute([':key' => $key, ':region' => $region]);

        $this->db->commit();
    }
}

ON CONFLICT(canonical_key) DO UPDATE means re-ingesting a video we already have is a no-op on identity: it just bumps last_seen and refreshes the title. Region membership is a separate INSERT OR IGNORE, so the eighth region to see a video adds itself to video_regions without touching or duplicating the canonical row. Run the same cron twice, run all eight regions concurrently, replay yesterday's feed, and the table converges to the same state every time. Idempotency is the property that lets you stop being afraid of your own cron.

Short links and redirects

One case the normalizer cannot handle on its own is a genuine redirect, a t.co or bare share link that only reveals its real target after an HTTP round trip. Resolving those inline would be a disaster: you would add network latency and an external failure mode to your ingest path, and a slow upstream would back up the whole cron.

So we resolve redirects out of band. A short link gets a single HEAD request with a three-second timeout and CURLOPT_FOLLOWLOCATION capped at three hops; the resolved target is cached in a url_redirects(short, resolved, resolved_at) table keyed on the short URL. The next time any region sees that short link, and across eight regions they will see it repeatedly, it is a local SQLite lookup instead of a network call. Redirect resolution is the one place this pipeline touches the network, so it is the one place we cache aggressively and fail soft: if the HEAD request times out we keep the short URL as-is and try again next cycle, rather than dropping the video.

Wiring it into the multi-region cron

The eight region crons do not each need their own canonicalization logic; they call the same three steps. A region fetch does: pull the raw feed, and for each item run normalize, then resolve identity, then upsert(region). Because region membership accumulates through the junction table, the order regions run in does not matter and they can overlap. The US cron at 02:00 and the JP cron at 02:05 both contributing the same video just produce two video_regions rows against one videos row.

This matters operationally because we deploy over FTP. There is no build step or container on the host; the cron entry points are plain PHP files synced up and run by the host's own scheduler. The canonicalization classes are pure, dependency-free PHP that behaves identically on a laptop and on the LiteSpeed box it lands on. The less environment-specific magic in your ingest path, the less there is to go wrong on a host you reach through an FTP client.

Backfilling the rows we already had

That left the eleven-row mess already sitting in the table. A one-shot Python script handled the backfill, Python because our offline tooling already lives there and plain sqlite3 is more than enough for a few hundred thousand rows.

import sqlite3
from collections import defaultdict
from urllib.parse import urlsplit, parse_qs

def youtube_id(url):
    parts = urlsplit(url)
    host = parts.netloc.lower()
    if 'youtu.be' in host:
        return parts.path.strip('/').split('/')[0] or None
    if 'youtube.com' in host:
        q = parse_qs(parts.query)
        if 'v' in q:
            return q['v'][0]
        seg = parts.path.strip('/').split('/')
        if len(seg) == 2 and seg[0] in {'embed', 'shorts', 'v'}:
            return seg[1]
    return None

def canonical_key(url):
    vid = youtube_id(url)
    return f'youtube:{vid}' if vid else None

con = sqlite3.connect('data/videos.db')
con.row_factory = sqlite3.Row
rows = con.execute('SELECT rowid, url, region FROM legacy_videos').fetchall()

groups = defaultdict(list)
for r in rows:
    key = canonical_key(r['url'])
    if key:
        groups[key].append(r)

merged, dropped = 0, 0
for key, members in groups.items():
    keeper = min(m['rowid'] for m in members)
    regions = {m['region'] for m in members}
    merged += 1
    dropped += len(members) - 1
    # upsert keeper into videos, INSERT OR IGNORE each region, delete the rest

print(f'{merged} canonical videos, {dropped} duplicate rows collapsed')
con.close()

The strategy is read everything, group by canonical key, keep the lowest rowid as the survivor, union every duplicate's region into the survivor, and delete the rest, all inside one transaction with a dry-run flag that prints the counts without writing. The first real run collapsed about 38,000 rows into roughly 24,000 canonical videos. Always dry-run a destructive backfill against a copy of the database first; the cost of being wrong about your canonical key is deleting real data.

What it bought us

Concrete results after shipping this across all eight regions:

Row count dropped by a third and stopped growing super-linearly with region count.
FTS5 search returns one hit per video. First-page duplicates were the single most common complaint and they are gone.
Region badges are correct because membership is tracked explicitly instead of inferred from whichever URL variant happened to land first.
The trending ranker stopped double-counting, which quietly improved the entire discovery feed.
The sitemap shrank and the duplicate-content warnings in Search Console cleared.

Conclusion

URL canonicalization looks like a string-cleaning chore and turns out to be a data-modeling decision. The leverage is in separating the two layers, normalize the string and then extract a platform-stable identity, and in making the write idempotent so that eight regions hammering the same endpoint converge instead of multiply. None of the code here is exotic; it is parse_url, a lookup table, a match, and an ON CONFLICT. The discipline is in choosing one canonical representative and routing every write through it. Build that boundary once, test it hard because platform URL formats will keep shifting under you, and your catalogue stays honest no matter how many regions you fan out to.

Building Video Metadata Semantic Search with pgvector and OpenAI Embeddings

ahmet gedik — Fri, 12 Jun 2026 00:00:01 +0000

Our keyword search was quietly failing a third of our users. Someone would type "funny cat falls off table" into ViralVidVault, and our SQLite LIKE '%funny%cat%' query would return nothing — because the actual trending clip was titled "Maine Coon yeets itself into the void." Same video, zero lexical overlap. We track viral video metadata across European markets, and viral content is exactly where titles drift furthest from the words people use to search. Slang, regional phrasing, emoji-laden descriptions, machine-translated captions — keyword matching breaks on all of it.

The fix was semantic search: embed every video's metadata into a vector, embed the query into the same space, and rank by cosine similarity. This post is the production write-up of how we built that on top of pgvector and OpenAI's text-embedding-3-small, how we kept it GDPR-clean, and how we glued it into a stack that is otherwise PHP 8.4, SQLite in WAL mode, LiteSpeed, and a Cloudflare Worker at the edge.

Why Postgres for vectors when the app runs on SQLite

The main site runs on SQLite with WAL enabled. It is fast, file-based, and survives traffic spikes on LiteSpeed without a separate daemon. But vector search wants three things SQLite does not give you natively: a vector column type, an approximate-nearest-neighbour index, and a distance operator that the planner understands. There are SQLite extensions (sqlite-vec is good), but our embedding corpus is ~2.1M rows and growing, and we wanted HNSW indexing with tunable recall. So we run a dedicated Postgres 16 instance with the pgvector extension purely as a search sidecar. The canonical metadata still lives in SQLite; Postgres holds a denormalised projection plus the vectors.

This split matters for a GDPR reason too. The embedding service is the only component that talks to a US-based API (OpenAI). By isolating it, we can document exactly what data crosses the border and keep personal data out of it entirely. We embed video metadata — titles, descriptions, tags, channel names — never user queries tied to an identity, never IP-linked search history. The Postgres box lives in the EU (Hetzner Falkenstein) and logs nothing query-side.

Schema first. The extension and the table:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE video_embeddings (
    video_id     TEXT PRIMARY KEY,
    region       TEXT NOT NULL,        -- 'DE','FR','PL', etc.
    title        TEXT NOT NULL,
    embedding    vector(1536) NOT NULL,
    content_hash TEXT NOT NULL,        -- sha256 of the embedded text
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- HNSW index for cosine distance. m and ef_construction
-- trade build time/memory for recall.
CREATE INDEX video_embeddings_hnsw
    ON video_embeddings
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- We filter by region constantly, so index it.
CREATE INDEX video_embeddings_region ON video_embeddings (region);

The content_hash column is the unglamorous hero here. Embeddings cost money and rate limit. Re-embedding a video whose metadata has not changed is pure waste. We hash the exact string we send to OpenAI; if the hash already exists with the same value, we skip the API call entirely. On our corpus this drops re-embed cost by about 94% on each nightly refresh, because viral videos churn in ranking far more than they churn in metadata.

Generating embeddings without melting your rate limit

The embedding pipeline is a Python service — Python because the OpenAI SDK and async batching are pleasant there, and because it runs as a cron job outside the request path. It reads new and changed rows from a queue table, batches them (the embeddings endpoint accepts up to 2048 inputs per call), and upserts into Postgres.

The text you embed is the highest-leverage decision in the whole system. Do not just embed the title. We concatenate a weighted, structured document so the vector captures channel and tag context:

import hashlib
import asyncio
import os
import psycopg
from openai import AsyncOpenAI

client = AsyncOpenAI(api_key=os.environ["OPENAI_API_KEY"])
MODEL = "text-embedding-3-small"  # 1536 dims, cheap, plenty good

def build_document(row: dict) -> str:
    # Order matters: titles first, they carry the most signal.
    parts = [
        row["title"],
        f"Channel: {row['channel']}",
        f"Tags: {', '.join(row['tags'][:12])}",
        (row["description"] or "")[:500],
    ]
    return "\n".join(p for p in parts if p)

def content_hash(text: str) -> str:
    return hashlib.sha256(text.encode("utf-8")).hexdigest()

async def embed_batch(rows: list[dict]) -> list[dict]:
    docs, hashes = [], []
    for r in rows:
        doc = build_document(r)
        docs.append(doc)
        hashes.append(content_hash(doc))

    resp = await client.embeddings.create(model=MODEL, input=docs)
    out = []
    for r, h, item in zip(rows, hashes, resp.data):
        out.append({
            "video_id": r["video_id"],
            "region": r["region"],
            "title": r["title"],
            "embedding": item.embedding,
            "content_hash": h,
        })
    return out

async def upsert(conn, records: list[dict]) -> None:
    async with conn.cursor() as cur:
        for rec in records:
            await cur.execute(
                """
                INSERT INTO video_embeddings
                    (video_id, region, title, embedding, content_hash, updated_at)
                VALUES (%(video_id)s, %(region)s, %(title)s,
                        %(embedding)s, %(content_hash)s, now())
                ON CONFLICT (video_id) DO UPDATE SET
                    region = EXCLUDED.region,
                    title = EXCLUDED.title,
                    embedding = EXCLUDED.embedding,
                    content_hash = EXCLUDED.content_hash,
                    updated_at = now()
                WHERE video_embeddings.content_hash
                      IS DISTINCT FROM EXCLUDED.content_hash
                """,
                rec,
            )
    await conn.commit()

Two things worth calling out. First, the WHERE ... IS DISTINCT FROM clause on the upsert means even if the queue feeds us a row we have already embedded, the write is a no-op unless the content actually changed — belt and braces on top of the pre-call hash check. Second, text-embedding-3-small at 1536 dimensions is deliberate. The -large model (3072 dims) scored maybe 2–3% higher on our internal relevance evals but doubled both storage and index memory. For viral video discovery, where the corpus turns over weekly anyway, that is not worth it.

Batching strategy in practice:

Chunk to 256 inputs per request, not the 2048 max. Smaller chunks fail cheaper on transient errors and keep individual request latency under a second.
Back off on 429 with jittered exponential delay. The embeddings endpoint rate-limits on tokens-per-minute, not just requests, so a burst of long descriptions can trip it unexpectedly.
Run embedding as a nightly cron, never inline. Search must keep working if OpenAI is down; you serve slightly stale vectors, nobody notices.

Querying from PHP 8.4

The search request itself is dead simple once the index exists. The PHP front end embeds the user's query (one tiny API call), then asks Postgres for nearest neighbours. We connect to Postgres via PDO only for this one feature — the rest of the app stays on SQLite.

Here is the search service. Note the set_config for hnsw.ef_search, which is the runtime recall knob: higher means more accurate, slower; we found 80 a good balance.

<?php
declare(strict_types=1);

final class SemanticVideoSearch
{
    public function __construct(
        private readonly \PDO $pg,
        private readonly string $openAiKey,
    ) {}

    /** @return list<array{video_id:string,title:string,score:float}> */
    public function search(string $query, string $region, int $limit = 20): array
    {
        $vector = $this->embedQuery($query);
        $literal = '[' . implode(',', $vector) . ']';

        // Tune recall for this session only.
        $this->pg->exec('SET LOCAL hnsw.ef_search = 80');

        $sql = <<<SQL
            SELECT video_id, title,
                   1 - (embedding <=> :vec) AS score
            FROM video_embeddings
            WHERE region = :region
            ORDER BY embedding <=> :vec
            LIMIT :limit
        SQL;

        $stmt = $this->pg->prepare($sql);
        $stmt->bindValue(':vec', $literal);
        $stmt->bindValue(':region', $region);
        $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
        $stmt->execute();

        return array_map(
            static fn(array $r): array => [
                'video_id' => $r['video_id'],
                'title'    => $r['title'],
                'score'    => (float) $r['score'],
            ],
            $stmt->fetchAll(\PDO::FETCH_ASSOC),
        );
    }

    /** @return list<float> */
    private function embedQuery(string $query): array
    {
        $ch = curl_init('https://api.openai.com/v1/embeddings');
        curl_setopt_array($ch, [
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_TIMEOUT        => 4,
            CURLOPT_HTTPHEADER    => [
                'Authorization: Bearer ' . $this->openAiKey,
                'Content-Type: application/json',
            ],
            CURLOPT_POSTFIELDS => json_encode([
                'model' => 'text-embedding-3-small',
                'input' => $query,
            ], JSON_THROW_ON_ERROR),
        ]);

        $raw = curl_exec($ch);
        if ($raw === false) {
            throw new \RuntimeException('embedding failed: ' . curl_error($ch));
        }
        curl_close($ch);

        $data = json_decode($raw, true, 512, JSON_THROW_ON_ERROR);
        return $data['data'][0]['embedding'];
    }
}

The <=> operator is pgvector's cosine distance. We return 1 - distance as a 0..1 similarity score so the front end can show a relevance bar and, more usefully, threshold out garbage — anything under 0.25 we drop rather than show a weak match. The SET LOCAL is scoped to the transaction, so it never leaks into other connections from the pool.

One gotcha that cost me an afternoon: passing the vector as a bound parameter. pgvector accepts the [1,2,3] text literal, and PDO sends it as a string, which is fine. But if you try to use a server-side prepared statement with a typed vector parameter through some drivers, you get cryptic cast errors. The text-literal-as-string approach is portable and the planner still uses the HNSW index. Verify with EXPLAIN ANALYZE that you see an Index Scan using video_embeddings_hnsw and not a sequential scan — if you see seq scan, your ORDER BY distance expression does not match the index's vector_cosine_ops.

Caching at the edge with a Cloudflare Worker

Embedding the query is one network round-trip to the US before we even hit Postgres. For popular queries — and viral search is extremely head-heavy, the same dozen trending terms dominate — that round-trip is wasted. We cache full search responses at the edge in a Cloudflare Worker keyed by normalised query plus region. Popular queries never touch the origin, never touch OpenAI, and never leave the EU edge node closest to the user.

export default {
  async fetch(request, env, ctx) {
    const url = new URL(request.url);
    const q = (url.searchParams.get("q") || "").trim().toLowerCase();
    const region = url.searchParams.get("region") || "DE";
    if (q.length < 2) return new Response("[]", { status: 400 });

    // Normalise so "Funny Cat " and "funny cat" share a cache entry.
    const norm = q.replace(/\s+/g, " ");
    const cacheKey = new Request(
      `https://cache.internal/search?q=${encodeURIComponent(norm)}&region=${region}`,
      { method: "GET" }
    );

    const cache = caches.default;
    let hit = await cache.match(cacheKey);
    if (hit) return hit;

    const origin = await fetch(
      `${env.ORIGIN}/api/semantic-search?q=${encodeURIComponent(norm)}&region=${region}`,
      { headers: { "X-Edge": "vvv" } }
    );

    const body = await origin.text();
    const resp = new Response(body, {
      status: origin.status,
      headers: {
        "Content-Type": "application/json",
        // Viral terms shift hourly; 600s is a sane TTL.
        "Cache-Control": "public, max-age=600",
      },
    });

    if (origin.ok) ctx.waitUntil(cache.put(cacheKey, resp.clone()));
    return resp;
  },
};

The 600-second TTL is a deliberate tradeoff: viral rankings move fast, but the set of videos matching "funny cat" semantically is stable across ten minutes even as their view counts climb. We invalidate aggressively only on the nightly re-embed by bumping a version prefix in the cache key, which the Worker reads from an environment variable. Edge hit rate sits around 70% during peak hours, which means 70% of searches cost us nothing in API calls or Postgres CPU.

Hybrid ranking: semantic is not always right

Pure vector search has a failure mode: it is too fuzzy. Someone searching for an exact channel name or a specific meme phrase wants the literal match first, not a vibes-based neighbour. So we run a cheap hybrid. The semantic results come back from Postgres, and we re-rank with a small lexical boost computed in PHP against the title:

If the query appears as a substring in the title, add a fixed boost to the score.
If query tokens overlap with the channel name, add a smaller boost.
Otherwise, trust the cosine score as-is.

This is not Reciprocal Rank Fusion or anything academic — it is a five-line nudge that fixed the most common complaint ("I searched the exact title and it was third"). The lesson generalises: semantic search is a recall machine, not a precision machine. Pair it with a cheap lexical signal and you get both.

We also clamp results by region at the SQL level rather than post-filtering, because a German user searching trending content does not want Polish-only virals diluting the top 20. Because region is indexed and the HNSW scan respects the WHERE, this stays fast even with the filter.

What it cost and what it bought

Numbers from the first month in production:

Initial backfill of 2.1M videos: about $42 in embedding API calls, run over two nights to stay under rate limits.
Nightly incremental: roughly $0.40/day thanks to the content-hash skip — only genuinely changed metadata gets re-embedded.
Storage: 1536 floats × 4 bytes × 2.1M rows ≈ 13 GB of vectors, plus the HNSW index at about 4 GB resident. Fits comfortably on a 32 GB Postgres box.
Query latency: p50 of 38 ms at the origin (Postgres only, query already embedded), p95 of 110 ms. With edge caching, p50 perceived latency is under 10 ms for cached terms.

The relevance win is harder to put one number on, but our "search returned zero results" rate fell from 31% to 4%. That 27-point swing is almost entirely the long tail of slang, translations, and creative titles that keyword matching could never reach.

Conclusion

Semantic search over video metadata turned out to be one of the highest-leverage, lowest-complexity features we have shipped. The architecture is boring on purpose: Postgres with pgvector as an isolated search sidecar, OpenAI embeddings generated offline in a nightly Python cron, a thin PHP query layer, and a Cloudflare Worker absorbing the head of the distribution at the edge. Nothing about it required abandoning our SQLite-on-LiteSpeed core.

If you take three things away: embed a structured document and not just the title; hash your content so you never pay to re-embed unchanged rows; and pair the semantic recall with a cheap lexical boost so exact matches still win. Keep the US-bound API surface tiny and metadata-only, and the whole thing stays GDPR-defensible by construction. The viral clip titled "Maine Coon yeets itself into the void" now shows up for "funny cat falls off table" — which is the entire point.

Building Video Heatmap Analytics with HyperLogLog in Postgres

ahmet gedik — Thu, 11 Jun 2026 16:00:01 +0000

The problem: counting unique viewers per second is a row explosion

A viewer scrubs to 4:12 of a 9-minute trending clip, watches for 40 seconds, jumps back to the intro, then bounces. Multiply that by the few hundred thousand sessions a day that hit a mid-size aggregator and you get the question every product person eventually asks: which parts of this video do people actually watch, and how many distinct people watched each part?

The naive answer is a watch_events table: one row per (user, video, second). It works until it doesn't. A 9-minute video is 540 seconds. One viewer who watches the whole thing generates 540 rows. A million viewers across our catalog generate hundreds of millions of rows per day, and the only query anyone runs against them is COUNT(DISTINCT user_id) GROUP BY second. That COUNT(DISTINCT) is a sort-or-hash over the entire partition every single time someone opens the analytics tab.

At TopVideoHub we aggregate trending video across Asia-Pacific, so a single popular clip can spike from zero to half a million sessions in an afternoon when it lands in the JP and KR feeds simultaneously. We did not want a fact table that grew by hundreds of millions of rows a day to answer a question whose answer is approximately fine. "Roughly 41,000 unique viewers saw the hook at 0:08" is just as actionable as "41,287". That tolerance for approximation is exactly what HyperLogLog is built for, and Postgres has a battle-tested extension for it.

This post is the design we landed on: fixed-size HLL sketches, one per (video, time_bucket), that you can merge, slice, and union across regions in milliseconds. The main app is PHP 8.4 on LiteSpeed behind Cloudflare, with our search layer on SQLite FTS5; the analytics store is a separate Postgres instance, and HLL is what made that store affordable.

Why HyperLogLog instead of COUNT(DISTINCT)

HyperLogLog estimates the cardinality of a set using a fixed amount of memory regardless of how many elements you throw at it. The intuition: hash every element, look at the longest run of leading zeros you've seen, and use that to estimate how many distinct things must have passed through to produce a run that long. Real implementations split the hash space into many registers and average them with a bias-corrected harmonic mean, which is where the accuracy comes from.

The properties that matter for heatmaps:

Fixed size. A sketch with log2m = 12 (4096 registers) is about 2.5 KB and answers for one viewer or fifty million viewers. Our per-bucket storage is constant, not linear in traffic.
Mergeable (unionable). The union of two HLL sketches is itself a valid HLL sketch. This is the killer feature: I can keep one sketch per region per bucket and union them on read to get a global heatmap, or union adjacent buckets to zoom out from per-second to per-10-second resolution. No re-scanning raw events.
Bounded, tunable error. At log2m = 12 the standard error is around 1.6%. For a watch heatmap that is invisible to the human eye.

We use the postgresql-hll extension (the Citus/Aggregate Knowledge implementation). Install and enable it:

-- as a superuser, once per database
CREATE EXTENSION IF NOT EXISTS hll;

-- one sketch per (video, time bucket, region).
-- bucket_sec is the second offset into the video, snapped to a resolution.
CREATE TABLE video_heatmap (
    video_id    text        NOT NULL,
    region      text        NOT NULL,      -- 'JP', 'KR', 'GLOBAL', ...
    bucket_sec  integer     NOT NULL,      -- 0, 1, 2, ... up to video duration
    viewers     hll         NOT NULL,      -- the sketch
    PRIMARY KEY (video_id, region, bucket_sec)
);

-- a covering index for the common 'one video, one region' read
CREATE INDEX video_heatmap_lookup
    ON video_heatmap (video_id, region, bucket_sec)
    INCLUDE (viewers);

The hll type stores the serialized sketch directly in the row. Postgres ships aggregate functions (hll_add_agg, hll_union_agg) and scalar functions (hll_add, hll_cardinality, || for union) that operate on it.

Ingesting watch progress without an event table

The client sends a heartbeat: every few seconds the player reports the range of seconds the viewer has been present for since the last beat, plus an anonymous, salted viewer id. We do not store the heartbeat. We fold it straight into the sketches.

The core write is "add this viewer's hash to every bucket they watched." In SQL that's hll_add per bucket, but doing it per-second per-request is chatty. Instead we batch on the ingestion worker. Here is the Python ingester that consumes heartbeats off a queue and applies them in one statement per heartbeat using generate_series to expand the watched range:

import hashlib
import os
import psycopg

DB = os.environ["ANALYTICS_DSN"]  # e.g. postgresql://app@db/analytics
VIEWER_SALT = os.environ["VIEWER_SALT"].encode()

# 64-bit hash of the viewer id. hll_hash_bigint expects a bigint.
def viewer_hash(viewer_id: str) -> int:
    digest = hashlib.blake2b(viewer_id.encode() + VIEWER_SALT, digest_size=8).digest()
    # interpret as signed 64-bit; Postgres bigint is signed
    return int.from_bytes(digest, "big", signed=True)

def record_watch(conn, video_id: str, region: str,
                 start_sec: int, end_sec: int, viewer_id: str) -> None:
    h = viewer_hash(viewer_id)
    with conn.cursor() as cur:
        cur.execute(
            """
            INSERT INTO video_heatmap (video_id, region, bucket_sec, viewers)
            SELECT %(vid)s, %(region)s, g.b,
                   hll_add(hll_empty(), hll_hash_bigint(%(h)s))
            FROM generate_series(%(start)s, %(end)s) AS g(b)
            ON CONFLICT (video_id, region, bucket_sec)
            DO UPDATE SET viewers = video_heatmap.viewers
                                    || hll_add(hll_empty(),
                                               hll_hash_bigint(%(h)s));
            """,
            {"vid": video_id, "region": region, "h": h,
             "start": start_sec, "end": end_sec},
        )

if __name__ == "__main__":
    with psycopg.connect(DB, autocommit=False) as conn:
        # in production these come off Redis/SQS; one demo row here
        record_watch(conn, "yt_abc123", "JP", start_sec=8, end_sec=47,
                     viewer_id="sess_91f2")
        conn.commit()

A few deliberate choices in that statement:

hll_hash_bigint is the extension's own hash. Always hash through it rather than feeding raw ids; HLL accuracy depends on a well-distributed hash, and mixing hash functions across writes corrupts the sketch. We pre-hash the viewer id with BLAKE2b only to anonymize and to fit a bigint, then let hll_hash_bigint do the HLL-specific mixing.
|| is union. video_heatmap.viewers || hll_add(...) folds the new viewer into the existing sketch. Adding the same viewer to the same bucket twice is a no-op for the cardinality estimate, which is exactly the dedup behavior we want for "unique viewers."
generate_series(start, end) expands the watched range into one row per bucket in a single round trip. A viewer present for 40 seconds touches 40 buckets in one statement.

The salt is important and easy to get wrong: it must be stable for the retention window, or the same person counts as many. We rotate it daily and keep heatmaps at daily granularity, then union days on read for weekly/monthly views — which works precisely because unions are free.

Reading the heatmap

Now the part that pays for itself. The heatmap read is a scan of one video's buckets with a cardinality estimate per bucket. Because each sketch is fixed-size and pre-aggregated, this is a tight index scan, not a COUNT(DISTINCT) over raw rows.

If a region is requested, read that region directly. If "global" is requested, union across regions per bucket with hll_union_agg:

-- per-second heatmap for one video, unioned across all regions
SELECT bucket_sec,
       hll_cardinality(hll_union_agg(viewers))::int AS unique_viewers
FROM   video_heatmap
WHERE  video_id = $1
GROUP  BY bucket_sec
ORDER  BY bucket_sec;

To zoom out from per-second to per-10-second resolution, union the buckets — no loss of correctness, because unioning sketches and then estimating is the right order of operations (estimating first and summing would double-count anyone present across buckets):

-- 10-second resolution, JP only
SELECT (bucket_sec / 10) * 10 AS bucket_start,
       hll_cardinality(hll_union_agg(viewers))::int AS unique_viewers
FROM   video_heatmap
WHERE  video_id = $1
  AND  region = 'JP'
GROUP  BY bucket_sec / 10
ORDER  BY bucket_start;

That second query is the one I want to stress, because it is the whole argument for HLL. With a raw event table, changing the bucket resolution means re-running COUNT(DISTINCT user_id) with a different GROUP BY over every raw row — and you cannot precompute per-second distinct counts and sum them, because a viewer present at second 3 and second 7 would be counted twice in a 10-second bucket. HLL solves both problems at once: precompute fine-grained sketches, union them at any granularity on read, estimate last.

Wiring it into the PHP app

The public site is PHP 8.4. The analytics dashboard endpoint reads from Postgres and returns a normalized array the frontend renders as a bar strip under the player. We compute a retention curve (cumulative unique viewers who reached each point) and a raw per-bucket count in one pass:

<?php
declare(strict_types=1);

final class HeatmapRepository
{
    public function __construct(private readonly \PDO $pdo) {}

    /**
     * @return list<array{bucket:int, viewers:int, retention:float}>
     */
    public function forVideo(string $videoId, string $region = 'GLOBAL', int $resolution = 5): array
    {
        $sql = <<<SQL
            SELECT (bucket_sec / :res) * :res AS bucket,
                   hll_cardinality(hll_union_agg(viewers))::int AS viewers
            FROM   video_heatmap
            WHERE  video_id = :vid
              AND  (:region = 'GLOBAL' OR region = :region)
            GROUP  BY bucket_sec / :res
            ORDER  BY bucket
        SQL;

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['vid' => $videoId, 'region' => $region, 'res' => $resolution]);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);

        // peak unique viewers is our denominator for the retention curve
        $peak = 0;
        foreach ($rows as $r) {
            $peak = max($peak, (int) $r['viewers']);
        }
        $peak = max($peak, 1);

        return array_map(static fn(array $r): array => [
            'bucket'    => (int) $r['bucket'],
            'viewers'   => (int) $r['viewers'],
            'retention' => round((int) $r['viewers'] / $peak, 4),
        ], $rows);
    }
}

// usage inside a controller
$pdo = new \PDO(getenv('ANALYTICS_DSN'), options: [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
]);
$repo = new HeatmapRepository($pdo);
header('Content-Type: application/json');
header('Cache-Control: public, max-age=300');   // Cloudflare/LiteSpeed will cache the strip
echo json_encode($repo->forVideo($_GET['v'], $_GET['region'] ?? 'GLOBAL'), JSON_THROW_ON_ERROR);

Note the Cache-Control: public, max-age=300. Heatmaps move slowly and the estimate is approximate anyway, so we let LiteSpeed and Cloudflare cache the JSON strip for five minutes. A trending video getting hammered serves the heatmap almost entirely from edge cache; Postgres sees one read per video per five minutes, not one per viewer.

Compaction: hot writes versus cold reads

There is one operational wrinkle. The ON CONFLICT DO UPDATE write path rewrites the sketch on every heartbeat, and a hot video produces a lot of contention on the same (video, region, bucket) rows. Two things keep this healthy.

First, shard the write, union on read. Instead of every worker updating the same row, each ingestion worker writes to its own partition keyed by a worker id, and a periodic compaction job unions partitions down into the canonical row. The read query already uses hll_union_agg, so reading across partitions needs no change — you just drop the worker-id predicate.

Second, let HLL pick its own representation. The extension stores small sketches in a sparse "explicit/sparse" mode and only promotes to the full dense representation once a bucket has accumulated enough distinct viewers. Buckets on the long tail of your catalog — the videos nobody scrubbed past second 2 — stay tiny. You can tune the promotion thresholds on the type itself:

-- log2m=12 (4096 registers, ~1.6% error), regwidth=5,
-- sparse->dense threshold and explicit threshold left at defaults.
ALTER TABLE video_heatmap
    ALTER COLUMN viewers TYPE hll(12, 5);

-- nightly compaction: fold worker shards into the canonical region rows
INSERT INTO video_heatmap (video_id, region, bucket_sec, viewers)
SELECT video_id, region, bucket_sec, hll_union_agg(viewers)
FROM   video_heatmap_shards
WHERE  captured_on = current_date - 1
GROUP  BY video_id, region, bucket_sec
ON CONFLICT (video_id, region, bucket_sec)
DO UPDATE SET viewers = video_heatmap.viewers || EXCLUDED.viewers;

TRUNCATE video_heatmap_shards;   -- or DELETE the compacted partition

With log2m = 12 every dense sketch is ~2.6 KB. A 10-minute video at per-second resolution across 8 regions is 600 * 8 ≈ 4800 rows, well under 13 MB even if every bucket went dense — which they don't, because the tail stays sparse. Compare that to the raw event table, where a single popular video could be tens of millions of rows for the same query.

What we gave up, and why it was fine

HLL is an estimate, so a few capabilities are simply off the table and you should know them going in:

No exact counts. If finance needs a billing-grade unique count, HLL is the wrong tool. For product analytics, the ~1.6% error is noise.
No per-user drill-down. You cannot ask "did viewer X watch the ending" — the sketch threw away identities by design. That's a feature for privacy (we keep no per-viewer watch log) but a constraint if you wanted cohort replay.
No set difference. HLL unions cleanly but does not support intersection or subtraction accurately. "Viewers who saw the intro but not the ending" is not answerable from sketches alone; inclusion-exclusion on HLL compounds error badly. We keep a separate, sampled raw stream for the rare questions that need it.

None of those mattered for the heatmap, which only ever asks "how many distinct people were present at this point."

Takeaways

If you're building watch heatmaps or any "unique-things-per-bucket-over-time" analytics, HyperLogLog in Postgres turns an unbounded, ever-scanning fact table into a fixed-size, pre-aggregated, mergeable store:

One hll sketch per (video, region, time_bucket) keeps storage constant in traffic.
hll_union_agg lets you union across regions and re-bucket on read with no re-scan and no double-counting — union first, estimate last.
Hash through hll_hash_bigint, keep your anonymizing salt stable across the retention window, and let the sparse/dense representation handle the long tail.
Shard writes, compact nightly, and cache the JSON strip at the edge so hot videos barely touch the database.

We shipped this against a Postgres instance an order of magnitude smaller than the raw-events design would have demanded, and the analytics tab renders the per-second curve in well under a frame budget. For a metric that's allowed to be approximately right, that's the trade you want.

Building a Fast Video Metadata Service with Litestar in Python

ahmet gedik — Thu, 11 Jun 2026 04:00:01 +0000

The 280ms problem that started this

When DailyWatch crossed a few million indexed videos, the endpoint that powers our discovery feed started misbehaving. It is a deceptively simple endpoint: give it a query and a region, and it returns a page of videos with title, channel, duration, thumbnail URLs, and a relevance score. Nothing fancy. But under load it was sitting at a p95 of 280ms, and almost none of that was CPU. It was waiting.

Most of DailyWatch runs on PHP 8.4 behind LiteSpeed, with SQLite (FTS5 for full-text search) as the primary store and Cloudflare in front. That stack is genuinely excellent for cached HTML pages — a category page or a watch page is rendered once, cached at the edge, and served thousands of times for free. But the metadata API is a different animal. It does lots of small concurrent reads, it fans out to validate thumbnail availability, and once in a while it calls an external enrichment service for channel data. Under PHP-FPM, every one of those waits held a worker process hostage. Add enough concurrent feed requests and you run out of workers long before you run out of CPU.

I did not want to rewrite the whole site. I wanted to peel off this one chatty, I/O-bound service and run it on something built for concurrency. I rebuilt it in Python on Litestar (the framework formerly known as Starlite). This is the honest write-up of how that went, with the code I actually shipped.

Why Litestar and not FastAPI or Flask

I evaluated FastAPI first because everyone does. It is a fine framework. But three things pushed me to Litestar for this specific job:

msgspec serialization by default. Litestar uses msgspec for encoding and decoding instead of Pydantic v2's slower-by-default path. For a service whose entire job is shuffling JSON in and out, the serializer is not a detail — it is the hot path. msgspec validates and encodes in C and is measurably faster on wide objects like video records.
A real dependency injection system. FastAPI's Depends is clever but everything is a function call graph. Litestar has layered DI you can attach at the app, router, controller, or handler level, which maps cleanly onto "this connection pool lives for the whole app, this request context lives for one request."
Controllers and layered config. I prefer grouping related routes into a class with shared dependencies and guards over a pile of decorated module functions. Litestar's Controller makes that first-class.

None of this means FastAPI is wrong. It means that for a serialization-bound, I/O-bound microservice, Litestar's defaults lined up with what I needed without me fighting them.

Modeling the payload with msgspec

The first thing I did was define the wire format with msgspec structs instead of dataclasses or Pydantic models. msgspec structs are roughly as cheap to instantiate as a plain tuple, and Litestar knows how to serialize them directly.

from __future__ import annotations

import msgspec
from litestar import Litestar, get
from litestar.controller import Controller
from litestar.params import Parameter


class VideoMeta(msgspec.Struct, frozen=True):
    id: str
    title: str
    channel: str
    duration_seconds: int
    thumbnail: str
    region: str
    score: float


class FeedResponse(msgspec.Struct, frozen=True):
    query: str
    region: str
    count: int
    results: list[VideoMeta]


class FeedController(Controller):
    path = "/feed"

    @get()
    async def search(
        self,
        db: "VideoStore",
        q: str = Parameter(min_length=1, max_length=120),
        region: str = Parameter(default="US", max_length=2),
        limit: int = Parameter(default=24, ge=1, le=100),
    ) -> FeedResponse:
        rows = await db.search(q, region=region, limit=limit)
        return FeedResponse(query=q, region=region, count=len(rows), results=rows)

A few things worth calling out. The Parameter markers give me validation and OpenAPI docs for free — a query shorter than one character or a limit over 100 is rejected before my handler runs. The handler is async, so while one request waits on SQLite, the event loop is free to serve others. And the db argument is not parsed from the request; it is injected. That VideoStore comes from dependency injection, which I will get to.

Because VideoMeta is a frozen msgspec struct, Litestar serializes the whole FeedResponse without ever building intermediate dicts. On a 24-item page that is 24 fewer dict allocations per request, which adds up when you are doing thousands of requests a second.

Async all the way down to SQLite

The interesting constraint is that I wanted to keep SQLite. It is the right database for DailyWatch — a single file, no server to operate, FTS5 built in, and Cloudflare absorbs most of the read traffic anyway. The catch is that SQLite is synchronous and the C library does blocking I/O. If you call it naively from an async handler you block the event loop, and you have thrown away the entire reason you went async.

The pragmatic answer is aiosqlite, which runs each connection in a dedicated thread and gives you an awaitable interface. SQLite releases the GIL during its I/O, so a handful of reader connections genuinely run in parallel. I also turn on WAL mode so readers never block on the writer.

import aiosqlite


class VideoStore:
    def __init__(self, conn: aiosqlite.Connection) -> None:
        self._conn = conn

    @classmethod
    async def connect(cls, path: str) -> "VideoStore":
        conn = await aiosqlite.connect(path)
        await conn.execute("PRAGMA journal_mode=WAL")
        await conn.execute("PRAGMA synchronous=NORMAL")
        await conn.execute("PRAGMA cache_size=-16000")  # ~16 MB page cache
        conn.row_factory = aiosqlite.Row
        return cls(conn)

    async def search(self, q: str, *, region: str, limit: int) -> list[VideoMeta]:
        # bm25() returns a distance: lower is better, so negate for a score.
        sql = """
            SELECT v.id, v.title, v.channel, v.duration_seconds,
                   v.thumbnail, v.region,
                   -bm25(video_fts, 8.0, 2.0) AS score
            FROM video_fts
            JOIN videos v ON v.rowid = video_fts.rowid
            WHERE video_fts MATCH :match
              AND (v.region = :region OR v.region = 'GLOBAL')
            ORDER BY score DESC
            LIMIT :limit
        """
        match = self._fts_query(q)
        async with self._conn.execute(
            sql, {"match": match, "region": region, "limit": limit}
        ) as cur:
            rows = await cur.fetchall()
        return [
            VideoMeta(
                id=r["id"],
                title=r["title"],
                channel=r["channel"],
                duration_seconds=r["duration_seconds"],
                thumbnail=r["thumbnail"],
                region=r["region"],
                score=r["score"],
            )
            for r in rows
        ]

    @staticmethod
    def _fts_query(raw: str) -> str:
        # Turn free text into a safe prefix query: 'cat video' -> 'cat* video*'
        terms = [t for t in raw.replace('"', " ").split() if t]
        return " ".join(f"{t}*" for t in terms) or '""'

The bm25() call is the part I care most about. FTS5 ships with BM25 ranking built in, and the two numbers after the table name are per-column weights — here the title column counts for 8x and the channel for 2x, because a query match in the title is far more meaningful than a match in a channel name. I negate the result because FTS5's bm25() returns a value where lower is more relevant, and I want a score where higher is better.

The _fts_query helper is small but it matters. Passing raw user input straight into a MATCH clause is how you get FTS5 syntax errors (or worse) from a stray quote or operator. Splitting on whitespace, stripping quotes, and appending * for prefix matching gives users the "search as you type" behavior they expect while keeping the query well-formed.

Dependency injection instead of globals

The VideoStore needs to exist once for the lifetime of the app, get its connection opened on startup, and closed on shutdown. Litestar's lifespan hooks plus app-level dependencies handle this without a single module-level global.

from contextlib import asynccontextmanager
from collections.abc import AsyncIterator

from litestar import Litestar
from litestar.di import Provide


@asynccontextmanager
async def lifespan(app: Litestar) -> AsyncIterator[None]:
    store = await VideoStore.connect("/var/data/videos.db")
    app.state.store = store
    try:
        yield
    finally:
        await store._conn.close()


async def provide_store(state) -> VideoStore:
    return state.store


app = Litestar(
    route_handlers=[FeedController],
    lifespan=[lifespan],
    dependencies={"db": Provide(provide_store)},
)

Now every handler that declares a db: VideoStore parameter gets the shared store, and nothing reaches into a global. In tests I swap provide_store for one that returns an in-memory store seeded with fixtures, and the handler code does not change at all. That testability is the quiet payoff of DI — it is not about ceremony, it is about being able to substitute the slow real thing for a fast fake one at the edges.

For heavier setups you would open several reader connections and round-robin them, since each aiosqlite.Connection is single-threaded. For DailyWatch's feed traffic, a small pool of three to five readers plus one dedicated writer connection is plenty, and WAL mode means the writer never blocks them.

Keeping the PHP stack honest

I did not throw away PHP. The site's pages still render in PHP 8.4 under LiteSpeed, and they call this Litestar service over the local network for the dynamic feed slot. The PHP side stays simple — it just asks for JSON and trusts the schema, which msgspec guarantees.

<?php
declare(strict_types=1);

function fetch_feed(string $query, string $region = 'US', int $limit = 24): array
{
    $url = 'http://127.0.0.1:8001/feed?' . http_build_query([
        'q'      => $query,
        'region' => $region,
        'limit'  => $limit,
    ]);

    $ch = curl_init($url);
    curl_setopt_array($ch, [
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_TIMEOUT_MS     => 400,
        CURLOPT_FAILONERROR    => true,
    ]);

    $raw = curl_exec($ch);
    if ($raw === false) {
        curl_close($ch);
        return [];           // degrade gracefully: render cached fallback
    }
    curl_close($ch);

    $data = json_decode($raw, true, flags: JSON_THROW_ON_ERROR);
    return $data['results'] ?? [];
}

The important design choice here is the 400ms timeout and the empty-array fallback. The feed is an enhancement, not a hard dependency. If the Litestar service is restarting or slow, PHP renders the cached "trending" block instead of throwing a 500. A free video discovery site lives and dies on never showing the user a broken page, so every cross-service call has a fallback that is good enough to ship.

This boundary also keeps the two languages playing to their strengths. PHP under LiteSpeed is unbeatable at serving cached, templated HTML. Python under an async server is better at the chatty, concurrent, I/O-bound work. Drawing the line at "HTML is PHP, dynamic JSON fan-out is Litestar" meant I migrated one service instead of rewriting a site.

Caching at the edge with Cloudflare

The feed responses are not unique per user — they are unique per (query, region). That makes them cacheable, and Cloudflare is already in front of everything. I set explicit cache headers from Litestar so the edge does the heavy lifting and the Python service mostly handles cache misses and warmups.

from litestar import get
from litestar.response import Response
from litestar.enums import MediaType


@get("/feed", cache=False)
async def search(self, db: VideoStore, q: str, region: str = "US") -> Response:
    rows = await db.search(q, region=region, limit=24)
    body = FeedResponse(query=q, region=region, count=len(rows), results=rows)
    return Response(
        content=body,
        media_type=MediaType.JSON,
        headers={
            # Edge caches for 5 min, serves stale for an hour while revalidating.
            "Cache-Control": "public, max-age=300, stale-while-revalidate=3600",
            "Vary": "Accept-Encoding",
        },
    )

The stale-while-revalidate directive is the trick that keeps latency flat. When a cached feed expires, Cloudflare serves the slightly stale copy immediately and refreshes it in the background, so a user never waits on a cache miss. For a discovery feed where "the trending list from four minutes ago" is completely acceptable, this turns the vast majority of requests into edge hits that never touch Python at all. The origin only sees genuinely cold queries.

One caveat I learned the hard way: be careful what you put in Vary. Varying on a cookie or a fine-grained header fragments the cache into uselessness. Vary on encoding, key your cache on the query and region in the URL, and keep the cacheable surface boring on purpose.

What the numbers looked like

I will not pretend this was a perfectly controlled benchmark — it was a production cutover with real traffic. But the before-and-after on the same hardware was clear:

p95 latency at the origin dropped from ~280ms to ~45ms for cache misses, mostly because requests no longer queued behind blocked workers.
Concurrency before saturation went up sharply. The PHP-FPM version started shedding requests around a few hundred concurrent feed calls because workers were stuck in I/O wait. The async version held steady well past that on the same box, since waiting requests cost an event-loop slot, not a whole process.
Edge hit ratio settled around 90%+ once stale-while-revalidate was tuned, which means the Python service is mostly idle and only earns its keep on the long tail of unique queries.

The serialization win from msgspec is real but it is not where the headline number comes from. The headline number comes from not blocking a worker on I/O. The msgspec speedup shows up as lower CPU per request, which matters for the bill, not the latency.

What I would do differently

A few honest regrets and notes:

I should have added a writer queue sooner. With WAL and a single writer connection, concurrent writes serialize fine, but I initially shared the writer connection across coroutines and hit "database is locked" under bursty ingestion. The fix was a dedicated writer with its own task and an async queue feeding it.
Prefix-only FTS5 matching is greedy. Appending * to every term is great for short queries and bad for long ones, where it over-matches. I now cap prefix expansion to the last token, which is what users are usually still typing.
Connection-per-thread is a real ceiling. aiosqlite is the right tool at this scale, but if read volume ever outgrows a small pool, the move is to a read-replica file or to switch the hot table to a server-based store. SQLite earned its place; I just want to know where the wall is before I hit it.

Conclusion

The lesson was not "rewrite everything in async Python." It was "find the one service that is I/O-bound and chatty, and move only that." PHP 8.4 under LiteSpeed still renders every page on the site, SQLite with FTS5 is still the store, and Cloudflare still absorbs most of the traffic. Litestar slotted in as a focused metadata service that does the concurrent, waiting-heavy work the blocking stack was bad at. msgspec kept the serialization cheap, async kept the workers free, and dependency injection kept the whole thing testable. If you have a corner of your stack that spends its life waiting on I/O, peeling it off onto Litestar is a small, low-risk bet that paid off cleanly for us.

Building a Chaos Testing Harness for Multi-Region Video API Endpoints

ahmet gedik — Wed, 10 Jun 2026 16:00:02 +0000

A truncated 200 took down discovery in three regions

At TrendVidStream we aggregate streaming-platform metadata across eight regions, and most of our reliability work used to assume failures announce themselves. A provider goes down, you get a 500 or a connection refused, your retry logic kicks in, life goes on. Then one night an upstream returned HTTP 200 OK with a body truncated at exactly 8 KB. Our ingest cron parsed the partial JSON without complaint, wrote half-populated rows into SQLite, rebuilt the FTS5 index, and the discovery endpoint started serving near-empty result sets to US, GB, and DE viewers for forty minutes before a user emailed us.

Nothing crashed. No alert fired. Every health check was green. The failure was a lie, and our test suite had no concept of a lying dependency. That incident is why we built a chaos testing harness specifically for the read paths that feed our video discovery API — not a generic Chaos Monkey clone, but a small, scriptable rig that injects the dishonest failures that actually happen in production: slow trickles, truncated bodies, wrong content lengths, garbage that still parses, and clock skew between regions.

This post walks through the harness we run today: a fault-injecting proxy in Python, a defensive PHP 8.4 client that survives it, and a Go driver that measures blast radius. It is deliberately low-tech — it has to deploy over the same FTP automation as everything else we ship.

What chaos testing means for a read-heavy video API

Most chaos engineering literature is written for microservice fleets with service meshes and sidecars. We have none of that. Our stack is PHP 8.4 behind LiteSpeed, SQLite with FTS5 for search, region-aware cron jobs that pull upstream metadata, and FTP-based deploys. Injecting a sidecar is not on the table.

So the goal is narrower and more useful: prove that a single bad upstream response cannot corrupt the cache or degrade more than one region. The properties we care about are:

Containment — a fault in the DE ingest path must not poison rows served to JP.
Honesty — if we cannot get a complete, valid response, we serve stale-but-correct data, never partial-fresh data.
Bounded latency — a slow upstream must trip a deadline well under the LiteSpeed 180s timeout, not ride it to the edge.
Idempotent recovery — after the fault clears, the next cron run fully heals the affected region with no manual step.

Everything in the harness exists to falsify one of those four claims.

The fault taxonomy we inject

Real upstreams fail in more interesting ways than timeout and 500. After a year of incident reviews, our taxonomy settled into seven fault types, each with a probability and a target region:

slowloris — send the body one byte every few hundred ms, never closing.
truncate — send a valid 200 with Content-Length for the full body but only flush the first N bytes.
lie_length — send a body shorter or longer than the advertised Content-Length.
valid_garbage — return well-formed JSON with the right shape but nonsense values (empty arrays, null IDs).
flap — alternate 200 and 503 on each request to defeat naive retry-once logic.
clock_skew — stamp Date and Last-Modified headers hours into the future.
dribble_then_reset — send headers, dribble half the body, then RST the connection.

The key design decision: faults are targeted by region, because containment is the property we most need to verify. We inject only into the DE path and assert that JP, US, and the other six stay clean.

A reverse proxy that lies

The heart of the harness is an aiohttp proxy that sits between our ingest cron and the real upstream. In normal mode it forwards transparently; with a fault profile loaded it corrupts responses according to the taxonomy. It runs locally on a staging box, never near production.

# chaos_proxy.py  —  run: python chaos_proxy.py --listen 8099 --upstream https://api.example.com
import argparse, asyncio, json, random
from aiohttp import web, ClientSession, ClientTimeout

FAULTS = {}  # loaded from --profile, keyed by region

def pick_fault(region: str):
    spec = FAULTS.get(region)
    if not spec:
        return None
    if random.random() > spec["probability"]:
        return None
    return random.choice(spec["types"])

async def handle(request: web.Request) -> web.StreamResponse:
    region = request.headers.get("X-Region", "US")
    fault = pick_fault(region)
    upstream = request.app["upstream"] + request.rel_url.path_qs

    timeout = ClientTimeout(total=20)
    async with ClientSession(timeout=timeout) as session:
        async with session.get(upstream, headers={"Accept": "application/json"}) as up:
            body = await up.read()

    if fault == "valid_garbage":
        body = json.dumps({"items": [], "region": region, "injected": True}).encode()
    elif fault == "clock_skew":
        resp = web.Response(body=body, status=200)
        resp.headers["Date"] = "Wed, 01 Jan 2031 00:00:00 GMT"
        return resp
    elif fault == "flap":
        request.app["flap"] = not request.app.get("flap", False)
        if request.app["flap"]:
            return web.Response(status=503, text="flap")

    # streaming faults need a manually driven response
    if fault in ("slowloris", "truncate", "lie_length", "dribble_then_reset"):
        resp = web.StreamResponse(status=200)
        if fault in ("truncate", "lie_length"):
            resp.headers["Content-Length"] = str(len(body))  # promise the full size
        await resp.prepare(request)
        if fault == "slowloris":
            for b in body:
                await resp.write(bytes([b]))
                await asyncio.sleep(0.3)
        elif fault == "truncate":
            await resp.write(body[: len(body) // 2])  # send half, then stop
        elif fault == "lie_length":
            await resp.write(body[:-32])  # 32 bytes short of the promise
        elif fault == "dribble_then_reset":
            await resp.write(body[: len(body) // 2])
            request.transport.abort()  # RST mid-stream
        await resp.write_eof()
        return resp

    return web.Response(body=body, status=200, content_type="application/json")

def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("--listen", type=int, default=8099)
    ap.add_argument("--upstream", required=True)
    ap.add_argument("--profile", help="JSON file of region->fault spec")
    args = ap.parse_args()
    if args.profile:
        FAULTS.update(json.load(open(args.profile)))
    app = web.Application()
    app["upstream"] = args.upstream.rstrip("/")
    app.router.add_route("*", "/{tail:.*}", handle)
    web.run_app(app, port=args.listen)

if __name__ == "__main__":
    main()

A profile looks like this — we only ever target one region at a time so we can assert containment on the rest:

{
  "DE": { "probability": 0.7, "types": ["truncate", "lie_length", "slowloris", "valid_garbage"] }
}

The lie_length case is the one that bit us originally: a Content-Length header that promises more bytes than arrive. A naive HTTP client either hangs waiting for the rest or, worse, hands you the short body as if it were complete.

Making the PHP endpoint survive the proxy

The defense lives in the ingest client. The old code did json_decode(file_get_contents($url)) and trusted whatever came back. The hardened version enforces a deadline, verifies the byte count against the advertised length, validates the decoded shape, and — critically — refuses to overwrite good cache rows with anything it cannot fully trust. It uses a tiny circuit breaker stored in SQLite so a flapping region trips fast and recovers on its own.

<?php
declare(strict_types=1);

// IngestClient.php  —  PHP 8.4
final class IngestClient
{
    private const DEADLINE_MS = 8000;       // well under LiteSpeed's 180s
    private const MIN_ITEMS   = 5;          // a real region never returns fewer
    private const TRIP_AFTER  = 3;          // consecutive failures before open

    public function __construct(private readonly PDO $db) {}

    public function fetchRegion(string $region, string $url): array
    {
        if ($this->breakerOpen($region)) {
            return ['ok' => false, 'reason' => 'breaker_open', 'region' => $region];
        }

        $ch = curl_init($url);
        curl_setopt_array($ch, [
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_TIMEOUT_MS     => self::DEADLINE_MS,
            CURLOPT_HTTPHEADER     => ["X-Region: {$region}", 'Accept: application/json'],
            CURLOPT_FAILONERROR    => false,
        ]);
        $body   = curl_exec($ch);
        $status = curl_getinfo($ch, CURLINFO_RESPONSE_CODE);
        $dlSize = (int) curl_getinfo($ch, CURLINFO_SIZE_DOWNLOAD);
        $claim  = (int) curl_getinfo($ch, CURLINFO_CONTENT_LENGTH_DOWNLOAD);
        $errno  = curl_errno($ch);
        curl_close($ch);

        $fail = fn(string $why) => $this->trip($region, $why);

        if ($errno !== 0 || $status !== 200 || $body === false) {
            return $fail("transport:{$errno}:{$status}");
        }
        // catch lie_length / truncate: advertised length must match what we read
        if ($claim > 0 && $claim !== $dlSize) {
            return $fail("length_mismatch:{$claim}!={$dlSize}");
        }
        $data = json_decode($body, true);
        if (!is_array($data) || !isset($data['items']) || !is_array($data['items'])) {
            return $fail('shape_invalid');
        }
        // catch valid_garbage: structurally fine but empty
        if (count($data['items']) < self::MIN_ITEMS) {
            return $fail('too_few_items');
        }

        $this->reset($region);
        return ['ok' => true, 'items' => $data['items'], 'region' => $region];
    }

    private function breakerOpen(string $region): bool
    {
        $st = $this->db->prepare(
            'SELECT fails, opened_until FROM breaker WHERE region = ?'
        );
        $st->execute([$region]);
        $row = $st->fetch(PDO::FETCH_ASSOC);
        return $row && (int) $row['opened_until'] > time();
    }

    private function trip(string $region, string $why): array
    {
        $this->db->prepare(
            'INSERT INTO breaker (region, fails, opened_until) VALUES (?, 1, 0)
             ON CONFLICT(region) DO UPDATE SET fails = fails + 1,
               opened_until = CASE WHEN fails + 1 >= ' . self::TRIP_AFTER .
            ' THEN ? ELSE opened_until END'
        )->execute([$region, time() + 300]);
        error_log("chaos-defense region={$region} tripped why={$why}");
        return ['ok' => false, 'reason' => $why, 'region' => $region];
    }

    private function reset(string $region): void
    {
        $this->db->prepare(
            'INSERT INTO breaker (region, fails, opened_until) VALUES (?, 0, 0)
             ON CONFLICT(region) DO UPDATE SET fails = 0, opened_until = 0'
        )->execute([$region]);
    }
}

The rule that makes containment work is what isn't here: there is no code path that writes $data['items'] to the cache unless ok is true. A failed region returns early, the existing rows stay untouched, and the discovery API keeps serving the last known-good snapshot. Stale beats wrong on a content discovery site — a video from six hours ago is fine; an empty grid is not.

The length check ($claim !== $dlSize) is the single line that would have prevented our original incident. curl exposes both the promised and actual download sizes, and comparing them is free.

Driving load and measuring blast radius

A fault that nobody hits proves nothing. The driver is a small Go program that hammers all eight region endpoints concurrently while a fault profile targets one of them, then reports per-region success rate and latency. We use Go here purely because goroutines make honest concurrent load trivial without a runtime to babysit.

// driver.go  —  go run driver.go -base http://staging.local -faulty DE -n 400
package main

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

var regions = []string{"US", "GB", "DE", "FR", "JP", "KR", "BR", "AU"}

type stat struct {
    ok, fail int
    lat      []time.Duration
}

func main() {
    base := flag.String("base", "http://localhost:8080", "discovery API base")
    n := flag.Int("n", 200, "requests per region")
    faulty := flag.String("faulty", "DE", "region under fault injection")
    flag.Parse()

    client := &http.Client{Timeout: 15 * time.Second}
    results := make(map[string]*stat, len(regions))
    var mu sync.Mutex
    var wg sync.WaitGroup

    for _, r := range regions {
        results[r] = &stat{}
        for i := 0; i < *n; i++ {
            wg.Add(1)
            go func(region string) {
                defer wg.Done()
                start := time.Now()
                req, _ := http.NewRequest("GET", *base+"/discover?region="+region, nil)
                resp, err := client.Do(req)
                d := time.Since(start)
                mu.Lock()
                defer mu.Unlock()
                s := results[region]
                if err != nil || resp.StatusCode != 200 {
                    s.fail++
                    return
                }
                resp.Body.Close()
                s.ok++
                s.lat = append(s.lat, d)
            }(r)
        }
    }
    wg.Wait()

    fmt.Printf("fault target: %s\n%-6s %-8s %-8s %-10s\n", *faulty, "region", "ok", "fail", "p95")
    for _, r := range regions {
        s := results[r]
        fmt.Printf("%-6s %-8d %-8d %-10s\n", r, s.ok, s.fail, p95(s.lat))
    }
}

func p95(d []time.Duration) time.Duration {
    if len(d) == 0 {
        return 0
    }
    sort.Slice(d, func(i, j int) bool { return d[i] < d[j] })
    return d[int(float64(len(d))*0.95)]
}

The output table is the whole point. A passing run looks like this: the faulty region shows degraded success (it is serving stale data, sometimes from an open breaker) but its p95 stays bounded, while every other region sits at 100% with normal latency. The moment another region's numbers move, containment is broken and we have a bug to fix before that code ships over FTP.

Wiring it into multi-region cron

The harness is not a one-off. Our region cron already loops over the region list to pull metadata, so the chaos run reuses the same loop on staging. A nightly job starts the proxy with a rotating single-region profile, runs the driver, and fails the build if any non-target region drops below 99.5% success or if any cache row for a clean region changes during the fault window.

#!/usr/bin/env bash
set -euo pipefail
REGIONS=(US GB DE FR JP KR BR AU)
TARGET=${REGIONS[$(( RANDOM % ${#REGIONS[@]} ))]}

# snapshot clean-region cache row counts before the storm
sqlite3 staging.db "SELECT region, count(*) FROM videos GROUP BY region" > /tmp/before.txt

jq -n --arg r "$TARGET" '{($r): {probability: 0.8,
  types: ["truncate","lie_length","slowloris","valid_garbage","flap"]}}' > /tmp/profile.json

python chaos_proxy.py --listen 8099 --upstream "$UPSTREAM" --profile /tmp/profile.json &
PROXY=$!
trap 'kill $PROXY' EXIT
sleep 1

go run driver.go -base http://staging.local -faulty "$TARGET" -n 400 | tee /tmp/run.txt

# assert containment: no clean region's row count moved
sqlite3 staging.db "SELECT region, count(*) FROM videos GROUP BY region" > /tmp/after.txt
if ! diff <(grep -v "^$TARGET|" /tmp/before.txt) <(grep -v "^$TARGET|" /tmp/after.txt); then
  echo "CONTAINMENT FAILURE: a clean region's cache changed during fault on $TARGET" >&2
  exit 1
fi
echo "chaos run clean — fault target $TARGET contained"

Randomizing the target each night means that over a couple of weeks every region gets exercised, and a containment regression in any one of them surfaces without us having to remember to test it.

What we found

Running this against our own code was humbling. The truncation defense worked on the first try, but three other things did not:

The flap fault broke our retry logic. We retried once on 503, and the alternating pattern meant the retry always hit the 200, masking that the upstream was unhealthy half the time. We now require two consecutive successes before clearing the breaker.
Clock skew poisoned conditional requests. A Last-Modified stamped in 2031 made our If-Modified-Since logic skip refreshes for that region indefinitely. We now clamp upstream timestamps to min(upstream, now).
The 8s deadline was too generous under slowloris. A trickle that delivered a valid body at byte 7,900 of an 8,000-byte deadline passed validation but starved a cron worker. We added a minimum-throughput check.

None of these would have shown up in a test that only mocked clean responses or hard failures. They lived in the gap between "working" and "broken" — exactly where lying dependencies operate.

Conclusion

Chaos testing for a read-heavy video API is not about randomly killing servers. It is about reproducing the dishonest failures real upstreams produce — truncated bodies, mismatched lengths, structurally-valid garbage, and skewed clocks — and proving that not one of them can escape a single region or corrupt your cache. The harness that does this is small: a lying proxy, a paranoid client that treats every response as guilty until verified, a concurrent driver that measures blast radius, and a cron wrapper that asserts containment and fails the build when it breaks.

The whole thing fits in a few hundred lines and deploys over the same boring FTP pipeline as the rest of our code, which is the only reason it actually runs every night instead of rotting in a branch. If your video API trusts its upstreams to fail honestly, build the proxy that lies to it first — you will find the bugs that wake you at 2am while you are still awake to fix them.

Streaming Live Video View Counts With Server-Sent Events in PHP 8.4

ahmet gedik — Wed, 10 Jun 2026 07:00:01 +0000

Last month a video about a Norwegian fjord drone crash went from 4,000 to 1.1 million views in nine hours on our discovery feed. The problem wasn't ingesting that traffic — it was the front end. Every visitor on the trending page had a setInterval firing a fetch('/api/views?id=...') every five seconds, and with a few thousand concurrent viewers that turned into a steady 600+ requests per second of nothing but "give me the current number." Each one opened a SQLite read, serialized a tiny JSON payload, and burned a LiteSpeed worker slot. The counts were stale by up to five seconds anyway, and the polling cost more CPU than the actual video metadata API.

Polling is the wrong tool for a value that changes continuously and that the server already knows about the instant it changes. The right tool — for a one-directional, server-to-client stream of small updates — is Server-Sent Events (SSE). At ViralVidVault we run a European viral-video discovery feed, and live view counts are the single most-watched number on the site. This post is how we replaced polling with SSE on a PHP 8.4 / SQLite (WAL) / LiteSpeed / Cloudflare stack, the buffering traps that cost me an afternoon, and how we keep it GDPR-clean.

Why SSE and not WebSockets

The knee-jerk answer to "live updates" is WebSockets, but WebSockets are a bidirectional, stateful protocol. View counts only ever flow one way: server to browser. The client never has anything to say back except "I'm still here," which TCP already handles. SSE gives us exactly that one-way channel over plain HTTP, and it brings three properties that matter on our stack:

It's just HTTP. No protocol upgrade, no separate port, no special LiteSpeed module. It rides through Cloudflare and our existing TLS termination unchanged.
Automatic reconnect is built in. The browser's EventSource reconnects on drop and replays the Last-Event-ID header, so we get resumable streams for free.
It degrades to text. An SSE stream is a text/event-stream body you can curl and read with your eyes. Debugging a WebSocket frame stream is a much worse afternoon.

The one real limitation — the ~6 connections-per-domain cap in HTTP/1.1 — disappears under HTTP/2, which both LiteSpeed and Cloudflare give us by default. Over h2 a browser multiplexes dozens of SSE streams down one connection.

The data side: counting views without melting SQLite

Before streaming anything we need a count that's cheap to read and safe to write under load. We use SQLite in WAL (write-ahead logging) mode, which lets readers and a single writer proceed concurrently — exactly the shape of a view counter: many reads, frequent small writes from one ingest process.

The naive approach — UPDATE videos SET views = views + 1 on every hit — serializes every viewer behind a row lock and thrashes the WAL. Instead we buffer increments in memory and flush them on an interval. Here is the writer side, a long-running PHP CLI process that drains an in-memory delta map into SQLite:

<?php
declare(strict_types=1);

final class ViewCounter
{
    private \PDO $db;
    /** @var array<int,int> videoId => pending delta */
    private array $pending = [];

    public function __construct(string $dbPath)
    {
        $this->db = new \PDO('sqlite:' . $dbPath, null, null, [
            \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
        ]);
        // WAL: concurrent readers + one writer, durable enough for counters.
        $this->db->exec('PRAGMA journal_mode = WAL');
        $this->db->exec('PRAGMA synchronous = NORMAL');
        $this->db->exec('PRAGMA busy_timeout = 5000');
    }

    public function record(int $videoId): void
    {
        $this->pending[$videoId] = ($this->pending[$videoId] ?? 0) + 1;
    }

    /** Flush buffered deltas in one transaction. Call every ~1s. */
    public function flush(): void
    {
        if ($this->pending === []) {
            return;
        }
        $stmt = $this->db->prepare(
            'UPDATE videos SET views = views + :d, updated_at = unixepoch() WHERE id = :id'
        );
        $this->db->beginTransaction();
        try {
            foreach ($this->pending as $id => $delta) {
                $stmt->execute([':d' => $delta, ':id' => $id]);
            }
            $this->db->commit();
            $this->pending = [];
        } catch (\Throwable $e) {
            $this->db->rollBack();
            error_log('view flush failed: ' . $e->getMessage());
        }
    }
}

Batching turns thousands of per-request writes into one transaction per second. With synchronous = NORMAL under WAL, that single commit is cheap and still crash-safe up to the last checkpoint — acceptable for a vanity counter where losing a sub-second of increments on a hard crash is a non-event.

A note on GDPR, because this is Europe and it matters: a raw increment carries no personal data. We do not log IP, user-agent, or any identifier against the view. The counter is a pure integer aggregate. Our actual analytics dedupe is done with a rotating, salted, daily-bucketed hash that we never persist past the aggregation window — but the count that feeds the SSE stream is just videos.views, an anonymous total. No consent banner is required to display a public number going up.

The SSE endpoint in PHP 8.4

Now the read side. The endpoint holds the connection open and pushes a new value each time the count changes. The critical detail on LiteSpeed (and behind Cloudflare) is disabling every layer of output buffering, or your events get held in a buffer and the client sees nothing until the buffer fills or the connection closes — which defeats the entire point.

<?php
declare(strict_types=1);

// --- Disable buffering everywhere it can hide ---
@ini_set('zlib.output_compression', '0');   // no gzip on a stream
@ini_set('output_buffering', '0');
while (ob_get_level() > 0) {
    ob_end_flush();
}
ignore_user_abort(false); // let us notice when the client leaves
set_time_limit(0);

header('Content-Type: text/event-stream');
header('Cache-Control: no-cache, no-transform');
header('Connection: keep-alive');
// LiteSpeed / nginx: do NOT buffer this response.
header('X-Accel-Buffering: no');

$videoId = (int)($_GET['id'] ?? 0);
if ($videoId <= 0) {
    http_response_code(400);
    exit;
}

$db = new PDO('sqlite:/var/www/data/vault.db', null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$db->exec('PRAGMA query_only = 1'); // this connection only reads
$stmt = $db->prepare('SELECT views FROM videos WHERE id = :id');

// Resume support: client sends Last-Event-ID on reconnect.
$lastSent = isset($_SERVER['HTTP_LAST_EVENT_ID'])
    ? (int)$_SERVER['HTTP_LAST_EVENT_ID']
    : -1;

$idleTicks = 0;
while (!connection_aborted()) {
    $stmt->execute([':id' => $videoId]);
    $views = (int)$stmt->fetchColumn();

    if ($views !== $lastSent) {
        // id: lets the browser resume; event name groups handlers client-side
        echo "id: {$views}\n";
        echo "event: views\n";
        echo 'data: ' . json_encode(['v' => $views], JSON_THROW_ON_ERROR) . "\n\n";
        $lastSent = $views;
        $idleTicks = 0;
    } else {
        // Heartbeat comment every ~15s keeps Cloudflare / proxies from
        // dropping an idle connection. Comments start with ':'
        if (++$idleTicks >= 15) {
            echo ": keepalive\n\n";
            $idleTicks = 0;
        }
    }

    flush();
    sleep(1);
}

A few things are doing real work here:

The id: field carries the last value. On reconnect the browser sends it back as Last-Event-ID, so we can skip re-emitting a number the client already has. We reuse the count itself as the event id since it's monotonic.
event: views names the event so the client can bind a specific handler and ignore heartbeats.
The : keepalive comment is an SSE comment line (anything after a leading colon is ignored by the parser). It exists purely to push bytes through idle proxies. Cloudflare will eventually close a connection with no traffic; a 15-second heartbeat keeps it warm without sending a fake count.
connection_aborted() + sleep(1) is a deliberately boring poll-the-DB-once-a-second loop. SQLite reads under WAL are sub-millisecond on an indexed primary key, so one read per connection per second is far cheaper than the old model of every client polling over HTTP. We moved the polling from the network edge to a local file read.

That last point is the whole trick: SSE doesn't make the data magically push itself. The server still has to learn the new value somehow. But doing it as a local SQLite read inside one held-open process is orders of magnitude cheaper than thousands of clients each making a full HTTP round-trip through Cloudflare and LiteSpeed.

The browser client

The front-end code is almost embarrassingly small, which is the point. EventSource handles connection, parsing, and reconnect-with-backoff for us.

const counter = document.querySelector('#view-count');
const videoId = counter.dataset.videoId;
const es = new EventSource(`/sse/views?id=${videoId}`);

es.addEventListener('views', (e) => {
  const { v } = JSON.parse(e.data);
  counter.textContent = new Intl.NumberFormat('en-GB').format(v);
});

// EventSource auto-reconnects on error; we only log.
es.addEventListener('error', () => {
  // readyState 0 = connecting (browser is already retrying)
  if (es.readyState === EventSource.CLOSED) {
    console.warn('view stream closed by server');
  }
});

// Stop streaming when the tab is hidden — saves a connection and respects
// the user's battery/data. Resume when they come back.
document.addEventListener('visibilitychange', () => {
  if (document.hidden) {
    es.close();
  }
});

We close the stream on visibilitychange to hidden. A user who tabbed away doesn't need a live counter, and dropping the connection frees a worker slot. (To resume cleanly you'd re-instantiate EventSource on visible — left out for brevity, but it's a few lines.) This one habit cut our average concurrent stream count by roughly 40% in production, because a large share of "viewers" are background tabs.

Fanning out at scale with a Go broker

The per-connection PHP loop is fine up to a point, but PHP holding thousands of long-lived connections means thousands of LiteSpeed workers parked in sleep(1). Past a few hundred concurrent streams per video you want a single process reading the DB and fanning the result out to every subscriber. Go's goroutines and channels are built for exactly this, so for our hottest videos a small Go broker sits behind the SSE path:

package main

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

type Broker struct {
    mu      sync.RWMutex
    clients map[chan int]struct{}
    latest  int
}

func NewBroker() *Broker {
    return &Broker{clients: make(map[chan int]struct{})}
}

// Publish is called by the single DB-poller goroutine.
func (b *Broker) Publish(v int) {
    b.mu.Lock()
    b.latest = v
    for ch := range b.clients {
        select {
        case ch <- v:
        default: // slow client: drop this tick, never block the broker
        }
    }
    b.mu.Unlock()
}

func (b *Broker) ServeHTTP(w http.ResponseWriter, r *http.Request) {
    fl, ok := w.(http.Flusher)
    if !ok {
        http.Error(w, "streaming unsupported", http.StatusInternalServerError)
        return
    }
    w.Header().Set("Content-Type", "text/event-stream")
    w.Header().Set("Cache-Control", "no-cache")
    w.Header().Set("X-Accel-Buffering", "no")

    ch := make(chan int, 4)
    b.mu.Lock()
    last := b.latest
    b.clients[ch] = struct{}{}
    b.mu.Unlock()

    defer func() {
        b.mu.Lock()
        delete(b.clients, ch)
        b.mu.Unlock()
    }()

    // Send current value immediately so a new tab isn't blank.
    fmt.Fprintf(w, "event: views\ndata: {\"v\":%d}\n\n", last)
    fl.Flush()

    heartbeat := time.NewTicker(15 * time.Second)
    defer heartbeat.Stop()

    for {
        select {
        case <-r.Context().Done():
            return // client disconnected
        case v := <-ch:
            fmt.Fprintf(w, "id: %d\nevent: views\ndata: {\"v\":%d}\n\n", v, v)
            fl.Flush()
        case <-heartbeat.C:
            fmt.Fprint(w, ": keepalive\n\n")
            fl.Flush()
        }
    }
}

Now one goroutine reads SQLite once a second and calls Publish; every connected client gets the update without anyone touching the database again. The select { case ch <- v: default: } pattern is the important defensive bit — a slow or stalled client must never block the broadcast, so we drop a tick for that client rather than back-pressuring everyone. A single modest Go process handles tens of thousands of these streams where the PHP-per-connection model would have exhausted worker pools long before.

We route only the genuinely viral videos through the Go broker (a Cloudflare Worker checks a "is hot" flag and rewrites the path); the long tail of cold videos stays on the simple PHP endpoint, which is more than adequate when a video has three concurrent viewers.

Load-testing the stream

Numbers you didn't measure are wishes. SSE load behaves differently from request/response load: the metric isn't requests-per-second, it's concurrent held connections and whether updates actually arrive on time. A quick Python harness with aiohttp opens N streams and records inter-event latency:

import asyncio, time, aiohttp

URL = "https://viralvidvault.com/sse/views?id=12345"
CONCURRENCY = 2000

async def one(session, stats):
    try:
        async with session.get(URL) as resp:
            last = time.monotonic()
            async for raw in resp.content:
                line = raw.decode("utf-8", "replace").strip()
                if line.startswith("data:"):
                    now = time.monotonic()
                    stats.append(now - last)
                    last = now
    except Exception as e:
        print("stream error:", e)

async def main():
    stats: list[float] = []
    timeout = aiohttp.ClientTimeout(total=None)  # streams never "finish"
    conn = aiohttp.TCPConnector(limit=CONCURRENCY)
    async with aiohttp.ClientSession(timeout=timeout, connector=conn) as s:
        tasks = [asyncio.create_task(one(s, stats)) for _ in range(CONCURRENCY)]
        await asyncio.sleep(60)
        for t in tasks:
            t.cancel()
    if stats:
        stats.sort()
        p50 = stats[len(stats)//2]
        p99 = stats[int(len(stats)*0.99)]
        print(f"events: {len(stats)}  p50={p50:.2f}s  p99={p99:.2f}s")

asyncio.run(main())

The key assertions are: do all 2,000 connections stay open for the full minute, and is p99 inter-event latency close to your tick interval (~1s) rather than spiking to your heartbeat interval (15s, which would mean updates aren't actually flowing). The first time I ran this against LiteSpeed I saw a clean 1.0s p50 locally but a 15s p99 through Cloudflare — the smoking gun that something between the origin and the client was buffering. The fix was the Cache-Control: no-transform header plus confirming zlib.output_compression was off; Cloudflare was trying to apply compression to the stream and holding bytes to do it.

Buffering traps, ranked by how long they cost me

If SSE "doesn't work," it's almost always buffering. In rough order of how much time each one ate:

PHP/zlib output compression. Gzip needs a buffer to compress; a stream never fills it. Set zlib.output_compression = 0 and send Cache-Control: no-transform so the edge doesn't re-add it.
LiteSpeed / nginx proxy buffering. Send X-Accel-Buffering: no. Without it the proxy collects your output and forwards in chunks.
Leftover output buffers. A framework or auto_prepend_file may have started ob_start(). Drain them all with the ob_end_flush() loop before the first event.
Cloudflare idle timeout. No bytes for ~100s and the connection drops. The 15-second heartbeat comment prevents it and is invisible to the EventSource parser.
HTTP/1.1 connection cap. Six streams per domain max. Serve over HTTP/2 (default on our stack) and it's a non-issue.

Conclusion

Server-Sent Events let us delete a whole category of wasteful traffic. The trending page went from ~600 polling requests/second to a set of held-open connections that each cost one local SQLite read per second, the counts update in near real time instead of on a 5-second sawtooth, and the client code shrank to a dozen lines because EventSource does the reconnect work. The architecture scales in tiers: a dead-simple PHP loop for the cold long tail, a Go broker for the handful of genuinely viral videos, and Cloudflare deciding which is which at the edge.

The two lessons worth carrying off this stack: SSE doesn't push data for free — you still need a cheap way to learn the new value, and batched SQLite-under-WAL is that cheap way; and when SSE looks broken, it's buffering, every time, so kill it at PHP, the proxy, and the CDN before you suspect anything else. And because a public view count is an anonymous aggregate, none of this added a line to our consent flow — which, when you operate in Europe, is exactly the kind of feature you want.

Scaling Video Viewership Analytics With TimescaleDB Hypertables

ahmet gedik — Tue, 09 Jun 2026 21:00:01 +0000

When SQLite Stopped Being Enough for View Events

TopVideoHub aggregates trending videos across the Asia-Pacific region, and most of the stack is deliberately boring: PHP 8.4, a SQLite catalog with an FTS5 index wired up to a CJK tokenizer for Japanese/Korean/Chinese search, LiteSpeed in front, and Cloudflare on the edge. That setup is wonderful for the catalog. It is a disaster for viewership analytics.

The problem showed up the week we crossed roughly 12 million view events per day. Every play, pause, seek, completion, and region/language tag was landing in one SQLite view_events table. SQLite serializes writers, so the ingest worker held the database long enough that the read replicas serving the trending dashboards started timing out. A simple "plays per region, bucketed by hour, last 7 days" query went from snappy to 40+ seconds, and the database file pushed past 60 GB. We tried partial indexes, WAL mode, and manual monthly tables. It bought us a month.

The honest conclusion: append-heavy time-series data is a different workload than a catalog, and it deserves a different engine. We moved the analytics half of TopVideoHub onto PostgreSQL with the TimescaleDB extension and its hypertables. The catalog stayed on SQLite. This article is the concrete migration — schema, ingest from PHP, continuous aggregates, compression, and the Go and Python jobs that read it.

Why Time-Series Is a Different Beast

Before reaching for any tool it helps to name why view events break a normal table:

Append-only, time-ordered. New rows are almost always now. You rarely update a row from last Tuesday.
Queries are range scans over time. "Last 24h", "this week per region" — never "give me random rows."
The hot data is tiny, the cold data is enormous. Yesterday matters a lot; the same day last year matters for one rollup query.
High write fan-out. Our Asia-Pacific regions (JP, KR, TW, SG, VN, TH, HK, plus US/GB baselines) each emit events in bursts tied to local prime time.

A single B-tree index across 4 billion rows tries to keep all of that in one structure. TimescaleDB's answer is the hypertable: one logical table that is transparently sharded under the hood into many physical "chunks," each covering a time range (and optionally a partition key). Queries and inserts hit only the relevant chunks, so the working index is the size of one chunk, not the universe.

Hypertables in One Command

A hypertable looks like a normal table to your application. You create an ordinary table, then promote it:

CREATE TABLE view_events (
    ts          TIMESTAMPTZ      NOT NULL,
    video_id    BIGINT           NOT NULL,
    region      TEXT             NOT NULL,
    language    TEXT             NOT NULL,  -- ja, ko, zh-Hant, etc.
    event_type  TEXT             NOT NULL,  -- play | pause | complete
    watch_ms    INTEGER          NOT NULL DEFAULT 0,
    device      TEXT
);

-- Promote to a hypertable: chunk by day, sub-partition by region.
SELECT create_hypertable(
    'view_events',
    by_range('ts', INTERVAL '1 day')
);

SELECT add_dimension(
    'view_events',
    by_hash('region', 4)
);

-- Indexes are per-chunk, so this stays small even at billions of rows.
CREATE INDEX ON view_events (video_id, ts DESC);
CREATE INDEX ON view_events (region, ts DESC);

The by_range('ts', INTERVAL '1 day') line is the whole trick: each day of data becomes its own chunk with its own indexes. At 12M events/day a chunk holds ~12M rows — comfortably indexable and cacheable. The by_hash('region', 4) adds space partitioning so that a query filtered to one region touches roughly a quarter of each day's data.

Pick the chunk interval so that the most recent chunk (and its indexes) fits in about 25% of shared_buffers. Too-small chunks create planning overhead from thousands of partitions; too-large chunks lose the locality benefit. One day was right for us; a low-traffic side project might use one week.

Ingesting View Events From PHP 8.4

Our edge collectors push events to a queue, and a PHP worker drains them in batches. The single biggest ingest win is to never insert one row at a time. PostgreSQL's COPY protocol is the fastest path, and PDO can drive a multi-row prepared statement that gets close.

Here is the batched writer we run. It uses PHP 8.4, native PDO with the pgsql driver, and groups events into chunks of 1,000 so each round trip carries real weight:

<?php
declare(strict_types=1);

final class ViewEventWriter
{
    private const BATCH = 1000;

    public function __construct(private readonly PDO $pdo)
    {
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

    /** @param list<array{ts:string,video_id:int,region:string,language:string,event_type:string,watch_ms:int,device:?string}> $events */
    public function flush(array $events): int
    {
        $written = 0;
        foreach (array_chunk($events, self::BATCH) as $chunk) {
            $placeholders = implode(',', array_fill(
                0,
                count($chunk),
                '(?,?,?,?,?,?,?)'
            ));

            $sql = "INSERT INTO view_events
                    (ts, video_id, region, language, event_type, watch_ms, device)
                    VALUES {$placeholders}";

            $stmt = $this->pdo->prepare($sql);
            $params = [];
            foreach ($chunk as $e) {
                array_push(
                    $params,
                    $e['ts'], $e['video_id'], $e['region'],
                    $e['language'], $e['event_type'], $e['watch_ms'],
                    $e['device'] ?? null
                );
            }

            $this->pdo->beginTransaction();
            $stmt->execute($params);
            $this->pdo->commit();
            $written += count($chunk);
        }
        return $written;
    }
}

$pdo = new PDO(
    'pgsql:host=analytics-db;port=5432;dbname=tvh_analytics',
    'tvh_ingest',
    getenv('TVH_PG_PASSWORD'),
    [PDO::ATTR_PERSISTENT => true]
);

$writer = new ViewEventWriter($pdo);
$writer->flush($eventsFromQueue);

A few things that mattered in production:

Persistent connections (ATTR_PERSISTENT) avoid the TCP + auth handshake on every worker tick. LiteSpeed keeps the PHP process warm, so this pays off.
One transaction per batch, not per row. Autocommit-per-row was our original sin in SQLite and it carried over until we profiled it.
Insert in roughly time-ascending order when you can. Hypertables tolerate out-of-order inserts, but mostly-ordered writes keep recent chunks hot and avoid touching old, compressed chunks.

Continuous Aggregates Precompute the Dashboards

The trending dashboards never need raw events — they need "plays per video per hour" or "watch minutes per region per day." Recomputing those from raw rows on every page load is exactly what was killing us. TimescaleDB's continuous aggregate is a materialized view that refreshes incrementally as new data lands, and it is itself a hypertable.

CREATE MATERIALIZED VIEW plays_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket(INTERVAL '1 hour', ts) AS bucket,
    video_id,
    region,
    count(*) FILTER (WHERE event_type = 'play')     AS plays,
    count(*) FILTER (WHERE event_type = 'complete') AS completes,
    sum(watch_ms)                                   AS watch_ms
FROM view_events
GROUP BY bucket, video_id, region
WITH NO DATA;

-- Refresh policy: keep the last 3 days fresh, recompute every 30 min,
-- but never touch the most recent hour (still being written).
SELECT add_continuous_aggregate_policy('plays_hourly',
    start_offset      => INTERVAL '3 days',
    end_offset        => INTERVAL '1 hour',
    schedule_interval => INTERVAL '30 minutes'
);

Now the dashboard query reads from plays_hourly — a table that is orders of magnitude smaller than the raw events — and the engine real-time aggregates the last uncovered hour on the fly so the numbers are never stale. The 40-second query became a 50-millisecond one.

The end_offset => INTERVAL '1 hour' detail is easy to miss and important: it tells the policy not to materialize the in-progress bucket, which would otherwise be rewritten constantly. Real-time aggregation fills that gap at query time.

Querying Rollups From Go for the Live Dashboard

The live "what's trending right now" panel is a small Go service behind Cloudflare. It hits the continuous aggregate with pgx, which is faster than database/sql for this kind of read path because it speaks the binary protocol natively:

package main

import (
    "context"
    "fmt"
    "os"
    "time"

    "github.com/jackc/pgx/v5/pgxpool"
)

type TrendRow struct {
    VideoID int64
    Region  string
    Plays   int64
    WatchMS int64
}

func topTrending(ctx context.Context, pool *pgxpool.Pool, region string) ([]TrendRow, error) {
    const q = `
        SELECT video_id, region, sum(plays) AS plays, sum(watch_ms) AS watch_ms
        FROM plays_hourly
        WHERE bucket >= now() - INTERVAL '6 hours'
          AND region = $1
        GROUP BY video_id, region
        ORDER BY plays DESC
        LIMIT 20`

    rows, err := pool.Query(ctx, q, region)
    if err != nil {
        return nil, fmt.Errorf("query trending: %w", err)
    }
    defer rows.Close()

    var out []TrendRow
    for rows.Next() {
        var r TrendRow
        if err := rows.Scan(&r.VideoID, &r.Region, &r.Plays, &r.WatchMS); err != nil {
            return nil, err
        }
        out = append(out, r)
    }
    return out, rows.Err()
}

func main() {
    ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
    defer cancel()

    pool, err := pgxpool.New(ctx, os.Getenv("TVH_PG_DSN"))
    if err != nil {
        panic(err)
    }
    defer pool.Close()

    rows, err := topTrending(ctx, pool, "JP")
    if err != nil {
        panic(err)
    }
    for _, r := range rows {
        fmt.Printf("video=%d plays=%d watch_ms=%d\n", r.VideoID, r.Plays, r.WatchMS)
    }
}

Because plays_hourly is partitioned the same way as the raw table, the region = $1 filter prunes chunks aggressively. We cache the JSON result of this at the Cloudflare edge for 60 seconds, so the database barely sees the dashboard traffic at all — it mostly serves cache misses and the 30-minute refresh.

Compression and Retention Pay the Storage Bill

This is where hypertables stopped a second crisis — disk. Raw view events are highly repetitive: the same region, language, and event_type repeat millions of times. TimescaleDB's columnar compression on older chunks gave us roughly a 12x reduction.

ALTER TABLE view_events SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'region, video_id',
    timescaledb.compress_orderby   = 'ts DESC'
);

-- Compress anything older than 7 days.
SELECT add_compression_policy('view_events', INTERVAL '7 days');

-- Drop raw events after 180 days; the continuous aggregates keep the history.
SELECT add_retention_policy('view_events', INTERVAL '180 days');

The mental model is clean:

Hot (0-7 days): uncompressed, fast to write and update.
Warm (7-180 days): compressed columnar, still queryable, ~12x smaller.
Cold (180+ days): raw rows dropped; the hourly and daily continuous aggregates retain the shape of history forever at a fraction of the size.

compress_segmentby should be the columns you filter on (so compressed chunks can be skipped per segment), and compress_orderby should match how you scan — newest first, in our case.

A Python Job for CJK Language Trend Anomalies

The payoff of clean rollups is that analytics jobs become short. We run a nightly Python job that flags videos whose plays in a given language spiked abnormally — useful for catching a video going viral in, say, Korean before it shows up in our global trending. It reads straight from the continuous aggregate with psycopg:

import os
import statistics
import psycopg

SQL = """
SELECT video_id, language,
       time_bucket('1 day', bucket) AS day,
       sum(plays) AS plays
FROM plays_hourly ph
JOIN view_events ve USING (video_id)         -- language lives on raw rows
WHERE bucket >= now() - INTERVAL '14 days'
GROUP BY video_id, language, day
ORDER BY video_id, language, day
"""

def detect_spikes(min_z: float = 3.0):
    dsn = os.environ["TVH_PG_DSN"]
    series: dict[tuple[int, str], list[int]] = {}

    with psycopg.connect(dsn) as conn, conn.cursor() as cur:
        cur.execute(SQL)
        for video_id, language, _day, plays in cur:
            series.setdefault((video_id, language), []).append(int(plays))

    alerts = []
    for (video_id, language), points in series.items():
        if len(points) < 8:
            continue
        history, today = points[:-1], points[-1]
        mean = statistics.fmean(history)
        stdev = statistics.pstdev(history) or 1.0
        z = (today - mean) / stdev
        if z >= min_z:
            alerts.append((video_id, language, today, round(z, 2)))

    alerts.sort(key=lambda a: a[3], reverse=True)
    return alerts


if __name__ == "__main__":
    for video_id, language, plays, z in detect_spikes():
        print(f"SPIKE video={video_id} lang={language} plays={plays} z={z}")

In practice we precompute language onto a dedicated plays_by_language_daily aggregate rather than joining back to raw rows, but the join version above is the honest first draft and it runs in seconds because the heavy lifting already happened in the continuous aggregate.

Numbers After the Migration

A few weeks in, with the same hardware budget:

Dashboard "plays per region, 7 days" query: ~40 s → ~50 ms.
Ingest throughput: comfortably past 30k events/sec in batched mode, with headroom.
On-disk size for 180 days of events: ~60 GB SQLite file → ~9 GB compressed hypertable.
Write contention against reads: gone — Postgres MVCC means the ingest worker never blocks the dashboards.

None of this required rewriting the product. The PHP catalog still runs on SQLite with the FTS5 CJK tokenizer; we simply stopped asking a document store to behave like a time-series engine.

Lessons Worth Keeping

If you are staring at a swelling events table, the order of operations that worked for us was: promote to a hypertable with a sane chunk interval, batch your inserts, build continuous aggregates for every dashboard, then turn on compression and retention. The hypertable solves write contention and query locality; the continuous aggregates solve dashboard latency; compression solves the disk bill. Skip any one of them and the wins are partial.

The broader lesson is about picking the right store for the shape of the data, not the convenience of one engine. SQLite is still perfect for our catalog and search. View events are append-only, time-ordered, and queried by range — that is the exact shape TimescaleDB hypertables are built for, and treating them that way turned our worst-performing surface into one of the quietest parts of the stack.

Building Video Recommendations With SurrealDB Graph Traversal Queries

ahmet gedik — Tue, 09 Jun 2026 09:00:04 +0000

The related-videos rail that ate our query planner

For about two years the "related videos" rail on TrendVidStream was a SQLite query with four self-joins over a views table, filtered by region, ordered by a hand-tuned co-occurrence score. It worked when we had a few hundred thousand view rows. It stopped working the week we crossed eight regions and started ingesting trending videos on a per-region cron. The query that powered "people who watched this also watched" went from 30ms to 900ms, the planner started picking the wrong index depending on which region's data was hot, and every attempt to make it region-aware added another JOIN and another WHERE region = ? that the optimizer had to reason about.

The root problem is that recommendation is not a relational problem pretending to be hard. It is a graph problem being forced through a relational engine. "Viewers who watched A also watched B" is a two-hop traversal: video → viewers → videos. In SQL that is a self-join on the bridge table, and every extra hop is another join. In a graph database it is a path expression you write once.

We kept SQLite FTS5 for full-text search — it is genuinely excellent for that and our PHP 8.4 stack is built around it — but we moved the recommendation graph into SurrealDB. This post is the honest version of how that went: the schema, the traversal queries, how we call them from PHP, how the multi-region cron feeds the graph, and the parts that bit us.

Why a graph model actually fits video discovery

Video discovery is a web of typed relationships, not a set of rows. A viewer watched a video. A video belongs to a channel. A channel publishes in a region. A video is tagged with topics. The recommendation you want is a walk over those edges: start at the video the user is on, walk to the viewers who watched it, walk forward to what else those viewers watched, then bias the result by shared tags and the user's region.

SurrealDB models this directly. Records live in tables, but relationships are first-class edge records created with RELATE, and you traverse them with arrow syntax: ->watched->video. The arrow direction is the edge direction, and you can chain arrows to express multi-hop paths inline. That single feature is what collapses our four-join SQL into one readable line.

There are three properties that made it worth the operational cost of running a second datastore:

Traversal cost is proportional to the edges you walk, not the size of the tables. A two-hop recommendation touches the neighborhood of one video, not the whole views table.
Edges carry data. A watched edge can store watch percentage, timestamp, and region, so we can weight a recommendation by how much of a video someone actually watched rather than treating a 3-second bounce the same as a full view.
Queries read like the question. When the on-call engineer reads ->watched<-watched->video, they can see it is "co-viewers' other videos" without reverse-engineering a join graph.

Defining the schema and the edges

SurrealDB is schemaless by default, but for a recommendation graph you want a schema so that bad writes from a flaky cron run get rejected instead of silently corrupting traversals. Here is the SurrealQL we run on migration. Note the edge tables (watched, tagged) defined with TYPE RELATION and constrained endpoints.

-- Run once via the /sql HTTP endpoint or the CLI
DEFINE TABLE video SCHEMAFULL;
DEFINE FIELD title       ON video TYPE string;
DEFINE FIELD channel     ON video TYPE record<channel>;
DEFINE FIELD region      ON video TYPE string ASSERT $value INSIDE ['us','gb','de','fr','in','br','au','ca'];
DEFINE FIELD duration    ON video TYPE int;
DEFINE FIELD published   ON video TYPE datetime;
DEFINE INDEX video_region ON video FIELDS region;

DEFINE TABLE viewer SCHEMAFULL;
DEFINE FIELD region ON viewer TYPE string;

-- Edge: viewer -> watched -> video, carrying engagement data
DEFINE TABLE watched TYPE RELATION IN viewer OUT video SCHEMAFULL;
DEFINE FIELD percent ON watched TYPE float ASSERT $value >= 0 AND $value <= 1;
DEFINE FIELD region  ON watched TYPE string;
DEFINE FIELD at      ON watched TYPE datetime;

-- Edge: video -> tagged -> topic
DEFINE TABLE topic   SCHEMAFULL;
DEFINE TABLE tagged  TYPE RELATION IN video OUT topic SCHEMAFULL;

Writing an edge is a RELATE statement. When our ingest sees a watch event, it does one upsert per video and one relate per view:

-- Ensure the nodes exist (idempotent), then connect them
UPSERT video:dQw4 SET title = 'Region trends recap', region = 'gb', duration = 612;
UPSERT viewer:ix92 SET region = 'gb';

RELATE viewer:ix92 -> watched -> video:dQw4
  SET percent = 0.83, region = 'gb', at = time::now();

The RELATE creates a watched record whose in is the viewer and whose out is the video. From now on the edge is traversable in both directions: forward from the viewer with ->watched->video, and backward from the video with <-watched<-viewer.

The recommendation query in one traversal

Here is the query that replaced the four-join SQL. Read it as the sentence it is: starting from a video, walk back to the viewers who watched it with real engagement, then forward to the other videos those viewers watched, count how often each appears, and rank.

-- $vid is the video the user is currently watching, e.g. video:dQw4
-- $region is the requesting user's region
LET $rec = (
  SELECT
    out AS video,
    count() AS co_views,
    math::mean(percent) AS avg_engagement
  FROM (
    -- co-viewers' other watches, excluding the source video
    SELECT ->watched.out AS out, ->watched.percent AS percent
    FROM video:dQw4<-watched<-viewer->watched
    WHERE percent >= 0.4
  )
  WHERE out != video:dQw4
  GROUP BY video
);

SELECT
  video,
  co_views,
  avg_engagement,
  -- region match is a soft boost, not a hard filter
  (co_views * avg_engagement) * IF video.region = $region { 1.5 } ELSE { 1.0 } AS score
FROM $rec
ORDER BY score DESC
LIMIT 12;

A few things worth calling out, because they are the difference between a demo and something that survives production:

The percent >= 0.4 filter on the edge is doing real work. It means a co-view only counts if the co-viewer actually watched 40% of the source. Drive-by clicks do not pollute recommendations.
Region is a soft boost (* 1.5), not a WHERE clause. This was a deliberate change from the SQL version. Hard-filtering by region gave users in smaller regions an empty rail. A multiplier keeps global hits visible while favoring local content.
The grouping and scoring happen server-side in one round trip. The PHP layer receives 12 ranked rows, not a result set it has to sort.

Calling it from PHP 8.4

Our application layer is PHP 8.4, deployed over FTP to LiteSpeed hosts, so we do not get to install a fancy SurrealDB extension on every box. That is fine — SurrealDB exposes an HTTP /sql endpoint that takes raw SurrealQL and returns JSON, so plain curl is all we need. Here is the client we actually use, trimmed to the relevant parts. PHP 8.4's typed properties and constructor promotion keep it tidy.

<?php
declare(strict_types=1);

final class SurrealClient
{
    public function __construct(
        private readonly string $endpoint = 'http://127.0.0.1:8000/sql',
        private readonly string $namespace = 'trendvid',
        private readonly string $database  = 'discovery',
        private readonly string $user      = 'rec_ro',
        private readonly string $pass      = '',
    ) {}

    /** @return list<array<string,mixed>> rows from the final statement */
    public function query(string $surql, array $vars = []): array
    {
        $ch = curl_init($this->endpoint);
        // SurrealDB takes bound vars as headers prefixed with the var name,
        // but for dynamic values we interpolate server-side LET is safer.
        curl_setopt_array($ch, [
            CURLOPT_POST           => true,
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_TIMEOUT        => 3,
            CURLOPT_HTTPHEADER     => [
                'Accept: application/json',
                'Content-Type: text/plain',
                'NS: ' . $this->namespace,
                'DB: ' . $this->database,
            ],
            CURLOPT_USERPWD  => "{$this->user}:{$this->pass}",
            CURLOPT_POSTFIELDS => $surql,
        ]);

        $raw = curl_exec($ch);
        if ($raw === false) {
            throw new RuntimeException('SurrealDB unreachable: ' . curl_error($ch));
        }
        curl_close($ch);

        /** @var list<array{status:string,result:mixed}> $batch */
        $batch = json_decode($raw, true, flags: JSON_THROW_ON_ERROR);
        $last  = end($batch) ?: ['result' => []];
        if (($last['status'] ?? '') !== 'OK') {
            throw new RuntimeException('Query failed: ' . json_encode($last));
        }
        return is_array($last['result']) ? $last['result'] : [];
    }
}

function relatedVideos(SurrealClient $db, string $videoId, string $region): array
{
    // SurrealDB returns one result block per statement; we read the last.
    $surql = <<<SURQL
        LET \$vid = type::thing('video', '{$videoId}');
        SELECT video, co_views, avg_engagement,
          (co_views * avg_engagement) *
            IF video.region = '{$region}' { 1.5 } ELSE { 1.0 } AS score
        FROM (
          SELECT out AS video, count() AS co_views, math::mean(percent) AS avg_engagement
          FROM (
            SELECT ->watched.out AS out, ->watched.percent AS percent
            FROM \$vid<-watched<-viewer->watched WHERE percent >= 0.4
          ) WHERE out != \$vid GROUP BY video
        )
        ORDER BY score DESC LIMIT 12;
        SURQL;

    return $db->query($surql);
}

One real lesson encoded here: never interpolate user-supplied IDs as bare record literals. We pass them through type::thing('video', $id) so SurrealDB parses the value as data, not as query syntax. Treat it exactly like you treat parameterized SQL — a record link built from request input is an injection vector if you string-concatenate it into a path.

Feeding the graph from the multi-region cron

Our ingest already runs as a per-region cron that pulls trending videos. The same job now writes edges into SurrealDB. We do it in Python because the cron orchestration was already Python, and we batch the writes — sending one RELATE per event over HTTP would melt under a region's worth of events.

import requests

SURREAL = "http://127.0.0.1:8000/sql"
HEADERS = {"Accept": "application/json", "Content-Type": "text/plain",
           "NS": "trendvid", "DB": "discovery"}
AUTH = ("rec_rw", "")

def flush_watch_events(events: list[dict], region: str) -> None:
    """events: [{viewer, video, percent}], all for one region."""
    if not events:
        return
    # Build one transaction so a mid-batch failure rolls back cleanly.
    stmts = ["BEGIN TRANSACTION;"]
    for e in events:
        v, vid, pct = e["viewer"], e["video"], float(e["percent"])
        stmts.append(f"UPSERT viewer:{v} SET region = '{region}';")
        stmts.append(
            f"RELATE viewer:{v} -> watched -> video:{vid} "
            f"SET percent = {pct:.3f}, region = '{region}', at = time::now();"
        )
    stmts.append("COMMIT TRANSACTION;")

    resp = requests.post(SURREAL, data="\n".join(stmts),
                         headers=HEADERS, auth=AUTH, timeout=10)
    resp.raise_for_status()
    blocks = resp.json()
    failed = [b for b in blocks if b.get("status") != "OK"]
    if failed:
        raise RuntimeError(f"{len(failed)} statements failed in {region} batch")

The transaction matters more than it looks. Our cron runs eight region jobs that can overlap, and an unbatched failure used to leave half-written view data that skewed recommendations for hours. Wrapping each batch in BEGIN/COMMIT means a region either lands fully or not at all, and the next run retries the whole batch.

Precomputing hot rails with a Go worker

The live traversal is fast — single-digit milliseconds for a normal video — but our homepage shows recommendation rails for the top few hundred trending videos per region, and computing those on every request is wasteful when they barely change between cron runs. So we precompute them with a small Go worker that runs after each ingest, writes the result back into SurrealDB as a materialized rail record, and lets the PHP layer read a single row instead of running the traversal.

package main

import (
    "bytes"
    "fmt"
    "io"
    "net/http"
    "time"
)

const surreal = "http://127.0.0.1:8000/sql"

func surql(query string) ([]byte, error) {
    req, _ := http.NewRequest("POST", surreal, bytes.NewBufferString(query))
    req.Header.Set("Accept", "application/json")
    req.Header.Set("Content-Type", "text/plain")
    req.Header.Set("NS", "trendvid")
    req.Header.Set("DB", "discovery")
    req.SetBasicAuth("rec_rw", "")

    client := &http.Client{Timeout: 8 * time.Second}
    resp, err := client.Do(req)
    if err != nil {
        return nil, err
    }
    defer resp.Body.Close()
    return io.ReadAll(resp.Body)
}

// precomputeRail runs the traversal and stores the top results on the video
// itself, so reads are a single SELECT with no graph walk.
func precomputeRail(videoID, region string) error {
    q := fmt.Sprintf(`
        LET $vid = type::thing('video', '%s');
        LET $rec = (
          SELECT out AS video, count() AS co_views, math::mean(percent) AS eng
          FROM (SELECT ->watched.out AS out, ->watched.percent AS percent
                FROM $vid<-watched<-viewer->watched WHERE percent >= 0.4)
          WHERE out != $vid GROUP BY video
          ORDER BY (co_views * eng) DESC LIMIT 12
        );
        UPDATE $vid SET rail = $rec, rail_built = time::now(), rail_region = '%s';`,
        videoID, region)

    _, err := surql(q)
    return err
}

func main() {
    // In production this list comes from the trending query per region.
    hot := []struct{ id, region string }{
        {"dQw4", "gb"}, {"a8Bz", "us"}, {"k0Lm", "de"},
    }
    for _, v := range hot {
        if err := precomputeRail(v.id, v.region); err != nil {
            fmt.Printf("rail %s failed: %v\n", v.id, err)
        }
    }
}

Now the homepage read is SELECT rail FROM video:dQw4 — one record, no traversal — and the expensive walk only happens for long-tail videos that are not in the precomputed set. This is the same split we use everywhere: cheap reads for the common case, live computation for the tail.

Keeping SQLite FTS5 for what it is good at

We did not throw away SQLite. Search — "find videos matching these words" — is still FTS5, because a graph database is the wrong tool for ranked text matching and FTS5 is genuinely fast and zero-ops. The clean division we settled on:

SQLite FTS5 owns search, filtering, and anything text- or attribute-based. It is the source of truth for video metadata.
SurrealDB owns relationships: who watched what, what is similar to what, what to recommend next.
The cron writes to both. Metadata goes to SQLite; the watch edges go to SurrealDB.

The two never need to join across the wire because the recommendation query returns video record IDs, and PHP hydrates the display data from SQLite by ID — a primary-key lookup, the cheapest read there is.

What bit us

A few things we learned the expensive way, so you do not have to:

Unbounded traversals will find your most popular video and never come back. A globally trending video has hundreds of thousands of co-viewers; a naive <-watched<-viewer->watched over it walks an enormous neighborhood. The percent >= 0.4 edge filter and the LIMIT are not optional — they are what keep the query bounded.
Schemaless writes from a flaky cron silently rot the graph. Before we made the edge tables SCHEMAFULL, a bug that wrote percent as a string instead of a float made math::mean skip rows without erroring. Define your fields and assert their ranges.
Region as a hard filter starves small regions. Soft-boosting with a multiplier was the single change that most improved perceived quality for users outside the US and GB.
Don't interpolate record IDs. Use type::thing(). A graph path built from raw request input is an injection vector exactly like raw SQL.
Materialize the hot set. The live traversal is fast enough for the tail, but precomputing rails for trending videos cut homepage database time by more than half.

Conclusion

The shift that mattered was conceptual, not just operational: recommendation stopped being a query we tuned and became a graph we traverse. The four-join SQL that fell over at eight regions is now a single arrow-path expression that reads like the question it answers, runs in single-digit milliseconds for the common case, and stays bounded because the engagement filter and limits are baked into the path. SQLite FTS5 still owns search, SurrealDB owns the relationship graph, and the multi-region cron feeds both without a cross-store join in sight. If you are still expressing "viewers who watched this also watched" as a self-join that grows a clause every time a product manager asks for one more signal, try modeling it as edges and walking them — the query gets shorter as the requirements get more complex, which is the opposite of what happens in SQL.

Running Ad-Hoc Video Analytics on Parquet Exports with DuckDB

ahmet gedik — Mon, 08 Jun 2026 18:00:01 +0000

We track every trending video across roughly thirty European markets. Every few hours a fetch job pulls fresh metrics — view count, likes, comments, and the rank a clip held in a given country — and appends a row to a video_snapshots table in SQLite. SQLite in WAL mode handles that write load beautifully: concurrent readers never block the writer, the database file lives on the same LiteSpeed box that serves the site, and there is no second daemon to babysit. For serving pages and feeding the API, it is close to perfect.

Then someone on the editorial side asks a question like "which categories went viral fastest in Poland and the Netherlands last month, and how did that compare to the same clips in Germany?" — and the whole arrangement falls over. That query touches tens of millions of rows, groups across regions and dates, and computes per-video growth curves with window functions. Run it against the live SQLite file and you either stall the page-cache warmers or wait four minutes for a single answer. Run it twice in parallel and an analyst has effectively DoS'd production.

For a long time the standard advice here is "stand up a warehouse." But we are a lean operation at ViralVidVault, and spinning up ClickHouse or shipping data into BigQuery introduces a second copy of personal-ish data to govern under GDPR, a network hop, egress cost, and an ops surface we do not want. DuckDB turned out to be the answer that fits the existing stack: it is an in-process analytical engine, it reads Parquet natively, and it never touches the live database unless we explicitly point it there. This post walks through how we export SQLite to partitioned Parquet, run genuinely heavy ad-hoc queries on it, and wire the whole thing behind a thin PHP endpoint — without the production database ever feeling it.

Why DuckDB and not a warehouse

The mental model that made this click for me: DuckDB is to OLAP what SQLite is to OLTP. It is a single library, no server, no cluster, no config files. You embed it, point it at files, and run SQL. For an ad-hoc analytics layer that gets queried a few hundred times a day by humans, that profile is exactly right.

The properties that mattered for us specifically:

It reads Parquet directly. No load step, no ingestion pipeline. The Parquet files on disk are the table. Add a new day's export and it is queryable immediately.
Columnar + vectorized execution. Aggregations that scan one or two columns out of fifteen only read those columns off disk. A SELECT region, avg(view_count) over 40M rows reads megabytes, not gigabytes.
It can read SQLite itself. The sqlite extension lets DuckDB attach a .db file read-only, which makes the export step a one-liner instead of a custom serializer.
Zero new attack surface. There is no listening port, no auth layer to misconfigure, no separate box to patch. It runs as a CLI invocation or an embedded library and exits.
Hive-style partition pruning. Partition by date and region on disk, and a query filtered to region = 'PL' for last week physically skips every other folder.

The trade-off is that DuckDB is not built for high-concurrency point lookups or transactional writes — that is still SQLite's job. We are not replacing the operational database; we are giving analysts a read-only mirror they cannot hurt.

Exporting SQLite to Parquet without blocking ingestion

The export is where most of the GDPR and performance thinking lives, so it is worth doing carefully. The key insight is that DuckDB can do the whole export by itself: attach the SQLite file in read-only mode, SELECT the columns we actually want for analytics (dropping anything PII-adjacent), and COPY straight to partitioned Parquet.

Running it read-only is what keeps production safe. WAL mode already allows readers alongside the writer, and READ_ONLY guarantees the export can never take a write lock or corrupt the file even if the process is killed mid-run.

import duckdb

con = duckdb.connect()
con.execute("INSTALL sqlite; LOAD sqlite;")

# READ_ONLY is the whole safety story: this can never lock or mutate prod.
con.execute("ATTACH '/srv/vvv/data/viral.db' AS src (TYPE sqlite, READ_ONLY);")

con.execute("""
COPY (
    SELECT
        video_id,
        region,
        category_id,
        view_count,
        like_count,
        comment_count,
        rank_position,
        captured_at,
        CAST(captured_at AS DATE) AS capture_date
    FROM src.video_snapshots
    WHERE captured_at >= now() - INTERVAL 2 DAY
)
TO '/srv/vvv/exports/snapshots'
(
    FORMAT parquet,
    PARTITION_BY (capture_date, region),
    COMPRESSION zstd,
    OVERWRITE_OR_IGNORE true
);
""")

print("export complete")

A few decisions worth calling out:

We project, we do not SELECT *. The export query is the natural choke point for data minimisation. There is no user_id, no IP, no session — none of it ever reaches the analytics layer, so an analyst literally cannot query personal data that is not there. That is GDPR data-minimisation enforced by schema, not by policy.
Partition by capture_date and region. This produces a directory tree like capture_date=2026-06-01/region=DE/data.parquet. DuckDB reads the partition values from the path, so date- and region-filtered queries prune entire folders.
ZSTD compression. Snapshot data is highly repetitive (the same video_id and category_id recur thousands of times), so ZSTD typically gets us 8-12x over the raw rows while staying fast to decompress.
Rolling two-day window. We re-export the last two days each night and let OVERWRITE_OR_IGNORE replace those partitions. Older partitions are immutable once written, which makes the export idempotent and cheap.

We schedule it as a plain cron entry next to the existing fetch jobs. There is nothing clever here, and that is the point:

# /etc/cron.d/vvv-export
# Nightly Parquet export, 03:17 to dodge the on-the-hour fetch jobs.
17 3 * * *  www-data  /usr/bin/python3 /srv/vvv/bin/export_snapshots.py >> /var/log/vvv/export.log 2>&1

Because the export reads SQLite read-only and writes to a separate exports/ directory, it can run while the fetch cron is appending new snapshots and while LiteSpeed is serving traffic. Nobody contends with anybody.

Queries that would melt SQLite

Now the fun part. Here is the actual shape of the "fastest-growing categories per market" question that started this whole thing. It uses a window function to compute each video's day-over-day view gain, then aggregates that into per-category medians and a 95th percentile — the kind of analytical SQL that SQLite technically supports but executes at a glacial pace over tens of millions of rows.

-- Fastest-growing categories per market, last 30 days.
WITH growth AS (
    SELECT
        video_id,
        region,
        category_id,
        capture_date,
        view_count,
        view_count - lag(view_count) OVER (
            PARTITION BY video_id, region
            ORDER BY capture_date
        ) AS daily_gain
    FROM read_parquet(
        '/srv/vvv/exports/snapshots/**/*.parquet',
        hive_partitioning = true
    )
    WHERE capture_date >= today() - 30
      AND region IN ('PL', 'NL', 'DE')
)
SELECT
    region,
    category_id,
    count(DISTINCT video_id)                AS clips,
    round(median(daily_gain))               AS median_daily_views,
    round(quantile_cont(daily_gain, 0.95))  AS p95_daily_views
FROM growth
WHERE daily_gain > 0
GROUP BY region, category_id
ORDER BY region, median_daily_views DESC
LIMIT 20;

The hive_partitioning = true flag is what makes this fast. The WHERE capture_date >= today() - 30 AND region IN ('PL','NL','DE') predicate is matched against the directory structure before any file is opened, so DuckDB only touches ~90 partition folders out of the thousands on disk. On our dataset this query returns in well under a second on a single shared LiteSpeed box, against a SQLite equivalent that took minutes and pinned a core the whole time.

A couple of DuckDB-specific niceties that show up constantly in ad-hoc work:

quantile_cont and median are built in. No extension, no custom aggregate. Percentile latency-style analysis on view gains is one expression.
read_parquet accepts globs and lists. You can point at **/*.parquet, a single file, or an explicit list, and DuckDB unions them with a consistent schema.
EXPLAIN ANALYZE prints how many partitions and bytes were actually scanned — invaluable for confirming pruning is doing its job rather than silently reading everything.

Wrapping DuckDB behind a PHP endpoint

Most of our app is PHP 8.4, and analysts do not write SQL into a terminal — they hit an internal dashboard. So we need a thin server-side wrapper. There is no need for a PHP extension here; the DuckDB CLI with the -json flag gives us machine-readable output, and proc_open lets us feed SQL over stdin so nothing ever lands on the shell command line.

The important safety property: we open DuckDB against an in-memory database (:memory:) and create a view over the Parquet files. The session can read the exports and nothing else — there is no attached writable database, so even a malicious query cannot mutate state.

<?php
declare(strict_types=1);

final class DuckQuery
{
    public function __construct(
        private readonly string $binary = '/usr/local/bin/duckdb',
        private readonly string $exportRoot = '/srv/vvv/exports/snapshots',
    ) {}

    /** @return list<array<string,mixed>> */
    public function run(string $sql, int $timeoutSeconds = 20): array
    {
        $glob = $this->exportRoot . '/**/*.parquet';
        $bootstrap = sprintf(
            "CREATE VIEW snapshots AS "
            . "SELECT * FROM read_parquet('%s', hive_partitioning = true);",
            str_replace("'", "''", $glob)
        );

        $descriptors = [
            0 => ['pipe', 'r'],
            1 => ['pipe', 'w'],
            2 => ['pipe', 'w'],
        ];

        // :memory: + -json => read-only against Parquet, machine-readable rows.
        $proc = proc_open(
            [$this->binary, '-json', ':memory:'],
            $descriptors,
            $pipes
        );

        if (!is_resource($proc)) {
            throw new RuntimeException('Could not start DuckDB');
        }

        fwrite($pipes[0], $bootstrap . "\n" . rtrim($sql, "; ") . ";\n");
        fclose($pipes[0]);

        $out = stream_get_contents($pipes[1]);
        $err = stream_get_contents($pipes[2]);
        fclose($pipes[1]);
        fclose($pipes[2]);

        if (proc_close($proc) !== 0) {
            throw new RuntimeException("DuckDB failed: {$err}");
        }

        /** @var list<array<string,mixed>> $rows */
        $rows = json_decode($out ?: '[]', true, flags: JSON_THROW_ON_ERROR);
        return $rows;
    }
}

// Usage from a dashboard controller:
$duck = new DuckQuery();
$rows = $duck->run(
    "SELECT region, count(DISTINCT video_id) AS clips
       FROM snapshots
      WHERE capture_date >= today() - 7
      GROUP BY region
      ORDER BY clips DESC"
);
header('Content-Type: application/json');
echo json_encode($rows, JSON_THROW_ON_ERROR);

The dashboard only ever sends parameterised, server-built queries — analysts pick filters from dropdowns, they do not type raw SQL — so the snapshots view plus a query-allowlist on our side keeps this from becoming an injection vector. For the cases where we genuinely want free-form SQL (internal, authenticated, trusted users only), the in-memory read-only session is the backstop: the worst a bad query can do is spin a CPU for up to timeoutSeconds and then get killed.

In front of this endpoint we let Cloudflare Workers cache the common dashboard queries for a few minutes keyed on the filter parameters. The cardinality of "region + date-range + category" combinations the editorial team actually uses is small, so a short edge TTL absorbs most of the load and the PHP layer barely sees repeat traffic.

Batch jobs in Go for the heavy lifters

Not everything runs interactively. Some rollups — the daily "top movers" email, the weekly trend digest — are batch jobs, and we run a few of those in Go using the marcboeker/go-duckdb driver. It speaks the standard database/sql interface, so the code looks like any other Go SQL job, except the "database" is a pile of Parquet files.

package main

import (
    "database/sql"
    "encoding/json"
    "log"
    "os"

    _ "github.com/marcboeker/go-duckdb"
)

func main() {
    db, err := sql.Open("duckdb", "") // empty DSN => in-memory
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    const q = `
        SELECT region,
               count(DISTINCT video_id) AS videos,
               round(avg(view_count))   AS avg_views
        FROM read_parquet('/srv/vvv/exports/snapshots/**/*.parquet',
                          hive_partitioning = true)
        WHERE capture_date >= today() - 7
        GROUP BY region
        ORDER BY avg_views DESC`

    rows, err := db.Query(q)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    type stat struct {
        Region   string  `json:"region"`
        Videos   int64   `json:"videos"`
        AvgViews float64 `json:"avg_views"`
    }

    var out []stat
    for rows.Next() {
        var s stat
        if err := rows.Scan(&s.Region, &s.Videos, &s.AvgViews); err != nil {
            log.Fatal(err)
        }
        out = append(out, s)
    }
    if err := rows.Err(); err != nil {
        log.Fatal(err)
    }

    if err := json.NewEncoder(os.Stdout).Encode(out); err != nil {
        log.Fatal(err)
    }
}

The nice thing about a static Go binary here is that it ships with DuckDB compiled in. The digest job is a single executable on a cron, reading the same Parquet exports the dashboard reads. No driver to install on the box, no runtime to keep in sync.

Keeping GDPR boring

The most reassuring part of this design is how little GDPR work it actually requires, precisely because the boundaries are physical rather than procedural:

PII never enters the analytics layer. The export query projects a fixed column list. There is no user identifier, IP, or session token in any Parquet file. Data minimisation is enforced by the export schema, not by reviewer discipline.
The data we keep is aggregate signal, not personal data. A view count and a category for a public video is not personal data about our visitors. We are analysing video performance, not people.
Partition layout makes retention trivial. Because everything is partitioned by capture_date, our retention job is a glorified rm -rf of folders older than the policy window. No DELETE over a live table, no vacuum, no fragmentation.
No third country, no extra processor. Because DuckDB is in-process and the Parquet files sit on the same EU-hosted infrastructure as the rest of the stack, there is no cross-border transfer and no new sub-processor to add to the records of processing.

If we ever did need a derived per-user metric, the right move would be to hash or pseudonymise it in the export SELECT so the raw identifier never reaches Parquet — but so far we have not needed to, and the cleanest PII is the kind that does not exist in your analytics store.

What it actually bought us

The before/after is stark. Queries that took minutes and risked production stability now run in well under a second against an isolated, read-only dataset. The export adds a few seconds to a nightly cron and a few hundred megabytes of compressed Parquet to disk. There is no new server, no new port, no new credential to rotate, and nothing an analyst can do from the dashboard that touches the operational SQLite file.

If you are running a small-to-medium product on SQLite and feeling the pain the moment analytical questions show up, this is the pattern I would reach for first:

Keep SQLite (in WAL mode) as the operational source of truth.
Export a projected, partitioned, ZSTD-compressed Parquet snapshot on a cron, reading the database read-only.
Query it with DuckDB — from the CLI, from PHP via proc_open, or from a Go batch job via the driver.
Let partition pruning and columnar scans do the heavy lifting, and cache the hot dashboard queries at the edge.

You get warehouse-class ad-hoc analytics without a warehouse, and the production database never even knows the analysts are there. For a lean European team that has to think about every byte of data it stores, that combination of speed, simplicity, and a small data footprint is hard to beat.

Building Cross-Language Video Search with pgvector and OpenAI Embeddings

ahmet gedik — Mon, 08 Jun 2026 09:00:07 +0000

On TopVideoHub we aggregate trending video metadata across the Asia-Pacific region — Japanese, Korean, Traditional Chinese, Thai, Vietnamese, plus the usual English firehose. For years our search ran on SQLite FTS5 with a custom CJK tokenizer, and for keyword lookups it is still excellent. But a support ticket finally forced the issue: a user searched kitchen fail compilation and got nothing, even though our catalog had a wildly popular clip titled 料理大失敗まとめ. Same concept, zero lexical overlap. FTS5 matches tokens, not meaning, and no amount of tokenizer tuning bridges kitchen fail to 料理失敗.

That is the wall keyword search hits on a multi-language catalog. This post is the architecture I landed on: keep SQLite FTS5 for what it is good at, and bolt on a Postgres + TopVideoHub pgvector sidecar that does semantic, cross-lingual retrieval using OpenAI embeddings. I will show the schema, the embedding pipeline, the PHP 8.4 query path, and the hybrid ranking that actually shipped.

Why keyword search hits a wall on multi-language catalogs

FTS5 is a lexical engine. It builds an inverted index of tokens and ranks with BM25. With a CJK-aware tokenizer it handles 料理失敗 fine — it segments the run of characters into searchable units. What it cannot do:

Match across languages. kitchen and 料理 are unrelated tokens.
Match synonyms or paraphrases. funny cat will not surface hilarious kitten.
Understand intent. videos to fall asleep to is a vibe, not a keyword.

Our Asia-Pacific audience searches in a mix of their native language and English, often in the same session. Roughly 22% of our zero-result queries were semantic misses like the kitchen example — real demand we were silently dropping on the floor.

Embeddings fix this because a good multilingual model maps kitchen fail and 料理失敗 to nearby points in vector space. OpenAI's text-embedding-3-small is genuinely multilingual: I measured cosine similarity of 0.61 between those two phrases, versus ~0.08 for unrelated pairs. That signal is more than enough to rank with.

Why a Postgres sidecar instead of replacing SQLite

Our primary store is SQLite — it is fast, file-based, plays nicely with LiteSpeed and our deployment model, and FTS5 keyword search stays sub-millisecond. I did not want to rip that out. Vector search is a fundamentally different workload (high-dimensional ANN indexes, periodic background re-embedding), so I run it as a separate Postgres instance with the pgvector extension. SQLite remains the source of truth for video metadata; Postgres holds only what semantic search needs: the video id, a bit of denormalized metadata for filtering, and the embedding.

This keeps each engine doing what it is good at and means a Postgres outage degrades search to keyword-only rather than taking the site down.

Schema and the pgvector column

text-embedding-3-small returns 1536-dimensional vectors. The table is deliberately thin — id, language, region, published timestamp for filtering, the source text we embedded (kept for debugging and re-embedding), and the vector itself.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE video_embeddings (
    video_id     BIGINT PRIMARY KEY,
    lang         TEXT        NOT NULL,
    region       TEXT        NOT NULL,
    published_at TIMESTAMPTZ NOT NULL,
    source_text  TEXT        NOT NULL,
    embedding    vector(1536) NOT NULL,
    model        TEXT        NOT NULL DEFAULT 'text-embedding-3-small',
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- HNSW index for approximate nearest-neighbour search.
-- vector_cosine_ops because we normalise and compare with cosine distance.
CREATE INDEX video_embeddings_hnsw
    ON video_embeddings
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- Partial b-tree indexes to keep metadata pre-filters cheap.
CREATE INDEX video_embeddings_region_idx ON video_embeddings (region, published_at DESC);

A note on the distance operator. pgvector exposes <-> (L2), <#> (negative inner product), and <=> (cosine distance). OpenAI embeddings are normalised to unit length, so cosine and inner product rank identically — but I use <=> explicitly because cosine distance lands in a predictable [0, 2] range, which makes thresholds and score blending much easier to reason about than raw inner products.

What text do you actually embed?

This decision mattered more than the model choice. A YouTube-style title alone is too sparse. I embed a compact composite of the fields that carry meaning, with light labels so the model gets structure:

Title (the strongest signal)
Channel name
Top 3-5 tags
First ~300 characters of the description

I deliberately do not embed view counts, durations, or IDs — numeric metadata pollutes the semantic space. I also truncate aggressively: text-embedding-3-small bills per token, and beyond a few hundred tokens of video metadata you get diminishing returns on retrieval quality while paying linearly more.

The embedding pipeline

This Python worker pulls rows that need (re-)embedding, calls OpenAI in batches — the embeddings endpoint accepts up to 2048 inputs per request, which slashes both latency and overhead — and upserts into Postgres. It is idempotent: a content hash of the source text means we never pay to re-embed text that has not changed.

import hashlib
import os
import openai
import psycopg
from psycopg.rows import dict_row

client = openai.OpenAI(api_key=os.environ["OPENAI_API_KEY"])
MODEL = "text-embedding-3-small"
BATCH = 256  # well under the 2048 input cap, keeps payloads sane


def build_source_text(row: dict) -> str:
    tags = ", ".join((row.get("tags") or "").split(",")[:5])
    desc = (row.get("description") or "")[:300]
    return (
        f"Title: {row['title']}\n"
        f"Channel: {row.get('channel', '')}\n"
        f"Tags: {tags}\n"
        f"Description: {desc}"
    ).strip()


def embed_batch(texts: list[str]) -> list[list[float]]:
    # One request embeds the whole batch; order of results matches input order.
    resp = client.embeddings.create(model=MODEL, input=texts)
    return [d.embedding for d in resp.data]


def run(pg: psycopg.Connection, rows: list[dict]) -> int:
    pending = []
    for row in rows:
        text = build_source_text(row)
        digest = hashlib.sha256(text.encode("utf-8")).hexdigest()
        if row.get("source_hash") == digest:
            continue  # unchanged, skip the API call entirely
        pending.append((row, text))

    written = 0
    for i in range(0, len(pending), BATCH):
        chunk = pending[i : i + BATCH]
        vectors = embed_batch([t for _, t in chunk])
        with pg.cursor() as cur:
            for (row, text), vec in zip(chunk, vectors):
                cur.execute(
                    """
                    INSERT INTO video_embeddings
                        (video_id, lang, region, published_at,
                         source_text, embedding, model)
                    VALUES (%s, %s, %s, %s, %s, %s, %s)
                    ON CONFLICT (video_id) DO UPDATE SET
                        source_text = EXCLUDED.source_text,
                        embedding   = EXCLUDED.embedding,
                        updated_at  = now()
                    """,
                    (row["video_id"], row["lang"], row["region"],
                     row["published_at"], text, vec, MODEL),
                )
        pg.commit()
        written += len(chunk)
    return written

psycopg3 adapts a Python list[float] straight into pgvector's text format, so no manual serialisation is needed. If you are on psycopg2 you would register the pgvector.psycopg2 adapter, or just format the list as '[0.1,0.2,...]' yourself.

We run this from the same cron that ingests trending videos. New videos get embedded within the hour; the cost is trivial (more on that below).

Querying from PHP 8.4

The read path lives in our PHP app. At query time we embed the user's search string once, then ask Postgres for the nearest neighbours with a metadata pre-filter. The pre-filter matters: restricting by region before the ANN search keeps the candidate set relevant and lets the planner use the partial index.

<?php
declare(strict_types=1);

final class SemanticVideoSearch
{
    public function __construct(
        private readonly PDO $pg,
        private readonly OpenAiClient $openai,
    ) {}

    /** @return list<array{video_id:int, distance:float}> */
    public function search(string $query, string $region, int $limit = 20): array
    {
        $vector = $this->openai->embed($query); // returns list<float>, 1536 dims
        $literal = $this->toVectorLiteral($vector);

        // ef_search trades recall for latency; set it per-connection.
        $this->pg->exec('SET hnsw.ef_search = 80');

        $sql = <<<SQL
            SELECT video_id, embedding <=> :vec AS distance
            FROM video_embeddings
            WHERE region = :region
              AND published_at > now() - interval '180 days'
            ORDER BY embedding <=> :vec
            LIMIT :limit
        SQL;

        $stmt = $this->pg->prepare($sql);
        $stmt->bindValue(':vec', $literal);
        $stmt->bindValue(':region', $region);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

        return array_map(
            static fn(array $r): array => [
                'video_id' => (int) $r['video_id'],
                'distance' => (float) $r['distance'],
            ],
            $stmt->fetchAll(PDO::FETCH_ASSOC),
        );
    }

    private function toVectorLiteral(array $vector): string
    {
        // pgvector accepts '[0.1,0.2,...]'. Bind as a string, cast in SQL if needed.
        return '[' . implode(',', array_map(
            static fn(float $v): string => rtrim(sprintf('%.6f', $v), '0'),
            $vector,
        )) . ']';
    }
}

One subtlety that bit me: ORDER BY embedding <=> :vec is what actually engages the HNSW index. If you order by a different expression — say a blended score computed in SQL — Postgres falls back to a sequential scan and re-computes distance for every row. Keep the ANN ordering pure, fetch an over-large candidate set, then re-rank in application code. That is exactly what the hybrid layer does.

Hybrid search: blend FTS5 keyword score with vector distance

Pure vector search has a failure mode that mirrors keyword search's: it is too fuzzy for exact intent. If someone searches a specific channel name or an exact title, BM25 nails it and embeddings wander off into vaguely-related territory. The fix is to run both engines and fuse the rankings. I use Reciprocal Rank Fusion (RRF) because it needs no score normalisation — it only cares about rank position, so a BM25 score and a cosine distance can be combined without pretending they live on the same scale.

<?php
declare(strict_types=1);

/**
 * Reciprocal Rank Fusion. k=60 is the value from the original Cormack et al.
 * paper and works well in practice — larger k flattens the contribution of
 * top ranks, smaller k sharpens it.
 *
 * @param list<int> $keywordRanked  video_ids from FTS5, best first
 * @param list<int> $vectorRanked   video_ids from pgvector, best first
 * @return list<int>                fused video_ids, best first
 */
function reciprocalRankFusion(
    array $keywordRanked,
    array $vectorRanked,
    int $k = 60,
): array {
    $scores = [];
    foreach ([$keywordRanked, $vectorRanked] as $ranking) {
        foreach ($ranking as $rank => $videoId) {
            $scores[$videoId] = ($scores[$videoId] ?? 0.0)
                + 1.0 / ($k + $rank + 1);
        }
    }
    arsort($scores);
    return array_keys($scores);
}

The flow per query:

Fire FTS5 (SQLite) and pgvector (Postgres) concurrently. In PHP that means kicking off the OpenAI embed + Postgres query while the local FTS5 query runs — the network round-trip dominates, so overlapping them hides most of the latency.
Take the top ~50 from each.
Fuse with RRF, then truncate to the page size.

This gives us exact-match precision from FTS5 and the cross-lingual recall from embeddings. The kitchen-fail query now surfaces 料理大失敗まとめ via the vector leg, while a search for an exact channel name still ranks that channel first via the keyword leg.

A Go worker for the re-embedding backlog

The Python cron handles incremental embedding fine, but when we change the composite text format (or bump the model) we need to re-embed the whole catalog — millions of rows. For that backfill I wrote a small concurrent Go worker, because the job is almost entirely I/O wait on the OpenAI API and Go's goroutines saturate the rate limit cleanly.

package main

import (
    "context"
    "sync"
    "time"

    "golang.org/x/time/rate"
)

// limiter caps us at the OpenAI tier rate; tune to your account's RPM.
var limiter = rate.NewLimiter(rate.Every(time.Minute/3000), 50)

type Job struct {
    VideoIDs []int64
    Texts    []string
}

func runWorkers(ctx context.Context, jobs <-chan Job, workers int,
    embed func(context.Context, []string) ([][]float32, error),
    store func(context.Context, []int64, [][]float32) error,
) {
    var wg sync.WaitGroup
    for i := 0; i < workers; i++ {
        wg.Add(1)
        go func() {
            defer wg.Done()
            for job := range jobs {
                if err := limiter.Wait(ctx); err != nil {
                    return
                }
                vecs, err := embed(ctx, job.Texts)
                if err != nil {
                    // log and skip; a later pass retries unembedded rows
                    continue
                }
                if err := store(ctx, job.VideoIDs, vecs); err != nil {
                    continue
                }
            }
        }()
    }
    wg.Wait()
}

A clean re-embed of our active catalog runs in under 20 minutes this way, bounded entirely by the rate limiter rather than CPU.

Tuning recall versus latency

Two knobs control the recall/latency tradeoff on the read path:

ef_construction (index build time, default 64). Higher builds a better graph but slower. 64 was fine for us; 128 gave marginal recall gains for double the build time.
hnsw.ef_search (query time, default 40). This is the one you tune live. At ef_search = 40 our p95 was ~8ms but recall@20 sat around 0.93. Bumping to 80 pushed recall to ~0.98 for ~14ms p95 — a trade I happily took, since the OpenAI embed call (~120ms) dwarfs the Postgres query anyway.

The lesson: do not micro-optimise the pgvector query when an API round-trip is your real latency budget. Which is why caching matters.

Cost and caching

Two costs to watch: the OpenAI bill and query latency.

Embedding cost. text-embedding-3-small is cheap — fractions of a cent per thousand short metadata strings. Embedding our whole active catalog costs a couple of dollars. The query-side embeds are the recurring cost, and they are tiny per call but add up at scale.
Cache the query embeddings. Search traffic is Zipfian — a small set of popular queries dominates. We cache query string -> embedding in our existing cache layer (and Cloudflare absorbs repeat full-result responses at the edge). That kills both the API cost and the 120ms round-trip for hot queries, which is most of them.
Normalise the cache key. Lowercase, trim, collapse whitespace, and Unicode-normalise (NFKC) before hashing — CJK input arrives in inconsistent width forms (full-width vs half-width), and skipping normalisation tanks your hit rate.

What I would do differently

Start with the hybrid layer, not pure vector. I shipped vector-only first and immediately regretted the regressions on exact-match queries. RRF should have been day one.
Store the source text from the start. Being able to re-embed without re-joining against SQLite saved me during the model-format change.
Watch the language distribution of misses, not just the volume. Our biggest wins were concentrated in Thai and Vietnamese queries, where our keyword tokenizer was weakest — embeddings quietly covered for tokenizer gaps I did not even know I had.

Semantic search did not replace FTS5 on TopVideoHub; it filled the holes FTS5 structurally cannot. Keyword search for precision, embeddings for meaning, RRF to fuse them, and a thin Postgres sidecar so none of it threatens the SQLite core. The kitchen-fail ticket is closed, and our zero-result rate dropped by roughly two thirds.

Implementing CSRF Double-Submit Cookies in a PHP Video Admin Panel

ahmet gedik — Sun, 07 Jun 2026 09:00:02 +0000

Last month a security researcher emailed me a proof-of-concept that quietly published a video to the front page of DailyWatch using nothing but an HTML form hosted on a throwaway domain. I was logged into the admin panel in another tab. He visited his page, my browser dutifully attached my session cookie, and his hidden form POSTed to /admin/videos/feature. No alert, no XSS, no stolen password — just a forged request riding on my already-authenticated session. That is CSRF, and if your video admin panel authorizes state-changing actions on the strength of a session cookie alone, you are exposed in exactly the same way.

This post walks through how we hardened the DailyWatch admin panel with the double-submit cookie pattern on PHP 8.4. It is the technique I reach for when a full server-side token store is more bookkeeping than the threat justifies, and it sits cleanly behind LiteSpeed and Cloudflare without forcing me to disable page caching everywhere. I will show the naive version, the signed HMAC version that closes a real subdomain hole, and the operational details that actually bit us in production.

Why I Skipped the Synchronizer Token Pattern

The textbook defense is the synchronizer token: generate a random value, stash it in the server-side session, embed it in every form, and compare the submitted value against the stored one on each request. It works, and for a stateful Rails-style app it is the right default.

It fit our admin panel badly for two concrete reasons.

First, caching. A meaningful chunk of DailyWatch is served straight from LiteSpeed's page cache, and the admin shell shares templates and partials with the public site. A synchronizer token is per-session state baked into HTML — the moment a token-bearing page lands in any shared cache, the token is either stale or shared across users. You end up carving cache exclusions around every fragment that touches the token. Double-submit needs zero server-side state: the cookie is the source of truth, and it travels with the user rather than with the cached HTML.

Second, multiple tabs. Editors at DailyWatch routinely keep three or four admin tabs open — one curating the homepage, one moderating comments, one editing channel metadata. Per-request synchronizer tokens that rotate aggressively turn that workflow into a parade of 403s. A cookie-scoped token with a sane TTL is stable across tabs by construction.

Double-submit trades a tiny amount of cryptographic care (which I will get to) for being effectively stateless. That trade is the whole point.

The Pattern in One Paragraph

The server sets a high-entropy random value in a cookie. Every state-changing request must echo that same value back — either in a request-body field or, for JSON APIs, in a custom request header. The server checks that the cookie value and the echoed value match. The security rests on the same-origin policy: an attacker's cross-site page can make the browser send your cookie along with a forged request, but it cannot read that cookie to copy the value into the body or a custom header. No read, no match, no forged action. That asymmetry is the entire defense.

Issuing the Token

Here is the minimal issuer. I generate 32 bytes of CSPRNG output, hex-encode it, and drop it in a cookie that JavaScript is allowed to read.

<?php
declare(strict_types=1);

final class Csrf
{
    private const COOKIE = 'dw_csrf';
    private const FIELD  = 'csrf_token';
    private const TTL    = 7200; // 2 hours

    public static function issue(): string
    {
        $token = bin2hex(random_bytes(32));
        setcookie(self::COOKIE, $token, [
            'expires'  => time() + self::TTL,
            'path'     => '/',
            'secure'   => true,   // HTTPS only — non-negotiable
            'httponly' => false,  // the front-end must read it to echo it back
            'samesite' => 'Lax',
        ]);
        return $token;
    }
}

Two decisions deserve a comment because they look wrong at first glance.

httponly is false on purpose. The whole mechanism depends on our own JavaScript reading the cookie to copy it into a header. That feels like it weakens things, but it does not: CSRF is not the same threat as XSS. If an attacker can run JavaScript on your origin (XSS), CSRF defenses are already moot — they can read any token regardless of the cookie flags. The httponly flag protects against script theft, which is an XSS concern, not a CSRF one.

secure is true and samesite is Lax. SameSite=Lax already blocks the most common cross-site POST, so think of double-submit as defense in depth layered on top of it, not a replacement. I will come back to why I do not rely on SameSite alone.

Call Csrf::issue() once per page render of the admin shell. If a valid dw_csrf cookie already exists you can skip re-issuing to keep multiple tabs in sync, or refresh the TTL — both are fine.

Validating Every State-Changing Request

Validation reads the cookie, reads whatever the client echoed back, and compares them in constant time.

public static function validate(): bool
{
    $cookie = $_COOKIE[self::COOKIE] ?? '';
    $sent   = $_POST[self::FIELD]
        ?? $_SERVER['HTTP_X_CSRF_TOKEN']
        ?? '';

    if ($cookie === '' || $sent === '') {
        return false;
    }

    // Constant-time compare. Never use == or === on a secret —
    // string comparison short-circuits and leaks length/timing.
    return hash_equals($cookie, $sent);
}

The hash_equals call matters more than it looks. A plain === returns as soon as it hits the first differing byte, which leaks timing information an attacker can in principle exploit to recover the value byte by byte. hash_equals compares the full length regardless. It is one function call; use it for every secret comparison you ever write.

Wire validation into the front controller so no route can forget it. The rule is simple: idempotent methods (GET, HEAD, OPTIONS) are exempt because they must not change state; everything else is checked.

$method = $_SERVER['REQUEST_METHOD'];

if (in_array($method, ['POST', 'PUT', 'PATCH', 'DELETE'], true)) {
    if (!Csrf::validate()) {
        http_response_code(403);
        header('Content-Type: application/json');
        echo json_encode(['error' => 'CSRF token mismatch']);
        exit;
    }
}

Putting this in the dispatcher rather than in each controller is the difference between a defense that holds and one that holds until someone adds a new endpoint and forgets the guard. The default must be secure; opting out (for a deliberately public webhook, say) must be the explicit, visible choice.

Wiring the Front-End

For classic form posts, render a hidden input with the token. For our JSON admin API, I prefer a custom header — it keeps the token out of request bodies and logs, and X-CSRF-Token is a request the browser will only let same-origin script set.

function csrfToken() {
  return document.cookie
    .split('; ')
    .find((c) => c.startsWith('dw_csrf='))
    ?.split('=')[1] ?? '';
}

async function adminPost(url, payload) {
  const res = await fetch(url, {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      'X-CSRF-Token': csrfToken(),
    },
    credentials: 'same-origin',
    body: JSON.stringify(payload),
  });

  if (res.status === 403) {
    throw new Error('CSRF rejected — reload the admin page to refresh the token');
  }
  return res.json();
}

There is a subtle reason the custom-header approach is strong on its own: a cross-origin fetch that sets a non-standard header like X-CSRF-Token triggers a CORS preflight, and absent a permissive CORS policy on your origin (you do not have one on an admin panel, right?) the browser blocks it before the real request ever fires. So for JSON endpoints the custom header is doing double duty. I still validate the double-submit match because forms and older flows fall back to the body field, and I want one uniform check.

The Hole in Plain Double-Submit, and the Signed Fix

Here is the part most tutorials skip, and it is the part that actually matters for a platform with subdomains.

Plain double-submit assumes the attacker cannot write your CSRF cookie. That assumption breaks if they control any sibling or subdomain that can set a cookie on your registrable domain — a technique called cookie tossing. If an attacker can get the browser to store a dw_csrf cookie scoped to .dailywatch.video with a value they chose, they can then forge a request whose body carries that same known value. Cookie and body match, validation passes, defense defeated. Shared hosting, a compromised marketing subdomain, or a wildcard you forgot about can all open this door.

The fix is to make the token unforgeable by binding it to the session with an HMAC. An injected cookie now fails the signature check because the attacker does not have the server secret.

final class SignedCsrf
{
    private const COOKIE = '__Host-dw_csrf';
    private const TTL    = 7200;

    public function __construct(private readonly string $secret) {}

    public function issue(string $sessionId): string
    {
        $random  = bin2hex(random_bytes(16));
        $expires = time() + self::TTL;
        $message = "{$sessionId}.{$random}.{$expires}";
        $sig     = hash_hmac('sha256', $message, $this->secret);
        $token   = "{$random}.{$expires}.{$sig}";

        setcookie(self::COOKIE, $token, [
            'expires'  => $expires,
            'path'     => '/',      // required for the __Host- prefix
            'secure'   => true,     // required for the __Host- prefix
            'httponly' => false,
            'samesite' => 'Lax',
            // note: NO 'domain' key — __Host- forbids it
        ]);
        return $token;
    }

    public function validate(string $sessionId, string $sent): bool
    {
        $cookie = $_COOKIE[self::COOKIE] ?? '';
        if ($cookie === '' || !hash_equals($cookie, $sent)) {
            return false;
        }

        [$random, $expires, $sig] = array_pad(explode('.', $sent), 3, '');
        if ((int) $expires < time()) {
            return false; // expired token
        }

        $expected = hash_hmac(
            'sha256',
            "{$sessionId}.{$random}.{$expires}",
            $this->secret
        );
        return hash_equals($expected, $sig);
    }
}

Two defenses are stacked here, and that is deliberate.

The HMAC binds the token to the current session id and an expiry, signed with a secret only the server knows. A tossed cookie carrying an attacker-chosen value will not carry a valid signature for my session, so validate rejects it. The expiry baked into the signed payload also gives you real, tamper-proof token lifetime rather than relying on the cookie's Max-Age, which the client controls.

The __Host- cookie name prefix is a browser-enforced belt to the HMAC's suspenders. A cookie named with the __Host- prefix is only accepted by the browser if it is Secure, has Path=/, and has no Domain attribute — which means it cannot be set from a different host and cannot be scoped to the parent domain. That structurally blocks subdomain cookie tossing at the source. Keep the secret out of your repo; on DailyWatch it lives in the environment, not in code.

SameSite, LiteSpeed, and Cloudflare Gotchas

The cryptography is the easy half. The operational edges are where I lost an afternoon.

Do not cache admin responses. LiteSpeed's CacheEnable public / is a blessing for the public site and a footgun for the admin panel. If a page that carries the token gets cached, you can pin every admin to one token, or worse cache a Set-Cookie. Exclude the admin path explicitly and send Cache-Control: no-store, private on every admin response. Double-submit still functions if the same token is shared (it only needs cookie and body to match), but caching Set-Cookie is a genuine cross-user leak.
Watch Cloudflare's Cache Everything. If you enable a Cache Everything page rule, make sure it bypasses on cookie and never applies to the admin path. Custom headers like X-CSRF-Token pass through Cloudflare untouched, so the header-based flow is safe across the proxy; cached HTML carrying a token is not.
SameSite=Lax is layered defense, not the whole wall. Lax blocks cross-site POSTs but still permits top-level cross-site GET navigations, and browser support and defaults vary across the long tail of clients you actually see in logs. Treat it as one layer. The double-submit check is what you can reason about deterministically.
Rotate on privilege change. Issue a fresh token on login and clear the cookie on logout. Binding the HMAC to the session id already invalidates old tokens when the session rotates, but clearing the cookie keeps things tidy and avoids confusing 403s after a re-login.
Mind clock skew on expiry. The signed token's expiry is checked server-side, so a single server clock governs it — no skew across a fleet to worry about, unlike client-side Max-Age.

Testing It

I do not trust a security control I have not watched fail and pass. A two-line curl harness covers the two cases that matter, and it drops straight into CI against a staging build.

# 1. Forged request with NO token must be rejected.
curl -s -o /dev/null -w "%{http_code}\n" \
  -X POST https://staging.dailywatch.video/admin/videos/feature \
  -b "session=valid-session-id" \
  -d "video_id=42"
# expected: 403

# 2. Legit request: echo the cookie token back in the header.
TOKEN=$(curl -s -c - https://staging.dailywatch.video/admin \
  | awk '/__Host-dw_csrf/ {print $7}')
curl -s -o /dev/null -w "%{http_code}\n" \
  -X POST https://staging.dailywatch.video/admin/videos/feature \
  -b "session=valid-session-id; __Host-dw_csrf=$TOKEN" \
  -H "X-CSRF-Token: $TOKEN" \
  -d "video_id=42"
# expected: 200

The first request proves the guard rejects the forged-form scenario that started this whole post. The second proves a legitimate same-origin flow still works. Add a third case that sends a mismatched token (cookie X, header Y) and assert 403 — that is the case a regression is most likely to silently break. Wire these into CI so a future refactor that drops the dispatcher guard fails the build instead of failing a user.

Conclusion

CSRF is unglamorous and easy to under-rate right up until someone publishes to your homepage from a domain you have never heard of. The double-submit cookie pattern gave us a defense that is stateless enough to coexist with aggressive LiteSpeed and Cloudflare caching, stable enough for editors juggling multiple tabs, and — in its signed form — robust against the subdomain cookie-tossing attack that plain double-submit quietly ignores.

If you take three things from this:

Validate in the dispatcher, not per controller, so secure is the default and opting out is explicit.
Always compare secrets with hash_equals, never ===.
If you have subdomains, use the signed HMAC variant with a __Host- cookie prefix — plain double-submit assumes an attacker cannot write your cookie, and that assumption is often false.

It is maybe a hundred lines of PHP. The researcher who reported our hole now gets a 403 for his trouble, which is exactly the result I was after.