Querying Video Relationships with Apache AGE on Postgres
Relational databases struggle with deep relationships.
At TrendVidStream, we manage a catalog of streaming titles across eight regions. We originally used SQLite with FTS5. It worked well for full-text search and simple deployments.
The problem started when users asked complex questions. They wanted to know: "What else is two or three hops away from this thriller with this specific director and these actors?"
In SQL, this requires many self-joins. Each extra hop makes the query harder to maintain and slower to run. The join fan-out becomes a nightmare.
We solved this by adding a graph layer using the Apache AGE extension on PostgreSQL.
Here is why this split works:
• You keep PostgreSQL. You use the same connections, transactions, and backup tools. You do not need a separate database like Neo4j. • You mix graph and relational data. You can run Cypher queries and join the results with standard SQL tables. • You handle variable depth easily. Finding connections across one to three hops is a simple command in Cypher. • You maintain simplicity. The graph is a projection of your data, not a separate source of truth.
Our model uses four vertex labels:
- Video
- Person
- Platform
- Region
We use specific edge types like SIMILAR_TO, WORKED_ON, and AVAILABLE_IN.
One critical lesson: always index your entry points. We index the ext_id property on Video vertices. Without this, every traversal starts with a slow sequential scan.
The result is a clean, readable query for recommendations:
SELECT * FROM cypher('discovery', $$ MATCH (seed:Video {ext_id: 'vid_8842'}) MATCH (seed)-[:SIMILAR_TO|WORKED_ON*1..3]-(rec:Video)-[:AVAILABLE_IN]->(:Region {code: 'DE'}) WHERE rec.ext_id <> 'vid_8842' RETURN DISTINCT rec.ext_id, rec.title, rec.year LIMIT 24 $$) AS (ext_id agtype, title agtype, year agtype);
This query finds related videos within three hops that are available in Germany. In SQL, this would be a massive pile of joins. In Cypher, it is a simple pattern.
We kept SQLite for text search and used AGE for connectivity. Each tool does what it does best.