Vector Databases & AI Search: pgvector, Pinecone & Embeddings Guide

-- Install pgvector 
CREATE EXTENSION IF NOT EXISTS vector;

-- Table with embedding column
CREATE TABLE blog_chunks (
  id SERIAL PRIMARY KEY,
  post_id INTEGER REFERENCES posts(id),
  chunk_index INTEGER,
  content TEXT NOT NULL,
  embedding vector(1536),  -- OpenAI text-embedding-3-small
  metadata JSONB DEFAULT '{}'
);

-- HNSW index for fast cosine similarity search
CREATE INDEX ON blog_chunks 
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- Insert with embedding
INSERT INTO blog_chunks (post_id, chunk_index, content, embedding, metadata)
VALUES (
  1, 0,
  'React Server Components render on the server and send zero JavaScript...',
  '[0.023, -0.041, 0.089, ...]'::vector,
  '{"category": "frontend", "tags": ["react", "rsc"]}'
);

-- Semantic search: find 5 most similar chunks
SELECT 
  bc.content,
  p.title AS post_title,
  1 - (bc.embedding <=> $1::vector) AS similarity
FROM blog_chunks bc
JOIN posts p ON p.id = bc.post_id
WHERE 1 - (bc.embedding <=> $1::vector) > 0.7
ORDER BY bc.embedding <=> $1::vector
LIMIT 5;

// Semantic chunking with overlap
function chunkDocument(text: string, options = {
  maxChunkSize: 800,    // tokens
  overlapSize: 100,     // tokens of overlap between chunks
  separator: '\n\n',   // Split on paragraphs first
}): string[] {
  const { maxChunkSize, overlapSize, separator } = options;
  
  // Step 1: Split by semantic boundaries (paragraphs)
  const paragraphs = text.split(separator);
  
  const chunks: string[] = [];
  let currentChunk = '';
  
  for (const paragraph of paragraphs) {
    const combined = currentChunk ? currentChunk + separator + paragraph : paragraph;
    
    if (estimateTokens(combined) > maxChunkSize && currentChunk) {
      chunks.push(currentChunk.trim());
      // Start next chunk with overlap from end of current chunk
      const words = currentChunk.split(' ');
      const overlapWords = words.slice(-Math.floor(overlapSize / 1.3));
      currentChunk = overlapWords.join(' ') + separator + paragraph;
    } else {
      currentChunk = combined;
    }
  }
  
  if (currentChunk.trim()) {
    chunks.push(currentChunk.trim());
  }
  
  return chunks;
}

function estimateTokens(text: string): number {
  return Math.ceil(text.length / 4); // Rough estimate: 1 token ≈ 4 chars
}

// Usage
const blogContent = await readFile('react-server-components.md');
const chunks = chunkDocument(blogContent);
// Embed each chunk separately
for (const chunk of chunks) {
  const embedding = await getEmbedding(chunk);
  await db.query(
    'INSERT INTO blog_chunks (content, embedding) VALUES ($1, $2)',
    [chunk, JSON.stringify(embedding)]
  );
}

import { createHash } from 'crypto';
import OpenAI from 'openai';

const openai = new OpenAI();

interface DocumentSource {
  id: string;
  title: string;
  content: string;
  category: string;
  tags: string[];
  updatedAt: Date;
}

async function ingestDocuments(documents: DocumentSource[]) {
  for (const doc of documents) {
    // 1. Check if already indexed (idempotency)
    const contentHash = createHash('sha256').update(doc.content).digest('hex');
    const existing = await db.query(
      "SELECT id FROM blog_chunks WHERE metadata->>'sourceId' = $1 AND metadata->>'hash' = $2",
      [doc.id, contentHash]
    );
    
    if (existing.rows.length > 0) {
      console.log(`Skipping ${doc.title} — unchanged`);
      continue;
    }
    
    // 2. Delete old chunks for this document
    await db.query("DELETE FROM blog_chunks WHERE metadata->>'sourceId' = $1", [doc.id]);
    
    // 3. Chunk the document
    const chunks = chunkDocument(doc.content);
    
    // 4. Embed all chunks in batch (up to 2048 per API call)
    const response = await openai.embeddings.create({
      model: 'text-embedding-3-small',
      input: chunks,
    });
    
    // 5. Store chunks with metadata
    for (let i = 0; i < chunks.length; i++) {
      await db.query(
        `INSERT INTO blog_chunks (content, embedding, metadata)
         VALUES ($1, $2::vector, $3)`,
        [
          chunks[i],
          JSON.stringify(response.data[i].embedding),
          JSON.stringify({
            sourceId: doc.id,
            title: doc.title,
            category: doc.category,
            tags: doc.tags,
            chunkIndex: i,
            hash: contentHash,
          }),
        ]
      );
    }
    
    console.log(`Indexed ${doc.title}: ${chunks.length} chunks`);
  }
}

-- Hybrid search: vector + full-text in a single query
WITH vector_results AS (
  SELECT id, content, 
    1 - (embedding <=> $1::vector) AS vector_score,
    NULL::float AS text_score
  FROM blog_chunks
  ORDER BY embedding <=> $1::vector
  LIMIT 20
),
text_results AS (
  SELECT id, content,
    NULL::float AS vector_score,
    ts_rank(to_tsvector('english', content), plainto_tsquery('english', $2)) AS text_score
  FROM blog_chunks
  WHERE to_tsvector('english', content) @@ plainto_tsquery('english', $2)
  LIMIT 20
),
combined AS (
  SELECT id, content,
    COALESCE(vector_score, 0) * 0.7 + COALESCE(text_score, 0) * 0.3 AS hybrid_score
  FROM (
    SELECT * FROM vector_results
    UNION ALL
    SELECT * FROM text_results
  ) all_results
)
SELECT DISTINCT ON (id) id, content, hybrid_score
FROM combined
ORDER BY id, hybrid_score DESC
LIMIT 10;