Semantic Search Over Video Metadata With pgvector and OpenAI Embeddings

A visitor typed space documentary with good narration into the search box and got an empty result page. The catalog had 41 videos that matched that description almost perfectly — Cosmos re-uploads, BBC Earth clips, a dozen astrophotography timelapses with narration credits in the description. Not one of them shared a meaningful token with the query. SQLite FTS5 did exactly what a lexical index is built to do, and still returned nothing.

That blank page is the reason this post exists. I run TrendVidStream, a multi-region streaming discovery site: PHP 8.4 on shared LiteSpeed hosting, a single SQLite file as the primary store, an FTS5 external-content table for search, a cron that pulls trending metadata from 8 regions on a staggered schedule, and deploys that happen over FTP with lftp mirror. No Composer on the remote, no Docker, no queue broker. Adding a vector index to that stack is less a database decision than a logistics problem.

Why FTS5 falls over on discovery queries

FTS5 is genuinely good. BM25 ranking, prefix queries, snippet() highlighting, zero operational cost, and it lives inside the same file I already deploy. It handles the query class "user knows the title" better than any vector index I tested. The failures are narrow and predictable:

• Vocabulary mismatch. space documentary vs. a title reading The Universe In 4K — Full Episode. Zero token overlap, zero results.
• Intent queries. something to fall asleep to, videos like Kurzgesagt, short funny clips. These describe a property of the content, not its wording.
• Cross-language drift. With 8 regions in the pipeline, roughly a third of my rows have titles in Japanese, Korean, German, or Portuguese while a large slice of queries arrive in English. FTS5's default tokenizer will not bridge that.
• Truncated descriptions. YouTube descriptions are half boilerplate. The signal is in the first two sentences; BM25 sees the whole soup, including "subscribe" and affiliate links, and weights accordingly.

What I wanted was to keep FTS5 for what it is good at and add a second retriever for everything else.

The deploy constraints shaped the architecture

I checked phpinfo() on all four hosts. Two of them do not have pdo_pgsql compiled in, and shared hosting is not where you go to request extensions. That killed the obvious design where PHP talks to Postgres directly.

So the split ended up like this:

• A small VPS runs Postgres 17 with pgvector 0.8. This is the only new piece of infrastructure.
• A Python ingest worker on the VPS reads video metadata, builds a document per video, embeds it, and upserts into Postgres. It runs on the same 2h/4h/7h stagger as the site crons so it never fights them for API quota.
• A Go query service on the same VPS exposes one endpoint: embed the query, run the ANN search, return IDs and scores. It caches query embeddings, because the embedding API call — not Postgres — is the latency floor.
• PHP 8.4 on each site calls that endpoint with a 250 ms timeout, merges the returned IDs with local FTS5 results, and hydrates rows from SQLite. If the call fails, the page renders FTS5-only results and nobody notices.

The FTP deploy never touches the vector path. public/ and app/ ship as before; the search service is deployed with git pull && systemctl restart. That separation was worth more to me than any latency I gave up to the extra hop.

Document construction beats model selection

I burned two days tuning ef_search and comparing text-embedding-3-small against 3-large before I accepted the boring truth: what you feed the embedder matters far more than which embedder you use.

My first version embedded title + " " + description. Recall was mediocre, and the failure mode was obvious once I looked at neighbors — videos clustered by description boilerplate, not by content. Three unrelated gaming clips came out as near-duplicates because all three descriptions ended with the same 200-word sponsor block.

The rules that actually moved the numbers:

• Strip URLs, hashtag walls, "subscribe to my channel", and social handles before embedding.
• Truncate the description to ~600 characters. The tail is almost never signal.
• Include channel name and category as labeled fields. Channel: NASA is a strong semantic prior.
• Include at most 8 tags. Beyond that, tags are keyword spam and they blur the vector.
• Use a stable labeled template, not concatenation. Title: ...\nChannel: ... outperformed raw concatenation on my eval set, consistently.
• Do not embed view counts, IDs, durations, or region codes. Those belong in SQL predicates, not in the vector.

import hashlib
import re
import time

import psycopg
from openai import OpenAI

DOC_TEMPLATE_VERSION = "v3"
MODEL = "text-embedding-3-small"
DIMS = 512
BATCH = 128

BOILERPLATE = re.compile(
    r"(subscribe to (my|our) channel|follow me on \w+|https?://\S+|#\w+)",
    re.IGNORECASE,
)
client = OpenAI()


def build_doc(row: dict) -> str:
    desc = BOILERPLATE.sub(" ", row.get("description") or "")
    desc = re.sub(r"\s+", " ", desc).strip()[:600]
    tags = [t for t in (row.get("tags") or "").split(",") if t][:8]

    parts = [
        f"Title: {row['title']}",
        f"Channel: {row['channel_title']}",
        f"Category: {row['category_name']}",
        f"Tags: {', '.join(tags)}" if tags else "",
        f"About: {desc}" if desc else "",
    ]
    return "\n".join(p for p in parts if p)


def doc_hash(doc: str) -> str:
    key = f"{DOC_TEMPLATE_VERSION}|{MODEL}|{DIMS}|{doc}"
    return hashlib.sha256(key.encode("utf-8")).hexdigest()


def embed_batch(docs: list[str]) -> list[list[float]]:
    for attempt in range(5):
        try:
            resp = client.embeddings.create(model=MODEL, input=docs, dimensions=DIMS)
            return [d.embedding for d in resp.data]
        except Exception:
            if attempt == 4:
                raise
            time.sleep(2 ** attempt)  # 429 and 5xx both land here
    raise RuntimeError("unreachable")


def to_literal(vec: list[float]) -> str:
    return "[" + ",".join(f"{v:.6f}" for v in vec) + "]"


def sync(conn: psycopg.Connection, rows: list[dict]) -> int:
    with conn.cursor() as cur:
        cur.execute("SELECT video_id, doc_hash FROM video_embeddings")
        known = dict(cur.fetchall())

    pending = []
    for row in rows:
        doc = build_doc(row)
        h = doc_hash(doc)
        if known.get(row["video_id"]) != h:
            pending.append((row["video_id"], doc, h, row["regions"]))

    written = 0
    for i in range(0, len(pending), BATCH):
        chunk = pending[i:i + BATCH]
        vectors = embed_batch([c[1] for c in chunk])
        params = [
            (c[0], c[2], to_literal(vec), c[3])
            for c, vec in zip(chunk, vectors)
        ]
        with conn.cursor() as cur:
            cur.executemany(
                """
                INSERT INTO video_embeddings
                    (video_id, doc_hash, embedding, regions, updated_at)
                VALUES (%s, %s, %s::halfvec, %s, now())
                ON CONFLICT (video_id) DO UPDATE SET
                    doc_hash  = EXCLUDED.doc_hash,
                    embedding = EXCLUDED.embedding,
                    regions   = EXCLUDED.regions,
                    updated_at = now()
                """,
                params,
            )
        conn.commit()
        written += len(chunk)
    return written

The hash is the load-bearing part. Video metadata churns — titles get edited, descriptions get sponsor blocks appended — but on a typical 2-hour cycle only ~3% of rows actually change. Hashing the rendered document rather than the source row means cosmetic upstream changes cost nothing, and putting the template version and model into the hash means that when I change either, everything re-embeds on the next run without me writing a migration. I learned that one the hard way after silently mixing v1 and v2 documents in the same index for a week and wondering why recall had gotten worse.

Schema, dimensions, and the index

I use dimensions=512 instead of the native 1536. text-embedding-3-small is Matryoshka-trained, so you can request a truncated embedding directly from the API and it stays useful. On my 120-query eval set the drop from 1536 to 512 cost about 1.5 points of recall@10 and cut index size by two-thirds. Combined with halfvec (16-bit floats) that is a 6x reduction against vector(1536) — 61k rows fit in roughly 60 MB of index instead of 380 MB, which means it stays in the page cache of a small VPS.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE video_embeddings (
    video_id     text          PRIMARY KEY,
    doc_hash     text          NOT NULL,
    embedding    halfvec(512)  NOT NULL,
    regions      text[]        NOT NULL DEFAULT '{}',
    published_at timestamptz,
    updated_at   timestamptz   NOT NULL DEFAULT now()
);

SET maintenance_work_mem = '2GB';

CREATE INDEX video_embeddings_hnsw
    ON video_embeddings
    USING hnsw (embedding halfvec_cosine_ops)
    WITH (m = 16, ef_construction = 96);

CREATE INDEX video_embeddings_regions
    ON video_embeddings USING gin (regions);

-- query side
SET LOCAL hnsw.ef_search = 100;
SET LOCAL hnsw.iterative_scan = 'relaxed_order';

SELECT video_id,
       1 - (embedding <=> $1::halfvec) AS score
FROM video_embeddings
WHERE regions && ARRAY[$2]::text[]
  AND published_at > now() - interval '18 months'
ORDER BY embedding <=> $1::halfvec
LIMIT 40;

Three notes on that, each of which cost me an evening:

• maintenance_work_mem decides whether your build takes 4 minutes or 40. If HNSW construction spills to disk, pgvector logs it. Watch for that line.
• The ORDER BY must use the distance operator directly against a parameter. Wrap it in a CTE, sort by an alias, or apply any expression around it, and the planner quietly drops to a sequential scan. EXPLAIN is not optional here; check for Index Scan using video_embeddings_hnsw.
• Region filtering is where naive HNSW goes wrong. A single-region site filters out ~85% of rows. With a plain ANN scan, Postgres fetches 40 neighbors, throws away 34 of them post-filter, and hands you 6 results. pgvector 0.8's iterative scan fixes this by re-entering the index until it has enough rows that survive the filter. Set hnsw.iterative_scan to relaxed_order for top-k search; strict_order is slower and I could not measure a quality difference at k=40.

The Go query service

Query-side latency is dominated by the embedding API call, not by Postgres. The ANN scan over 61k rows runs in 3–7 ms. The embedding round trip is 90–140 ms at p50. Since search queries follow a brutal power law — my top 200 queries account for over 60% of traffic — an in-process cache erases most of that.

package main

import (
    "context"
    "encoding/json"
    "net/http"
    "strconv"
    "strings"
    "sync"
    "time"

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

type Hit struct {
    VideoID string  `json:"video_id"`
    Score   float64 `json:"score"`
}

type cached struct {
    vec []float32
    exp time.Time
}

type Server struct {
    db    *pgxpool.Pool
    embed func(context.Context, string) ([]float32, error)

    mu    sync.RWMutex
    cache map[string]cached
}

func (s *Server) queryVector(ctx context.Context, q string) ([]float32, error) {
    key := strings.ToLower(strings.TrimSpace(q))

    s.mu.RLock()
    c, ok := s.cache[key]
    s.mu.RUnlock()
    if ok && time.Now().Before(c.exp) {
        return c.vec, nil
    }

    vec, err := s.embed(ctx, key)
    if err != nil {
        return nil, err
    }
    s.mu.Lock()
    s.cache[key] = cached{vec: vec, exp: time.Now().Add(6 * time.Hour)}
    s.mu.Unlock()
    return vec, nil
}

func literal(vec []float32) string {
    var b strings.Builder
    b.WriteByte('[')
    for i, v := range vec {
        if i > 0 {
            b.WriteByte(',')
        }
        b.WriteString(strconv.FormatFloat(float64(v), 'f', 6, 32))
    }
    b.WriteByte(']')
    return b.String()
}

// pool config sets hnsw.ef_search + iterative_scan in AfterConnect,
// so every pooled session already has the right GUCs.
func (s *Server) handleSearch(w http.ResponseWriter, r *http.Request) {
    ctx, cancel := context.WithTimeout(r.Context(), 200*time.Millisecond)
    defer cancel()

    q := r.URL.Query()
    if strings.TrimSpace(q.Get("q")) == "" {
        http.Error(w, "missing q", http.StatusBadRequest)
        return
    }
    k, _ := strconv.Atoi(q.Get("k"))
    if k <= 0 || k > 100 {
        k = 40
    }

    vec, err := s.queryVector(ctx, q.Get("q"))
    if err != nil {
        http.Error(w, "embed failed", http.StatusBadGateway)
        return
    }

    rows, err := s.db.Query(ctx, `
        SELECT video_id, 1 - (embedding <=> $1::halfvec) AS score
        FROM video_embeddings
        WHERE regions && ARRAY[$2]::text[]
        ORDER BY embedding <=> $1::halfvec
        LIMIT $3`, literal(vec), q.Get("region"), k)
    if err != nil {
        http.Error(w, "query failed", http.StatusBadGateway)
        return
    }
    defer rows.Close()

    hits := make([]Hit, 0, k)
    for rows.Next() {
        var h Hit
        if err := rows.Scan(&h.VideoID, &h.Score); err != nil {
            http.Error(w, "scan failed", http.StatusInternalServerError)
            return
        }
        hits = append(hits, h)
    }

    w.Header().Set("Content-Type", "application/json")
    _ = json.NewEncoder(w).Encode(map[string]any{"hits": hits})
}

The map-with-mutex cache is deliberately dumb. It is bounded in practice by query diversity and a 6-hour TTL, and it holds about 40k entries at 512 float32s each — roughly 80 MB worst case. If that ever became a problem I would swap in an LRU, but measuring first told me it would not.

Hybrid ranking in PHP 8.4

Vector-only search regressed on exact-title queries, which are the ones users are most annoyed to see fail. The fix is Reciprocal Rank Fusion: merge the two ranked lists by 1 / (k + rank) with k = 60. It needs no score normalization — which matters, because BM25 scores and cosine similarities are not on comparable scales and any attempt to normalize them across queries is guesswork.

<?php
declare(strict_types=1);

final class HybridVideoSearch
{
    private const int RRF_K = 60;
    private const int TIMEOUT_MS = 250;

    public function __construct(
        private readonly PDO $sqlite,
        private readonly string $vectorEndpoint,
        private readonly string $region,
    ) {}

    /** @return list<array{video_id:string, score:float, sources:list<string>}> */
    public function search(string $query, int $limit = 20): array
    {
        $lists = [
            'fts' => $this->fts5($query, 40),
            'vec' => $this->semantic($query, 40), // [] when the service is slow or down
        ];

        $scores = [];
        foreach ($lists as $source => $ids) {
            foreach ($ids as $rank => $id) {
                $scores[$id]['score'] ??= 0.0;
                $scores[$id]['score'] += 1 / (self::RRF_K + $rank + 1);
                $scores[$id]['sources'][] = $source;
            }
        }
        uasort($scores, static fn (array $a, array $b): int => $b['score'] <=> $a['score']);

        $out = [];
        foreach (array_slice($scores, 0, $limit, preserve_keys: true) as $id => $row) {
            $out[] = [
                'video_id' => (string) $id,
                'score'    => $row['score'],
                'sources'  => $row['sources'],
            ];
        }
        return $out;
    }

    /** @return list<string> */
    private function fts5(string $query, int $limit): array
    {
        $terms = preg_split('/[^\p{L}\p{N}]+/u', $query, -1, PREG_SPLIT_NO_EMPTY) ?: [];
        if ($terms === []) {
            return [];
        }
        // quote every term so FTS5 operators in user input cannot reach the parser
        $match = implode(' OR ', array_map(
            static fn (string $t): string => '"' . $t . '"*',
            $terms,
        ));

        $st = $this->sqlite->prepare(
            'SELECT video_id FROM videos_fts
             WHERE videos_fts MATCH :m
             ORDER BY bm25(videos_fts, 8.0, 1.0)
             LIMIT :l'
        );
        $st->bindValue(':m', $match);
        $st->bindValue(':l', $limit, PDO::PARAM_INT);
        $st->execute();

        return $st->fetchAll(PDO::FETCH_COLUMN);
    }

    /** @return list<string> */
    private function semantic(string $query, int $limit): array
    {
        $url = $this->vectorEndpoint . '?' . http_build_query([
            'q'      => $query,
            'region' => $this->region,
            'k'      => $limit,
        ]);

        $ch = curl_init($url);
        curl_setopt_array($ch, [
            CURLOPT_RETURNTRANSFER    => true,
            CURLOPT_TIMEOUT_MS        => self::TIMEOUT_MS,
            CURLOPT_CONNECTTIMEOUT_MS => 80,
        ]);
        $raw = curl_exec($ch);
        $ok  = $raw !== false && curl_getinfo($ch, CURLINFO_RESPONSE_CODE) === 200;
        curl_close($ch);

        if (!$ok) {
            return []; // degrade to FTS5-only, no error page
        }

        try {
            $decoded = json_decode((string) $raw, true, 512, JSON_THROW_ON_ERROR);
        } catch (JsonException) {
            return [];
        }
        return array_column($decoded['hits'] ?? [], 'video_id');
    }
}

The bm25(videos_fts, 8.0, 1.0) weights matter: column 1 is the title, column 2 the description, and weighting the title 8x pushed a lot of junk out of the lexical list before fusion ever saw it. Also note the escaping in fts5() — unquoted user input goes straight to the FTS5 query parser, where a stray " or NEAR is a syntax error and a 500 page.

What the numbers actually looked like

I built a 120-query eval set by hand: real queries from the site's search log, each with 1–5 video IDs I judged relevant. Small, biased toward the cases I already knew were broken, and absolutely not a benchmark — but it is my traffic, which is the only distribution I care about.

• FTS5 only — recall@10 0.41, MRR 0.29
• Vector only — recall@10 0.68, MRR 0.51
• RRF hybrid — recall@10 0.79, MRR 0.61

Cost was the surprise. Backfilling 61,000 unique videos at ~180 tokens per document is roughly 11M tokens — about $0.22 one-time with text-embedding-3-small. Incremental churn runs ~900 documents a day, so ongoing embedding cost is well under a dollar a month. Query embeddings are ~8 tokens each with a 60–70% cache hit rate; they round to zero. The real cost is the VPS.

End-to-end p95 from PHP, measured at the controller: 210 ms on a cache miss, 34 ms on a hit. The 250 ms timeout fires on roughly 0.4% of requests, and those users get FTS5 results rather than an error.

Things I would do differently

• Start at 512 dimensions. I backfilled at 1536 first, then re-embedded everything a week later. That is entirely avoidable planning.
• Version the document template from day one. Mixing template versions in one index is a silent quality bug with no error message attached.
• Keep FTS5. It is not a fallback I tolerate; it is half the ranking signal and it is the half that handles exact lookups.
• Test a multilingual open-weights model. With a third of the catalog in non-English titles, running something like bge-m3 locally on the same VPS may beat the API on quality for my specific mix, and removes the network call entirely.

Conclusion

The part I underestimated was document construction. Choosing pgvector over a dedicated vector database, tuning m and ef_search, picking between HNSW and IVFFlat — all of that moved recall by single-digit percentages. Stripping boilerplate from descriptions and adding labeled channel and category fields moved it by twenty points. If you are adding semantic search to an existing catalog, spend your first day printing out the exact strings you are about to embed and reading them. You will find sponsor blocks, and you will find that removing them was the whole job.

And keep the lexical index. Hybrid retrieval on a small, honest eval set beat every single-retriever configuration I tried, and it degrades gracefully when the network does what networks do.