Last quarter I hit a wall. Our recommendation query on TrendVidStream had grown into a 140-line SQL monstrosity with seven self-joins on a video_relations table, three correlated subqueries, and a GROUP BY clause that made the SQLite query planner cry. The goal was simple: given a video the user just watched in Singapore, find videos that are (a) watched by viewers who also watched this one, (b) from channels in the same regional cluster, (c) within two hops of a topical tag the user has engaged with, and (d) currently trending in at least one of our eight regions. In relational terms, that is a multi-hop traversal with filtering at every hop. In graph terms, it is a single Cypher query.
I run TrendVidStream, a multi-region video streaming discovery site that pulls from eight YouTube regional pools (US, GB, JP, KR, TW, SG, VN, TH, HK rotating) and serves PHP 8.4 over LiteSpeed with SQLite FTS5 as the primary store. SQLite is fantastic for read-heavy workloads, but graph traversals are not its sweet spot. I spent two weekends moving the relationship layer to Apache AGE — the PostgreSQL extension that adds openCypher to a relational database — and the rewrite turned a 380ms query into a 22ms one. This post is what I learned, including the parts that bit me.
