Vector Databases with pgvector: Semantic Search in PostgreSQL

Vectors in Your Database

pgvector adds vector operations to PostgreSQL — no separate vector database needed. Store embeddings alongside your relational data.

Setup

-- Enable pgvector extension
CREATE EXTENSION vector;

-- Add embedding column to products
ALTER TABLE products ADD COLUMN embedding vector(1536);  -- OpenAI ada-002 dimensions

-- Or create new table
CREATE TABLE documents (
  id BIGSERIAL PRIMARY KEY,
  content TEXT NOT NULL,
  metadata JSONB,
  embedding vector(1536),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Storing Embeddings

import { OpenAI } from 'openai'
import { Pool } from 'pg'

const openai = new OpenAI()
const pool = new Pool({ connectionString: process.env.DATABASE_URL })

async function embedAndStore(text: string, metadata: Record<string, any>) {
  // Get embedding from OpenAI
  const response = await openai.embeddings.create({
    model: 'text-embedding-ada-002',
    input: text,
  })
  
  const embedding = response.data[0].embedding  // 1536-dim array
  
  // Store in PostgreSQL
  const result = await pool.query(
    'INSERT INTO documents (content, metadata, embedding) VALUES ($1, $2, $3) RETURNING id',
    [text, JSON.stringify(metadata), JSON.stringify(embedding)]
  )
  
  return result.rows[0].id
}

Similarity Search

-- L2 distance (Euclidean) — lower = more similar
SELECT id, content, embedding <-> $1 AS distance
FROM documents
ORDER BY embedding <-> $1
LIMIT 10;

-- Cosine similarity — higher = more similar
SELECT id, content, 1 - (embedding <=> $1) AS similarity
FROM documents
ORDER BY embedding <=> $1
LIMIT 10;

-- Inner product
SELECT id, content, (embedding <#> $1) * -1 AS score
FROM documents
ORDER BY embedding <#> $1
LIMIT 10;

async function semanticSearch(query: string, topK = 10) {
  // Embed the query
  const response = await openai.embeddings.create({
    model: 'text-embedding-ada-002',
    input: query,
  })
  const queryEmbedding = response.data[0].embedding
  
  // Search
  const result = await pool.query(
    `SELECT id, content, metadata, 1 - (embedding <=> $1) AS similarity
     FROM documents
     WHERE 1 - (embedding <=> $1) > 0.7  -- Minimum similarity threshold
     ORDER BY embedding <=> $1
     LIMIT $2`,
    [JSON.stringify(queryEmbedding), topK]
  )
  
  return result.rows
}

HNSW Index (Fast Approximate Search)

-- IVFFlat (good for most cases)
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);  -- lists = sqrt(rows)

-- HNSW (faster queries, slower build, more memory)
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
-- m: connections per node (higher = more accurate, more memory)
-- ef_construction: build quality (higher = more accurate, slower build)

-- Set search depth (higher = more accurate, slower)
SET hnsw.ef_search = 100;

Hybrid Search (Vector + Keyword)

-- Combine semantic and full-text search with RRF
WITH semantic AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> $1) AS rank
  FROM documents
  LIMIT 50
),
keyword AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY ts_rank(search_vector, query) DESC) AS rank
  FROM documents, to_tsquery('english', $2) query
  WHERE search_vector @@ query
  LIMIT 50
),
rrf AS (
  SELECT
    COALESCE(s.id, k.id) AS id,
    COALESCE(1.0 / (60 + s.rank), 0) + COALESCE(1.0 / (60 + k.rank), 0) AS score
  FROM semantic s
  FULL OUTER JOIN keyword k USING (id)
)
SELECT d.id, d.content, r.score
FROM rrf r
JOIN documents d ON d.id = r.id
ORDER BY r.score DESC
LIMIT 10;

Performance at Scale

For 1M+ vectors:

• Use HNSW with m=16, ef_construction=128
• Set maintenance_work_mem = 4GB during index build
• Use pgvector.ivfflat.probes = 10 for better recall
• Consider pgvectorscale for 28x faster queries at scale
• Or use dedicated vector databases (Pinecone, Weaviate, Qdrant) for 100M+

Chunking Strategy for Documents

// Split long documents into chunks for better search
function chunkText(text: string, maxTokens = 512, overlap = 50): string[] {
  const words = text.split(' ')
  const chunks: string[] = []
  
  for (let i = 0; i < words.length; i += maxTokens - overlap) {
    chunks.push(words.slice(i, i + maxTokens).join(' '))
  }
  
  return chunks
}