Hybrid Search in Pinecone and pgvector
Configure hybrid search in Pinecone using sparse-dense index mode and in pgvector using parallel queries with RRF merging, benchmarking retrieval quality on your dataset.
Hybrid Search in Pinecone and pgvector is a free AI Engineering Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the AI Engineering Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Native Hybrid Search in Vector Databases
While you can implement hybrid search yourself using two separate indexes and RRF fusion, production vector databases increasingly offer built-in hybrid search that handles sparse and dense retrieval in a single query. Pinecone and pgvector both support hybrid modes, but with different architecture choices and trade-offs. Understanding both options lets you pick the right tool for your infrastructure.
Pinecone Sparse-Dense Index Mode
Pinecone supports a sparse-dense index where each vector record stores both a dense embedding (as a float array) and a sparse vector (as a dictionary of token ID to weight). Queries can specify both a dense query vector and a sparse query vector, and Pinecone merges results using a weighted linear combination. This is different from RRF — Pinecone uses raw score fusion with configurable weights called alpha.
from pinecone import Pinecone, ServerlessSpec
pc = Pinecone(api_key='YOUR_API_KEY')
# Create a sparse-dense index
pc.create_index(
name='hybrid-index',
dimension=1536, # dense vector dimension
metric='dotproduct', # must use dotproduct for hybrid
spec=ServerlessSpec(cloud='aws', region='us-east-1'),
)
index = pc.Index('hybrid-index')Generating Sparse Vectors with SPLADE
To use Pinecone's sparse-dense mode, you need to generate sparse vectors for each document. The most effective approach is SPLADE (Sparse Lexical and Expansion), a neural model that produces learned sparse representations with expansion — it adds semantically related terms to the sparse vector beyond what literally appears in the text. Alternatively, you can use simple BM25 term weights as sparse vectors.
from transformers import AutoTokenizer, AutoModelForMaskedLM
import torch
# Load SPLADE model
tokenizer = AutoTokenizer.from_pretrained('naver/splade-cocondenser-ensembledistil')
model = AutoModelForMaskedLM.from_pretrained('naver/splade-cocondenser-ensembledistil')
def generate_sparse_vector(text: str) -> dict:
tokens = tokenizer(text, return_tensors='pt', truncation=True, max_length=512)
with torch.no_grad():
output = model(**tokens)
logits = output.logits
# Max-pool over sequence length and apply log1p activation
sparse = torch.max(torch.log1p(torch.relu(logits)), dim=1).values.squeeze()
# Return non-zero indices and values as a sparse dict
nz_indices = sparse.nonzero().squeeze().tolist()
nz_values = sparse[nz_indices].tolist()
return {'indices': nz_indices, 'values': nz_values}Upserting Hybrid Vectors to Pinecone
Each Pinecone record in a hybrid index stores an id, a dense values array, a sparse_values dictionary with indices and values, and optional metadata. Upsert records in batches of 100 to maximize throughput while staying within Pinecone's request size limits.
def upsert_hybrid_docs(index, documents: list[dict], batch_size: int = 100):
records = []
for doc in documents:
dense_vec = embed(doc['text']) # OpenAI embedding
sparse_vec = generate_sparse_vector(doc['text']) # SPLADE
records.append({
'id': doc['id'],
'values': dense_vec,
'sparse_values': sparse_vec,
'metadata': {'text': doc['text'], 'source': doc['source']},
})
for i in range(0, len(records), batch_size):
batch = records[i:i + batch_size]
index.upsert(vectors=batch)
print(f'Upserted batch {i//batch_size + 1}')Querying the Pinecone Hybrid Index
A hybrid query passes both a dense vector and a sparse_vector to the Pinecone query API. The alpha parameter (0 to 1) controls the balance: alpha=1.0 is pure dense, alpha=0.0 is pure sparse, and alpha=0.5 gives equal weight to both. Tune alpha on a validation set — typical optimal values are between 0.3 and 0.7.
def hybrid_query_pinecone(index, query: str, alpha: float = 0.5, top_k: int = 5):
dense_vec = embed(query)
sparse_vec = generate_sparse_vector(query)
# Scale vectors by alpha for weighted fusion
scaled_dense = [v * alpha for v in dense_vec]
scaled_sparse = {
'indices': sparse_vec['indices'],
'values': [v * (1 - alpha) for v in sparse_vec['values']],
}
results = index.query(
vector=scaled_dense,
sparse_vector=scaled_sparse,
top_k=top_k,
include_metadata=True,
)
return results.matchespgvector: Vector Search in PostgreSQL
The pgvector extension adds a vector column type and distance operators to PostgreSQL. For hybrid search, you run two queries in parallel — an approximate nearest neighbor query on the vector column and a full-text search query using PostgreSQL's built-in tsvector and tsquery — then merge the results with RRF in your application code.
-- Setup: enable extensions
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- Create hybrid-capable table
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
content TEXT NOT NULL,
embedding vector(1536),
content_tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', content)) STORED
);
-- Indexes for both search modes
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);
CREATE INDEX ON documents USING GIN (content_tsv);Running Dense and Sparse Queries in pgvector
With pgvector, run the dense query using the <=> cosine distance operator to find the nearest neighbors of the query embedding, and the sparse query using ts_rank_cd against the tsvector column to score keyword matches. Retrieve both result sets independently, then fuse them using RRF in Python.
import psycopg2
import json
def pgvector_hybrid_search(conn, query: str, top_k: int = 5):
dense_vec = embed(query)
tsquery = ' & '.join(query.split()) # simple AND query
# Dense query
dense_sql = '''
SELECT id, content, 1 - (embedding <=> %s::vector) AS score
FROM documents
ORDER BY embedding <=> %s::vector
LIMIT 20
'''
# Sparse query
sparse_sql = '''
SELECT id, content, ts_rank_cd(content_tsv, to_tsquery('english', %s)) AS score
FROM documents
WHERE content_tsv @@ to_tsquery('english', %s)
ORDER BY score DESC
LIMIT 20
'''
with conn.cursor() as cur:
cur.execute(dense_sql, [json.dumps(dense_vec), json.dumps(dense_vec)])
dense_rows = cur.fetchall()
cur.execute(sparse_sql, [tsquery, tsquery])
sparse_rows = cur.fetchall()
return fuse_with_rrf(dense_rows, sparse_rows, top_k)Applying RRF to pgvector Results
After collecting ranked rows from both PostgreSQL queries, apply RRF by extracting document IDs in rank order from each result set and running the fusion algorithm. The final merged list gives you the top-K documents that are most relevant across both semantic meaning and keyword overlap.
def fuse_with_rrf(dense_rows, sparse_rows, top_k: int = 5, k: int = 60):
from collections import defaultdict
doc_texts = {}
scores = defaultdict(float)
for rank, row in enumerate(dense_rows, start=1):
doc_id, text, _ = row
scores[doc_id] += 1.0 / (k + rank)
doc_texts[doc_id] = text
for rank, row in enumerate(sparse_rows, start=1):
doc_id, text, _ = row
scores[doc_id] += 1.0 / (k + rank)
doc_texts[doc_id] = text
ranked = sorted(scores.items(), key=lambda x: x[1], reverse=True)
return [{'id': doc_id, 'text': doc_texts[doc_id], 'rrf_score': s}
for doc_id, s in ranked[:top_k]]Benchmarking Retrieval Quality
After implementing hybrid search, benchmark it rigorously against your baseline dense-only retrieval. Use a golden test set of at least 100 queries with known relevant documents. Measure NDCG@5, MRR, and hit rate@3. Typical improvements from hybrid search range from 5 to 20 percent on NDCG@5, with the largest gains on queries containing exact technical terms that dense models miss.
def evaluate_retrieval(search_fn, test_queries, golden_relevant, k=5):
ndcg_scores = []
hit_count = 0
for query, relevant_ids in zip(test_queries, golden_relevant):
results = search_fn(query, top_k=k)
retrieved_ids = [r['id'] for r in results]
# Hit rate
if any(rid in relevant_ids for rid in retrieved_ids):
hit_count += 1
# Simplified NDCG (binary relevance)
dcg = sum(
1.0 / (i + 1)
for i, rid in enumerate(retrieved_ids)
if rid in relevant_ids
)
idcg = sum(1.0 / (i + 1) for i in range(min(len(relevant_ids), k)))
ndcg_scores.append(dcg / idcg if idcg > 0 else 0)
return {
'hit_rate': hit_count / len(test_queries),
'ndcg': sum(ndcg_scores) / len(ndcg_scores),
}Tuning Alpha in Pinecone Hybrid Mode
The alpha parameter in Pinecone's hybrid query requires systematic tuning. Query-type stratification is a useful approach: classify your test queries as predominantly semantic (paraphrases, conceptual) or predominantly lexical (exact terms, codes), and measure optimal alpha separately for each group. Some production systems implement dynamic alpha selection that classifies the query at runtime and picks the appropriate alpha automatically.
def find_optimal_alpha(index, test_queries, golden_relevant, alphas=None):
if alphas is None:
alphas = [0.0, 0.2, 0.4, 0.5, 0.6, 0.8, 1.0]
best_alpha, best_score = 0.5, 0
for alpha in alphas:
hits = 0
for query, relevant in zip(test_queries, golden_relevant):
results = hybrid_query_pinecone(index, query, alpha=alpha, top_k=5)
if any(r['id'] in relevant for r in results):
hits += 1
hit_rate = hits / len(test_queries)
print(f'alpha={alpha}: hit_rate={hit_rate:.3f}')
if hit_rate > best_score:
best_score, best_alpha = hit_rate, alpha
return best_alphaChoosing Between Pinecone and pgvector Hybrid
Use Pinecone hybrid when you need managed infrastructure, automatic scaling, and want the complexity of sparse vector generation offloaded to a single API. Use pgvector hybrid when you already have PostgreSQL infrastructure, need transactional consistency between documents and their metadata, want full SQL flexibility for filtering, or need to avoid vendor lock-in. Both approaches produce excellent hybrid retrieval quality when tuned correctly.
Quick Check
Test your understanding of hybrid search implementation from this lesson.
Lesson Recap
In this lesson you learned: Pinecone sparse-dense mode stores both dense and sparse vectors per record and fuses them with a configurable alpha parameter, pgvector hybrid search combines SQL full-text search with vector queries fused by RRF in application code, and alpha tuning on a validation set is essential to find the optimal balance for your query distribution. Next up we explore two-stage retrieval with re-ranking.
Frequently asked questions
Is the “Hybrid Search in Pinecone and pgvector” lesson free?
Yes — the full text of “Hybrid Search in Pinecone and pgvector” is free to read here on the web, and the AI Engineering Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the AI Engineering Academy course, upgrade to CoddyKit PRO.
What will I learn in “Hybrid Search in Pinecone and pgvector”?
Configure hybrid search in Pinecone using sparse-dense index mode and in pgvector using parallel queries with RRF merging, benchmarking retrieval quality on your dataset. You practise AI Engineering Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start AI Engineering Academy?
No prior experience is required. AI Engineering Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Hybrid Search in Pinecone and pgvector” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this AI Engineering Academy lesson?
Yes. Every AI Engineering Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Dense vs Sparse Retrieval: Trade-offs
- Implementing BM25 Keyword Search
- Reciprocal Rank Fusion for Score Merging
- Hybrid Search in Pinecone and pgvector