Migrate from pgvector: Moving Agent RAG Memory to Graphs

Every engineering team I know building production AI agents starts on PostgreSQL with pgvector. It's the pragmatic choice. You already run Postgres for users, sessions, and billing, so adding a vector column to your chunks table avoids provisioning another database.

That setup works until your agents need real memory instead of isolated document retrieval. An agent answering user queries or running autonomous workflows needs more than semantic similarity. It needs to know which user authored a note, which project owns a document, which Jira tickets reference that document, and what happened in the last forty-eight hours. In pgvector, answering that means joining four normalized tables across a recursive CTE while filtering an HNSW index. Queries that took twenty milliseconds balloon into three-second table scans.

This guide shows you how to migrate from pgvector to HelixDB without re-embedding your corpus. You will inventory your current schema, map foreign keys directly into graph edges, bulk-load your existing float32 vectors, and rewrite disjoint SQL queries into a single graph-vector traversal.

Step 1: Decide if you actually need to migrate (the signs pgvector has hit its ceiling)

Postgres with pgvector is an excellent tool for straightforward semantic search. If your RAG pipeline takes a query, computes an embedding, fetches the top ten document chunks, and passes them to an LLM, stay on pgvector. Don't take on migration complexity if your retrieval patterns are strictly flat.

Migration becomes necessary when your retrieval hits three specific bottlenecks. First, you need multi-hop relationship awareness. When an agent retrieves a conversation chunk, it also needs the parent organization, the user's role, and the five most recent action items linked to that entity. In Postgres, you either run three successive round trips or write complex recursive CTEs that degrade under concurrency.

Second, your vector filtering hits the pre-filter trap. If you filter by tenant, project, and permission status before running vector similarity, pgvector must either scan an iterative index or fall back to an unindexed sequential scan. When filters are highly restrictive, index scans often struggle to find enough valid neighbors, causing recall to drop.

Third, orphaned chunk sprawl. When parent documents update, cascade deletes across foreign key tables leave dead vector rows or force complex transaction coordination across your memory pipeline. If your application code spends more lines joining edge tables to vector tables than parsing agent actions, you need a graph-vector database designed for relational context. For a deeper breakdown of this mismatch, read our guide on vector database vs graph database.

Step 2: Inventory your tables, vector columns, dimensions, distance metric, and foreign keys

Before writing export scripts, audit your Postgres database. You need a complete schema catalog covering vector properties, distance operators, and the foreign key relationships that will become graph edges.

Connect to your Postgres instance with psql and run an inventory query against the information schema:

SELECT 
    c.table_name, 
    c.column_name, 
    c.udt_name,
    i.indexdef
FROM information_schema.columns c
LEFT JOIN pg_indexes i 
    ON c.table_name = i.tablename
WHERE c.udt_name = 'vector';

Identify the exact vector dimensionality and operator class. Check whether your indexes use vector_cosine_ops (<=>), vector_l2_ops (<->), or vector_ip_ops (<#). HelixDB indexes normalize vectors to float32 and natively support cosine, euclidean, and manhattan distance. Make sure your application's distance calculations line up with cosine or euclidean configurations before migrating.

Next, extract the foreign keys that establish entity context. In a standard agent setup, you typically have tables for users, workspaces, agent_sessions, messages, and document_chunks. Query your relational constraints:

SELECT
    tc.table_name AS source_table,
    kcu.column_name AS source_column,
    ccu.table_name AS target_table,
    ccu.column_name AS target_column
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';

Note any many-to-many join tables, such as message_mentions or document_tags. These won't become nodes in HelixDB. They map directly into directional graph edges connecting your primary entities.

Step 3: Map your Postgres schema to a HelixDB graph (rows to nodes, foreign keys and join tables to edges, vectors attached)

HelixDB stores data as a property graph where vectors live as native properties directly on nodes and edges. You don't maintain a separate vector lookup table. A node represents an entity, and an edge represents a typed relationship between entities.

Map your Postgres relational tables to graph elements using these rules:

1. Entity rows become Nodes. Rows in your workspaces, users, agent_sessions, and documents tables map directly to nodes. Each node gets a label matching the table name (for example, Document or User) and keeps its original Postgres primary key as an external ID property.

2. Foreign keys become Edges. In Postgres, a message has a session_id foreign key column pointing to agent_sessions. In HelixDB, drop that column property and create a directed edge labeled BELONGS_TO from the Message node to the Session node.

3. Many-to-many join tables become Edges with properties. If you have a document_mentions join table containing document_id, entity_id, and mention_count, map it to a MENTIONS edge running from Document to Entity, with mention_count stored as an edge property.

4. Vector columns become top-level node properties. In HelixDB, vectors must be configured as top-level properties on the node or edge. If your document_chunks table has an embedding column with 1536 dimensions, that array attaches directly to the Chunk node under an embedding property.

Turning foreign key lookups into traversable graph edges removes join planning overhead. When you query relationships, the database traverses graph connections directly instead of computing B-tree index intersections.

Step 4: Export from Postgres in batches and bulk-load nodes, edges, and embeddings without re-embedding

Re-embedding a production dataset of millions of chunks costs thousands of dollars and days of rate-limited API calls. You don't need to recompute a single vector. Your float32 embeddings stored in Postgres transfer directly into HelixDB.

Export your data from Postgres in cursor-paginated JSON batches using Python. Skip raw SQL dumps, which need heavy transformation. Stream your records using psycopg:

import psycopg2
import psycopg2.extras
import json

conn = psycopg2.connect("postgresql://user:pass@localhost:5432/rag_db")
cursor = conn.cursor(name="stream_cursor", cursor_factory=psycopg2.extras.RealDictCursor)

cursor.itersize = 2000
cursor.execute("""
    SELECT id, document_id, content, created_at, embedding::text 
    FROM document_chunks
""")

batch = []
for row in cursor:
    # pgvector text format is "[0.012,-0.034,...]"
    vector_floats = json.loads(row["embedding"])
    batch.append({
        "id": str(row["id"]),
        "document_id": str(row["document_id"]),
        "content": row["content"],
        "created_at": row["created_at"].isoformat(),
        "embedding": vector_floats
    })
    if len(batch) >= 2000:
        # Write batch to HelixDB
        batch = []

To insert these batches into HelixDB, use the TypeScript SDK (@helix-db/helix-db) or the Python SDK. HelixDB supports atomic transactions, so graph structures and vector index insertions commit together. Here is how you write nodes and directed edges using the TypeScript SDK:

import { writeBatch, NodeRef } from "@helix-db/helix-db";

async function loadBatch(items: any[]) {
  const mutations = [];
  
  for (const item of items) {
    // Create the chunk node with attached vector
    const chunkNode = NodeRef.new("Chunk", item.id);
    mutations.push({
      type: "create_node",
      ref: chunkNode,
      properties: {
        content: item.content,
        created_at: item.created_at,
        embedding: item.embedding // Raw float32 array
      }
    });
    
    // Create the edge linking chunk to parent document
    const docNode = NodeRef.new("Document", item.document_id);
    mutations.push({
      type: "create_edge",
      label: "PART_OF",
      from: chunkNode,
      to: docNode,
      properties: {}
    });
  }
  
  await writeBatch(mutations);
}

Load entities first (users, workspaces, documents), then child nodes with embeddings (chunks, messages), and finally cross-cutting relational edges. If your model or vector dimensions ever change down the road, follow our checklist on re-embedding agent memory.

Step 5: Rewrite retrieval as one query: vector seed, graph traversal, and time-range filter

In Postgres, retrieving contextual agent memory takes two or three distinct queries. You first run a vector similarity query to find relevant chunks. Then you collect the foreign keys, query the users and documents tables, and finally query a message log to fetch recent context.

HelixDB collapses this pattern into a single JSON query executed over the /v2/query HTTP endpoint. HelixDB provides vector pre-filtering, so you can run similarity search strictly over entities that share specific graph relationships or time boundaries instead of scanning the entire index.

Take a common agent memory retrieval task: find relevant knowledge chunks authored by members of Team Engineering created in the last 30 days. In Postgres, you write a join across four tables with an order-by distance clause. In HelixDB, you express the same retrieval using the TypeScript SDK:

import { g, Predicate, VectorDistanceMetric } from "@helix-db/helix-db";

const query = g
  .nodes("Team")
  .has("name", Predicate.eq("Engineering"))
  .out("MEMBER_OF") // Traverse to Users
  .out("AUTHORED")   // Traverse to Documents
  .out("HAS_CHUNK")  // Traverse to Chunks
  .whereTimeRange("created_at", { gte: Date.now() - 30 * 86400 * 1000 })
  .vectorSearch({
    property: "embedding",
    vector: queryEmbedding,
    metric: VectorDistanceMetric.Cosine,
    limit: 10
  })
  .extract(["content", "created_at"]);

Look at how retrieval works here. The traversal walks from the team entity directly along relational edges to candidate chunks, filters those chunks using HelixDB's time-range indexing, and runs cosine vector similarity only over the surviving candidate set. You don't hit the 800 effective result limit on unrestricted vector searches because the graph pre-filters the search space. To master edge-level filtering, consult our guide on vector search on graph edges.

Step 6: Validate with the same query vectors against both systems and compare top-k and filtering

Don't swap production traffic until you've verified that retrieval outputs match or improve. Build a validation script that runs one thousand real production queries against both Postgres and HelixDB simultaneously.

Your evaluation harness must measure three metrics: recall overlap, latency under load, and candidate filtering correctness.

For pure vector lookups without graph filtering, compute the Jaccard similarity of the top-ten ID sets returned by pgvector and HelixDB:

def calculate_overlap(pg_results: list[str], helix_results: list[str]) -> float:
    set_a = set(pg_results)
    set_b = set(helix_results)
    intersection = len(set_a.intersection(set_b))
    union = len(set_a.union(set_b))
    return intersection / union if union > 0 else 1.0

pgvector uses an HNSW approximation while HelixDB uses its own native index over float32 vectors, so top-ten sets won't be identical. Top-k overlap should still remain high on raw similarity.

Next, validate filtered queries. Test scenarios where a query targets a small sub-graph, such as an isolated tenant with only five hundred documents out of a two-million-node database. In pgvector, check the execution plan using EXPLAIN ANALYZE. You will often see Postgres abandon the vector index for a sequential heap scan, pushing latency from 15 milliseconds up to 800 milliseconds.

In HelixDB, latency stays flat. Graph traversal prunes non-qualifying nodes before vector scoring runs, so execution scales with the size of the subgraph, not the size of the total database. For more on time-scoped indexing benchmarks, read query agent memory by time range.

Step 7: Sync the delta, shadow-read, cut over, and keep pgvector as a rollback path

Never do a hard cutover for agent memory. Production agents depend on continuous conversational state. If your new database fails or drops connections during peak load, your agents lose context immediately.

Run the transition as a three-phase deployment:

Phase 1: Dual writing. Update your application's ingestion workers to write incoming memories to both PostgreSQL and HelixDB. Make HelixDB writes asynchronous via an in-memory queue or background worker, so transient errors don't block existing Postgres operations.

Phase 2: Shadow reading. Route live agent queries to both databases. Return the Postgres result to the LLM agent, but log HelixDB's response, execution time, and memory payload to your telemetry pipeline (such as Datadog or OpenTelemetry). Monitor shadow reads for forty-eight hours. Verify that HelixDB returns zero query errors. A candidate stream exceeding 1,000,000 unique entities will raise a query error, so check that your graph traversals are properly bounded.

Phase 3: Live switch with rollback buffer. Flip the feature flag so agent prompts get retrieved context from HelixDB. Keep dual-writing to PostgreSQL for at least seven days. If edge cases emerge, flipping the retrieval feature flag instantly routes traffic back to pgvector without data loss.

Once shadow traffic confirms stability, decommission your Postgres vector extensions and drop the embedding columns. Keep Postgres for standard transactional application data and let HelixDB serve as your dedicated AI memory store.

What to do next and troubleshooting (dimension or metric mismatches, missing edges, tenant scoping)

Migrating production workloads off pgvector, you may run into three common pitfalls.

First, distance metric and normalization discrepancies. If your Postgres embeddings were generated with OpenAI text-embedding-3-small and queried using dot product (<#), remember that dot product only equals cosine distance if vectors are strictly unit-normalized. If your vectors were stored unnormalized, cosine distance in HelixDB will produce different rank ordering. Either normalize vectors explicitly in your embedding pipeline before insertion, or pick euclidean distance if absolute magnitudes carry semantic weight.

Second, orphaned nodes from missing foreign keys. In Postgres, loose application code often creates rows where foreign keys are null or unenforced. Mapped to HelixDB, these entities become disconnected islands that graph traversal can't reach. Run audit queries in Postgres before export to find chunks missing document_id or author_id references. Fix these orphaned rows or assign them default fallback nodes.

Third, tenant scoping. In Postgres, multi-tenancy is often handled via schema separation or Row-Level Security (RLS). It offers native tenant partitioning for index isolation instead. When creating vector indexes in HelixDB, designate tenant identifiers in your index definitions to partition memory spaces cleanly across organizations. Your application authorization must validate tenant access before issuing JSON queries.

Conclusion

Postgres with pgvector took your AI product from prototype to production. But when your agents need deep relational context, multi-hop traversals, and temporal precision, relational joins over vector tables become an operational bottleneck.

You don't need to discard your existing investments or spend weeks re-embedding your data. Migrating from pgvector to HelixDB keeps your vector embeddings and turns rigid foreign keys into a knowledge graph. Star HelixDB on GitHub or deploy an instance today to replace multi-table SQL duct tape with a single graph-vector engine built for agent memory.