Scaling Video Recommendations With Graph Traversal Queries

The architecture of modern video platforms relies heavily on how quickly and accurately they can surface relevant content to users. For years, engineering teams treated recommendation engines as standard database queries, relying on relational structures to map viewer behavior. As platforms expanded across multiple geographic regions and ingested millions of daily interactions, those traditional approaches began to fracture under the weight of complex self-joins and optimizer inconsistencies. The transition from a relational model to a graph-based architecture emerged not as a theoretical exercise, but as a necessary response to measurable performance degradation.

This article examines how a video streaming platform replaced a failing SQLite recommendation query with SurrealDB graph traversal. It details the schema design, multi-hop query execution, PHP integration, and operational lessons learned while scaling a discovery engine across eight regions.

Why did relational databases struggle with video discovery?

The initial implementation relied on a SQLite query featuring four self-joins over a views table. This approach functioned adequately when the platform managed a few hundred thousand view records. Performance remained stable, and the query planner consistently selected appropriate indexes. The architecture changed dramatically once the system crossed eight operational regions and began ingesting trending content through a scheduled cron process. The query responsible for identifying related content experienced a latency spike from thirty milliseconds to nine hundred milliseconds. The query planner began selecting incorrect indexes depending on which regional dataset held the most traffic. Every attempt to introduce region awareness required adding another join and another conditional filter. The optimizer struggled to reason through the expanding complexity.

The fundamental issue stems from treating recommendation logic as a relational problem. Video discovery operates as a web of typed relationships rather than a static set of rows. A viewer watches a video. A video belongs to a specific channel. A channel publishes content within a designated region. A video carries multiple topic tags. The desired recommendation requires walking across those edges. In a relational engine, this requires a self-join on a bridge table. Each additional hop demands another join. A graph database handles this through a path expression written once. The architectural shift collapses four separate joins into a single readable line.

How does a graph model resolve the scaling bottleneck?

SurrealDB models this structure directly by treating relationships as first-class edge records. Records live in tables, but connections are established using explicit relation definitions. The system supports arrow syntax to traverse these connections inline. The arrow direction indicates the edge orientation, allowing developers to chain arrows and express multi-hop paths without constructing complex join graphs. This single capability addresses the core scaling limitation that plagued the previous architecture. Three specific properties made the operational overhead of running a secondary datastore worthwhile.

Traversal cost scales proportionally to the edges being walked rather than the total table size. A two-hop recommendation touches the immediate neighborhood of a single video instead of scanning an entire views table. Edges carry their own data payload. A watched edge can store watch percentage, timestamp, and region information. This allows the system to weight recommendations based on actual engagement depth rather than treating a brief click the same as a complete viewing session. Queries also read like natural language questions. An engineer reviewing the traversal syntax can immediately understand the data flow without reverse-engineering a join graph.

Defining the schema prevents silent corruption during automated ingestion cycles. The platform runs a migration script that defines tables with schema enforcement. The video table stores metadata like title, channel, region, duration, and publication date. The viewer table tracks regional assignment. Edge tables for watched and tagged relationships are defined with strict type constraints and validated endpoints. This structure ensures that flaky cron runs reject malformed data instead of corrupting the graph. Writing an edge requires a specific relation statement. The system creates a record that links the viewer to the video while storing engagement metrics. The edge becomes traversable in both directions, enabling forward and backward path queries.

Executing the recommendation traversal

The replacement query eliminates the previous four-join structure by walking the graph in a single pass. The logic starts at the current video, moves backward to the viewers who engaged with it, and then moves forward to identify other videos those viewers watched. The system counts how often each target video appears and calculates an average engagement score. A region match applies a soft multiplier rather than acting as a hard filter. This deliberate design choice prevents users in smaller regions from receiving empty result sets. The grouping and scoring happen server-side during one network round trip. The application layer receives a ranked list of twelve rows instead of processing an unsorted result set.

The engagement filter performs critical work by excluding low-quality interactions. A co-view only counts if the co-viewer watched at least forty percent of the source video. Drive-by clicks do not pollute the recommendation pool. The region multiplier keeps global hits visible while favoring local content. The grouping and scoring happen server-side in one round trip. The application layer receives twelve ranked rows instead of a raw result set that requires client-side sorting. This architecture ensures that the recommendation rail remains responsive even as the underlying graph expands.

What architectural decisions shape the deployment?

The application layer communicates with the database through a structured HTTP client. The platform uses PHP eight point four and deploys over standard hosting environments. Installing a native database extension on every server is not feasible. The database exposes an SQL endpoint that accepts raw queries and returns JSON. The client handles authentication, sets the correct headers, and parses the response. The code enforces strict typing and uses constructor promotion to keep the implementation clean. A critical security practice involves preventing injection through record identifiers. User-supplied IDs are passed through a type conversion function so the database parses the value as data rather than query syntax. Treating a record link built from request input as a potential injection vector ensures long-term stability.

Ingestion relies on a Python cron job that batches writes across multiple regions. Sending individual relation statements over HTTP would overwhelm the system during peak traffic. The job constructs a transaction block that wraps the entire batch. This approach guarantees that a region either lands fully or not at all. Overlapping cron runs used to leave half-written view data that skewed recommendations for hours. Wrapping each batch in transaction boundaries eliminates that risk. The system retries the whole batch on failure, maintaining graph consistency. For teams evaluating similar infrastructure scaling patterns, Choosing the Right Infrastructure for AI Applications in 2026 provides additional context on balancing operational overhead with performance requirements.

A Go worker handles precomputation for trending videos. The live traversal runs in single-digit milliseconds for normal content, but the homepage displays recommendation rails for hundreds of trending videos per region. Computing those on every request wastes resources when the data barely changes between cron runs. The worker runs after each ingestion cycle, writes the top results back into the database as a materialized rail record, and allows the application layer to read a single row. The homepage read becomes a simple primary-key lookup. The expensive walk only executes for long-tail videos that fall outside the precomputed set. This split keeps the common case cheap while preserving accuracy for the tail.

The platform maintains a clear division of labor between storage engines. SQLite handles search, filtering, and text-based attributes. A graph database is the wrong tool for ranked text matching. The platform uses FTS five for full-text search because it is fast and requires zero operational overhead. Metadata goes to SQLite while watch edges go to the graph database. The two systems never need to join across the wire. The recommendation query returns video record identifiers, and the application hydrates display data from the relational store by ID. This primary-key lookup remains the cheapest read available. Maintaining this separation aligns with modern practices for Managing Context Integrity at the AI Agent Handoff, where distinct systems handle specialized data transformations without cross-store coupling.

Which operational pitfalls require careful mitigation?

Unbounded traversals will find the most popular video and never return. A globally trending video has hundreds of thousands of co-viewers. A naive traversal over that node walks an enormous neighborhood. The engagement filter and the limit clause are not optional optimizations. They are structural requirements that keep the query bounded. Schemaless writes from a flaky cron job silently rot the graph. A bug that wrote a percentage as a string instead of a float caused the aggregation function to skip rows without raising an error. Defining fields and asserting their ranges prevents this class of failure.

Treating region as a hard filter starves smaller geographic markets. Soft boosting with a multiplier was the single change that most improved perceived quality for users outside major markets. Materializing the hot set reduces homepage database time by more than half. The live traversal remains fast enough for the tail, but precomputing rails for trending videos cuts latency significantly. The conceptual shift matters more than the operational details. Recommendation stops being a query that requires constant tuning and becomes a graph that developers traverse. The four-join SQL that failed at eight regions is now a single arrow-path expression that reads like the question it answers.

Conclusion

The evolution of video discovery architectures demonstrates that scaling recommendation engines requires matching the data model to the problem domain. Relational databases excel at structured metadata and full-text search, but they struggle when recommendation logic demands repeated multi-hop path resolution. Moving the relationship graph to a dedicated system allows traversal costs to scale with neighborhood size rather than total table volume. The separation of concerns between search, metadata, and relationship mapping creates a resilient foundation. Engineering teams that treat recommendations as edges to walk rather than joins to tune will find their queries shortening as requirements grow more complex. The operational lessons around transaction boundaries, schema enforcement, and precomputation provide a practical blueprint for platforms navigating similar scaling challenges.