Unifying Vector, Graph, and Relational Queries: A Real-World Agent Memory Deep Dive

Unifying Vector, Graph, and Relational Queries: A Real-World Agent Memory Deep Dive

We hit a wall with agent memory. Autonomous agents needed to retrieve facts (vector search), follow entity relationships (graph traversal), and filter by metadata (relational) — all in one query. Separate stores meant two-phase commits, inconsistent state, and latency spikes.

So we built a unified query layer on top of PostgreSQL, using pgvector for embeddings, recursive CTEs for graph walks, and standard SQL for relational filters. The result: a single SELECT that returns context-rich memories efficiently.

The Problem: Three Minds, One Agent

Our agents store three kinds of memory:

• Episodic: raw experiences, indexed by embedding (vector).
• Semantic: facts about entities and relationships (graph).
• Procedural: rules and parameters (relational).

Initially, we used separate stores: a vector database for embeddings, a graph database for relationships, and a relational database for metadata. Every agent step required multiple queries across these systems, adding significant latency. Worse, data consistency was a nightmare: if the graph updated but the vector index didn't, agents acted on stale context.

Why PostgreSQL + Extensions?

We wanted a single database. PostgreSQL with pgvector and recursive CTEs can handle all three query types. Vector search (IVFFlat), graph traversal (recursive CTEs), and relational filters all run in the same engine, eliminating network hops and consistency issues.

Schema Design

We store everything in three tables:

CREATE TABLE episodes (
    id BIGSERIAL PRIMARY KEY,
    agent_id UUID NOT NULL,
    content TEXT NOT NULL,
    embedding vector(768),          -- pgvector
    metadata JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE entities (
    id BIGSERIAL PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    type TEXT NOT NULL,
    properties JSONB
);

CREATE TABLE relationships (
    id BIGSERIAL PRIMARY KEY,
    source_id BIGINT REFERENCES entities(id),
    target_id BIGINT REFERENCES entities(id),
    rel_type TEXT NOT NULL,
    weight FLOAT DEFAULT 1.0
);

CREATE INDEX idx_episodes_embedding ON episodes USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
CREATE INDEX idx_episodes_agent ON episodes (agent_id);
CREATE INDEX idx_episodes_metadata ON episodes USING gin (metadata);
CREATE INDEX idx_relationships_source ON relationships (source_id);
CREATE INDEX idx_relationships_target ON relationships (target_id);

The Unified Query

The core insight: use a recursive CTE to traverse the graph, then join with vector search results. Here's the combined query:

WITH vector_matches AS (
    SELECT id, content, metadata, 1 - (embedding <=> $query_embedding) AS similarity
    FROM episodes
    WHERE agent_id = $agent_id
    ORDER BY embedding <=> $query_embedding
    LIMIT 10
),
graph_context AS (
    SELECT DISTINCT e.id, e.name, e.type, e.properties
    FROM entities e
    JOIN relationships r ON e.id = r.target_id
    WHERE r.source_id IN (
        SELECT unnest(metadata->'entity_ids')::bigint
        FROM vector_matches
    )
    UNION
    SELECT e.id, e.name, e.type, e.properties
    FROM entities e
    JOIN relationships r ON e.id = r.source_id
    WHERE r.target_id IN (
        SELECT unnest(metadata->'entity_ids')::bigint
        FROM vector_matches
    )
),
filtered AS (
    SELECT vm.id, vm.content, vm.similarity, gc.name AS entity_name, gc.type AS entity_type
    FROM vector_matches vm
    LEFT JOIN graph_context gc ON vm.metadata->'entity_ids' ? gc.id::text
    WHERE vm.metadata->>'priority' = 'high'
       OR vm.created_at > NOW() - INTERVAL '1 hour'
)
SELECT * FROM filtered ORDER BY similarity DESC LIMIT 5;

This returns the top-5 high-priority or recent episodes, along with related entity names. All in one round trip.

Performance Optimizations

1. Precomputed Entity IDs

We store entity_ids as a JSONB array in episodes.metadata. This avoids a join during vector search. The array is updated via triggers when relationships change.

2. Partial Indexes for Metadata

CREATE INDEX idx_high_priority ON episodes (created_at) WHERE metadata->>'priority' = 'high';

This speeds up the WHERE clause in filtered.

3. Recursive CTE with LIMIT

For deeper graph traversals, we add a depth limit:

WITH RECURSIVE graph_context AS (
    SELECT id, name, type, 0 AS depth
    FROM entities
    WHERE id IN (SELECT unnest(metadata->'entity_ids')::bigint FROM vector_matches)
    UNION ALL
    SELECT e.id, e.name, e.type, gc.depth + 1
    FROM entities e
    JOIN relationships r ON e.id = r.target_id
    JOIN graph_context gc ON r.source_id = gc.id
    WHERE gc.depth < 2
)
SELECT * FROM graph_context;

4. Vector Index Tuning

IVFFlat with appropriate list count balances recall and speed. For higher recall, HNSW can be used with an appropriate ef_search parameter.

Tradeoffs

• Write amplification: Updating entity_ids on every relationship change adds overhead to writes, but reads are significantly faster than separate stores.
• Graph depth: Recursive CTEs are fast up to moderate depths. Beyond that, consider a materialized path pattern (store path as array).
• Vector dimension: Higher dimensions slow down IVFFlat. Consider dimensionality reduction for very high dimensions.
• Consistency: Use READ COMMITTED isolation for performance. For strict consistency, use SERIALIZABLE but expect lower throughput.

When Not to Use This

• If your graph has very large scale with deep traversals, consider a dedicated graph DB.
• If your vector dimension is extremely high, use pgvector's halfvec type or a dedicated vector DB.
• If you need full-text search combined with vector, consider the pg_bm25 extension.

The Code

Here's a Go client example:

package unified

import (
    "context"
    "github.com/jackc/pgx/v5/pgxpool"
)

type UnifiedQuery struct {
    Embedding []float32
    AgentID   string
    MaxDepth  int
    Limit     int
}

type MemoryResult struct {
    ID         int64
    Content    string
    Similarity float64
    EntityName string
    EntityType string
}

func (u *UnifiedQuery) Execute(ctx context.Context, pool *pgxpool.Pool) ([]MemoryResult, error) {
    query := `
        WITH vector_matches AS (
            SELECT id, content, metadata, 1 - (embedding <=> $1) AS similarity
            FROM episodes
            WHERE agent_id = $2
            ORDER BY embedding <=> $1
            LIMIT $3
        ),
        graph_context AS (
            SELECT DISTINCT e.id, e.name, e.type
            FROM entities e
            JOIN relationships r ON e.id = r.target_id
            WHERE r.source_id = ANY(
                SELECT jsonb_array_elements_text(metadata->'entity_ids')::bigint
                FROM vector_matches
            )
            UNION
            SELECT e.id, e.name, e.type
            FROM entities e
            JOIN relationships r ON e.id = r.source_id
            WHERE r.target_id = ANY(
                SELECT jsonb_array_elements_text(metadata->'entity_ids')::bigint
                FROM vector_matches
            )
        )
        SELECT vm.id, vm.content, vm.similarity, gc.name, gc.type
        FROM vector_matches vm
        LEFT JOIN graph_context gc ON vm.metadata->'entity_ids' ? gc.id::text
        WHERE vm.metadata->>'priority' = 'high'
           OR vm.created_at > NOW() - INTERVAL '1 hour'
        ORDER BY vm.similarity DESC
        LIMIT $4;
    `
    rows, err := pool.Query(ctx, query, u.Embedding, u.AgentID, u.Limit, u.Limit)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var results []MemoryResult
    for rows.Next() {
        var r MemoryResult
        if err := rows.Scan(&r.ID, &r.Content, &r.Similarity, &r.EntityName, &r.EntityType); err != nil {
            return nil, err
        }
        results = append(results, r)
    }
    return results, nil
}

Lessons Learned

1. Start with PostgreSQL: It handles a wide range of use cases. Only add specialized stores when you hit clear bottlenecks.
2. Measure before optimizing: Merging separate stores into one unified query can dramatically reduce latency.
3. Embrace JSONB: It's surprisingly fast for metadata filtering. Use GIN indexes.
4. Recursive CTEs are underrated: They scale well for moderate graph depths and avoid ETL pipelines.

Future Work

We're exploring:

• Hybrid search: Combining vector similarity with BM25 using pg_bm25.
• Graph neural networks: Using pgvector to store node embeddings for link prediction.
• Auto-indexing: A background worker that monitors query patterns and creates indexes dynamically.

Unified querying isn't just about performance — it's about making agent memory a single source of truth. No sync jobs, no stale caches. Just SQL.