Postgres vectors with persistent context

Your database, your vectors, your memory

The Problem

pgvector is PostgreSQL's secret weapon. Vectors in your existing database, queryable with SQL, managed with the tools you already know. No separate vector database to deploy, monitor, and pay for. Your embeddings sit right next to your application data, joined with a simple WHERE clause.

But SQL can't remember why you're querying.

pgvector turns Postgres into a capable vector database. It doesn't turn it into a context-aware assistant. Your database knows that document A is 0.85 similar to your query vector. It doesn't know that you already tried approach A yesterday and it didn't work for your specific situation.

You've got the cleanest RAG setup imaginable: one database for everything. Documents, embeddings, application data, all in one transactional system. Beautiful simplicity.

And every query starts fresh, with zero memory of previous interactions.

Your WHERE clause can filter by date, by author, by document type. It can't filter by "things that are actually relevant given what we've already decided." That kind of context doesn't live in columns—and pgvector, for all its brilliance, only knows about columns.

SQL joins don't solve the memory problem.

How Stompy Helps

Stompy adds the project memory layer that complements pgvector's SQL simplicity.

Your all-in-Postgres RAG gains contextual intelligence: - **SQL + context**: Your familiar vector queries, enriched with project decisions that SQL can't store - **Transactional documents, persistent decisions**: pgvector handles your corpus; Stompy handles what you've learned - **No architecture change**: Add context alongside your existing pgvector queries—no schema migration needed - **Development workflow**: The same SQLAlchemy/Prisma/raw SQL you're already using, just with smarter results

The combination is elegant: pgvector does what it does brilliantly (similarity search in SQL), and Stompy adds what it can't do (persistent project memory). Two simple tools that together create context-aware RAG.

Postgres for data. Stompy for decisions. Both queryable.

Integration Walkthrough

Set up pgvector + Stompy architecture

Keep your existing pgvector setup, add Stompy for project context. Both use simple APIs.

import asyncpg
import httpx
import os

# pgvector: Vectors in your Postgres
async def get_pg_pool():
    return await asyncpg.create_pool(os.environ['DATABASE_URL'])

# Stompy: Project context alongside your database
async def get_project_context(topic: str) -> str:
    async with httpx.AsyncClient() as client:
        response = await client.post(
            "https://mcp.stompy.ai/sse",
            headers={"Authorization": f"Bearer {os.environ['STOMPY_TOKEN']}"},
            json={"tool": "recall_context", "topic": topic}
        )
        return response.json().get("content", "")

# Your existing pgvector table stays exactly as-is
# CREATE TABLE documents (
#   id SERIAL PRIMARY KEY,
#   content TEXT,
#   embedding vector(1536),
#   doc_type VARCHAR(50),
#   created_at TIMESTAMP DEFAULT NOW()
# );

Context-aware vector queries

Your SQL stays familiar; you just add project context to inform how results are interpreted.

async def contextual_sql_rag(user_query: str, doc_type: str = None):
    """RAG with SQL simplicity and project context."""
    pool = await get_pg_pool()

    # Get project context from Stompy
    architecture_context = await get_project_context("architecture_decisions")
    tech_stack = await get_project_context("tech_stack")

    # Build pgvector query (your existing SQL patterns work)
    query_embedding = await embed(user_query)

    sql = """
        SELECT content, doc_type, 1 - (embedding <=> $1) AS similarity
        FROM documents
        WHERE ($2::varchar IS NULL OR doc_type = $2)
        ORDER BY embedding <=> $1
        LIMIT 5
    """

    async with pool.acquire() as conn:
        results = await conn.fetch(sql, query_embedding, doc_type)

    # Combine SQL results with project context
    docs = [{"content": r["content"], "similarity": r["similarity"]} for r in results]

    prompt = f"""Project Context:
{architecture_context}

Tech Stack:
{tech_stack}

Retrieved Documents (from pgvector):
{docs}

Question: {user_query}

Answer considering our project's specific context:"""

    return await call_llm(prompt)

Track decisions made from queries

When your pgvector RAG helps you make decisions, save them to Stompy so future queries benefit.

async def save_decision_from_rag(
    topic: str,
    decision: str,
    supporting_docs: list[str],
    query_used: str
):
    """Track decisions made based on pgvector retrieval."""
    async with httpx.AsyncClient() as client:
        await client.post(
            "https://mcp.stompy.ai/sse",
            headers={"Authorization": f"Bearer {os.environ['STOMPY_TOKEN']}"},
            json={
                "tool": "lock_context",
                "topic": topic,
                "content": f"""Decision: {decision}

Based on query: "{query_used}"
Supporting documents retrieved: {len(supporting_docs)}

Key supporting evidence:
{chr(10).join(f'- {doc[:200]}...' for doc in supporting_docs[:3])}

This decision was made using pgvector RAG retrieval.""",
                "tags": "decisions,pgvector,rag"
            }
        )

# Example: After deciding on caching approach
await save_decision_from_rag(
    topic="caching_architecture",
    decision="Use Redis Cluster with read replicas",
    supporting_docs=results,
    query_used="distributed caching strategies high availability"
)

What You Get

No new database: Add project memory without deploying another system—pgvector + Stompy is enough
SQL familiarity: Your existing queries work unchanged; Stompy adds context on top
Transactional safety: Documents stay in Postgres with ACID guarantees; decisions persist in Stompy
Cost-efficient: One database for vectors means lower infrastructure costs; Stompy adds memory affordably
Migration-free: No schema changes needed—just add context to your existing RAG pipeline

Ready to give pgvector a memory?

Join the waitlist and be the first to know when Stompy is ready. Your pgvector projects will never forget again.

Related Integrations

Pinecone

RAG & Knowledge

Vector search meets project memory

Learn more

Chroma

RAG & Knowledge

Open-source vectors with persistent context

Learn more

LlamaIndex

Agent Frameworks

Indexes that remember what they indexed

Learn more

View all integrations