Building Enterprise Hybrid Search with PostgreSQL and pgvector

#Quick Summary: Why Combine Vector and Keyword Search?

While vector embeddings excel at grasping semantic context and conceptual synonyms ("cybersecurity services" matching "infosec consulting"), they often fail on exact keyword lookups, SKU numbers, acronyms, or specific product codes. Hybrid Search combines dense vector similarity (cosine/inner product) with sparse BM25 full-text keyword ranking inside PostgreSQL using the pgvector extension. This delivers the highest precision and recall for enterprise RAG and search portals.

#1. Setting Up pgvector in PostgreSQL

Enable the extension and create a schema optimized for vector indexing:

-- Create knowledge documents table CREATE TABLE knowledge_documents ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), title TEXT NOT NULL, content TEXT NOT NULL, category VARCHAR(50), embedding vector(1536), -- Dimension for text-embedding-3-small tsv_content tsvector GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || content)) STORED, created_at TIMESTAMPTZ DEFAULT now() );

-- Full-text GIN index CREATE INDEX idx_knowledge_tsv ON knowledge_documents USING gin(tsv_content);

-- HNSW vector similarity index for high query concurrency CREATE INDEX idx_knowledge_embedding_hnsw ON knowledge_documents USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64); ```

#2. Implementing Reciprocal Rank Fusion (RRF) for Hybrid Ranking

Reciprocal Rank Fusion blends rank positions from both algorithmic pipelines without requiring score normalization:

WITH semantic_search AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> $1::vector) as rank_semantic
  FROM knowledge_documents
  ORDER BY embedding <=> $1::vector
  LIMIT 20
),
keyword_search AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY ts_rank(tsv_content, plainto_tsquery('english', $2)) DESC) as rank_keyword
  FROM knowledge_documents
  WHERE tsv_content @@ plainto_tsquery('english', $2)
  LIMIT 20
)
SELECT 
  kd.id,
  kd.title,
  kd.content,
  COALESCE(1.0 / (60 + s.rank_semantic), 0.0) +
  COALESCE(1.0 / (60 + k.rank_keyword), 0.0) AS hybrid_score
FROM knowledge_documents kd
LEFT JOIN semantic_search s ON kd.id = s.id
LEFT JOIN keyword_search k ON kd.id = k.id
WHERE s.id IS NOT NULL OR k.id IS NOT NULL
ORDER BY hybrid_score DESC
LIMIT 10;

#3. Index Performance: HNSW vs. IVFFlat in Production

For production applications with mission-critical response requirements, HNSW (Hierarchical Navigable Small World) is strongly recommended.

#4. Architectural Best Practices

• Chunk Size Strategy: Divide long documents into 400-to-600 token chunks with 10% overlap to preserve semantic coherence.
• Async Background Embedding Generation: Generate vector embeddings through background message queues (e.g. BullMQ / Redis) rather than blocking user write transactions.
• Connection Pooling: Use PgBouncer in transaction pooling mode to handle high concurrency without exhausting PostgreSQL worker processes.

#Frequently Asked Questions (FAQ)

What embedding dimension should I use? OpenAI's `text-embedding-3-small` uses 1536 dimensions, while modern open-source models like BAAI/bge-small use 384 dimensions. Higher dimensions capture richer nuance but require more RAM in HNSW graphs.

Can pgvector replace dedicated vector databases like Pinecone or Weaviate? For 90% of enterprises managing under 10 million vectors, pgvector eliminates the operational burden of maintaining a separate database, provides ACID transactions, and simplifies data backups.