AI Engineering Academy · Lección

Búsqueda híbrida en Pinecone y pgvector

Búsqueda híbrida en Pinecone y pgvector es una lección gratuita de AI Engineering Academy en CoddyKit. Esta es la lección 4 de 4. Puedes leer la lección completa abajo gratuitamente — luego la practicas en el navegador con un editor de código integrado y un tutor de IA 24/7. Forma parte de la ruta de aprendizaje de AI Engineering Academy, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de AI Engineering Academy incluye 4 lecciones en total.

Partes de esta lección aún no han sido traducidas y se muestran en inglés.

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.

Preguntas frecuentes

¿La lección «Búsqueda híbrida en Pinecone y pgvector» es gratis?

Sí — el texto completo de «Búsqueda híbrida en Pinecone y pgvector» es gratis para leer aquí en la web. Para practicarla de forma interactiva (editor de código integrado y tutor de IA 24/7) y desbloquear el resto del curso de AI Engineering Academy, actualiza a CoddyKit PRO. El curso de AI Engineering Academy incluye 4 lecciones en total.

¿Qué aprenderé en «Búsqueda híbrida en Pinecone y pgvector»?

Configure la búsqueda híbrida en Pinecone mediante el modo de índice disperso-denso y en pgvector mediante consultas paralelas con combinación RRF, y evalúe la calidad de recuperación en su conjunto… Practicas AI Engineering Academy con código real que ejecutas directamente en el navegador, y un tutor de IA 24/7 responde tus preguntas mientras trabajas en la lección.

¿Necesito experiencia previa para empezar AI Engineering Academy?

No se requiere experiencia previa. AI Engineering Academy en CoddyKit está estructurado para principiantes hasta estudiantes avanzados, así que puedes empezar aquí o desde el inicio y avanzar a tu ritmo. Esta es la lección 4 de 4.

¿Cuánto tiempo toma la lección «Búsqueda híbrida en Pinecone y pgvector»?

La mayoría de las lecciones de CoddyKit toman alrededor de 5–10 minutos. Cada una es compacta e interactiva, así que avanzas constantemente y retomas exactamente por donde dejaste en la web y la app.

¿Puedo escribir y ejecutar código en esta lección de AI Engineering Academy?

Sí. Cada lección de AI Engineering Academy incluye un editor de código integrado, así que escribes y ejecutas código real directamente en tu navegador y obtienes retroalimentación instantánea de IA — sin configuración local necesaria.

Todas las lecciones de este curso

Recuperación densa frente a dispersa: ventajas y desventajas
Implementación de búsquedas por palabras clave con BM25
Fusión de rangos recíprocos para combinar puntuaciones
Búsqueda híbrida en Pinecone y pgvector

← Volver a AI Engineering Academy