PGVector Hybrid Search

Production-grade semantic + keyword search using PostgreSQL

Overview

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

When to use this skill:

• Building semantic search (RAG, knowledge bases, recommendations)
• Implementing hybrid retrieval (vector + keyword)
• Optimizing PGVector performance
• Working with large document collections (1M+ chunks)

---

Quick Reference

Search Type Comparison

Aspect	Semantic (Vector)	Keyword (BM25)
Query	Embedding similarity	Exact word matches
Strengths	Synonyms, concepts	Exact phrases, rare terms
Weaknesses	Exact matches, technical terms	No semantic understanding
Index	HNSW (pgvector)	GIN (tsvector)

Index Comparison

Metric	IVFFlat	HNSW
Query speed	50ms	3ms (17x faster)
Index time	2 min	20 min
Best for	< 100k vectors	100k+ vectors
Recall@10	0.85-0.95	0.95-0.99

Recommendation: Use HNSW for production (scales to millions).

RRF Formula

rrf_score = 1/(k + vector_rank) + 1/(k + keyword_rank)  # k=60 (standard)

---

Database Schema

CREATE TABLE chunks (
    id UUID PRIMARY KEY,
    document_id UUID REFERENCES documents(id),
    content TEXT NOT NULL,
    embedding vector(1024),  -- PGVector
    content_tsvector tsvector GENERATED ALWAYS AS (
        to_tsvector('english', content)
    ) STORED,
    section_title TEXT,
    content_type TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_chunks_embedding ON chunks
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

CREATE INDEX idx_chunks_content_tsvector ON chunks
    USING gin (content_tsvector);

---

Hybrid Search Query (SQLAlchemy)

async def hybrid_search(
    query: str,
    query_embedding: list[float],
    top_k: int = 10
) -> list[Chunk]:
    FETCH_MULTIPLIER = 3  # Fetch 30 for better RRF coverage
    K = 60  # RRF smoothing constant

    # Vector search subquery
    vector_subq = (
        select(Chunk.id,
            func.row_number().over(
                order_by=Chunk.embedding.cosine_distance(query_embedding)
            ).label("vector_rank"))
        .limit(top_k * FETCH_MULTIPLIER)
        .subquery()
    )

    # Keyword search subquery
    ts_query = func.plainto_tsquery("english", query)
    keyword_subq = (
        select(Chunk.id,
            func.row_number().over(
                order_by=func.ts_rank_cd(Chunk.content_tsvector, ts_query).desc()
            ).label("keyword_rank"))
        .where(Chunk.content_tsvector.op("@@")(ts_query))
        .limit(top_k * FETCH_MULTIPLIER)
        .subquery()
    )

    # RRF fusion with FULL OUTER JOIN
    rrf_subq = (
        select(
            func.coalesce(vector_subq.c.id, keyword_subq.c.id).label("chunk_id"),
            (func.coalesce(1.0 / (K + vector_subq.c.vector_rank), 0.0) +
             func.coalesce(1.0 / (K + keyword_subq.c.keyword_rank), 0.0)
            ).label("rrf_score"))
        .select_from(vector_subq.outerjoin(keyword_subq, ..., full=True))
        .order_by("rrf_score DESC")
        .limit(top_k)
        .subquery()
    )

    return await session.execute(
        select(Chunk).join(rrf_subq, Chunk.id == rrf_subq.c.chunk_id)
    )

---

Common Patterns

Filtered Search

results = await hybrid_search(
    query="binary search",
    query_embedding=embedding,
    content_type_filter=["code_block"]
)

Similarity Threshold

results = await hybrid_search(query, embedding, top_k=50)
filtered = [r for r in results if (1 - r.vector_distance) >= 0.75][:10]

Multi-Query Retrieval

queries = ["machine learning", "ML algorithms", "neural networks"]
all_results = [await hybrid_search(q, embed(q)) for q in queries]
final = deduplicate_and_rerank(all_results)

---

Performance Tips

1. Pre-compute tsvector - 5-10x faster than to_tsvector() at query time
2. Use HNSW index - 17x faster queries than IVFFlat
3. 3x fetch multiplier - Better RRF coverage (30 results per search for top 10)
4. Iterative scan for filtered queries - Set hnsw.iterative_scan = 'relaxed_order'
5. Metadata boosting - +6% MRR with title/path matching

---

References

Detailed Implementation Guides

Reference	Description	Use When
index-strategies.md	HNSW vs IVFFlat, tuning, iterative scans	Choosing/optimizing indexes
hybrid-search-rrf.md	RRF algorithm, SQL implementation, debugging	Implementing hybrid search
metadata-filtering.md	Pre/post filtering, score boosting	Improving relevance

External Resources

• PGVector GitHub
• HNSW Index Guide

Related Skills

• ai-native-development - Embeddings and vector concepts
• database-schema-designer - Schema design for vector search

---

Version: 1.2.0 | Status: Production-ready | Updated: pgvector 0.8.1

---

Capability Details

hybrid-search-rrf

Keywords: hybrid search, rrf, reciprocal rank fusion, vector bm25, semantic keyword search Solves:

• How do I combine vector and keyword search?
• Implement hybrid retrieval with RRF
• Merge semantic and BM25 results

semantic-search

Keywords: semantic search, vector similarity, embedding, nearest neighbor, cosine distance Solves:

• How does semantic search work?
• When to use semantic vs keyword search
• Semantic search strengths and weaknesses

keyword-search-bm25

Keywords: bm25, full-text search, tsvector, tsquery, keyword search Solves:

• How does BM25 keyword search work?
• Implement PostgreSQL full-text search
• BM25 vs semantic search trade-offs

rrf-algorithm

Keywords: rrf, reciprocal rank fusion, rank-based fusion, score normalization Solves:

• How does Reciprocal Rank Fusion work?
• Why use rank instead of scores?
• RRF smoothing constant (k parameter)

database-schema

Keywords: pgvector schema, chunk table, embedding column, tsvector, generated column Solves:

• How do I design schema for hybrid search?
• Store embeddings with vector(1024)
• Pre-compute tsvector for performance

search-query-implementation

Keywords: hybrid search query, sqlalchemy, vector distance, ts_rank_cd, full outer join Solves:

• How do I write hybrid search SQL?
• Implement RRF in SQLAlchemy
• Use fetch multiplier for better coverage

indexing-strategies

Keywords: pgvector index, hnsw, ivfflat, vector index performance, index tuning Solves:

• HNSW vs IVFFlat comparison
• Optimize vector search speed
• Scale to millions of vectors

pre-computed-tsvector

Keywords: tsvector, gin index, full-text index, pre-computed column, generated column Solves:

• Optimize keyword search performance
• 5-10x speedup with indexed tsvector

metadata-filtering

Keywords: metadata filter, faceted search, content type filter, score boosting Solves:

• Filter search by metadata
• Boost results by section title
• Pre-filter by content type

common-patterns

Keywords: filtered search, similarity threshold, multi-query retrieval, search patterns Solves:

• Filter search by content type
• Set minimum similarity threshold
• Implement multi-query retrieval

golden-dataset-testing

Keywords: golden dataset, search evaluation, pass rate, mrr, retrieval testing Solves:

• Test hybrid search quality
• Evaluate search with golden queries
• Calculate pass rate and MRR metrics