Hybrid Search in PostgreSQL: Combining Full-Text Search with pgvector via Reciprocal Rank Fusion

The Fundamental Tension in Search Architectures

In modern intelligent applications, search expectations have evolved dramatically. Users expect the search bar to understand semantic intent—identifying that a search for "fault-tolerant background tasks" should surface articles on Celery dead-letter queues. Simultaneously, users expect exact keyword precision: querying a product code like SKU-9481-X, an error hash, or a specific API method must return that exact entity at rank #1.

Deploying pure vector semantic search using cosine or inner product distance frequently fails the precision test. Dense neural embedding models compress high-dimensional tokens into a continuous geometric space where rare strings, IDs, and exact acronyms lose their distinctiveness. Conversely, traditional inverted-index search (like PostgreSQL's native tsvector or Elasticsearch's BM25) fails when users articulate concepts without matching the author's exact vocabulary.

Rather than managing a fragile, dual-write synchronization pipeline between PostgreSQL and an external vector database, you can execute world-class hybrid search directly inside PostgreSQL by blending BM25 lexical ranking and dense vector similarity using Reciprocal Rank Fusion (RRF).

1. Schema Setup: Unifying tsvector and pgvector

To support hybrid search with zero data duplication, we define both a dense vector column and an auto-generated lexical search vector within the same relational table:

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE knowledge_documents (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    body_content TEXT NOT NULL,
    -- Dense semantic representation (OpenAI text-embedding-3-small or self-hosted BGE)
    embedding vector(1536) NOT NULL,
    -- Generated lexical search document
    search_vector tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(body_content, '')), 'B')
    ) STORED,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

2. Dual Indexing: HNSW and GIN Co-existence

The speed of hybrid retrieval hinges on specialized indexes optimized for disparate access patterns. For dense vectors, we deploy a Hierarchical Navigable Small World (HNSW) index. For lexical tokens, we use a Generalized Inverted Index (GIN):

-- 1. HNSW index for sub-15ms vector nearest-neighbor traversal
CREATE INDEX idx_docs_hnsw_embedding ON knowledge_documents 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- 2. GIN index for inverted full-text keyword lookups
CREATE INDEX idx_docs_gin_search ON knowledge_documents 
USING gin (search_vector);

When tuning memory for these concurrent index structures, ensure PostgreSQL's maintenance_work_mem is sized generously during index creation (e.g. 512MB to 1GB) and configure shared_buffers to hold both the HNSW graph and GIN root nodes in hot memory.

3. Reciprocal Rank Fusion (RRF) in a Single SQL Query

The mathematical challenge of hybrid search lies in score normalization: vector cosine similarity produces scores bounded between -1.0 and 1.0 (or 0.0 to 2.0 distance), while lexical search algorithms produce unbounded positive floats. Attempting to linearly blend raw scores (e.g. 0.7 * vector_score + 0.3 * text_score) produces brittle, unpredictable rankings that drift with query length.

Reciprocal Rank Fusion (RRF) resolves this elegantly by scoring documents based purely on their ordinal rank position rather than raw arbitrary scores. The standard RRF formula is:

RRF_Score(d) = SUM( 1 / (k + rank_i(d)) )

where k is a smoothing constant (standard benchmark value: 60). Here is the complete production SQL query executing dual retrieval and RRF re-ranking using Common Table Expressions (CTEs):

WITH semantic_search AS (
    -- Retrieve top 30 semantic nearest neighbors
    SELECT 
        id, 
        ROW_NUMBER() OVER (ORDER BY embedding <=> '[0.012, -0.043, ...]'::vector) AS rank_semantic
    FROM knowledge_documents
    ORDER BY embedding <=> '[0.012, -0.043, ...]'::vector
    LIMIT 30
),
lexical_search AS (
    -- Retrieve top 30 keyword matches
    SELECT 
        id, 
        ROW_NUMBER() OVER (ORDER BY ts_rank_cd(search_vector, websearch_to_tsquery('english', 'PostgreSQL Celery')) DESC) AS rank_lexical
    FROM knowledge_documents
    WHERE search_vector @@ websearch_to_tsquery('english', 'PostgreSQL Celery')
    ORDER BY ts_rank_cd(search_vector, websearch_to_tsquery('english', 'PostgreSQL Celery')) DESC
    LIMIT 30
)
SELECT 
    d.id,
    d.title,
    d.slug,
    -- Compute reciprocal rank fusion score (k = 60)
    COALESCE(1.0 / (60 + s.rank_semantic), 0.0) + 
    COALESCE(1.0 / (60 + l.rank_lexical), 0.0) AS rrf_score,
    s.rank_semantic,
    l.rank_lexical
FROM knowledge_documents d
LEFT JOIN semantic_search s ON d.id = s.id
LEFT JOIN lexical_search l ON d.id = l.id
WHERE s.id IS NOT NULL OR l.id IS NOT NULL
ORDER BY rrf_score DESC
LIMIT 10;

Workload Isolation: To ensure high-dimensional vector similarity queries do not saturate primary transactional memory or block write threads, consider offloading analytical querying via zero-impact analytics using PostgreSQL read replicas and DuckDB.

Production Takeaway

Native PostgreSQL hybrid search with RRF delivers enterprise-grade retrieval quality without introducing third-party vector databases. By keeping lexical and semantic vectors synchronized within a single relational row, you maintain strict ACID transactional integrity, eliminate ETL latency, and achieve sub-25ms response times on millions of indexed records.