Graph Storage Alongside Vectors

Similarity search finds text that resembles your question. Some questions are not about resemblance at all: they are about paths through relationships, and the document containing the answer may share no vocabulary with the query. Those need edges. This page puts nodes, edges and vectors in one Postgres schema and traverses them in SQL.

The questions similarity cannot answer

The useful distinction is not “graphs are for connected data”. It is that four specific question shapes have no embedding-space answer, because the thing being asked for is not textually similar to anything in the question.

• Multi-hop. “Which of our suppliers are subsidiaries of a company we have an exclusivity agreement with?” No document contains both halves. The answer is a join across two relationships, and retrieval by similarity returns the documents about suppliers or the ones about agreements, never the intersection.
• Aggregation over a neighbourhood. “How many open issues are attached to components owned by this team?” This is a count over a traversal. Similarity search does not count.
• Provenance and impact. “What depends on this deprecated function?” and “which answers cited this retracted document?” are traversals of a dependency edge. The second one is exactly the inverted retrieval query from the audit schema.
• Negation and absence. “Which policies have no owner?” Embeddings have no representation of absence; a set difference over edges does.

Conversely, if your questions are “what does the handbook say about parental leave”, edges add machinery and answer nothing you did not already have. The wider comparison is in graph RAG; this page is about the storage, on the assumption you have decided you need it.

One schema for both

You do not need a graph database to store a graph. Two tables and the right indexes give you traversal in the same transaction as your vector search, which is worth more than a specialised query language for the scale most applications operate at.

CREATE TABLE nodes (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id  uuid NOT NULL,
  kind       text NOT NULL,            -- 'person' | 'company' | 'document' | 'component'
  name       text NOT NULL,
  props      jsonb NOT NULL DEFAULT '{}'::jsonb,
  embedding  vector(1536),             -- nodes are searchable by similarity too
  UNIQUE (tenant_id, kind, name)
);

CREATE TABLE edges (
  src        bigint NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
  dst        bigint NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
  rel        text   NOT NULL,          -- 'owns' | 'reports_to' | 'cites' | 'depends_on'
  tenant_id  uuid   NOT NULL,
  weight     real   NOT NULL DEFAULT 1,
  source_chunk_id bigint,              -- provenance: which text asserted this
  PRIMARY KEY (src, rel, dst)
);

-- Forward traversal. INCLUDE makes it index-only.
CREATE INDEX edges_out ON edges (src, rel) INCLUDE (dst);
-- Reverse traversal, which you will need as often as forward.
CREATE INDEX edges_in  ON edges (dst, rel) INCLUDE (src);

-- Chunks link to the nodes they mention, which is the bridge
-- between the two halves of the system.
CREATE TABLE chunk_mentions (
  chunk_id  bigint NOT NULL REFERENCES chunks(id) ON DELETE CASCADE,
  node_id   bigint NOT NULL REFERENCES nodes(id)  ON DELETE CASCADE,
  PRIMARY KEY (chunk_id, node_id)
);
CREATE INDEX chunk_mentions_node ON chunk_mentions (node_id, chunk_id);

source_chunk_id on the edge is the column that makes this trustworthy. An edge extracted by a language model from a document is an assertion, and when it turns out to be wrong you need to know which sentence produced it. An edge table with no provenance is a set of claims nobody can check.

Traversal in SQL

A recursive common table expression walks the graph. The two things that must be right are the depth bound and the cycle handling, and real data always has cycles.

-- Everything reachable from node $1 by 'owns' or 'depends_on',
-- up to three hops, cycles handled.
WITH RECURSIVE reachable (id, depth, path) AS (
  SELECT e.dst, 1, ARRAY[e.src, e.dst]
  FROM edges e
  WHERE e.src = $1 AND e.rel IN ('owns', 'depends_on') AND e.tenant_id = $2
  UNION ALL
  SELECT e.dst, r.depth + 1, r.path || e.dst
  FROM edges e
  JOIN reachable r ON e.src = r.id
  WHERE r.depth < 3
    AND e.rel IN ('owns', 'depends_on')
    AND e.tenant_id = $2
    AND NOT e.dst = ANY(r.path)          -- manual cycle guard
)
SELECT DISTINCT n.id, n.name, min(r.depth) AS hops
FROM reachable r JOIN nodes n ON n.id = r.id
GROUP BY n.id, n.name
ORDER BY hops;

Postgres 14 added a CYCLE clause that does the same thing declaratively and is easier to get right:

WITH RECURSIVE reachable (id, depth) AS (
  SELECT e.dst, 1 FROM edges e WHERE e.src = $1
  UNION ALL
  SELECT e.dst, r.depth + 1
  FROM edges e JOIN reachable r ON e.src = r.id
  WHERE r.depth < 3
) CYCLE id SET is_cycle USING traversal_path
SELECT id, depth FROM reachable WHERE NOT is_cycle;

Two performance notes that decide whether this is viable. UNION instead of UNION ALL deduplicates at every step, which is often what you want and is dramatically cheaper on a dense graph — a graph with average out-degree ten, traversed three hops with UNION ALL, generates a thousand rows per starting node before deduplication, and at five hops a hundred thousand. And the depth bound is not optional: without it, a cycle plus UNION ALL is an infinite loop that will consume all available memory.

Combining the two

The pattern that makes graph-plus-vector worth the schema: use similarity to find an entry point, traverse to expand, then retrieve the text attached to the expanded set.

WITH seeds AS (
  -- 1. Vector search finds the nodes the question is about.
  SELECT id FROM nodes
  WHERE tenant_id = $tenant AND embedding IS NOT NULL
  ORDER BY embedding <=> $qvec
  LIMIT 5
),
expanded AS (
  -- 2. Two hops out from each seed.
  WITH RECURSIVE walk (id, depth) AS (
    SELECT id, 0 FROM seeds
    UNION
    SELECT e.dst, w.depth + 1
    FROM edges e JOIN walk w ON e.src = w.id
    WHERE w.depth < 2 AND e.tenant_id = $tenant
  )
  SELECT DISTINCT id FROM walk
)
-- 3. The text attached to everything in the expanded set, ranked by
--    similarity to the original question.
SELECT c.id, c.content, e.embedding <=> $qvec AS distance
FROM expanded x
JOIN chunk_mentions m ON m.node_id = x.id
JOIN chunks c ON c.id = m.chunk_id
JOIN chunk_embeddings e ON e.chunk_id = c.id
ORDER BY distance
LIMIT 20;

One statement, one transaction, one consistent view. That is the argument for keeping both in Postgres rather than operating a graph database next to a vector store: with two systems, step 2 is a network call whose results may reflect a different moment than step 1, and keeping them in agreement becomes a permanent background task.

Where the edges come from

Three sources, in decreasing order of how much you should trust them.

1. Structured systems you already have. The org chart, the CRM, the dependency manifest, the ticket tracker. These edges are correct by construction and cost nothing to extract. Start here, and in many cases stop here.
2. Deterministic parsing. Citations, imports, hyperlinks, foreign keys. Also reliable, also cheap, and frequently overlooked because it is not interesting.
3. Model extraction from text. Ask a model to emit triples from each chunk. This is where recall comes from and where errors come from. Three rules make it survivable: constrain the relation vocabulary to a fixed list rather than letting the model invent predicates; store source_chunk_id on every edge; and require a threshold of independent assertions before an edge is treated as fact, which is a HAVING count(DISTINCT source_chunk_id) >= 2 on the aggregation that promotes candidate edges into the table.

The failure mode of an unconstrained vocabulary is worth naming: the model will emit owns, is_owner_of, has_ownership_of and owned_by for the same relationship, and your traversal — which filters on rel — silently misses three quarters of the graph. A fixed enum in the prompt and a check constraint on the column prevent it.

When this is the wrong tool

Postgres recursive CTEs are good to a few hops on a graph of millions of edges. They are not a graph engine and the boundary is real.

• Deep or unbounded traversals. Shortest path over six or more hops, connected components, PageRank, community detection — these are algorithms, not queries, and a purpose-built engine or a batch computation is the right answer. A recursive CTE attempting them will consume the machine.
• Very high degree nodes. One node with a million edges makes every traversal through it explode. Cap the fan-out per hop — LATERAL (SELECT … LIMIT 50) per node — or exclude hub nodes explicitly. This is a data property you must check, not assume.
• Graphs that do not fit the relational shape. If your edges carry rich, queryable, heterogeneous properties and your questions are genuinely pattern-matching over subgraphs, a language built for that will be shorter and faster than SQL pretending.

The honest position is that two datastores is a real cost — two backup procedures, two consistency stories, two failure modes, two things to restore in a recovery drill — and the schema above defers paying it until the queries genuinely require it. Most applications that reach for a graph database are answering questions two tables and a recursive CTE would have answered.

The failure that actually kills these systems is not performance though. It is entity resolution: the same real-world thing arriving as four different nodes. “Acme Ltd”, “Acme Limited”, “ACME” and “Acme Ltd.” are one company and four rows, so every traversal through it finds a quarter of the edges and the graph quietly under-answers. A graph with unresolved entities is worse than no graph, because it returns confident partial results rather than nothing.

The UNIQUE (tenant_id, kind, name) constraint in the schema is the first line of defence and it only catches exact duplicates. Two further mechanisms are worth the effort, in this order:

1. Normalise before insert. Case-fold, strip legal suffixes and punctuation, collapse whitespace, and store the normalised form in a separate indexed column that the uniqueness constraint uses. Deterministic, cheap, and it catches most of the problem for organisation and product names.
2. Use the node embedding for candidate detection, not for merging. The embedding column on nodes makes “which existing node is this new one probably the same as” a nearest-neighbour query. Surface candidates above a similarity threshold for review; do not merge automatically. Similarity confuses related with identical, and an automatic merge of two genuinely different companies is unrecoverable once edges have been rewritten to point at the survivor.

-- Candidate duplicates: nodes of the same kind whose embeddings
-- are close. Review queue, not an automatic action.
SELECT a.id, a.name, b.id, b.name, a.embedding <=> b.embedding AS distance
FROM nodes a
JOIN LATERAL (
  SELECT n.id, n.name, n.embedding FROM nodes n
  WHERE n.kind = a.kind AND n.tenant_id = a.tenant_id AND n.id > a.id
  ORDER BY n.embedding <=> a.embedding LIMIT 3
) b ON true
WHERE a.embedding <=> b.embedding < 0.08
ORDER BY distance;

If you do merge, keep the merged-away node as a row with a merged_into pointer rather than deleting it, so that an edge extracted later from an older document still resolves and so the decision can be reversed. Deleting the duplicate makes the merge permanent and makes any provenance referring to it dangle.