How remii Uses pgvector for Retrieval-Augmented Generation

To provide personalized context without bloating LLM context windows, remii utilizes an advanced Retrieval-Augmented Generation (RAG) pipeline. Instead of storing memories in flat files or relying on in-memory arrays, we store them as high-dimensional vector embeddings in a PostgreSQL database using the `pgvector` extension.

This architectural decision allows us to query complex relationship data (like user profiles, team organizations, and API credentials) alongside semantic memories in a single, atomic database transaction using standard SQL.

### The Limits of In-Memory RAG

When building a prototype AI agent, developers often use in-memory vector stores like FAISS or HNSWLIB, or they use document-based NoSQL stores. While these are fast for local testing, they present massive challenges when scaling to a multi-tenant cloud application:

1. **Statefulness and Scaling**: In-memory vector databases require significant RAM and complicate horizontal scaling. If you have a cluster of 50 Kubernetes pods processing agent requests, keeping an in-memory vector index synced across all pods is a distributed systems nightmare. 2. **Data Consistency**: When a user deletes a memory, you must ensure it is deleted from both the main application database and the separate vector store simultaneously. This requires complex distributed transactions or eventual consistency patterns. 3. **Security**: Applying granular Row-Level Security (RLS) to an external vector database is difficult. You often have to fetch the vectors, bring them into the application layer, and filter them manually, which is slow and error-prone.

### Why We Chose PostgreSQL and pgvector

PostgreSQL is arguably the most robust, battle-tested relational database in the world. By using the `pgvector` extension, we bring vector similarity search directly into the relational engine.

#### 1. Unified Querying With pgvector, we can combine vector similarity search with standard SQL `WHERE` clauses. For example, if an agent needs to retrieve memories about "deployments," but only memories created in the last 7 days, we can execute:

```sql SELECT content FROM agent_memories WHERE created_at > NOW() - INTERVAL '7 days' ORDER BY embedding <=> '[0.1, 0.4, -0.2...]' LIMIT 5; ```

This combined query executes in a single pass, utilizing standard database caching mechanisms.

#### 2. Acid Compliance and Consistency Because the vectors are stored in standard Postgres tables, they inherit full ACID compliance. If a user deletes their account, a standard `ON DELETE CASCADE` foreign key constraint automatically and instantly wipes their associated vector memories. There are no dangling embeddings or synchronization delays.

#### 3. Simplified Infrastructure By using our existing primary Postgres cluster for vector storage, we eliminate the need to provision, monitor, and scale a completely separate infrastructure component (like Pinecone or Milvus). This simplifies our DevOps overhead and reduces points of failure.

### pgvector Query Optimization

Calculating the exact cosine distance between a query vector and millions of stored vectors is computationally expensive (an $O(N)$ operation). To ensure sub-10ms query times even as our user base grows, we implement indexing.

When a vector table is small, an exact nearest neighbor (KNN) search is sufficient. However, as tables grow beyond 100,000 rows, we use **HNSW (Hierarchical Navigable Small World)** indexing.

HNSW is a graph-based algorithm that provides Approximate Nearest Neighbor (ANN) search. It builds a multi-layered graph where the bottom layer contains all the vectors, and upper layers contain progressively fewer vectors. A search starts at the top layer, finds the closest nodes, and drops down to the next layer, rapidly converging on the nearest neighbors without scanning the entire table.

### Constructing the RAG Prompt

When the agent receives a prompt, the full pipeline executes in milliseconds: 1. The user's query is sent to an embedding model (e.g., OpenAI `text-embedding-3-small`). 2. The resulting 1536-dimensional vector is passed to Postgres. 3. The HNSW index rapidly identifies the top 5 semantically similar memories. 4. The backend injects these memory strings into a hidden section of the LLM system prompt. 5. The LLM generates a response, natively incorporating the injected context.

By deeply integrating `pgvector` into our core architecture, remii delivers lightning-fast, secure, and infinitely scalable context retrieval, giving every AI employee perfect, long-term memory.