๐๐๐ถ๐น๐ฑ๐ถ๐ป๐ด ๐ฉ๐ถ๐ฑ๐ฒ๐ผ ๐ฅ๐ฒ๐ฐ๐ผ๐บ๐บ๐ฒ๐ป๐ฑ๐ฎ๐๐ถ๐ผ๐ป๐ ๐๐ถ๐๐ต ๐ฆ๐๐ฟ๐ฟ๐ฒ๐ฎ๐น๐๐
Our related videos feature broke. We used SQLite with four self-joins. It worked for small data. It failed when we added more regions. Query time jumped from 30ms to 900ms.
Recommendation is a graph problem. SQL treats it like a table problem. Finding viewers who watched similar videos requires hops. In SQL, every hop is a new join. In a graph database, it is one path.
We kept SQLite for text search. We moved recommendations to SurrealDB. SurrealDB uses edges to connect data. You use arrow syntax to traverse edges. One line of code replaced four SQL joins.
Why this works:
- Speed depends on the edges you walk.
- It does not depend on table size.
- Edges store data like watch percentage.
- Queries are easy to read.
Key lessons for production:
- Use filters. Only count views over 40 percent. This stops noise.
- Use soft multipliers. Multiply local scores by 1.5. Hard filters leave empty rails.
- Precompute hot data. Store results for trending videos to save time.
- Secure inputs. Use type::thing() to stop injection.
Our architecture is simple:
- SQLite handles search and metadata.
- SurrealDB handles relationships.
The logic is now a graph traversal. The query is shorter. The performance is faster. Stop using self-joins for recommendations. Use edges.