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.
