๐ฆ๐๐ผ๐ฝ ๐๐ผ๐ฟ๐ฐ๐ถ๐ป๐ด ๐๐ฟ๐ฎ๐ฝ๐ต ๐ค๐๐ฒ๐ฟ๐ถ๐ฒ๐ ๐๐ป๐๐ผ ๐ฅ๐ฒ๐น๐ฎ๐๐ถ๐ผ๐ป๐ฎ๐น ๐๐ฎ๐๐ฎ๐ฏ๐ฎ๐๐ฒ๐
Our recommendation logic used to be a 280-line SQLite query with six self-joins.
We wanted to answer one question: what should a user watch next?
To do this, we had to find videos watched by people who watched the current video. We also needed to factor in recency and region.
The real problem was the engine. We tried to force a graph problem through a relational model.
The results were poor:
- Queries took 400ms on a warm cache.
- Queries timed out on a cold cache.
- Adding one new signal required another join and added 50ms of latency.
Recommendations are inherently a graph problem. Users, videos, creators, and tags are nodes. Actions like "watched" or "tagged" are edges.
We moved this subsystem to SurrealDB. We kept the rest of our stack on PHP 8.4 and SQLite.
The results changed immediately:
- The next-video query dropped to 12-30ms.
- Adding new ranking signals became easy.
- We improved our GDPR compliance by using pseudonymous session tokens instead of personal data.
How it works:
In SQL, you need multiple joins through a "watches" table. In a graph database, you use a single traversal. The edges are the index.
SurrealDB allows us to:
- Store properties directly on edges (like completion rate and region).
- Use schemafull tables to enforce data types.
- Run as a single binary without heavy managed infrastructure.
We also simplified our privacy approach. We do not store personal identifiers on the graph. We use rotating session tokens with a hard expiration. This follows the principle of data minimization.
The technical takeaway:
If you have a query that uses many joins to follow relationships, you are modeling a graph problem relationally. You pay for that mismatch on every single page view.
Do not try to optimize a bad model in place. Isolate that specific subsystem, reshape it as a graph, and watch your latency drop.
Source: https://dev.to/ahmet_gedik778845/using-surrealdb-graph-queries-to-power-video-recommendations-303c