Using Apache AGE for Graph-Based Video Relationship Queries in Postgres

The "related videos" panel is a graph problem wearing a table costume

Every viral-video product eventually hits the same wall. You have a videos table, a channels table, maybe a tags join table, and a product manager who wants a "more like this" rail that actually feels good. So you write a self-join: same channel, overlapping tags, published in the last 30 days, order by view velocity. It works for a week.

Then the questions get harder. "Show me videos that share an audience with this one, even if they have zero tags in common." "Which channels are two hops away from the creators our German users watch most?" "If this video goes viral, which three channels historically ride the wave next?" Each of these is a traversal — you follow edges, not columns. On a relational store, every extra hop is another join, and by hop three your query plan is a smoking crater.

At ViralVidVault, our primary store is SQLite in WAL mode — fast, embedded, and perfect for the read-heavy pages that Cloudflare Workers cache at the edge. But SQLite is exactly the wrong tool for multi-hop relationship queries. Rather than bolt a graph model onto a store that hates it, we run a small PostgreSQL instance with the Apache AGE extension purely for relationship queries, and keep SQLite as the source of truth for everything a page actually renders. This post is how that split works, with the real Cypher, Python, and PHP 8.4 we run in production.

Why Apache AGE instead of a dedicated graph database

Apache AGE ("A Graph Extension") is a Postgres extension that adds openCypher query support and graph storage on top of a normal Postgres database. The pitch that won me over: I already know how to operate Postgres. I do not want to run, back up, monitor, and patch a separate Neo4j cluster for one feature.

Concretely, AGE gives us:

• Cypher inside SQL. You call cypher('graph_name', $$ ... $$) from a normal SELECT, so every Postgres tool — psql, pg_dump, connection poolers, your existing PHP PDO driver — just works.
• One backup story. The graph lives in Postgres. pg_dump covers it. No second disaster-recovery runbook.
• Transactional writes. Ingestion runs in the same transaction as any relational bookkeeping we want alongside it.
• A migration escape hatch. If we ever outgrow it, the data is Cypher-shaped already and ports to another openCypher engine.

The trade-off is honest: AGE is not as fast as a purpose-built native graph engine at very deep traversals, and its planner is younger. For our workload — graphs in the low millions of edges, traversals of two to four hops — it is comfortably fast enough, and the operational simplicity is worth more to a small team than raw hop throughput.

Modeling the video graph

We keep the graph deliberately thin. It stores relationships and just enough properties to filter on, not the full video record. The heavy fields — title, thumbnail, description, transcript — stay in SQLite. The graph answers "which ids relate and how," then we hydrate those ids from SQLite for rendering.

Our node and edge types:

• Nodes: Video, Channel, Tag, Region (EU countries — we are a European product, so region is a first-class citizen).
• Edges: PUBLISHED_BY (Video→Channel), TAGGED (Video→Tag), TRENDING_IN (Video→Region), and the interesting one, CO_WATCHED (Video→Video), a weighted edge we compute from anonymized session co-occurrence.

That last edge is where the GDPR discipline shows up. CO_WATCHED is derived from aggregate counts across many sessions — never from an individual user's watch history, and never keyed to a user id. The graph literally cannot store a personal profile because there is no person node. More on that below.

Setting up the graph is a one-time migration:

-- Run once, as a superuser or a role with AGE granted.
CREATE EXTENSION IF NOT EXISTS age;
LOAD 'age';
SET search_path = ag_catalog, "$user", public;

SELECT create_graph('vvv_relations');

-- Cypher runs inside a SQL SELECT. The $$...$$ block is the Cypher body.
SELECT * FROM cypher('vvv_relations', $$
  CREATE (v:Video   {video_id: 'yt_9bZkp7q19f0', velocity: 0.0})
  CREATE (c:Channel {channel_id: 'ch_officialartist'})
  CREATE (r:Region  {code: 'DE'})
  CREATE (v)-[:PUBLISHED_BY]->(c)
  CREATE (v)-[:TRENDING_IN {rank: 3}]->(r)
$$) AS (result agtype);

-- AGE creates a table per label under the graph's schema; index the
-- lookup property you filter on constantly.
CREATE INDEX video_id_idx
  ON vvv_relations."Video"
  USING btree (agtype_access_operator(properties, '"video_id"'::agtype));

That index matters more than anything else in this post. Without it, every Cypher match that starts from a specific video does a sequential scan of the Video table, and your traversal latency is dominated by finding the start node. With it, the anchor lookup is a b-tree hit and the traversal starts immediately.

Loading relationships from SQLite into AGE

Ingestion runs as a scheduled job. It reads the current relationship state out of SQLite, then upserts nodes and edges into AGE. I use Python with psycopg (v3) because the ingestion logic is easier to read than the equivalent PHP, and it runs off the request path anyway.

The pattern that keeps this idempotent is MERGE — Cypher's get-or-create. Running the job twice does not duplicate nodes or edges; it just refreshes properties.

import sqlite3
import psycopg  # psycopg 3

GRAPH = "vvv_relations"

def fetch_cowatched(sqlite_path: str):
    """Aggregate co-watch counts. Note: grouped across ALL sessions,
    never keyed to a user. The GROUP BY is the anonymization."""
    db = sqlite3.connect(sqlite_path)
    db.row_factory = sqlite3.Row
    rows = db.execute(
        """
        SELECT a.video_id AS src, b.video_id AS dst, COUNT(*) AS weight
        FROM session_views a
        JOIN session_views b
          ON a.session_hash = b.session_hash
         AND a.video_id < b.video_id
        GROUP BY a.video_id, b.video_id
        HAVING weight >= 5           -- k-anonymity floor: drop rare pairs
        """
    ).fetchall()
    db.close()
    return rows

def upsert_cowatched(pg_dsn: str, edges) -> None:
    with psycopg.connect(pg_dsn) as conn:
        with conn.cursor() as cur:
            cur.execute("LOAD 'age';")
            cur.execute('SET search_path = ag_catalog, "$user", public;')
            for e in edges:
                # Parameters are inlined into the Cypher body as literals.
                # video_id values are opaque platform ids, not user data.
                cypher = f"""
                SELECT * FROM cypher('{GRAPH}', $$
                  MERGE (s:Video {{video_id: '{e['src']}'}})
                  MERGE (d:Video {{video_id: '{e['dst']}'}})
                  MERGE (s)-[r:CO_WATCHED]->(d)
                  SET r.weight = {int(e['weight'])}
                $$) AS (result agtype);
                """
                cur.execute(cypher)
        conn.commit()

if __name__ == "__main__":
    edges = fetch_cowatched("/var/data/vvv.sqlite")
    upsert_cowatched("postgresql://vvv:***@127.0.0.1/vvv_graph", edges)
    print(f"upserted {len(edges)} co-watch edges")

Two things worth calling out. First, the HAVING weight >= 5 clause is a k-anonymity floor — a co-watch pair that only shows up in a handful of sessions never enters the graph, which keeps us from encoding anything that could single out a small group. Second, AGE's Cypher does not bind parameters the way a normal SQL driver does; you build the Cypher body as text. Because every value here is a platform-issued opaque id or an integer we cast with int(), there is no user-controlled string reaching the query. If you ever interpolate anything user-supplied, you must escape it yourself — treat this exactly like building raw SQL.

Querying the graph from PHP 8.4

On the render side, PHP asks the graph a question, gets back a set of video_ids with scores, and hydrates the full records from SQLite. AGE returns results as agtype, which is JSON-compatible, so PDO hands you strings you can json_decode.

Here is the real "related videos" query. It blends two signals: videos sharing tags, and videos that are co-watched, weighted so co-watch dominates because it reflects actual behavior.

<?php
declare(strict_types=1);

final class VideoGraph
{
    public function __construct(private readonly \PDO $pg) {}

    /** @return list<array{video_id:string, score:float}> */
    public function related(string $videoId, int $limit = 12): array
    {
        // AGE requires LOAD + search_path per session; a persistent
        // pooled connection amortizes this across requests.
        $this->pg->exec("LOAD 'age'");
        $this->pg->exec('SET search_path = ag_catalog, "$user", public');

        // $videoId is a platform id we validate against SQLite before
        // this call, but we still hard-restrict its shape defensively.
        if (!preg_match('/^[A-Za-z0-9_\-]{1,64}$/', $videoId)) {
            return [];
        }

        $cypher = <<<CYPHER
        SELECT * FROM cypher('vvv_relations', \$\$
          MATCH (v:Video {video_id: '{$videoId}'})-[r:CO_WATCHED]-(rec:Video)
          RETURN rec.video_id AS video_id, r.weight AS w
          ORDER BY w DESC
          LIMIT {$limit}
        \$\$) AS (video_id agtype, w agtype);
        CYPHER;

        $stmt = $this->pg->query($cypher);
        $out = [];
        foreach ($stmt as $row) {
            // agtype comes back JSON-quoted, e.g. "\"yt_abc\"" and 42
            $id = json_decode((string) $row['video_id']);
            $w  = (float) json_decode((string) $row['w']);
            if (is_string($id)) {
                $out[] = ['video_id' => $id, 'score' => $w];
            }
        }
        return $out;
    }
}

// Usage on the watch page:
$graph   = new VideoGraph($pgConnection);
$related = $graph->related('yt_9bZkp7q19f0', 12);
$ids     = array_column($related, 'video_id');
// Hydrate titles/thumbnails from SQLite WAL — the render source of truth.
$videos  = $sqliteRepo->findManyById($ids);

The division of labor is the whole point: Postgres/AGE answers which videos relate and in what order, SQLite answers what those videos are. The graph query touches a few hundred rows; the SQLite hydration is a single indexed IN (...) lookup that Cloudflare Workers then cache at the edge for the next visitor.

The queries that justify the whole thing

A single-hop co-watch lookup is nice, but you could fake it with a materialized table. The reason to run a real graph engine is the query you cannot reasonably express in SQL. Here is our "trend cascade" query: find channels that are two hops away through co-watched videos and are currently trending in the same EU region — the channels that historically ride the wave after a given video pops.

SELECT * FROM cypher('vvv_relations', $$
  MATCH (seed:Video {video_id: 'yt_9bZkp7q19f0'})
        -[:TRENDING_IN]->(region:Region)
  MATCH (seed)-[c:CO_WATCHED]-(bridge:Video)-[:PUBLISHED_BY]->(chan:Channel)
  MATCH (bridge)-[:TRENDING_IN]->(region)
  WHERE c.weight >= 20
  RETURN chan.channel_id AS channel, region.code AS region,
         count(bridge)   AS shared_videos,
         sum(c.weight)   AS total_weight
  ORDER BY total_weight DESC
  LIMIT 10
$$) AS (channel agtype, region agtype, shared_videos agtype, total_weight agtype);

Read what that traversal does: start at a video, find the EU regions it is trending in, walk co-watch edges to bridge videos, jump to the channels that published them, and keep only bridges also trending in the same region. Then aggregate. Expressing that as relational joins means three self-joins plus two joins to the trending table plus a group-by, and the planner has no good way to prune early. In Cypher it is five lines and the engine prunes as it walks. That gap — readability and an execution model that fits the shape of the question — is the entire argument for AGE.

A quick worker that consumes these results and pushes trending channels into a queue looks like this in Go, which we use for the off-request background pipeline:

package main

import (
    "context"
    "encoding/json"
    "fmt"

    "github.com/jackc/pgx/v5"
)

func cascadeChannels(ctx context.Context, conn *pgx.Conn, videoID string) ([]string, error) {
    _, _ = conn.Exec(ctx, "LOAD 'age'")
    _, _ = conn.Exec(ctx, `SET search_path = ag_catalog, "$user", public`)

    q := fmt.Sprintf(`SELECT * FROM cypher('vvv_relations', $$
        MATCH (s:Video {video_id: '%s'})-[c:CO_WATCHED]-(b:Video)-[:PUBLISHED_BY]->(ch:Channel)
        WHERE c.weight >= 20
        RETURN ch.channel_id AS channel ORDER BY c.weight DESC LIMIT 25
    $$) AS (channel agtype);`, videoID)

    rows, err := conn.Query(ctx, q)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var channels []string
    for rows.Next() {
        var raw string
        if err := rows.Scan(&raw); err != nil {
            return nil, err
        }
        var id string
        // agtype scalar decodes as a JSON string.
        if err := json.Unmarshal([]byte(raw), &id); err == nil {
            channels = append(channels, id)
        }
    }
    return channels, rows.Err()
}

GDPR: the graph has no people in it

Because we are European and say so on the tin, the data model has to survive a data-protection review, not just a code review. The design principle is simple: there is no person in the graph. Nodes are videos, channels, tags, and regions. The CO_WATCHED edge is an aggregate weight computed by grouping across sessions with a minimum-count floor, so it never encodes an individual and never survives below the k-anonymity threshold.

What that buys us:

• No profiling. Recommendations come from population-level co-occurrence, not from a stored history of any one visitor. There is no per-user vector to leak or to explain in a subject access request.
• Trivial erasure. A right-to-erasure request touches the raw session_views in SQLite (which we retain briefly and hashed), and the next ingestion run simply recomputes weights. Nothing in the graph is tied to the erased subject to begin with.
• Cache-safe by construction. Since related-video output depends only on the video id, not the viewer, Cloudflare Workers cache it publicly at the edge with no risk of serving one user's personalization to another.

The lesson I would give anyone building recommendations in the EU: push the anonymization upstream, into the aggregation query, so the downstream store — graph or otherwise — is structurally incapable of holding personal data. It is far easier to defend "the system cannot do that" than "the system could but we promise we don't."

Performance notes from running it for real

A few things I wish someone had told me before I shipped this:

• Index every anchor property. The single biggest win is the b-tree index on video_id. Traversal cost is dominated by finding the start node; index it and everything else is cheap.
• Keep node properties thin. The graph should hold ids and filterable scalars, not descriptions. Fat property blobs bloat the label tables and slow the anchor scan. Hydrate from your render store.
• Bound your hops. AGE is happy at two to four hops on our data size. Unbounded variable-length paths (-[*]-) will find you a bad time; cap them (-[*1..3]-) and set a LIMIT.
• Reuse the AGE session setup. LOAD 'age' and the search_path set are per-connection. With a pooled/persistent PDO or pgx connection you pay that once, not once per query.
• Batch ingestion in one transaction. Wrapping the whole upsert loop in a single transaction turned our nightly job from minutes into seconds and kept the graph consistent for readers throughout.

Conclusion

Apache AGE let us add genuine graph capabilities to a stack that was never going to grow them natively — SQLite for the fast cached render path, Postgres plus AGE for the relationship questions that are graphs at heart. We did not have to operate a second database species, our backup and DR story stayed exactly one pg_dump, and the recommendation logic reads like the question it answers instead of a pile of self-joins.

The part I am proudest of is not the Cypher, it is that the graph contains no people. Push anonymization into the aggregation step, model relationships between content rather than between users and content, and the whole GDPR conversation gets short. Graph databases have a reputation as heavyweight infrastructure; AGE proves you can get most of the value as a Postgres extension you already know how to run. If your "related videos" join has quietly turned into a five-table monster, that is the graph trying to escape — let it.