Pgvector PostgreSQL Vector Search: Building Enterprise RAG Applications

When building Retrieval-Augmented Generation (RAG) applications for AI products, engineering teams often reach for dedicated cloud vector databases like Pinecone or Qdrant. However, `pgvector` brings vector similarity search directly into your existing PostgreSQL infrastructure.

**TL;DR:** Combine PostgreSQL relational data with `pgvector` to perform HNSW-indexed vector similarity searches and SQL joins in a single database transaction.

---

1. Enabling Pgvector & HNSW Indexing

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

-- Create documents table with a 1536-dimensional vector embedding column
CREATE TABLE enterprise_documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    embedding vector(1536),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Build Hierarchical Navigable Small World (HNSW) index for sub-10ms Cosine distance searches
CREATE INDEX ON enterprise_documents 
USING hnsw (embedding vector_cosine_ops) 
WITH (m = 16, ef_construction = 64);

---

2. Hybrid Search (SQL + Cosine Similarity)

-- Combine keyword full-text matching with semantic vector similarity
SELECT id, title, content,
       (1 - (embedding <=> '[0.012, -0.045, ...]')) AS cosine_similarity
FROM enterprise_documents
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'refund & SaaS')
ORDER BY embedding <=> '[0.012, -0.045, ...]' ASC
LIMIT 5;