Vector Database Design

Infrastructure Decision

Step 2 — Embedding Model Selection

Model Comparison (2025)

Matryoshka Embeddings (Dimension Reduction)

# OpenAI text-embedding-3 supports truncation via `dimensions` param
# Reduces storage/compute while preserving most accuracy

import openai

def embed(text: str, dimensions: int = 256) -> list[float]:
    """256 dimensions retains ~95% of full accuracy at 6x less storage."""
    response = openai.embeddings.create(
        model="text-embedding-3-small",
        input=text,
        dimensions=dimensions,
    )
    return response.data[0].embedding

Step 3 — Document Chunking Strategy

Chunking is the most impactful decision for RAG quality.

Chunking Strategies

Implementation with LangChain / Custom

# Recursive text splitter — respects sentence and paragraph boundaries
from langchain.text_splitter import RecursiveCharacterTextSplitter

splitter = RecursiveCharacterTextSplitter(
    chunk_size=1000,
    chunk_overlap=200,
    separators=["\n\n", "\n", ". ", " ", ""],
    length_function=len,
)

chunks = splitter.split_text(document_content)

# Enrich each chunk with metadata for filtering
enriched_chunks = [
    {
        "content": chunk,
        "metadata": {
            "source_id": document.id,
            "source_url": document.url,
            "page_number": estimate_page(chunk, document),
            "chunk_index": i,
            "total_chunks": len(chunks),
            "heading": extract_nearest_heading(chunk, document),
        }
    }
    for i, chunk in enumerate(chunks)
]

Parent-Child Chunking (Best Retrieval + Context)

# Store small chunks for precise retrieval
# Store parent chunks for context in generation
small_splitter = RecursiveCharacterTextSplitter(chunk_size=300, chunk_overlap=50)
large_splitter = RecursiveCharacterTextSplitter(chunk_size=1500, chunk_overlap=100)

# Index small chunks; retrieve and return parent chunks
small_chunks = small_splitter.split_text(doc)
parent_chunks = large_splitter.split_text(doc)

# Map small → parent for retrieval expansion

Step 4 — pgvector Schema & Index Design

Schema

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

CREATE TABLE document_chunks (
  id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  document_id UUID        NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  content     TEXT        NOT NULL,
  embedding   vector(1536),          -- Match your model's dimensions
  metadata    JSONB       NOT NULL DEFAULT '{}',
  chunk_index INT         NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Metadata for filtering
CREATE INDEX idx_chunks_document ON document_chunks(document_id);
CREATE INDEX idx_chunks_metadata ON document_chunks USING GIN(metadata);

HNSW vs IVFFlat Index

-- HNSW index (recommended for production)
CREATE INDEX idx_chunks_embedding_hnsw ON document_chunks
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);
-- m: number of connections per layer (16 is default; higher = more recall, more memory)
-- ef_construction: search width during build (higher = better index, slower build)

-- IVFFlat index (alternative for large datasets)
-- Must have data first (k-means clustering)
CREATE INDEX idx_chunks_embedding_ivf ON document_chunks
  USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 100);  -- lists ≈ sqrt(row_count)
-- Set probes at query time: SET ivfflat.probes = 10;

Cosine Similarity Query

-- Semantic search with metadata filter
SELECT
  dc.id,
  dc.content,
  dc.metadata,
  1 - (dc.embedding <=> $1::vector) AS similarity
FROM document_chunks dc
WHERE dc.metadata->>'source_type' = 'documentation'
  AND dc.created_at > NOW() - INTERVAL '30 days'
ORDER BY dc.embedding <=> $1::vector  -- <=> cosine, <-> L2, <#> inner product
LIMIT 10;

Step 5 — Hybrid Search

Combines dense (semantic) + sparse (keyword/BM25) retrieval for best recall.

-- Hybrid search using RRF (Reciprocal Rank Fusion)
-- Dense: pgvector cosine similarity
-- Sparse: full-text search with ts_rank

WITH semantic AS (
  SELECT id, content,
         ROW_NUMBER() OVER (ORDER BY embedding <=> $1::vector) AS rank
  FROM document_chunks
  WHERE embedding <=> $1::vector < 0.3  -- cosine distance threshold
  LIMIT 20
),
keyword AS (
  SELECT id, content,
         ROW_NUMBER() OVER (ORDER BY ts_rank(to_tsvector('english', content), query) DESC) AS rank
  FROM document_chunks,
       plainto_tsquery('english', $2) AS query
  WHERE to_tsvector('english', content) @@ query
  LIMIT 20
),
rrf AS (
  SELECT
    COALESCE(s.id, k.id) AS id,
    COALESCE(s.content, k.content) AS content,
    (COALESCE(1.0 / (60 + s.rank), 0) + COALESCE(1.0 / (60 + k.rank), 0)) AS rrf_score
  FROM semantic s
  FULL OUTER JOIN keyword k ON s.id = k.id
)
SELECT id, content, rrf_score
FROM rrf
ORDER BY rrf_score DESC
LIMIT 5;

TypeScript RAG Pipeline

// lib/rag.ts
export async function retrieve(query: string, filters?: Record<string, string>) {
  const embedding = await embed(query);

  const results = await db.$queryRaw<Chunk[]>`
    SELECT id, content, metadata,
           1 - (embedding <=> ${embedding}::vector) AS similarity
    FROM document_chunks
    WHERE 1 - (embedding <=> ${embedding}::vector) > 0.7
      ${filters ? Prisma.sql`AND metadata @> ${filters}::jsonb` : Prisma.empty}
    ORDER BY embedding <=> ${embedding}::vector
    LIMIT 5
  `;

  return results;
}

export async function generateWithContext(question: string) {
  const chunks = await retrieve(question);
  const context = chunks.map(c => c.content).join('\n\n---\n\n');

  return openai.chat.completions.create({
    model: 'gpt-4o-mini',
    messages: [
      { role: 'system', content: 'Answer based on the provided context only.' },
      { role: 'user', content: `Context:\n${context}\n\nQuestion: ${question}` },
    ],
    temperature: 0,
  });
}

Quality Criteria

1. Embedding model dimension matches vector column — vector(1536) for OpenAI v3-small; mismatches cause silent errors.
2. HNSW index built after initial data load — Building on empty table then inserting defeats HNSW quality.
3. Cosine distance operator matches index — <=> with vector_cosine_ops index; mixing operators bypasses the index.
4. Chunk size validated for retrieval quality — Tested against representative queries, not just inserted blindly.
5. Metadata filtering at DB level — Not post-filtering in application code; defeats index efficiency.
6. Similarity threshold enforced — WHERE similarity > 0.7 prevents returning irrelevant chunks to LLM.
7. Parent-child or context window — Retrieved chunk includes surrounding context for generation.
8. Embedding cost tracked — Embeddings are not free; batch processing and caching used for repeated content.

Anti-Patterns

• Embedding entire documents as one vector — A 10-page PDF produces one vector; retrieval returns the whole document or misses specific answers. Always chunk.
• No vector index (brute-force search) — SELECT ... ORDER BY embedding <=> $1 LIMIT 10 without an index does O(n) comparisons; acceptable at < 10k rows, catastrophic at 1M+.
• Wrong distance metric for the model — OpenAI embeddings are normalized (unit vectors); cosine and dot product give the same ranking, but L2 (<->) does not. Check model documentation.
• Chunking without overlap — Information spanning chunk boundaries is never retrieved; always use 10-20% overlap.
• Re-embedding unchanged documents on every pipeline run — Recompute embeddings only on content change (hash-based change detection); embeddings are expensive.
• Storing raw embeddings in application memory — Cache at the database layer with pgvector, not in Redis or application memory; embeddings are large (6KB per 1536-dim vector).
• Not testing retrieval quality with real queries — "Indexing works" is not the same as "retrieval returns relevant results"; evaluate with MRR or NDCG metrics.