When Your Data Is Really a Graph: Write the Recursive Query First

Ana cannot open a document, and you need to know why.

She is in the London team. The London team sits inside Engineering. Engineering was granted read access to a folder two years ago by somebody who has since left. The document was moved into a subfolder last week, and that subfolder inherits from its parent, except where a rule overrides it.

Your schema looks perfectly sensible:

CREATE TABLE membership (member_id, group_id);
CREATE TABLE grant (group_id, resource_id, permission);

Your first query looks sensible too. Then somebody nests a group inside another group, so you add a join. Then somebody nests that one, so you add another. Six months later there is a function in your codebase called resolveEffectivePermissions that nobody wants to touch, and it has a hard-coded depth limit that started as a temporary measure.

The problem is not that SQL cannot answer this question. The problem is that the number of joins depends on the data, not on the query. You cannot write the query, because the query changes every time somebody creates a group.

That is the moment your data stops being rows that reference each other and starts being a graph. Noticing it early enough for it to be a design decision, rather than an incident, is most of the skill.

How to tell whether you have a graph problem

Three signs, and you want at least two of them before changing anything.

The question is about a path, not a row. "Can Ana read this?" is really "is there any route from Ana to this document that ends in a read grant?" Fraud detection asks whether two accounts are connected through any chain of shared devices, addresses or cards. Dependency analysis asks what breaks if this service goes down, including the things that break because of the things that break.

The depth is decided by the data. Two hops today, nine hops after a reorg. If you cannot write down the maximum before looking at production, no fixed query will hold.

The relationships carry their own information. Not just "Ana is in London", but "Ana joined London in March, as a contractor, granted by Sam". Once your join table has four columns of its own and people keep asking to add a fifth, the relationship has become a thing in its own right.

What you are describing is a property graph: nodes for the things, edges for the relationships between them, and key-value properties on both. If you have written a breadth-first search by hand, you already have the model. I wrote up graphs as a data structure in TypeScript separately, and nothing about the shape changes when it moves into a database. Only the cost of walking it changes.

Read that top to bottom and you have your answer, along with the reason. The reason is the part your WHERE clause was never going to give you.

What a graph database actually buys you

One thing, and it is worth being precise about it: the cost of a traversal is proportional to the part of the graph you touch, not to the size of the data you are storing.

A row in a relational table does not know where its related rows are. Finding them means going through an index, and that index grows as the table grows. Do that once and it is fast. Do it once per hop, per row, on a chain whose length you do not control, and the work multiplies at every level.

A graph database stores the edges as direct references from the node itself. Stepping from Ana to her groups is closer to following a pointer than to searching. Walking six hops from Ana costs roughly what walking six hops from anyone costs, whether the database holds ten thousand people or ten million.

That is a real advantage and it is narrower than it sounds. It only pays when you are traversing. A query that filters, groups and aggregates across everything is a relational query, and a graph database will do it worse. Most systems have a handful of genuinely graph-shaped questions surrounded by a large amount of ordinary reporting, which is why "we moved everything to a graph database" tends to be followed a year later by "we moved most of it back". The same trade-off runs underneath the usual SQL versus NoSQL argument, and it resolves the same way: the storage engine is chosen by the query you cannot afford to get wrong, not by the data.

Before you move anything, write the recursive query. Postgres has had one for years:

WITH RECURSIVE reachable AS (
  SELECT group_id FROM membership WHERE member_id = $1
  UNION
  SELECT m.group_id
  FROM membership m
  JOIN reachable r ON m.member_id = r.group_id   -- a group inside a group
)
SELECT DISTINCT g.resource_id
FROM grant g JOIN reachable r ON g.group_id = r.group_id;

That handles unbounded depth in the database you already run, backed up and monitored, with no new operational surface. For an org chart, a folder tree, or a permissions model with thousands of groups, this is very often the correct answer and the end of the story. Reach for a graph database when this query becomes the slowest thing you own, and not before.

Deciding what becomes a node

This is where graph projects are won or lost, and the rule is short. If you ever traverse to it, or ask a question about it on its own, it is a node. Otherwise it is a property.

country: "UK" on a person is a property. You filter by it and you group by it, and nothing more. But the day somebody asks "which suppliers do we reach through companies registered in the same country as this customer", the country has become a place you travel through, and it needs to be a node.

Promote it too eagerly and you get the failure that arrives in month three. A supernode is a node with a vast number of edges, and it is created almost every time by turning a low-cardinality attribute into a node. A Country node connected to every person in your database is not a useful hub, it is a place where traversals go to die: any path that touches it has to consider millions of neighbours, and every query that wanders near it gets slow at once. The Neo4j community has been writing up the supernode problem for years, and the fixes all amount to the same move, which is splitting the hub into something more specific so that no single node carries the whole population.

The honest version of this rule is that you will get it wrong somewhere, and refactoring a graph model in production is slower than refactoring a schema, because the data and the shape are the same thing. Model the traversals you have, not the ones you can imagine.

Building a graph out of documents you did not write

Everything so far assumed you know your entities, because you designed them. The newer and harder job is building a knowledge graph, which is a graph whose nodes and edges are extracted from text rather than defined by a schema.

The reason people do this is retrieval. Ordinary vector search finds the chunks that look most like the question, which works well when the answer sits in one place and badly when it has to be assembled from several. Ask "what are the main themes in this corpus" and there is no chunk to find, because the answer is about all of it at once. Microsoft's GraphRAG paper, From Local to Global, puts the problem plainly: conventional retrieval "fails on global questions directed at an entire text corpus", because that is summarisation wearing the clothes of a lookup. Their answer builds an index in two stages, deriving an entity graph from the documents and then pre-generating summaries for clusters of closely related entities, so a global question can be answered from the summaries instead of from the text.

It works, and the bill arrives before any of the answers do. Microsoft's own follow-up, LazyGraphRAG, was built specifically because those up-front indexing costs are, in their words, "prohibitive for some users and use cases". By deferring the language model work until a question is actually asked, its indexing cost is "identical to vector RAG and 0.1% of the costs of full GraphRAG", at "more than 700 times lower query cost" than GraphRAG global search for comparable quality on global questions.

Read those two numbers next to each other, because together they say something useful. Pre-computing structure over an entire corpus is enormously expensive, and most of what you pre-compute is never asked about. If you are choosing today, start lazy. Pay to build structure up front only for the questions you know get asked constantly, which is the same instinct that makes chunking a product design decision rather than a parameter.

The dangerous part: two names for one thing

Read this section twice, because it is where knowledge graphs quietly stop being true.

Your documents call the same company Acme, Acme Ltd, ACME Limited, the client, and a deal codename that only appears in three emails from 2024. A language model reading those documents will happily produce five nodes. Nothing errors. Your graph looks bigger and more impressive than it did yesterday, and it now answers questions wrongly, because the relationships that mattered got divided across five copies of one company and none of the copies has the whole picture.

Fixing this is entity resolution, deciding when two extracted mentions refer to the same real thing. Neo4j's own guidance is blunt about the stakes: without it, "the same entity exists as multiple disconnected nodes, losing the relationships that make graph memory powerful". It is not a preprocessing step you do once. It is the part of the pipeline that decides whether the rest of it was worth building.

In code the mistake is easy to spot once you know the shape. The surface name must never be the key:

// Don't
graph.upsertNode({ id: extracted.name, type: "Company" });

// Do
const canonical = await resolveEntity({
  name: extracted.name,
  type: "Company",
  context: extracted.sourceDocumentId,
});
graph.upsertNode({ id: canonical.id, type: "Company", aliases: [extracted.name] });

Two rules that will save you a rebuild. Keep the alias, always, because you will need to explain later why the graph believes these are one company. And make the two errors cost different amounts: splitting one company into two loses you an answer, whereas merging two companies into one gives you a confident wrong answer that nobody catches. When the resolver is unsure, leave them apart.

Keeping it true after the demo

A graph built by extraction is a derived index over a corpus that keeps moving, which puts it in the same family as a search index or a cache, with the same obligations. The documents change. The extraction prompt changes. The model changes, and the same paragraph now yields a slightly different set of relationships.

The most useful study I have read on this is an interview study of 19 knowledge graph practitioners across eight organisations, and it is worth reading because it is about operations rather than architecture. Data quality came up with 15 of the 19, covering exactly the problems above: duplicates, obsolete records, entity disambiguation. Querying came up with 11 of them, and one participant described the daily experience as waiting "like 15 minutes to load, and then you'd get no results". Their finding on schemas is the one I would put on a wall: building a reliable schema takes months, and that delay lands squarely in the middle of your development.

So version the pipeline, not just the data. Every node and edge should record which document it came from and which extraction version produced it, so that changing the prompt means rebuilding a slice rather than re-deriving everything and hoping. The same discipline that keeps agent memory honest applies here, for the same reason: anything derived from a model's output needs to say where it came from, or you cannot tell a correction from a regression.

Try the boring version first

The standards have quietly caught up while everybody was arguing about databases. Property graph queries became part of SQL itself in SQL:2023, as SQL/PGQ, and a full standalone graph language, GQL, was published as an ISO standard in 2024. More concretely, SQL/PGQ support was committed to PostgreSQL's master branch in March 2026, adding CREATE PROPERTY GRAPH and pattern matching over ordinary tables. It is not in a released version yet, and property graphs there are rewritten into relational queries rather than stored as a graph, so it changes the language you write and not the cost of a deep traversal.

It still matters, because it removes the part of this decision that was never really a data-modelling question. Declaring that your permissions are a graph is about to stop requiring a second database, a second query language, a second backup story and a second thing to be woken up for. What remains is the question worth arguing about, which is whether the thing you are modelling is genuinely a network or just a table you have not finished normalising.

Write the recursive query first. If it holds, you were never a graph problem. If it does not, you now know exactly which traversal broke it, and that traversal is the only part you should move.