Apache AGE under load: how PostgreSQL graphs behave under stress

This story started when we found several segfaults in Apache AGE. Segfaults in a young extension are not exactly shocking, but they raised a fair question: if stability is still a bit shaky, what does performance look like? The question hung in the air, then turned into a task, and then, as usually happens, grew into a full-blown investigation that took much longer than it looked like it would at first. The main reason: there was simply no solid way to benchmark Apache AGE. No ready-made benchmarks, no published results.

Before we dive into flame graphs, let’s first look at what Apache AGE actually is and why anyone would want graphs inside PostgreSQL in the first place.

Why put a graph inside PostgreSQL?

Take the usual relational model: one table for people, one table for cities, one table for relationships, and the last one says who lives where. From that, you can figure out that Alice lives in New York. Now imagine the same information as a graph: a vertex for “Alice”, a vertex for “New York”, and a “lives in” edge between them. Same data, different representation.

Apache AGE stands for A Graph Extension. It lets you work with graph data directly inside PostgreSQL. You enable the extension, write queries in openCypher, the open version of Neo4j’s Cypher, and get your results. No need to deploy a separate graph database, sync data between systems, or make the team learn a whole new stack.

The fact that openCypher is an open version and not a full copy of Cypher will come back to haunt us later. But more on that in a bit.

At this point, a fair question might pop up: if AGE stores everything in regular PostgreSQL tables anyway, why bother with a separate query language? Let’s look at an example. Say you need to find the friends of a person with ID 123 and sort them by the friendship date. In Cypher, you use the pattern (person)-[knows]->(friend) and describe what you want to find. In SQL, you build a JOIN and describe how to assemble the result from tables.

For a simple query, the difference is not dramatic. But add one more level, friends of friends, and SQL starts growing recursive CTEs and multiple JOINs. In Cypher, you just change [:KNOWS] to [:KNOWS*1…2]. Three extra characters, done. By the third level of depth, the readability gap starts to look like the difference between a poem and a washing machine manual.

How Apache AGE works internally

Connecting it is standard for a PostgreSQL extension:

CREATE EXTENSION age;
LOAD ‘age’;
SET search_path = ag_catalog, “$user”, public;

Create a graph:

SELECT * FROM ag_catalog.create_graph(‘social_network’);

At that point, AGE creates a schema with the same name and two parent tables: the vertex table ag_label_vertex and the edge table ag_label_edge. When a Cypher query introduces a type like Person or KNOWS, AGE automatically creates an inherited child table. No manual CREATE TABLE needed.

SELECT * FROM cypher('social_network', $$

    CREATE (:Person {name: 'Alice'})-[:KNOWS {since: ‘2020’}]->(:Person {name: 'Bob'})

$$) AS (result agtype);

This query creates two people and a relationship between them. The Person and KNOWS tables will appear automatically.

Each vertex has an id (type graphid), a type (implicitly, via the child table), and a properties field of type agtype. This agtype is a superset of JSONB with its own set of operators. You can put anything into properties: first name, last name, date of birth, IP address, gender, basically the full set of attributes for an entity. Edges have the same thing, plus start_id and end_id, which point to the connected vertices.

Here’s the key thing to remember: the properties field on vertices is almost always much heavier than on edges. An edge usually stores one or two fields, such as creation date or weight. A vertex can easily carry a dozen attributes.

The whole architecture is built around the fact that AGE does not invent its own storage engine. The data lives in regular PostgreSQL tables, so MVCC, WAL, the planner, indexes, all of that works as usual. But there’s an interpretation layer on top: every Cypher query gets translated into SQL, and agtype is parsed every time properties are accessed. Convenience comes at a cost.

Looking for a benchmark. Spoiler: we don’t find one

The standard performance engineer workflow looks like this: task comes in -> find a benchmark -> run it -> check the results. We honestly tried to follow that path and tripped on step two.

Benchmarks for graph databases do exist. The best-known one is LDBC SNB. LDBC (Linked Data Benchmark Council) is an organisation that professionally benchmarks graph DBMSs. SNB (Social Network Benchmark) is their specific benchmark: a well-thought-out social network data model, dozens of query types, and ready-made implementations for Neo4j, TigerGraph, GraphDB, and even PostgreSQL.

The catch is that the PostgreSQL implementation uses a relational model: regular tables with foreign keys, queries in SQL. And the Cypher implementation is written for Neo4j and uses functions that AGE simply does not have. Other benchmarks work through Gremlin, which AGE does not support either.

We dug through Apache AGE GitHub Issues. There are quite a few questions from users along the lines of: “How do you even measure performance here?” The developers answer something like: “Unfortunately, that’s not available yet, but you’re welcome to work on it.” So we did.

Writing our own benchmark

There were two options: invent queries from scratch or take an existing standard and adapt it. We picked the second route: used the data model and query set from LDBC SNB, then built our own implementation on top of the internal pg_microbench utility, which talks to AGE directly. That gave us nineteen test scenarios split into three groups: short reads, heavy reads, and writes.

The graph schema is a social network. Vertices: Person, Post, Comment, Forum, Tag, TagClass, Place, Organisation. Edges are exactly what you would expect in a social network:

(:Person)-[:KNOWS]->(:Person)

(:Post)-[:HAS_CREATOR]->(:Person)

(:Comment)-[:REPLY_OF]->(:Post)

(:Person)-[:HAS_INTEREST]->(:Tag)

(:Person)-[:IS_LOCATED_IN]->(:Place)

(:Forum)-[:HAS_MEMBER]->(:Person)

This produces a fairly dense graph: each user has several friends, several posts, and each post comes with tags and comments. That makes traversals across 2–3 levels of depth do some real work.

The data was generated with the scale factor (SF) parameter: persons = SF × 2000, organisations = SF × 200, tags = SF × 2000, forums = persons / 25, posts = persons × 5, comments = posts. We tested three graph sizes: SF = 1 (~20,000 objects), SF = 10 (~200,000), and SF = 100 (~2,000,000), so we would not miss scaling issues.

Indexes deserve their own section

Since properties is agtype, basically JSONB on steroids, indexing needs a separate discussion.

We created two types. B-tree on id: both on the vertex/edge id, and on the id inside the properties field:

CREATE INDEX idx_person_id_btree

ON social_network.“Person” USING BTREE (

agtype_access_operator(properties, ‘“id”’::agtype)

);

One important nuance: this index is only needed if your queries actually use WHERE n.id = 'value'. If not, the index is redundant and in some cases can even make performance worse.

GIN on properties is for searching by arbitrary properties. If your queries use filters likeMATCH (n:Label {prop: value}), create a GIN index:

CREATE INDEX idx_person_gin

ON social_network.“Person” USING gin (properties);

For edges, we also added B-tree indexes on start_id and end_id.

The GIN index turned out to be absolutely necessary. We checked: remove GIN, and the result gets ten times worse. Without GIN, all property-based queries turn into SeqScan. The slowdown is less obvious, but only because everything is already slow. Not exactly a great optimization story.

For point queries, such as lookup by ID, B-tree is more efficient. With GIN, the plan for a single row looks like this: Bitmap Index Scan → Bitmap Heap Scan → Recheck Condition. Three steps instead of one. With B-tree, it is just Index Scan. In practice, you need both types: B-tree for point lookups, GIN for searching by arbitrary properties.

Test setup

VMs: 30 cores, 32 GB RAM. PostgreSQL 17.5.1 with Apache AGE for version 17. Load: from 1 to 128 parallel threads. Standard metrics: TPS, query plans, flame graphs, locks, and wait events.

All right, let’s get into it.

Short reads: boring, which Is exactly what you want

The first group is simple queries: find friends, get a user’s city, find a post’s author. Range lookups, filtering, sorting. We are not going deep into the graph here, just checking how indexes behave and how quickly AGE handles basic patterns.

SELECT * FROM cypher('social_network', $$

    MATCH (p:Person {id: 123})-[:IS_LOCATED_IN]->(city:Place)

    RETURN p.firstName, p.lastName, city.id

$$) AS (firstName agtype, lastName agtype, cityId agtype);

SELECT * FROM cypher('social_network', $$

    MATCH (post:Post {id: 456})-[:HAS_CREATOR]->(author:Person)

    RETURN author.id, author.firstName, author.lastName

$$) AS (id agtype, firstName agtype, lastName agtype);

There are about fifteen queries like this in the group. The actual content varies, but the pattern is always the same: take a vertex, walk one edge, return the result.

The outcome: a stable ~40,000 TPS on 128 threads, predictable scaling as the graph grows, and no surprises. We found the plateau, but no degradation. There is honestly not much to say here, which is probably the best compliment you can pay a system.

What matters most:

• creating indexes is mandatory;
• always specify a label in the query: MATCH (p:Person {id: 123}), not MATCH (p {id: 123}). Without the label, all vertex tables will be scanned, and hello there, SeqScan;
• start from the side with fewer entities: there are fewer people than posts, so start with people;
• filter as early as possible;
• if something looks off, run EXPLAIN ANALYZE and check the indexes.

Heavy reads: this is where it gets interesting

The second group is where we deal with deeper traversals, path search, and friends-of-friends reached through a few handshakes. In other words, the reason you choose a graph database in the first place.

For example: which forums do our friends and friends of friends belong to, and how many posts are there in each one?

SELECT * FROM cypher('social_network', $$

    MATCH (p:Person {id: 123})-[:KNOWS*1..2]->(friend:Person)

          <-[:HAS_MEMBER]-(forum:Forum)

    OPTIONAL MATCH (friend)-[:HAS_CREATOR]->(post:Post)

                   <-[:CONTAINER_OF]-(forum)

    RETURN forum.title, count(post) AS postCount

$$) AS (title agtype, postCount agtype);

[:KNOWS*1…2] is the depth: either direct acquaintances or people reached through one intermediary. OPTIONAL MATCH means that if there are no posts, the forum still stays in the result with a zero count. The query is no longer trivial, but AGE handled it well.

Then we got to shortest-path search. And that is when things became genuinely interesting.

Neo4j has a built-in shortestPath() function for this, with internal optimizations. We had adapted queries from the Neo4j benchmark, so using it would have been the obvious move. The only problem: Apache AGE has no built-in path functions at all. None. If you want the shortest path, you have to implement the logic manually:

SELECT * FROM cypher('social_network', $$

    MATCH path = (p1:Person {id: 123})-[:KNOWS*1..5]-(p2:Person {id: 777})

    RETURN min(length(path))

$$) AS (shortestPath agtype);

You can search for paths with a bounded depth limit up to 5 hops, make them directional or bidirectional, or skip the depth limit entirely. That last option is a bad idea, and here is why.

Remember those 40,000 TPS on short reads? On shortest-path search over a small graph, we got 7 TPS. Not seven thousand. Seven. 3,000 times slower.

We checked the query plan: indexes were being used, everything looked correct. So we moved on to the flame graph. A flame graph is a visualisation where the wider the block, the more time it consumed. And that is where we saw get_edge_by_entry() glowing like a festival light.

We dug into it. In essence, the function pulls an edge record from a hash table and returns either “found” or “not found”. The function itself is simple, so there is not much to optimise there. Which means the issue is not the function, but how many times it gets called.

Then we stepped back and looked at how path traversal is implemented in Apache AGE overall. That is where we found three problems, each one multiplying the amount of work for no good reason.

First: expensive work happens too early. AGE first extracts all the details for every edge, including properties and connected vertices, and only then checks whether that edge is needed at all. It is like reading the full ingredient list of every product on the shelf before even checking the price tag. The fix is simple: check the conditions first, and only if the edge matches, fetch the rest of the data.

Second: branches of the algorithm do not share results. One traversal step has already found an edge, but another step goes and searches for it all over again. That becomes double or triple work depending on the depth.

Third: there is no caching for explored paths. The algorithm keeps walking the same routes again and again, even when it already knows they lead nowhere. Imagine a navigation app that forgets every five minutes that a road is blocked and happily tells you to turn there again.

So what can you do for now?

• limit the search depth, do not look for infinitely long paths;
• tune the depth to the size of the graph: a small graph can tolerate more, a large one cannot;
• write one-way queries, because bidirectional ones put a noticeably heavier load on the system;
• increase work_mem to at least 128 MB instead of the default 4 MB. And be extra careful with OPTIONAL MATCH.

Write queries: a detective story

The third group is inserts. From a logical point of view, we can insert either a vertex or an edge. Sounds like a small distinction, right?

Edge inserts, for example, adding a member to a forum:

SELECT * FROM cypher('social_network', $$

    MATCH (f:Forum {id: 2}), (p:Person {id: 123})

    CREATE (f)-[:HAS_MEMBER {joinDate: '2024-06-15'}]->(p)

$$) AS (result agtype);

A steady ~30,000 TPS. Good scaling. No degradation. Moving on.

Vertex inserts mean creating a user with the full set of properties: first name, last name, date of birth, email, browser, IP, the whole attribute sheet, and then attaching that user with edges to organisations, places, and tags so the person is not left floating around the graph all by themselves.

And this is where, instead of 30,000, we got only ~1,000 TPS. Plus a smooth performance drop over time. The longer the test ran, the worse it got. At any thread count.

A 30x gap between edge inserts and vertex inserts is not a minor quirk, it is a proper red flag. So we started digging.

Suspect #1: heavy properties. Vertex properties are much bulkier. An edge has a couple of fields. A vertex has around ten attributes, all in agtype, and every insert means parsing, serialisation, and index updates. Sounds like a solid suspect. But this explains the gap in absolute numbers (30,000 vs 1,000), not the degradation over time. Degradation means it is not just slow, it keeps getting slower. Not guilty.

Suspect #2: GIN indexes. They are heavy by design, and updates get more expensive as the dataset grows. Maybe that was it? We tested it by removing GIN. The result was 10x worse. The good news: the degradation was barely visible :) The bad news: that was only because everything was already slow. Without GIN, it turned into a SeqScan festival. Not exactly a great trade-off. Also not guilty.

Suspect #3: WAL and locks. In pg_stat_activity, we saw RowExclusiveLock, wait events on WAL, the works. Looked promising. We disabled synchronous_commit, but that changed nothing. WAL locks were a side effect, not the root cause. So this one walks too.

By this point, we had run out of obvious hypotheses and moved on to the flame graph. That is where the whole story finally came out.

Suspect #4: entityExists(). At the start of the test, this function took a noticeable but not dominant share of time. By the end, it was eating almost 90%. Everything else became background noise next to it.

What does it do? Every time a vertex is inserted, AGE calls entityExists() to check whether an entity with the same ID already exists. The function extracts the label ID, looks up the table for that label from cache, opens it and then… scans it. Sequentially. A full SeqScan. On the entire table. For every insert.

Each new vertex makes the table larger. Each next SeqScan gets a little more expensive. That is where the slow but stubborn degradation comes from.

When we saw that, it was equal parts relief (found it!) and disbelief (seriously, SeqScan?). The fix was conceptually simple: replace SeqScan with indexed access. So we wrote a patch.

Result:

• entityExists is almost invisible on the flame graph
• TPS went from 1,000 to 15,000
• the degradation disappeared

A 15x speed-up from replacing a single SeqScan with index access. Sometimes the nastiest problems hide in the most obvious places.

One more important detail: entityExists() is not called only during CREATE. The same code path is also used for vertex MERGE and DELETE. One patch, and a whole chunk of operations gets faster right away. We are pushing it to the Apache AGE community: https://github.com/apache/age/pull/2351

PostgreSQL 18: the surprise nobody asked for

Since we had already broken AGE down piece by piece on version 17, it would have been odd not to try the same thing on 18. We ran the same scenarios on PostgreSQL 18.

And we found a performance drop. On average, it was around 15-20%. On some short ID lookup queries, it went up to 50%. The new version was slower. We double-checked the configuration and parameters, and everything matched. The issue reproduced consistently.

The flame graph showed that version 18 was spending a disproportionate amount of time on planning. Around 33% of the total time was eaten by the agtype_contains() function.

We dug into it. In Apache AGE for version 17, the @> operator for agtype used the contsel selectivity estimation function: rough, but fast. In the version for 18, the definition changed and now uses matchingsel. For the record, the switch from contsel to matchingsel happened for all operators, but it really blows up on @>: the very operator AGE uses to translate all property lookup queries.

The difference is not just in the function itself. To estimate selectivity, matchingsel actively calls internal functions, including the heavy agtype_contains. That is exactly what showed up in the flame graph: unpacking the agtype constant from the query, parsing the JSON structure, all to get a more accurate estimate of how many rows will be returned.

For complex analytical queries, a precise estimate is a good thing: better estimate, better plan. But for simple point operations like properties @> ‘{“id”: 1234}’, it is pure overhead. The planner spends time doing deep analysis even though the plan is obvious: fetch one row through the index.

The situation gets worse because AGE generates a custom plan on every call instead of a generic one, so planning with the expensive matchingsel happens again for every query. Why the planner does not cache the selectivity estimation results in this case is still an open question. We filed a bug report with the Apache AGE community and are still investigating it.

openCypher limitations that bit us

It is worth remembering that openCypher is an open version, not a full copy. Here is what that meant in practice:

• there is no shortestPath() support, so we had to implement it manually using a variable-length pattern and min(length(path));
• there is no type union support, so a construct like MATCH (m:Post|Comment {id: 123}) does not work, and you have to split it into two queries or patch it up with a WHERE;
• WITH support is limited, and some WITH chains behaved unpredictably, so we had to split complex queries apart;
• you cannot reference an alias in ORDER BY, so you either duplicate the expression in ORDER BY or wrap the query in an additional WITH.

Each limitation on its own is minor. Put them together, though, and adapting queries from the Neo4j benchmarks turns into painstaking work. You write a query based on the openCypher docs, run it, get an error, rewrite it, and repeat. For every non-trivial scenario.

What’s next

Testing is still ongoing. The patch for entityExists() is ready, and we’re pushing it upstream to the Apache AGE community. At the same time, we’re working on three path traversal issues that turn 20,000 TPS into seven.

There are still a few open questions. Can AGE be made to use generic plans instead of custom ones, so it doesn’t have to call the expensive matchingsel on every query? How do we improve statistics for agtype so the planner can estimate selectivity more accurately? Is there room for optimisation at the hook level, where AGE intercepts and rewrites queries? Those are the topics for the next round.

On GitHub, users regularly ask how AGE compares with Neo4j. The AGE developers answer honestly: Neo4j is faster on large graphs and on path-finding queries. That is expected. Neo4j was built from scratch as a graph database, while AGE is layered on top of a relational one. With the right patches, though, the gap may shrink.

Living with it: the takeaway

Let’s draw a clear line under what works, what does not, and what you should do right now.

Workload type	TPS	Status
Short reads	~40000	Stable
Path finding	~7	Patches in progress
Edge inserts	30000	Stable
Vertex inserts (before patch)	1 000 → degradation	Patch submitted to the community
Vertex inserts (after patch)	15000	Stable

Key recommendations:

• create B-tree indexes on id and GIN indexes on properties — mandatory;
• always specify the label in the query, otherwise you get a SeqScan across all tables;
• start the query from the more selective side;
• filter as early as possible;
• for path finding, lock the depth and prefer one-way queries;
• increase work_mem to 120+ MB for heavy traversals;
• be careful with OPTIONAL MATCH;
• specify label types when writing data.

A graph model on top of PostgreSQL does work. For short queries and medium-complexity workloads, AGE is absolutely usable. But our testing showed that sometimes the real bottleneck is not graph traversal algorithms or query language complexity. It can be a single SeqScan inside a vertex existence check, called on every insert, with no business being there in the first place.