Building Production RAG with pgvector & Hybrid Search

When building a Retrieval-Augmented Generation (RAG) system, retrieval accuracy is everything. If the retrieved documents aren't relevant, your LLM's response won't be either. This is where pgvector hybrid search shines. By combining traditional full-text search with vector embeddings, you get the best of both worlds.

In this guide, we'll explore how to build a production-ready RAG architecture using PostgreSQL, the pgvector extension, Reciprocal Rank Fusion (RRF), and a complete Python client - including HNSW tuning, query plan analysis, and a production checklist.

If you are exploring other AI stack components, be sure to check out our overview of AI Tools.

The RAG Architecture

Let's look at how a hybrid search RAG architecture operates under the hood. The system performs two parallel searches: a dense vector search for semantic meaning and a sparse keyword search (BM25) for exact matches. Then, it merges the results using Reciprocal Rank Fusion (RRF).

Dense vs. Sparse Search

Why do we need a hybrid approach? Let's break down the differences between semantic vector searches and traditional keyword matching.

While semantic search is powerful, it sometimes fails when a user searches for specific serial numbers, acronyms, or exact names. Combining it with PostgreSQL full-text search ensures you catch both exact terms and conceptual matches.

Choosing Your Embedding Dimension

Before setting up the schema, decide on your embedding model. The dimension you pick is permanent - changing it requires re-indexing all documents.

Larger dimensions improve recall on nuanced semantic queries but cost more in both storage and search latency. For most production RAG systems, 1024 or 1536 is the sweet spot.

Setting Up pgvector

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
    id          bigserial PRIMARY KEY,
    content     text      NOT NULL,
    metadata    jsonb     DEFAULT '{}',
    embedding   vector(1536),  -- match your model's dimension
    fts         tsvector  GENERATED ALWAYS AS
                  (to_tsvector('english', content)) STORED,
    created_at  timestamptz DEFAULT now()
);

CREATE INDEX documents_embedding_hnsw_idx
    ON documents
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

CREATE INDEX documents_fts_gin_idx ON documents USING GIN (fts);

SET hnsw.ef_search = 100; -- default is 40; 100 is good for production

Implementing Hybrid Search with RRF

Reciprocal Rank Fusion combines the ranked results from both searches. A document that ranks #2 in vector search and #3 in keyword search gets a higher fused score than one that ranks #1 in only one of them.

WITH vector_search AS (
  SELECT id, content, metadata,
         RANK() OVER (ORDER BY embedding <=> $1) AS rank
  FROM documents
  ORDER BY embedding <=> $1
  LIMIT 50
),
keyword_search AS (
  SELECT id, content, metadata,
         RANK() OVER (
           ORDER BY ts_rank_cd(fts, plainto_tsquery('english', $2)) DESC
         ) AS rank
  FROM documents
  WHERE fts @@ plainto_tsquery('english', $2)
  ORDER BY ts_rank_cd(fts, plainto_tsquery('english', $2)) DESC
  LIMIT 50
)
SELECT
  COALESCE(v.id, k.id)               AS id,
  COALESCE(v.content, k.content)     AS content,
  COALESCE(v.metadata, k.metadata)   AS metadata,
  (
    COALESCE(1.0 / (60 + v.rank), 0.0) +
    COALESCE(1.0 / (60 + k.rank), 0.0)
  )                                   AS rrf_score
FROM vector_search v
FULL OUTER JOIN keyword_search k ON v.id = k.id
ORDER BY rrf_score DESC
LIMIT 10;
-- $1: embedding vector, $2: query text string

The constant 60 in the denominator is the RRF constant k - it reduces the weight of the top-ranked documents and prevents any single list from dominating. A value of 60 is the standard used in the original RRF paper.

Verifying Your Query Plan

Before shipping to production, run EXPLAIN ANALYZE to confirm the HNSW index is being used and check actual vs. estimated row counts:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, content, embedding <=> '[0.1, 0.2, ...]' AS dist
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'
LIMIT 10;

Look for Index Scan using documents_embedding_hnsw_idx in the output. If you see a sequential scan instead, your ef_search may be too high or statistics need updating (ANALYZE documents;).

Python Client with asyncpg

Here's a production-grade Python implementation using asyncpg and OpenAI embeddings:

import asyncpg
import openai
from typing import Any

async def embed(text: str) -> list[float]:
    resp = await openai.AsyncOpenAI().embeddings.create(
        model="text-embedding-3-small",
        input=text,
    )
    return resp.data[0].embedding

async def hybrid_search(
    pool: asyncpg.Pool,
    query: str,
    limit: int = 10,
) -> list[dict[str, Any]]:
    embedding = await embed(query)
    # asyncpg expects vectors as lists of floats
    vector_str = "[" + ",".join(str(x) for x in embedding) + "]"

    rows = await pool.fetch(
        """
        WITH vector_search AS (
          SELECT id, content, metadata,
                 RANK() OVER (ORDER BY embedding <=> $1::vector) AS rank
          FROM documents ORDER BY embedding <=> $1::vector LIMIT 50
        ),
        keyword_search AS (
          SELECT id, content, metadata,
                 RANK() OVER (
                   ORDER BY ts_rank_cd(fts, plainto_tsquery('english', $2)) DESC
                 ) AS rank
          FROM documents
          WHERE fts @@ plainto_tsquery('english', $2)
          LIMIT 50
        )
        SELECT
          COALESCE(v.id, k.id)             AS id,
          COALESCE(v.content, k.content)   AS content,
          COALESCE(v.metadata, k.metadata) AS metadata,
          (COALESCE(1.0/(60+v.rank),0) + COALESCE(1.0/(60+k.rank),0)) AS score
        FROM vector_search v
        FULL OUTER JOIN keyword_search k ON v.id = k.id
        ORDER BY score DESC LIMIT $3
        """,
        vector_str,
        query,
        limit,
    )
    return [dict(r) for r in rows]

Chunking Strategy

How you split your documents before embedding is as important as the index configuration. Poor chunking causes two failure modes:

• Chunks too small: The embedding captures only part of a concept, semantic search misses context.
• Chunks too large: A single chunk dilutes relevance - one relevant sentence buried in 2,000 tokens.

For most RAG applications, 512 tokens with 50-token overlap is a solid starting point. Overlap ensures a concept split across a chunk boundary still appears in at least one chunk's context window.

Optional: Re-Ranking with a Cross-Encoder

RRF is a good fuser, but it doesn't understand the query-document relationship - it only combines rankings. For high-stakes RAG (medical, legal, financial), add a cross-encoder re-ranking step after retrieval:

from sentence_transformers import CrossEncoder

reranker = CrossEncoder('cross-encoder/ms-marco-MiniLM-L-6-v2')

def rerank(query: str, docs: list[dict]) -> list[dict]:
    pairs = [(query, d['content']) for d in docs]
    scores = reranker.predict(pairs)
    ranked = sorted(zip(docs, scores), key=lambda x: x[1], reverse=True)
    return [d for d, _ in ranked]

Cross-encoders are slower than bi-encoders (they process each query-document pair jointly), so only re-rank the top 20-50 results from hybrid search, then pass the top 5-10 to the LLM.

Test Your Knowledge

FAQ

Production Checklist

Before going live with your hybrid RAG pipeline:

• ☑ Set hnsw.ef_search = 100 (or higher) on your connection pool - the default 40 sacrifices too much recall
• ☑ Verify query plan uses the HNSW index with EXPLAIN ANALYZE
• ☑ Run VACUUM ANALYZE documents after bulk inserts to update statistics
• ☑ Store metadata (source URL, section, date) in jsonb for post-retrieval filtering
• ☑ Add WHERE metadata @> '{"source": "docs"}'::jsonb to filter by source before ranking
• ☑ Monitor embedding API latency separately from DB query latency in your observability stack
• ☑ Set statement_timeout on hybrid search queries to avoid slow queries blocking your LLM pipeline
• ☑ Use asyncpg connection pools (min 2, max 20) - avoid creating a new connection per request

By combining these methods into a cohesive PostgreSQL hybrid search RAG pipeline, you dramatically improve the precision and reliability of your AI application.

You Might Also Like

• pgvector in Production: High-Scale Vector Search & Hybrid RRF in PostgreSQL
• Scaling Vector Search with pgvector
• AI Coding Assistants: Best Practices for 2026
• Cursor vs Claude Code: Which ai Coding Tool Actually Wins in 2026?