Modeling Video Recommendations With Apache AGE and Cypher Graph Queries

The recursive join that killed our recommendation endpoint

Our "related videos" sidebar started as three lines of SQL. A video belongs to categories, shares tags with other videos, and appears in the same regional trend windows. Rank by overlap, return the top eight. Fine.

Then product asked for something that sounds simple and is not: "Show videos two hops away — things that co-trend with videos that co-trend with this one, but that the viewer has not already been recommended this week." That is a graph traversal wearing a relational costume. In SQLite it became a self-join stacked on a self-join, a WITH RECURSIVE block nobody on the team wanted to touch, and a p95 that drifted from 40ms to 900ms as the trend graph grew. We run ViralVidVault, a GDPR-compliant European viral-video discovery service, and the main application is deliberately boring: PHP 8.4, SQLite in WAL mode, LiteSpeed in front, Cloudflare Workers at the edge. Boring is a feature. But recursive relationship queries are the one workload where a relational engine fights you the whole way, so we carved out a single subsystem and moved it to PostgreSQL with the Apache AGE graph extension. This is what that looked like in practice — the schema, the Cypher queries, and the boring glue that keeps it in sync with the SQLite source of truth.

Why a graph engine instead of another CTE

The honest framing: recursive CTEs work. The problem is not correctness, it is that every additional hop multiplies the join fan-out, and the query planner has no notion of "a path." You end up materializing intermediate rows that you immediately throw away. A property graph engine models edges as first-class objects, so a two- or three-hop traversal is a bounded walk instead of a Cartesian explosion.

Apache AGE ("A Graph Extension") is a PostgreSQL extension that adds the openCypher query language on top of a normal Postgres database. That last part is what sold me: it is not a separate database process to operate, back up, and monitor. It is CREATE EXTENSION age; inside a Postgres you already know how to run. Our edge functions and PHP already speak Postgres wire protocol through PDO, so there was no new client library to vet for GDPR data-processing concerns.

What we specifically wanted from a graph model:

• Variable-length paths — -[*1..3]-> expresses "one to three hops" in one clause.
• Typed edges with properties — a CO_TRENDS edge can carry a weight and a region, so a French co-trend and a German co-trend are different edges, not a join condition.
• Cheap "not already seen" filtering — exclude nodes reachable through a RECOMMENDED edge in the last seven days, in the same query.

The thing I did not want was to rewrite the whole app. So AGE runs as a read-optimized projection. SQLite stays the write path and the source of truth.

Setting up AGE and the graph schema

AGE ships as a Postgres extension. On a fresh Postgres 16 you install the matching AGE build, then bootstrap the graph. AGE stores each named graph in its own schema, and every query has to load the extension and set the search path — that per-session ritual trips up everyone the first time.

-- One-time bootstrap
CREATE EXTENSION IF NOT EXISTS age;
LOAD 'age';
SET search_path = ag_catalog, "$user", public;

SELECT create_graph('vvv_graph');

-- Vertex + edge label creation is optional (AGE creates on first use),
-- but declaring labels up front lets us index them.
SELECT create_vlabel('vvv_graph', 'Video');
SELECT create_vlabel('vvv_graph', 'Category');
SELECT create_elabel('vvv_graph', 'IN_CATEGORY');
SELECT create_elabel('vvv_graph', 'CO_TRENDS');
SELECT create_elabel('vvv_graph', 'RECOMMENDED');

Our node and edge model is small on purpose:

• Video vertices carry video_id (the same ID as in SQLite), title, region, and published_at.
• Category vertices carry a slug.
• IN_CATEGORY edges connect a video to its categories.
• CO_TRENDS edges connect two videos that appeared in the same regional trend window, weighted by how many windows they shared.
• RECOMMENDED edges are written when we serve a video to a viewer segment, with a timestamp, so we can subtract already-seen items.

One detail that matters for GDPR: there is no personal data in this graph. RECOMMENDED edges point at segments ("DE / mobile / evening"), never at users. The graph answers "what relates to what," not "who watched what," which keeps it entirely out of scope for personal-data retention rules — a deliberate design constraint, not an afterthought.

Indexing is plain Postgres. AGE stores vertex properties in a JSONB-ish column, so you index the extracted property:

-- Index the video_id property so lookups by our SQLite ID are fast.
CREATE INDEX idx_video_id
  ON vvv_graph."Video"
  USING btree (ag_catalog.agtype_access_operator(properties, '"video_id"'::agtype));

CREATE INDEX idx_cotrends_weight
  ON vvv_graph."CO_TRENDS"
  USING btree (ag_catalog.agtype_access_operator(properties, '"weight"'::agtype));

Writing the graph queries in Cypher

Here is the query that started this whole migration — related videos up to three hops away through co-trending, excluding anything recommended to the same segment in the last week. In a recursive CTE this was 60-plus lines. In Cypher it is a single readable pattern.

AGE wraps Cypher inside a cypher() function call, and you have to declare the shape of the returned columns. That wrapper is ugly but mechanical:

LOAD 'age';
SET search_path = ag_catalog, "$user", public;

SELECT * FROM cypher('vvv_graph', $$
  MATCH (start:Video {video_id: 'yt_A1b2C3'})
        -[:CO_TRENDS*1..3]->(rec:Video)
  WHERE rec.region IN ['DE', 'FR', 'NL']
    AND NOT EXISTS {
      MATCH (:Segment {key: 'de_mobile_evening'})
            -[r:RECOMMENDED]->(rec)
      WHERE r.served_at > (timestamp() - 604800000)
    }
  WITH rec, count(*) AS path_count
  RETURN rec.video_id AS video_id,
         rec.title    AS title,
         path_count
  ORDER BY path_count DESC
  LIMIT 8
$$) AS (video_id agtype, title agtype, path_count agtype);

The [:CO_TRENDS*1..3] is doing the heavy lifting: it walks one to three CO_TRENDS edges without you writing a single join. path_count is a crude but effective relevance signal — a video reachable by many distinct short paths is more strongly related than one reachable by a single long path.

A second query we lean on is the "bridge" query: find videos that connect two otherwise-separate trend clusters. Editorially, these are gold — they are the videos crossing over from, say, a German gaming cluster into a French music cluster, which is exactly the viral cross-pollination our European focus cares about.

SELECT * FROM cypher('vvv_graph', $$
  MATCH (a:Category {slug: 'gaming'})<-[:IN_CATEGORY]-(v:Video)
        -[:IN_CATEGORY]->(b:Category {slug: 'music'})
  MATCH (v)-[c:CO_TRENDS]->(:Video)
  WITH v, sum(c.weight) AS bridge_strength
  WHERE bridge_strength > 5
  RETURN v.video_id AS video_id, bridge_strength
  ORDER BY bridge_strength DESC
  LIMIT 20
$$) AS (video_id agtype, bridge_strength agtype);

Note that agtype return values come back JSON-encoded — a string is "yt_A1b2C3" with the quotes, a number is bare. You strip that on the client side, which the PHP layer handles below.

Calling AGE from PHP 8.4

The application layer stays PHP. We talk to Postgres through PDO exactly like any other database, with two wrinkles: every connection has to LOAD 'age' and set the search path, and the returned agtype values need parsing. We wrap both in a small class so the rest of the codebase never sees AGE-specific noise.

<?php
declare(strict_types=1);

final class GraphClient
{
    private \PDO $pdo;

    public function __construct(string $dsn, string $user, string $pass)
    {
        $this->pdo = new \PDO($dsn, $user, $pass, [
            \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
            \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
            \PDO::ATTR_PERSISTENT         => true, // reuse the AGE-primed session
        ]);
        // Prime the session once; persistent connections keep this state.
        $this->pdo->exec("LOAD 'age'");
        $this->pdo->exec('SET search_path = ag_catalog, "$user", public');
    }

    /**
     * @return list<array<string,mixed>>
     */
    public function relatedVideos(string $videoId, string $segment, array $regions): array
    {
        // Cypher params can't be bound as native PDO placeholders inside
        // the $$...$$ body, so we build a params JSON and pass it as $1.
        $sql = <<<SQL
            SELECT * FROM cypher('vvv_graph', \$\$
              MATCH (start:Video {video_id: \$vid})
                    -[:CO_TRENDS*1..3]->(rec:Video)
              WHERE rec.region IN \$regions
                AND NOT EXISTS {
                  MATCH (:Segment {key: \$seg})-[r:RECOMMENDED]->(rec)
                  WHERE r.served_at > (timestamp() - 604800000)
                }
              WITH rec, count(*) AS path_count
              RETURN rec.video_id AS video_id, rec.title AS title, path_count
              ORDER BY path_count DESC LIMIT 8
            \$\$, \$1) AS (video_id agtype, title agtype, path_count agtype)
        SQL;

        $params = json_encode([
            'vid'     => $videoId,
            'seg'     => $segment,
            'regions' => array_values($regions),
        ], JSON_THROW_ON_ERROR);

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$params]);

        return array_map(
            static fn (array $row): array => [
                'video_id'   => self::decodeAgtype($row['video_id']),
                'title'      => self::decodeAgtype($row['title']),
                'path_count' => (int) self::decodeAgtype($row['path_count']),
            ],
            $stmt->fetchAll()
        );
    }

    /** agtype scalars are JSON-encoded; strings arrive quoted. */
    private static function decodeAgtype(?string $raw): mixed
    {
        if ($raw === null) {
            return null;
        }
        return json_decode($raw, true, 512, JSON_THROW_ON_ERROR);
    }
}

Passing Cypher parameters as a single JSON object bound to $1 is the part the AGE docs bury. You cannot sprinkle ? placeholders inside the $$...$$ body because that body is opaque to PDO — it is a string argument to cypher(). Building a params JSON keeps the query injection-safe without string-concatenating user input into Cypher, which matters because video_id can ultimately trace back to a URL parameter.

Keeping the graph in sync with SQLite

SQLite is still the write path. Every fetch cron run inserts trending videos, tags, and regional windows into SQLite in WAL mode. The graph is a downstream projection rebuilt incrementally. We do not dual-write from the request path — that would couple request latency to Postgres availability and defeat the point of keeping the front door boring.

Instead a small worker reads the SQLite change log (we keep a graph_outbox table populated by triggers) and replays it into AGE. The MERGE clause is idempotent, so replaying the same event twice is harmless — important when a worker crashes mid-batch.

import json
import sqlite3
import psycopg2

SQLITE_PATH = "/var/data/vvv.sqlite"
PG_DSN = "dbname=vvv_graph user=vvv host=127.0.0.1"


def prime(cur):
    cur.execute("LOAD 'age'")
    cur.execute('SET search_path = ag_catalog, "$user", public')


def upsert_cotrend(cur, a_id: str, b_id: str, weight: int, region: str):
    # MERGE makes the replay idempotent: same event twice == no-op.
    cypher = """
        SELECT * FROM cypher('vvv_graph', $$
          MERGE (a:Video {video_id: $a})
          MERGE (b:Video {video_id: $b})
          MERGE (a)-[e:CO_TRENDS {region: $region}]->(b)
          SET e.weight = $weight
        $$, $1) AS (v agtype)
    """
    params = json.dumps({"a": a_id, "b": b_id, "weight": weight, "region": region})
    cur.execute(cypher, (params,))


def drain_outbox():
    lite = sqlite3.connect(SQLITE_PATH)
    lite.row_factory = sqlite3.Row
    pg = psycopg2.connect(PG_DSN)
    cur = pg.cursor()
    prime(cur)

    rows = lite.execute(
        "SELECT id, payload FROM graph_outbox "
        "WHERE processed = 0 ORDER BY id LIMIT 500"
    ).fetchall()

    for row in rows:
        event = json.loads(row["payload"])
        if event["type"] == "co_trends":
            upsert_cotrend(
                cur, event["a"], event["b"], event["weight"], event["region"]
            )
        lite.execute("UPDATE graph_outbox SET processed = 1 WHERE id = ?", (row["id"],))

    pg.commit()
    lite.commit()
    print(f"drained {len(rows)} events")


if __name__ == "__main__":
    drain_outbox()

The worker runs every few minutes from the same cron that fetches trending videos. If Postgres is down, the outbox rows simply stay processed = 0 and drain when it recovers. The SQLite side never blocks, so an AGE outage degrades the recommendation sidebar to a simpler SQLite fallback rather than taking the site down.

Caching the results at the edge

Graph traversals are cheaper than recursive CTEs but they are not free — a three-hop query on a hot node still costs single-digit to low-double-digit milliseconds. Since related-videos results only change when the graph changes (every few minutes at most), they cache beautifully. Cloudflare Workers already sit in front of every route, so the related-videos fragment gets an edge cache keyed by video_id plus segment, with a short TTL and stale-while-revalidate so a viewer never waits on a cold graph query.

export default {
  async fetch(request, env, ctx) {
    const url = new URL(request.url);
    if (!url.pathname.startsWith("/api/related/")) {
      return fetch(request);
    }

    const cache = caches.default;
    const cacheKey = new Request(url.toString(), request);
    let response = await cache.match(cacheKey);
    if (response) return response;

    response = await fetch(request);
    if (response.ok) {
      response = new Response(response.body, response);
      // Graph changes at most every few minutes; serve stale while we refresh.
      response.headers.set(
        "Cache-Control",
        "public, max-age=180, stale-while-revalidate=600"
      );
      ctx.waitUntil(cache.put(cacheKey, response.clone()));
    }
    return response;
  },
};

With the edge cache in place, the AGE database only sees traffic for cold keys — typically a few queries per second even during a viral spike, which a modest Postgres instance handles without breaking a sweat.

What I would tell you before you migrate

A few hard-won notes after running this in production for a European viral-video workload:

• Do not move your whole app. AGE earns its keep on genuinely recursive, path-shaped queries. Everything else stayed in SQLite and is better for it. A graph engine is a specialized tool, not a general upgrade.
• Treat the graph as a projection. Making SQLite the source of truth and AGE a rebuildable read model meant an AGE outage is a degraded feature, not an incident.
• The agtype tax is real. Every query returns JSON-encoded values you must decode, and every session needs LOAD 'age'. Wrap it once, forget it forever.
• Index the extracted property, not the vertex. AGE won't use an index on the raw properties column; you index agtype_access_operator(properties, ...) explicitly.
• Cap your hop count. *1..3 is deliberate. Unbounded * traversals on a dense trend graph will find you eventually, and not in a good way.
• Keep personal data out. Modeling RECOMMENDED against segments rather than users kept the graph free of personal data, which is one less system in GDPR scope.

The recursive join that started this is gone. The two-hop query that used to hit 900ms now returns in the low teens of milliseconds cold, and single digits warm behind the edge cache. More importantly, the query is readable — the next engineer who needs to add a fourth relationship type edits a Cypher pattern instead of decoding a recursive CTE. For one narrow, graph-shaped subsystem sitting beside an otherwise deliberately boring PHP-and-SQLite stack, Apache AGE was exactly the right amount of new technology.