pgvector-search

Aspirational — these patterns are not yet implemented in the reference platform.

PGVector Hybrid Search

Production-grade semantic + keyword search using PostgreSQL

Overview

Hybrid search combines semantic similarity (vector embeddings) with keyword matching (BM25) to achieve better retrieval than either alone.

Architecture:

Query
  ↓
[Generate embedding] → Vector Search (PGVector) → Top 30 results
  ↓
[Generate ts_query]  → Keyword Search (BM25)    → Top 30 results
  ↓
[Reciprocal Rank Fusion (RRF)] → Merge & re-rank → Top 10 final results

Core Concepts

1. Semantic Search (Vector Similarity)

How it works:

1. Embed query: "database indexing strategies" → [0.23, -0.15, ..., 0.42] (1024 dims)
2. Find nearest neighbors: ORDER BY embedding <=> query_embedding LIMIT 30
3. Returns: Conceptually similar documents (even with different words)

Example:

• Query: "machine learning model training"
• Matches: "neural network optimization", "deep learning techniques"
• Misses: "ML model training" (different embeddings despite similar meaning)

Strengths:

• Captures semantic meaning
• Works across languages
• Handles synonyms ("car" matches "automobile")

Weaknesses:

• Slow for exact keyword matches
• Sensitive to embedding quality
• Doesn't handle rare technical terms well

---

2. Keyword Search (BM25)

How it works:

1. Tokenize query: "database indexing" → database & indexing
2. Full-text search: WHERE content_tsvector @@ to_tsquery('database & indexing')
3. Rank by BM25 score (TF-IDF + document length normalization)

Example:

• Query: "PostgreSQL B-tree index"
• Matches: Documents with exact phrase "PostgreSQL B-tree index"
• Misses: "Postgres tree-based indexing" (different words)

Strengths:

• Fast exact matches
• Handles technical terms well
• Works for rare/specific phrases

Weaknesses:

• No semantic understanding
• Requires exact word matches
• Sensitive to typos

---

3. Reciprocal Rank Fusion (RRF)