Choosing the databases, and the spike that failed

In the first post I said Semantic Graph keeps a graph and a vector index, and that I stopped fighting to make one store do both. This is the story of how we got to the two we have. It took about two weeks, an architecture decision record with a long comment thread, and one experiment that failed in a way that made everything else faster.

If you only want the conclusion: Memgraph for the graph, Elasticsearch for the vectors. The rest is why.

Start with what you already have

My first preference was zero new infrastructure. We run Postgres everywhere. If Postgres could hold the graph, nobody had to learn anything and nobody had to approve anything.

So the first thing I tried was Postgres with Apache AGE, the extension that adds openCypher on top. It works. Up to a point. The queries that matter for impact analysis go three, four, five hops deep: method calls method, which calls method, which is behind an endpoint. Past three hops the traversal got slow in a way that no index or configuration fixed. Some Cypher patterns that a native graph engine accepts did not run at all, because the openCypher implementation is not complete.

The reason is not a bug. The Postgres planner was built for joins, not for following pointers from node to node. You can make it look like a graph. You cannot make it behave like one at the depth we needed.

I also looked at in-memory graph libraries like NetworkX and igraph. No persistence, no query language you would put in production. Ruled out on paper.

That left a dedicated graph database, and at the time the obvious one was Neo4j Community. Native Cypher, a storage engine built for traversal, and a Python driver that just works. It passed every traversal test I threw at it.

It had two problems. The Community edition has no clustering, no hot backups and no role-based access. Those are paid features. We could live with that for an internal tool. The second problem was the licence: AGPLv3 with the Commons Clause. Nothing wrong with it, but it meant a legal review before anything could go to production, and legal reviews take time we did not have.

The vector side

For the vectors the list was longer and the decision was less clear.

ChromaDB was out quickly. The open source version is single node, and one disk failure takes your whole index with it.

pgvector was tempting for the same reason AGE was: we already run Postgres. But the 8 KB page size caps the dimensions you can index at around two thousand. HNSW rebuilds on a large table need a maintenance window. And if we ever wanted to put the graph next to it, we would hit the AGE ceiling again.

Qdrant was my personal favourite. It is written in Rust, it does sub-ten-millisecond nearest-neighbour search with quantisation, clustering is included in the open source version, and the licence is Apache 2.0 with no review needed. I had it running end to end in a local Kubernetes cluster before the decision was made. The downsides were that it has no BM25, so keyword-heavy queries depend entirely on the embedding quality, and that it was new to the company. New means runbooks, on-call knowledge and an entry on the technology radar, all from zero.

Elasticsearch was already on the radar and already in production for other things. Version 8 does dense-vector kNN with HNSW, and it does BM25 in the same query. The latency is closer to fifty milliseconds than ten. The HNSW index needs off-heap memory you have to plan for. But the cluster, the backups, the monitoring and the people who know how to run it all existed.

I wrote the recommendation as Qdrant, with Elasticsearch as the documented fallback. Then I did the sensible thing and actually tried Elasticsearch behind the same VectorStore interface. It worked the same. Index writes and queries were a bit slower. Nothing that mattered for a tool where the graph traversal is the expensive part anyway.

That is when the hybrid search started to earn its place. Half the questions people ask contain an identifier, and an embedding model is not great at exact identifiers. BM25 with a tokenizer that splits camelCase is. Having both in one query changed the quality of the results more than the latency changed the experience.

The spike

At this point the recommendation was Neo4j plus Elasticsearch, and a colleague asked a fair question. If the problem is two new technologies at once, why not one database that does both?

ArcadeDB is a multi-model database: graph, document, key-value and vectors in one engine, Apache 2.0, and it speaks several wire protocols including Neo4j’s Bolt. On paper it was a perfect fit. I gave it the better part of a week.

The first surprise was that there is no first-party driver. You point an existing client at one of its compatible endpoints. I used the Neo4j Bolt driver against the Bolt endpoint, which is the documented way. Cypher over Bolt was unusable for us. Queries that took milliseconds on Neo4j took minutes. I never fully understood why, and after a few days I stopped trying to.

The path that did work was ArcadeDB’s own SQL dialect, SQLScript, over HTTP. So I rewrote the whole write path in SQLScript. The two biggest repositories we index took around fifteen and twenty-five minutes on Neo4j at the time. On ArcadeDB, with the write path fully native, they took around twenty-five and forty. Sixty percent slower, with the driver and the Cypher layer already out of the equation.

I spent many more hours tuning it: batching, upsert shapes, index definitions. I got it down to about fifteen and twenty minutes, which was a bit faster than the Neo4j baseline. But the read side never recovered. Queries were still around sixty percent slower, with a lot of silent errors. And every one of our multi-hop Cypher queries would have needed a rewrite into a SQL dialect without graph semantics. That is not a migration. That is a second implementation, with all the risk of getting the impact analysis wrong somewhere nobody notices.

I dropped it.

What the failed spike paid for

Here is the part I did not expect. Everything I had learned tuning ArcadeDB was about batch shapes and index coverage, and none of it was specific to ArcadeDB.

So I went back to the Neo4j implementation and applied the same ideas. Every write became an UNWIND over a list of rows with a MERGE per row, one query per entity type per chunk, instead of one query per entity. Every label got an index on fqn, name and project, because those are the three properties every lookup filters on. Queries that matched by pattern were reshaped to start from an indexed property and expand outwards.

The two biggest repositories went from fifteen and twenty-five minutes to about six and eight. Cross-repository reference resolution, the pass that links a call in one repo to a method in another, went from minutes to seconds. The failed experiment more than halved indexing time on the stack we kept.

Over the whole life of the project, the biggest repository has gone from almost an hour and a half for a full index to about eight minutes. That week was the last big step, and the one I learned the most from.

Memgraph

Then a second colleague suggested Memgraph. I had tried it briefly early on and remembered two things: the Cypher was almost identical, and it keeps the whole graph in memory.

The licence was the deciding argument. Memgraph Community is under the Business Source License, which allows internal, non-competing use without the legal review Neo4j needed. The Community edition also supports replication, so a replica plus a small Kubernetes sidecar that promotes it on failure gives an acceptable availability story for an internal tool.

The migration took days, not weeks. Bolt is the same wire protocol, so the Python driver did not change. A handful of queries needed small edits around procedure calls and a few built-in functions. Index and constraint declarations were translated to Memgraph’s syntax. Every multi-hop traversal, every UNWIND plus MERGE batch, every pattern comprehension ported as it was.

Performance ended up at or above the tuned Neo4j numbers. Indexing was about the same. Queries were faster, and the difference was largest exactly where it mattered: multi-hop traversals and impact analysis, where an in-memory engine chasing pointers has a natural advantage.

The trade-off is real. RAM is now the capacity dimension, not disk. That is the reason there is one Memgraph instance per product instead of one big graph. Each product is a natural data boundary, and splitting along it keeps every instance a size that fits comfortably on a normal node.

What I would tell someone doing this again

Pick the boring option that your organisation already runs, unless you can measure why it is not enough. I wanted Qdrant. Elasticsearch was fine, and it came with a team that already knew how to keep it alive.

Measure a spike properly before you kill it, even when you know it is dead. The ArcadeDB numbers were what made me go back and question the shape of my own queries. If I had stopped at “Cypher over Bolt takes minutes”, the Neo4j implementation would still have been slow.

Do not merge two stores into one just to have one service. The graph and the vector index have different access patterns, different scaling dimensions and different failure modes. Two well-understood services were easier to run than one clever one.

And take licences seriously early. A legal review is not a technical problem, but it is a schedule problem, and it decided the graph database in the end.

The next post goes down a level: how a parser that has never seen a compiler works out which method a call is pointing at.