Vector Search & Embeddings with pgvector: Building an Open-Source RAG Engine

Every modern SaaS wants an AI assistant or RAG (Retrieval-Augmented Generation) search engine to answer user queries using product documentation.

However, the prevailing advice from vendor marketing is to spin up a specialized vector database (Pinecone, Qdrant, Weaviate). For 95% of applications, introducing a dedicated vector database introduces severe operational friction:

• Data synchronization lag between your primary PostgreSQL database and your vector store.
• Lack of ACID transactions across relational records and embeddings.
• Additional cloud service bills and complex IAM permissions.

In ⚡ PLYXO (CRO • SEO • AIO • AEO • GEO), we build our entire RAG embedding engine directly inside PostgreSQL using pgvector.

---

1. Setting Up pgvector & HNSW Indexing

Postgres with pgvector can easily handle millions of vector embeddings with sub-10ms query times using the HNSW (Hierarchical Navigable Small World) indexing algorithm.

-- Enable the vector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Table to store documentation chunks and embeddings
CREATE TABLE doc_embeddings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  url TEXT NOT NULL,
  chunk_index INT NOT NULL,
  content TEXT NOT NULL,
  embedding vector(1536), -- Dimension for text-embedding-3-small or Gemini
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Fast Approximate Nearest Neighbor Index using Cosine Distance
CREATE INDEX doc_embeddings_hnsw_idx 
ON doc_embeddings 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

---

2. End-to-End RAG Ingestion Pipeline in TypeScript

Here is our production pipeline for chunking documentation, generating embeddings, and storing them in Postgres:

import { GoogleGenAI } from '@google/genai';
import { db } from '@/db';
import { docEmbeddings } from '@/db/schema';

const ai = new GoogleGenAI({});

export async function ingestDocument(url: string, rawMarkdown: string, tenantId: string) {
  // Step 1: Semantic Chunking (split by headings or ~500 token blocks)
  const chunks = chunkMarkdown(rawMarkdown, 500);

  for (let i = 0; i < chunks.length; i++) {
    // Step 2: Generate high-dimensional vector embeddings
    const response = await ai.models.embedContent({
      model: 'text-embedding-004',
      contents: chunks[i],
    });

    const vector = response.embedding?.values;
    if (!vector) continue;

    // Step 3: Insert directly into PostgreSQL
    await db.insert(docEmbeddings).values({
      tenantId,
      url,
      chunkIndex: i,
      content: chunks[i],
      embedding: vector,
    });
  }
}

---

3. Cosine Similarity Query with Hybrid Keyword Search

One of the greatest advantages of pgvector is Hybrid Search: combining semantic vector proximity with traditional Postgres full-text search (tsvector) in a single query:

import { sql } from 'drizzle-orm';

export async function searchDocumentation(queryVector: number[], queryText: string, tenantId: string) {
  return await db.execute(sql`
    SELECT 
      id,
      content,
      url,
      (1 - (embedding <=> ${JSON.stringify(queryVector)}::vector)) AS vector_score,
      ts_rank(to_tsvector('english', content), plainto_tsquery('english', ${queryText})) AS text_score
    FROM doc_embeddings
    WHERE tenant_id = ${tenantId}
    ORDER BY (
      (1 - (embedding <=> ${JSON.stringify(queryVector)}::vector)) * 0.7 +
      ts_rank(to_tsvector('english', content), plainto_tsquery('english', ${queryText})) * 0.3
    ) DESC
    LIMIT 5;
  `);
}

---

4. Why This Architecture Wins

• Zero Sync Drift: When a document is deleted in Postgres, its embeddings are deleted in the same atomic transaction.
• Cost Efficiency: No $70/month starter fees for external vector cloud vendors.
• RLS Compatible: pgvector respects PostgreSQL Row-Level Security, guaranteeing zero cross-tenant embedding retrieval.

👉 Inspect Plyxo's pgvector RAG implementation on GitHub