Build a Hybrid Search Engine with pgvector & FastAPI

Step-by-step engineering tutorial to building a production hybrid search engine combining full-text search, pgvector HNSW, RRF, and FastAPI.

Engineering teams building Retrieval-Augmented Generation (RAG) pipelines, enterprise search portals, and catalog recommendation systems face a common architectural trap: premature infrastructure sprawl. Early prototypes often introduce dedicated vector databases—such as Pinecone, Milvus, or Qdrant—alongside primary operational databases. While these specialized vector stores handle dense embedding indexing efficiently, maintaining them in production introduces severe operational penalties. Teams must manage distributed transactions, build complex change-data-capture (CDC) pipelines, debug synchronization lag between relational state and vector records, and sacrifice native relational SQL joins.

Furthermore, relying exclusively on dense vector search introduces fundamental retrieval failure modes. Pure semantic embeddings frequently suffer from “keyword blindness” and “semantic drift.” When users search for exact alphanumeric identifiers, product SKUs, error trace codes, or specific legal terminology, vector similarity models map those exact queries to broadly related conceptual neighbors rather than the exact string match. Conversely, traditional lexical search (BM25) excels at exact keyword matching but fails when users search with natural language synonyms or conceptual descriptions.

The solution is hybrid search: executing sparse lexical keyword search and dense semantic vector search simultaneously, then combining the candidate sets using Reciprocal Rank Fusion (RRF). By implementing this architecture directly inside PostgreSQL using the pgvector extension and exposing it via an asynchronous FastAPI service, developers can build a unified, ACID-compliant search engine without managing separate database clusters.

This technical guide dissects the mechanics of hybrid search, benchmarks HNSW indexing parameters, and provides a production-grade Python implementation using FastAPI, SQLAlchemy 2.0, and asyncpg.

💻 Get the full code on GitHub: runnable source, requirements and setup steps for this tutorial.

Core Architecture and Reciprocal Rank Fusion Mechanics

Hybrid search resolves the tension between lexical precision and semantic recall by querying two independent indexing structures within the same database engine.

┌────────────────────────────────────────────────────────────────────────┐
│              POSTGRESQL HYBRID SEARCH PIPELINE TOPOLOGY                │
├────────────────────────────────────────────────────────────────────────┤
│                                                                        │
│   [ Client Query: "error code 0x80070005 access denied" ]              │
│                                  │                                     │
│                 ┌────────────────┴────────────────┐                    │
│                 ▼                                 ▼                    │
│   [ Branch A: Lexical Search ]       [ Branch B: Dense Vector Search ] │
│   ├─ Parse into tsquery              ├─ Generate query embedding       │
│   ├─ Scan GIN Index on tsvector      ├─ Scan HNSW Index (<=> Cosine)   │
│   ├─ Compute ts_rank_cd score        ├─ Rank by vector distance        │
│   └─ Retrieve Top-K (Lexical Rank)   └─ Retrieve Top-K (Vector Rank)   │
│                 │                                 │                    │
│                 └────────────────┬────────────────┘                    │
│                                  ▼                                     │
│   [ Reciprocal Rank Fusion (RRF) Ranking Node ]                        │
│   ├─ Evaluates unified score: RRF(d) = Σ [ 1 / (k + rank_i(d)) ]       │
│   ├─ Reconciles non-comparable scores (BM25 vs. Cosine Distance)       │
│   └─ Surfaces exact SKU matches AND conceptually similar passages       │
│                                  │                                     │
│                                  ▼                                     │
│   [ Asynchronous FastAPI Endpoint (asyncpg Connection Pool) ]          │
│   └─ Formats JSON response with metadata, highlighted text, and score  │
│                                                                        │
└────────────────────────────────────────────────────────────────────────┘

The system operates across three distinct stages:

1. Dual Query Execution: Incoming text queries are bifurcated. The lexical branch converts the query into a PostgreSQL tsquery (filtering language-specific stopwords and applying Snowball stemming) to search an inverted Generalized Inverted Index (GIN). Simultaneously, the dense branch embeds the text using a transformer model and searches a Hierarchical Navigable Small World (HNSW) index using cosine distance (<=>).
2. The Scoring Incompatibility Problem: Lexical search engines output unbounded relevance scores (e.g., BM25 scores from 0 to 45+), whereas vector indexes output bounded distance metrics (e.g., cosine distances between 0 and 2). Standardizing these values using linear min-max scaling is brittle: a single extreme outlier compresses all remaining scores, corrupting search ranking.
3. Reciprocal Rank Fusion (RRF): To bypass score incompatibility, RRF operates purely on positional ranks rather than raw numerical scores. For every document d present in either result set, its score is calculated as:
   RRF(d) = Σ [ 1 / (k + rank_m(d)) ]
   Where M represents the search methods (lexical and vector), rank_m(d) is the document’s 1-indexed rank within method m, and k is a smoothing constant (standardized at 60). If a document ranks highly in both lexical and semantic searches, its reciprocal values sum aggressively, pushing it to the top of the combined result set. If a document only appears in one branch, it is still retained but penalized appropriately.

Technical Comparison Matrix

Selecting the appropriate storage and retrieval layer requires evaluating transactional integrity, indexing latency, memory consumption, and operational complexity.

For datasets containing under 10 million vectors, PostgreSQL with pgvector offers the optimal balance between high query performance and operational simplicity. Running hybrid search inside Postgres eliminates the risk of “ghost vectors”—vector entries that point to relational rows that were deleted or modified in another system.

Hands-On Implementation: Building the Production Search Engine

The following production implementation provides an asynchronous FastAPI application with an end-to-end database schema, GIN and HNSW index creation, and an optimized SQL query executing Reciprocal Rank Fusion directly inside PostgreSQL using Common Table Expressions (CTEs).

Prerequisites and Dependencies

Install the necessary dependencies in a clean Python 3.11 virtual environment:

pip install fastapi uvicorn[standard] sqlalchemy[asyncio] asyncpg pgvector pydantic sentence-transformers

Ensure PostgreSQL 16+ is running with the pgvector extension enabled:

CREATE EXTENSION IF NOT EXISTS vector;

Complete Implementation: main.py

"""
Production Hybrid Search Engine using PostgreSQL, pgvector, and FastAPI.
Tested with Python 3.11+, PostgreSQL 16, pgvector 0.7+, and SQLAlchemy 2.0.30.
"""

from __future__ import annotations

import logging
from contextlib import asynccontextmanager
from typing import AsyncGenerator, List, Optional

from fastapi import Depends, FastAPI, HTTPException, status
from pydantic import BaseModel, Field
from sentence_transformers import SentenceTransformer
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine

logging.basicConfig(level=logging.INFO, format="%(asctime)s - %(levelname)s - %(message)s")
logger = logging.getLogger(__name__)

# Database Configuration
DATABASE_URL = "postgresql+asyncpg://postgres:postgres@localhost:5432/hybrid_db"
EMBEDDING_MODEL_NAME = "all-MiniLM-L6-v2"
EMBEDDING_DIM = 384

# Async Engine and Session Pool
engine = create_async_engine(
    DATABASE_URL,
    pool_size=20,
    max_overflow=10,
    pool_recycle=1800,
    pool_pre_ping=True,
)
AsyncSessionLocal = async_sessionmaker(bind=engine, class_=AsyncSession, expire_on_commit=False)


class EmbeddingService:
    """
    Manages local neural sentence embeddings using SentenceTransformers.
    """
    def __init__(self, model_name: str) -> None:
        logger.info("Loading embedding model: %s", model_name)
        self.model = SentenceTransformer(model_name)

    def embed_text(self, text_input: str) -> List[float]:
        return self.model.encode(text_input, normalize_embeddings=True).tolist()


# Global Singleton for Embedding Generation
embedding_service = EmbeddingService(EMBEDDING_MODEL_NAME)


# Pydantic Schemas
class DocumentIngest(BaseModel):
    title: str = Field(..., max_length=255)
    content: str = Field(..., min_length=1)


class SearchQuery(BaseModel):
    query: str = Field(..., min_length=1)
    limit: int = Field(default=10, ge=1, le=50)
    rrf_k: int = Field(default=60, ge=1, le=100)


class SearchResult(BaseModel):
    id: int
    title: str
    content: str
    rrf_score: float
    lexical_rank: Optional[int]
    vector_rank: Optional[int]


async def get_db() -> AsyncGenerator[AsyncSession, None]:
    async with AsyncSessionLocal() as session:
        yield session


@asynccontextmanager
async def lifespan(app: FastAPI):
    # Initialize table and indexes on startup
    logger.info("Verifying database schema and HNSW/GIN indexes...")
    async with engine.begin() as conn:
        await conn.execute(text("CREATE EXTENSION IF NOT EXISTS vector;"))
        await conn.execute(text(f"""
            CREATE TABLE IF NOT EXISTS documents (
                id SERIAL PRIMARY KEY,
                title VARCHAR(255) NOT NULL,
                content TEXT NOT NULL,
                tsv TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || content)) STORED,
                embedding VECTOR({EMBEDDING_DIM})
            );
        """))
        # GIN Index for fast Lexical Full-Text Search
        await conn.execute(text("""
            CREATE INDEX IF NOT EXISTS idx_documents_tsv ON documents USING GIN(tsv);
        """))
        # HNSW Index for fast Vector Similarity Search
        await conn.execute(text("""
            CREATE INDEX IF NOT EXISTS idx_documents_hnsw 
            ON documents USING hnsw (embedding vector_cosine_ops)
            WITH (m = 16, ef_construction = 64);
        """))
    logger.info("Database schema verification complete.")
    yield
    await engine.dispose()
    logger.info("Database connection pools closed.")


app = FastAPI(title="Production Hybrid Search Engine", lifespan=lifespan)


@app.post("/documents", status_code=status.HTTP_201_CREATED)
async def ingest_document(
    doc: DocumentIngest,
    db: AsyncSession = Depends(get_db),
) -> dict:
    """
    Ingests raw text, generates dense vector embedding, and stores records in PostgreSQL.
    """
    try:
        combined_text = f"{doc.title} {doc.content}"
        vector = embedding_service.embed_text(combined_text)

        insert_sql = text("""
            INSERT INTO documents (title, content, embedding)
            VALUES (:title, :content, :embedding)
            RETURNING id;
        """)
        
        result = await db.execute(
            insert_sql,
            {"title": doc.title, "content": doc.content, "embedding": str(vector)},
        )
        await db.commit()
        doc_id = result.scalar()
        return {"id": doc_id, "status": "indexed"}
    except Exception as err:
        await db.rollback()
        logger.error("Failed to index document: %s", err)
        raise HTTPException(status_code=500, detail="Database indexing error")


@app.post("/search", response_model=List[SearchResult])
async def search_documents(
    payload: SearchQuery,
    db: AsyncSession = Depends(get_db),
) -> List[SearchResult]:
    """
    Executes parallel lexical and vector search with Reciprocal Rank Fusion (RRF) inside PostgreSQL.
    """
    try:
        query_vector = embedding_service.embed_text(payload.query)

        # Unified RRF Query utilizing Common Table Expressions (CTEs)
        hybrid_sql = text(f"""
            WITH lexical_search AS (
                SELECT 
                    id,
                    ROW_NUMBER() OVER (ORDER BY ts_rank_cd(tsv, plainto_tsquery('english', :query)) DESC) AS rank
                FROM documents
                WHERE tsv @@ plainto_tsquery('english', :query)
                LIMIT 50
            ),
            vector_search AS (
                SELECT 
                    id,
                    ROW_NUMBER() OVER (ORDER BY embedding <=> :vector) AS rank
                FROM documents
                ORDER BY embedding <=> :vector
                LIMIT 50
            ),
            combined_scores AS (
                SELECT 
                    COALESCE(l.id, v.id) AS id,
                    COALESCE(1.0 / (:k + l.rank), 0.0) + COALESCE(1.0 / (:k + v.rank), 0.0) AS rrf_score,
                    l.rank AS lexical_rank,
                    v.rank AS vector_rank
                FROM lexical_search l
                FULL OUTER JOIN vector_search v ON l.id = v.id
            )
            SELECT 
                d.id,
                d.title,
                d.content,
                c.rrf_score,
                c.lexical_rank,
                c.vector_rank
            FROM combined_scores c
            JOIN documents d ON c.id = d.id
            ORDER BY c.rrf_score DESC
            LIMIT :limit;
        """)

        result = await db.execute(
            hybrid_sql,
            {
                "query": payload.query,
                "vector": str(query_vector),
                "k": payload.rrf_k,
                "limit": payload.limit,
            },
        )
        
        rows = result.fetchall()
        return [
            SearchResult(
                id=row.id,
                title=row.title,
                content=row.content,
                rrf_score=float(row.rrf_score),
                lexical_rank=row.lexical_rank,
                vector_rank=row.vector_rank,
            )
            for row in rows
        ]
    except Exception as err:
        logger.error("Hybrid search query failed: %s", err)
        raise HTTPException(status_code=500, detail="Search query execution failed")


if __name__ == "__main__":
    import uvicorn
    uvicorn.run("main:app", host="0.0.0.0", port=8000, reload=True)

Real-World Production Gotchas and Edge Cases

Operating hybrid search at production scale reveals critical performance and infrastructure pitfalls that standard benchmarks obscure.

1. HNSW Memory Residency and Eviction Penalties

HNSW indexes construct multi-layered geometric graphs over high-dimensional vector space.

• The Problem: HNSW traversal requires random memory pointer hops across graph layers. If an HNSW index exceeds the dedicated PostgreSQL shared_buffers and the operating system page cache, queries begin thrashing the underlying disk. P95 latency can spike from 5ms to over 250ms when reading cold graph nodes from SSD storage.
• The Mitigation: Size database RAM so that all HNSW indexes fit entirely in memory. Monitor index sizes via pg_relation_size('idx_documents_hnsw'). For large-scale deployments, utilize the halfvec (16-bit half-precision floating-point) data type introduced in pgvector 0.7+. Halving vector storage cut RAM requirements by 50% with negligible impact on retrieval recall.

2. Parameter Tuning: Trade-Offs of m, ef_construction, and ef_search

Configuring HNSW indexing involves balancing construction overhead with query recall.

• The Problem: Default HNSW parameters can lead to degraded recall on clustered vector spaces. Setting m (maximum connections per node) too low degrades recall, while setting it too high exponentially inflates memory footprint and write latency.
• The Mitigation: For production RAG systems with 1536-dimensional vectors, set m=16 and ef_construction=64 to 128 during table creation. At query time, tune runtime recall by executing SET hnsw.ef_search = 40; before executing vector queries. Increasing ef_search expands the candidate search pool, boosting recall during high-concurrency periods.

3. Lexical Stemming Edge Cases on Technical Terms

PostgreSQL’s plainto_tsquery applies English Snowball stemming by default.

• The Problem: Standard stemming algorithms frequently corrupt exact alphanumeric identifiers. For instance, technical terms like “Postgres”, “PostgreSQL”, or part numbers like “SKU-9941A” can be aggressively stemmed or stripped of punctuation, causing the lexical branch to return zero results.
• The Mitigation: Use the 'simple' dictionary configuration (to_tsvector('simple', ...)) for code repositories, catalog SKUs, and technical documentation. The 'simple' dictionary preserves exact string tokens and lowercases without applying destructive morphological stemming.

Related Technical Resources

For further architectural blueprints, vector database comparisons, and backend scaling guides, explore our engineering publications on ISMARTANJI CREATIONS:

• Agentic RAG Architecture: Implementing Multi-Agent Document Retrieval — Learn query routing, self-grading loops, and multi-agent retrieval pipelines.
• Vector Database Architecture for AI: Indexing, Latency, and Enterprise Scale — Compare indexing methods, latency trade-offs, and scaling patterns for vector search.
• SGLang vs vLLM: Local LLM Serving Benchmarks & Architecture Comparison — See how batching and caching affect throughput in self-hosted inference.
• vLLM vs Ollama: Architecture, Memory Footprint, and Local Deployment Guide — Choose the right local serving stack for your hardware.

Frequently Asked Questions

Why is Reciprocal Rank Fusion preferred over normalizing and summing similarity scores?

BM25 scores and vector distance metrics operate on completely different numerical scales. BM25 scores are unbounded and sensitive to document length and keyword rarity, whereas cosine distance is bounded between 0 and 2. Min-max normalization fails when outliers skew the distribution. RRF ignores numerical magnitudes and operates purely on relative positional ranks, ensuring robust merging without arbitrary scaling constants.

When should engineering teams use pgvector instead of a dedicated vector database?

Teams should use pgvector when their vector dataset contains under 10 million vectors and requires tight coupling with relational metadata, ACID guarantees, and standard SQL queries. Dedicated vector databases become necessary only when scaling beyond tens of millions of vectors with massive write throughput that would overwhelm a single relational database instance.

How does HNSW indexing compare to IVFFlat in pgvector?

IVFFlat (Inverted File Flat) partitions vector space into clusters and requires periodic retraining as data changes; it has a smaller memory footprint but lower recall and slower query speeds. HNSW (Hierarchical Navigable Small World) builds a multi-layer graph that can be updated incrementally without retraining, delivering superior query throughput and high recall at the cost of higher RAM consumption and longer index build times.

Sources & Documentation

• pgvector Official Documentation: GitHub – pgvector/pgvector: Open-source vector similarity search for Postgres
• PostgreSQL Full-Text Search Manual: PostgreSQL 16 Documentation: Chapter 12. Full Text Search
• Reciprocal Rank Fusion Research Paper: Reciprocal Rank Fusion outperforms Condorcet and individual Rank Learning Methods (Cormack, Clarke, and Buettcher)
• FastAPI Framework: FastAPI High-Performance Async Framework Reference
• Testbed Environment: Ubuntu 22.04 LTS, Python 3.11, PostgreSQL 16.3 with pgvector 0.7.0, NVIDIA GeForce RTX 4090 (24GB VRAM), AMD EPYC 7763.