0Pricing
AI Engineering Academy · 课时

在 Pinecone 和 pgvector 中实现混合搜索

在 Pinecone 中使用稀疏-稠密索引模式配置混合搜索，在 pgvector 中使用并行查询和 RRF 合并实现混合搜索，并在您的数据集上评测检索质量。

在 Pinecone 和 pgvector 中实现混合搜索 是 CoddyKit 上的免费 AI Engineering Academy 课时。 这是第 4 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 AI Engineering Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 AI Engineering Academy 课程共包含 4 节课。

本课时的部分内容尚未翻译，以英文显示。

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.matches

pgvector: 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_alpha

Choosing 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.

常见问题解答

「在 Pinecone 和 pgvector 中实现混合搜索」课时是免费的吗？

是的 — 「在 Pinecone 和 pgvector 中实现混合搜索」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 AI Engineering Academy 课程的其余内容，请升级到 CoddyKit PRO。 AI Engineering Academy 课程共包含 4 节课。

「在 Pinecone 和 pgvector 中实现混合搜索」这节课中我会学到什么？

在 Pinecone 中使用稀疏-稠密索引模式配置混合搜索，在 pgvector 中使用并行查询和 RRF 合并实现混合搜索，并在您的数据集上评测检索质量。 你通过在浏览器中直接运行的动手代码来练习 AI Engineering Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 AI Engineering Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 AI Engineering Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 4 节课，共 4 节。

「在 Pinecone 和 pgvector 中实现混合搜索」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 AI Engineering Academy 课中编写并运行代码吗？

能。每节 AI Engineering Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

  1. 稠密检索与稀疏检索：权衡取舍
  2. 实现 BM25 关键词搜索
  3. 使用倒数排名融合合并分数
  4. 在 Pinecone 和 pgvector 中实现混合搜索
← 返回 AI Engineering Academy