Building Video Recommendation Graphs With Apache AGE and Cypher

The join that killed our "related videos" panel

DailyWatch surfaces trending and evergreen videos to a global audience, and the single most valuable piece of real estate on a watch page is the "related videos" rail. For a long time that rail was powered by a SQLite query that joined a videos table against a video_tags bridge table, counted shared tags, and ordered by the overlap. It worked until it didn't.

The problem is that "related" is rarely one hop deep. A user watching a cooking video does not just want other videos with the same tags — they want videos watched by people who watched this video, videos from channels that frequently co-appear in the same sessions, and videos two or three hops away in a co-view network. Expressing "videos co-watched with videos co-watched with X, excluding what X's channel already published" in SQL means a stack of self-joins, temporary tables, and recursive CTEs that nobody on the team wanted to touch. Each new relationship type added another join and another 40ms to the query.

That is a graph problem wearing a relational costume. This post walks through how I prototyped the co-view network on DailyWatch using Apache AGE, the PostgreSQL extension that adds openCypher graph queries on top of a normal Postgres database, so we could keep our existing relational tables and add graph traversal without standing up a separate Neo4j cluster.

Why Apache AGE instead of a dedicated graph database

Before writing a line of Cypher I made a short list of what actually mattered for a small team running a free product on a tight budget:

• No new operational surface. We already run Postgres for a couple of internal services. AGE is an extension (CREATE EXTENSION age;), not a new server to monitor, back up, and secure.
• Mixed workloads in one place. I can join a graph traversal result against a plain relational videos table in a single statement. A separate graph database would force a network round trip and application-side joins.
• openCypher is portable. If we ever outgrow AGE, the Cypher queries mostly transfer to Neo4j or Memgraph. The mental model does not get thrown away.
• SQL escape hatch. AGE returns graph query results as a set of agtype columns you can wrap in a normal SELECT, so existing tooling, connection pools, and PHP PDO code keep working.

The honest tradeoff: AGE is not as fast as a purpose-built graph engine at very deep traversals over hundreds of millions of edges, and its query planner is younger than Postgres core's. For a co-view network of a few million videos and edges, where most useful queries are two to three hops, it is more than enough. Our production discovery stack still leans on SQLite FTS5 behind LiteSpeed and Cloudflare for full-text search; AGE is a complementary system for relationship queries, not a replacement for search.

Modeling videos and channels as a graph

The first step is deciding what the nodes and edges are. For the co-view network I settled on:

• Nodes: Video (with id, title, channel_id, views) and Channel (with id, name).
• Edges: PUBLISHED from a Channel to a Video, and CO_VIEWED between two Video nodes carrying a weight property — how many sessions watched both.

After installing AGE and loading it into the session, you create a graph namespace and start defining vertices and edges with Cypher. AGE ships a cypher() function that you call from inside a SQL statement.

-- Load the extension and put its operators on the search path
LOAD 'age';
SET search_path = ag_catalog, "$user", public;

-- One graph namespace holds all our video relationships
SELECT create_graph('dailywatch');

-- Create a couple of channels and videos
SELECT * FROM cypher('dailywatch', $$
    CREATE (c:Channel {id: 'ch_101', name: 'WeeknightKitchen'}),
           (v1:Video {id: 'vid_a1', title: '15-Minute Garlic Noodles',
                      channel_id: 'ch_101', views: 48210}),
           (v2:Video {id: 'vid_b2', title: 'One-Pan Lemon Chicken',
                      channel_id: 'ch_101', views: 33190}),
           (c)-[:PUBLISHED]->(v1),
           (c)-[:PUBLISHED]->(v2)
$$) AS (result agtype);

A few things worth calling out. The graph name ('dailywatch') is passed as a string to every cypher() call, and the trailing AS (result agtype) is mandatory — AGE forces you to declare the shape of the returned columns in SQL, because SQL is statically typed and Cypher is not. If your RETURN clause yields three values, you declare three columns.

Loading edges in bulk from your relational tables

You almost never build the graph by hand. In our case the co-view weights are computed by a nightly batch job that reads watch sessions and produces pairs of video ids with a count. The cleanest way to get that data into AGE is to generate the co-view edges from a query, matching existing Video nodes by id and creating the relationship between them.

Here is the Python loader I use. It reads pre-aggregated co-view pairs and issues one MERGE per pair so the load is idempotent — rerunning it updates weights instead of duplicating edges.

import psycopg2

def load_coview_edges(conn, pairs):
    """pairs: iterable of (video_id_a, video_id_b, weight)"""
    cur = conn.cursor()
    cur.execute("LOAD 'age';")
    cur.execute('SET search_path = ag_catalog, "$user", public;')

    cypher = """
        SELECT * FROM cypher('dailywatch', $$
            MATCH (a:Video {id: $aid}), (b:Video {id: $bid})
            MERGE (a)-[e:CO_VIEWED]->(b)
            SET e.weight = $w
            RETURN e
        $$, %s) AS (edge agtype);
    """

    for a_id, b_id, weight in pairs:
        params = psycopg2.extras.Json(
            {"aid": a_id, "bid": b_id, "w": weight}
        )
        cur.execute(cypher, (params,))

    conn.commit()
    cur.close()


if __name__ == "__main__":
    conn = psycopg2.connect("dbname=discovery user=age_writer")
    sample = [
        ("vid_a1", "vid_b2", 512),
        ("vid_a1", "vid_c3", ើ 331),
        ("vid_b2", "vid_c3", 198),
    ]
    load_coview_edges(conn, sample)

The parameter passing here is the part people trip over. AGE lets you pass a single SQL parameter into a Cypher query — a JSON object — and reference its keys inside Cypher as $aid, $bid, and $w. You must pass it as a JSON value (psycopg2's Json adapter), and the placeholder count in SQL (%s) has to match. Do not try to string-format video ids directly into the Cypher text; that path is both a SQL injection risk and a quoting nightmare.

For a first bulk load of millions of edges, per-row MERGE is too slow. In that situation I stage the pairs in a plain relational table and use AGE's load_edges_from_file helpers, or generate the graph inside a single Cypher statement that UNWINDs a list. The per-row loop above is what I run for the incremental nightly deltas, which are only a few thousand changed pairs.

Querying the co-view network

Now the payoff. The "related videos" rail becomes a two-hop traversal: start at the video being watched, walk CO_VIEWED edges, and rank the destinations by edge weight, excluding videos from the same channel so the rail does not fill up with the current creator's back catalog.

SELECT * FROM cypher('dailywatch', $$
    MATCH (start:Video {id: 'vid_a1'})-[e:CO_VIEWED]->(rec:Video)
    WHERE rec.channel_id <> start.channel_id
    RETURN rec.id, rec.title, e.weight
    ORDER BY e.weight DESC
    LIMIT 12
$$) AS (video_id agtype, title agtype, weight agtype);

That single query replaced roughly 40 lines of SQL with three self-joins. But the real win is depth. Suppose a freshly ingested video has almost no direct co-views yet — a cold-start problem. We can reach through one intermediate video to find second-degree neighbors and still surface something relevant:

SELECT * FROM cypher('dailywatch', $$
    MATCH (start:Video {id: 'vid_new'})
          -[:CO_VIEWED]->(mid:Video)
          -[:CO_VIEWED]->(rec:Video)
    WHERE rec.id <> start.id
      AND rec.channel_id <> start.channel_id
    RETURN rec.id, rec.title, count(*) AS paths
    ORDER BY paths DESC
    LIMIT 12
$$) AS (video_id agtype, title agtype, paths agtype);

The count(*) here counts how many distinct two-hop paths reach each candidate, which is a surprisingly good relevance signal: a video reachable through many different intermediates is broadly related, not a fluke of one weird session. Trying to write that path-counting logic in relational SQL means a recursive CTE plus manual cycle detection. In Cypher it is one MATCH pattern.

Wiring it into a PHP watch page

On the application side, AGE results come back as agtype, which is a superset of JSON. From PHP 8.4 I query it over PDO exactly like any other Postgres result and decode the values. Because agtype string values arrive wrapped in quotes, I strip them before use.

<?php
declare(strict_types=1);

final class RelatedVideos
{
    public function __construct(private readonly \PDO $pdo) {}

    /** @return list<array{id:string,title:string,weight:int}> */
    public function forVideo(string $videoId, int $limit = 12): array
    {
        $this->pdo->exec("LOAD 'age'");
        $this->pdo->exec('SET search_path = ag_catalog, "$user", public');

        $sql = <<<SQL
            SELECT * FROM cypher('dailywatch', \$\$
                MATCH (s:Video {id: \$vid})-[e:CO_VIEWED]->(r:Video)
                WHERE r.channel_id <> s.channel_id
                RETURN r.id, r.title, e.weight
                ORDER BY e.weight DESC
                LIMIT \$lim
            \$\$, :params) AS (id agtype, title agtype, weight agtype)
        SQL;

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':params' => json_encode([
                'vid' => $videoId,
                'lim' => $limit,
            ], JSON_THROW_ON_ERROR),
        ]);

        $out = [];
        foreach ($stmt->fetchAll(\PDO::FETCH_NUM) as $row) {
            $out[] = [
                'id'     => trim((string) $row[0], '"'),
                'title'  => trim((string) $row[1], '"'),
                'weight' => (int) $row[2],
            ];
        }

        return $out;
    }
}

A couple of production notes from wiring this up behind LiteSpeed and Cloudflare:

• Cache the rail, not the query. The related-videos rail changes slowly — new co-view weights land once a night. We render it into the watch page HTML and let LiteSpeed's page cache and Cloudflare hold it for hours. AGE only gets hit on a cache miss, which keeps the extension well away from the hot path.
• Read replica for graph reads. The nightly loader writes to the primary; the watch pages read from a replica. Graph traversals are read-heavy and benefit from being isolated from write contention.
• Set a statement timeout. A malformed or unexpectedly deep traversal can run long. SET statement_timeout = '250ms' on the connection used for page rendering means a pathological query fails fast instead of holding a worker.

Indexing so traversals stay fast

The naive graph query above does a label scan to find the start node. On a graph of any size you want an index on the property you look nodes up by. AGE stores each vertex label as its own table under the graph's schema, so you index it like any Postgres table, reaching into the properties column with the same agtype operators Cypher uses.

-- Index the id property of Video vertices for fast MATCH lookups
CREATE INDEX video_id_idx
    ON dailywatch."Video"
    USING btree (agtype_access_operator(properties, '"id"'::agtype));

-- And an index on the CO_VIEWED edge weight for ORDER BY
CREATE INDEX coview_weight_idx
    ON dailywatch."CO_VIEWED"
    USING btree (agtype_access_operator(properties, '"weight"'::agtype));

Without the first index, every MATCH (v:Video {id: ...}) scans the whole Video table. With it, the start-node lookup is a single index probe and the traversal only touches the edges connected to that node. On our dataset that took the two-hop related query from roughly 180ms to under 15ms. Run EXPLAIN on the wrapping SQL statement to confirm the planner actually uses the index — because the graph query is generated inside cypher(), you occasionally need to nudge it by making sure the property access expression in your index exactly matches what the query generates.

What I would watch out for

A few sharp edges I hit that are worth knowing before you commit:

• agtype quoting leaks everywhere. String values come back JSON-quoted. Build one decode helper and route every result through it, or you will scatter trim($x, '"') across your codebase.
• The single-parameter rule. You get exactly one SQL parameter into a Cypher call, and it has to be a JSON object. Design your loaders around passing a map, not positional args.
• Planner immaturity on deep queries. Three hops is fine. Once you go four or five hops over a dense graph, test with real data volumes — the plan can degrade, and you may need to restructure the pattern or add intermediate WITH clauses to bound the working set.
• Version pinning matters. AGE tracks specific Postgres major versions closely. Pin both in your infrastructure so an unplanned Postgres upgrade does not leave the extension unbuildable.
• It is a complement, not a replacement. Full-text discovery still belongs in a search engine. AGE is for the relationship questions that search cannot answer cheaply.

Conclusion

The co-view network on DailyWatch stopped being a pile of self-joins the moment we modeled it as an actual graph. Apache AGE let us do that without adopting a whole new database — the graph lives next to our relational tables, speaks openCypher, and returns results we can join and cache with the tooling we already run. The wins were concrete: the related-videos query dropped from dozens of lines of SQL to a single readable pattern, cold-start videos get sensible recommendations through two-hop traversals, and with the right property indexes the traversals run in single-digit milliseconds behind our existing cache layers.

If you have a "things related to things related to things" problem trapped inside recursive CTEs, try modeling it as nodes and edges in AGE before you stand up a separate graph cluster. Start with one graph namespace, load a slice of your data, write the traversal you have been dreading in SQL, and measure. The Cypher version is almost always shorter, and more importantly, it is the version you will still understand six months from now.