DEV Community

Ankit Arora
Ankit Arora

Posted on

Hybrid Schematic Search in PostgreSQL with Full-Text and Vector Similarity

#vectordatabase #ai #postgressql #pgvetor

The Search Problem We're Really Solving

If you're building any modern AI application—whether it's a RAG (Retrieval-Augmented Generation) pipeline, a semantic search engine, or an intelligent document retrieval system—you've likely encountered a fundamental tension:

Vector similarity search understands meaning but can miss exact keyword matches.

Full-text search catches exact terms but fails to understand semantic relationships.

What if you could have both?

This is the promise of hybrid search: combining the semantic understanding of vector embeddings with the precision of traditional full-text search, all within a single PostgreSQL database using the pgvector extension.

In this comprehensive guide, we'll build a working hybrid search system from scratch, analyze its performance characteristics, and understand exactly why it works—and when you should consider implementing it in your own AI engineering projects.

Table of Contents

  1. Why Hybrid Search Matters for AI Applications
  2. Understanding the Two Pillars of Search
  3. Setting Up Your PostgreSQL Environment
  4. Building the Dataset: From Random Text to Embeddings
  5. Indexing Strategy: GIN and HNSW Explained
  6. Implementing Vector Search
  7. Implementing Full-Text Search
  8. The Magic of Reciprocal Rank Fusion (RRF)
  9. Constructing the Hybrid Search Query
  10. Performance Analysis with EXPLAIN ANALYZE
  11. Production Considerations and Trade-offs
  12. Next Steps and Future Research

1. Why Hybrid Search Matters for AI Applications {#why-hybrid-search-matters}

The RAG Revolution and Its Retrieval Problem

Retrieval-Augmented Generation has become the dominant paradigm for building AI applications that need to access external knowledge. The pattern is deceptively simple:

  1. User asks a question
  2. System retrieves relevant documents
  3. LLM generates an answer using retrieved context

The quality of step 2—retrieval—often determines the entire system's success. And here's the uncomfortable truth: most RAG implementations rely solely on vector similarity search, which has significant blind spots.

When Vector Search Fails

Consider these scenarios where pure vector search disappoints:

Scenario Vector Search Behavior Problem
Product codes ("SKU-12345") Treats as semantic tokens May miss exact matches
Technical terminology Averages meaning across context Loses specificity
Proper nouns Depends on training data Inconsistent results
Rare phrases Embedding space may be sparse Poor discrimination

When Full-Text Search Fails

Traditional full-text search has complementary weaknesses:

Scenario Full-Text Search Behavior Problem
Synonyms ("car" vs "automobile") No match without thesaurus Misses relevant docs
Conceptual queries Requires exact terms Poor recall
Natural language questions Word-by-word matching Ignores intent
Multilingual content Dictionary-dependent Inconsistent coverage

The Hybrid Search Solution

Hybrid search combines both approaches, using techniques like Reciprocal Rank Fusion (RRF) to merge results intelligently. The result: better recall, better precision, and more robust retrieval across diverse query types.

2. Understanding the Two Pillars of Search {#understanding-the-two-pillars}

Before diving into implementation, let's establish a clear mental model of what each search method actually does.

Vector Similarity Search

Vector search converts text into high-dimensional embeddings—numerical representations that capture semantic meaning. Similar meanings produce similar vectors, enabling:

  • Semantic matching: "happy" finds "joyful"
  • Conceptual search: "how to fix a leak" finds "plumbing repair guide"
  • Cross-lingual retrieval: With multilingual models, "hello" finds "hola"

The similarity is typically measured using cosine distance, where smaller values indicate greater similarity.

cosine_distance = 1 - cosine_similarity
Enter fullscreen mode Exit fullscreen mode

Full-Text Search

PostgreSQL's full-text search uses:

  1. Tokenization: Breaking text into words
  2. Normalization: Stemming ("running" → "run"), lowercasing
  3. Stop word removal: Eliminating common words ("the", "a", "is")
  4. tsvector creation: A sorted list of normalized tokens with positions
  5. tsquery matching: Boolean operations on search terms

The result is a lexeme-based index that enables fast, precise keyword matching.

The Complementarity Principle

Here's the key insight: vector search and full-text search fail in different ways. When you combine them, failures in one method can be compensated by successes in the other.

                    ┌─────────────────┐
                    │   User Query    │
                    └────────┬────────┘
                             │
              ┌──────────────┴──────────────┐
              │                             │
              ▼                             ▼
    ┌─────────────────┐           ┌─────────────────┐
    │  Vector Search  │           │ Full-Text Search│
    │   (Semantic)    │           │   (Lexical)     │
    └────────┬────────┘           └────────┬────────┘
             │                             │
             │    ┌─────────────────┐      │
             └───►│  RRF Fusion     │◄─────┘
                  │  (Combining)    │
                  └────────┬────────┘
                           │
                           ▼
                  ┌─────────────────┐
                  │ Ranked Results  │
                  └─────────────────┘
Enter fullscreen mode Exit fullscreen mode

3. Setting Up Your PostgreSQL Environment {#setting-up-postgresql}

Requirements

To follow along, you'll need:

  • PostgreSQL (version 14+ recommended)
  • pgvector extension (v0.5+, I'll use v0.7.4)
  • Python 3.8+ with these packages:
    • psycopg (PostgreSQL adapter)
    • pgvector (Python integration)
    • faker (test data generation)
    • sentence_transformers (embedding generation)

Installation 

# Install pgvector (varies by platform)
# macOS with Homebrew:
brew install pgvector

# Ubuntu/Debian:
sudo apt install postgresql-15-pgvector

# From source:
git clone https://github.com/pgvector/pgvector.git
cd pgvector && make && make install

# Python dependencies
pip install psycopg[binary] pgvector faker sentence-transformers
Enter fullscreen mode Exit fullscreen mode

Database Schema

Let's create our schema with careful attention to production-readiness:

-- Enable the vector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Create the products table
CREATE TABLE products (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    description text NOT NULL,
    embedding vector(384) NOT NULL
);

-- Create a helper function for RRF scoring
-- This will be used in our hybrid search query
CREATE OR REPLACE FUNCTION rrf_score(rank int, rrf_k int DEFAULT 50)
RETURNS numeric
LANGUAGE SQL
IMMUTABLE PARALLEL SAFE
AS $$
    SELECT COALESCE(1.0 / ($1 + $2), 0.0);
$$;
Enter fullscreen mode Exit fullscreen mode

Why 384 dimensions? The multi-qa-MiniLM-L6-cos-v1 model produces 384-dimensional embeddings. This is a deliberate choice balancing:

  • Expressiveness: Enough dimensions to capture semantic nuance
  • Storage efficiency: Smaller than 768 or 1536-dimensional alternatives
  • Query speed: Faster distance calculations

4. Building the Dataset: From Random Text to Embeddings {#building-the-dataset}

The Data Generation Strategy

For this demonstration, we'll generate synthetic data using Faker and encode it with a sentence transformer. While the data is artificial, the methodology is production-ready.

from faker import Faker
import psycopg
from pgvector.psycopg import register_vector
from sentence_transformers import SentenceTransformer

# Initialize Faker for synthetic data
fake = Faker()

# Generate 50,000 random sentences (50 words each)
# This simulates a product description corpus
sentences = [fake.sentence(nb_words=50) for _ in range(50_000)]

print(f"Generated {len(sentences)} sentences")
print(f"Sample: {sentences[0][:100]}...")
Enter fullscreen mode Exit fullscreen mode

Generating Embeddings 

# Load the sentence transformer model
# multi-qa-MiniLM-L6-cos-v1 is optimized for question-answering retrieval
model = SentenceTransformer('multi-qa-MiniLM-L6-cos-v1')

# Generate embeddings for all sentences
# This may take several minutes depending on your hardware
print("Generating embeddings...")
embeddings = model.encode(sentences, show_progress_bar=True)
print(f"Embedding shape: {embeddings.shape}")  # Should be (50000, 384)
Enter fullscreen mode Exit fullscreen mode

Loading Data into PostgreSQL 

# Connect to your database
# Replace with your actual connection details
conn = psycopg.connect(
    dbname="your_database",
    user="your_user",
    password="your_password",
    host="localhost",
    port="5432",
    autocommit=True
)

# Register the vector type with psycopg
register_vector(conn)

cur = conn.cursor()

# Use COPY for efficient bulk loading
with cur.copy("COPY products (description, embedding) FROM STDIN WITH (FORMAT BINARY)") as copy:
    copy.set_types(["text", "vector"])
    for content, embedding in zip(sentences, embeddings):
        copy.write_row((content, embedding))

print("Data loaded successfully!")
cur.close()
conn.close()
Enter fullscreen mode Exit fullscreen mode

Performance Tip: The COPY command is orders of magnitude faster than individual INSERT statements for bulk loading. For 50,000 rows, this approach typically completes in seconds rather than minutes.

5. Indexing Strategy: GIN and HNSW Explained {#indexing-strategy}

Creating the Indexes 

-- Full-text search index using GIN (Generalized Inverted Index)
CREATE INDEX products_description_gin_idx ON products
    USING GIN (to_tsvector('english', description));

-- Vector search index using HNSW (Hierarchical Navigable Small World)
CREATE INDEX products_embeddings_hnsw_idx ON products
    USING hnsw(embedding vector_cosine_ops) WITH (ef_construction=256);
Enter fullscreen mode Exit fullscreen mode

Deep Dive: The GIN Index

The GIN index on to_tsvector('english', description) deserves careful explanation:

Expression Index: We're not indexing the raw description column—we're indexing the output of to_tsvector(). This means:

  1. Storage efficiency: We don't need a separate tsvector column
  2. Automatic consistency: The index is always in sync with the text
  3. Query optimization: Queries using the same expression can use the index

Why 'english'? PostgreSQL requires immutable functions in expression indexes. Since to_tsvector() with a dictionary argument is immutable (the dictionary is fixed), we must specify it explicitly. This ensures:

  • Consistent tokenization across index builds and queries
  • Reproducible results
  • No surprises from session-level configuration changes

Deep Dive: The HNSW Index

HNSW (Hierarchical Navigable Small World) is a graph-based algorithm for approximate nearest neighbor search:

Layer 3 (coarsest):    A ─────────────────── B
                       │                     │
Layer 2:          C ───┼─── D ─── E ──────── F
                  │    │    │     │         │
Layer 1:     G ───┼────┼────┼─────┼────H────┼─── I
             │    │    │    │     │    │    │   │
Layer 0 (finest): All vectors connected to nearest neighbors
Enter fullscreen mode Exit fullscreen mode

Key parameters:

Parameter Default Our Setting Trade-off
m 16 16 Higher = better recall, more memory
ef_construction 64 256 Higher = better index quality, slower build
ef_search 40 40 Higher = better recall, slower queries

Why ef_construction=256? This increases the quality of the graph structure during index construction. The trade-off is longer build time, but better query performance and recall.

6. Implementing Vector Search {#implementing-vector-search}

Generating a Query Embedding 

from sentence_transformers import SentenceTransformer

model = SentenceTransformer('multi-qa-MiniLM-L6-cos-v1')
query_embedding = model.encode('travel computer')
Enter fullscreen mode Exit fullscreen mode

The Vector Search Query 

SELECT 
    id, 
    description, 
    rank() OVER (ORDER BY $1 <=> embedding) AS rank
FROM products
ORDER BY $1 <=> embedding
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

Understanding the operators:

  • <=>: Cosine distance operator (0 = identical, 2 = opposite)
  • rank() OVER (ORDER BY ...): Window function assigning rank based on distance
  • $1: Parameterized query placeholder for the embedding vector

Sample Results 

  id   | description             | rank 
-------+-------------------------+-------
 10578 | ... travel ... computer |    1
 20763 | ... computer ...        |    2
 20894 | ... computer ...        |    3
   838 | Computer ...            |    4
 11045 | ...computer ...         |    5
 18548 | ... travel computer ... |    6  ← Should be higher!
 16564 | ... computer ...        |    7
 20402 | ...computer ...         |    8
 10346 | ... computer ...        |    9
 11243 | ... travel ... computer |   10
Enter fullscreen mode Exit fullscreen mode

Observation: Record 18548 contains the exact phrase "travel computer" but ranks only 6th. This is the fundamental limitation of pure vector search—it prioritizes overall semantic similarity over exact phrase matching.

7. Implementing Full-Text Search {#implementing-full-text-search}

The Full-Text Search Query 

SELECT
    id,
    description,
    rank() OVER (
        ORDER BY ts_rank_cd(
            to_tsvector(description), 
            plainto_tsquery('travel computer')
        ) DESC
    ) AS rank
FROM products
WHERE
    plainto_tsquery('english', 'travel computer') @@ 
    to_tsvector('english', description)
ORDER BY rank
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

Deconstructing the Query

  1. plainto_tsquery('english', 'travel computer'): Converts plain text to a tsquery

    • Result: 'travel' & 'comput' (stemmed, AND-connected)

  2. to_tsvector('english', description): Converts document to searchable form

    • Result: Sorted array of stemmed tokens with positions

  3. @@ operator: Tests if tsquery matches tsvector

  4. ts_rank_cd(): Cover density ranking

    • Considers proximity of search terms
    • Higher scores for terms appearing close together

Sample Results 

  id   | description                 | rank 
-------+-----------------------------+------
 18548 | ... travel computer ...     |    1  ← Correct!
  7372 | ... travel computer ...     |    1
 49374 | ... travel computer ...     |    1
 39214 | ... travel computer ...     |    1
 12875 | ... computer travel ...     |    1
  3712 | ... travel computer ...     |    1
 24719 | ... travel ... computer ... |    7  ← Terms far apart
 31607 | ... travel ... computer ... |    7
 13674 | ... travel ... computer ... |    7
 42755 | ... computer ... travel ... |    7
Enter fullscreen mode Exit fullscreen mode

Observation: Full-text search correctly identifies 18548 as a top result, but it returns many results with identical ranks. It lacks the ability to distinguish overall semantic relevance.

8. The Magic of Reciprocal Rank Fusion (RRF) {#rrf-explained}

What is RRF?

Reciprocal Rank Fusion is a rank aggregation method that combines multiple ranked lists into a single ranking. It was introduced by Cormack et al. in 2009 and has become a standard technique in information retrieval.

The Formula 

RRF_score(d) = Σ (1 / (k + rank_i(d)))
Enter fullscreen mode Exit fullscreen mode

Where:

  • d = document
  • k = smoothing constant (typically 50-60)
  • rank_i(d) = rank of document d in result list i

Why RRF Works

  1. Scale-independent: Combines rankings, not raw scores

    • Vector distances and ts_rank scores have different scales
    • Rankings are universally comparable

  2. Robust: Outliers in one list don't dominate

    • A single #1 ranking contributes ~0.02
    • Multiple high rankings compound

  3. Simple: No training required

    • Unlike learning-to-rank approaches
    • Deterministic and explainable

The PostgreSQL Implementation 

CREATE OR REPLACE FUNCTION rrf_score(rank int, rrf_k int DEFAULT 50)
RETURNS numeric
LANGUAGE SQL
IMMUTABLE PARALLEL SAFE
AS $$
    SELECT COALESCE(1.0 / ($1 + $2), 0.0);
$$;
Enter fullscreen mode Exit fullscreen mode

Why COALESCE? This handles NULL ranks gracefully. If a document appears in only one result list, its "missing" rank is treated as contributing 0 to the sum.

Why IMMUTABLE PARALLEL SAFE?

  • IMMUTABLE: Same inputs always produce same output (required for index expressions)
  • PARALLEL SAFE: Can be executed in parallel workers

Score Distribution Analysis

For k=50:

Rank Score Contribution
1 1/51 0.0196
2 1/52 0.0192
5 1/55 0.0182
10 1/60 0.0167
40 1/90 0.0111

Key insight: The difference between rank 1 and rank 40 is only about 2x. This means appearing in both lists is more valuable than ranking #1 in just one.

9. Constructing the Hybrid Search Query {#constructing-hybrid-query}

The Complete Query 

SELECT
    searches.id,
    searches.description,
    sum(rrf_score(searches.rank)) AS score
FROM (
    -- Vector search subquery
    (
        SELECT
            id,
            description,
            rank() OVER (ORDER BY $1 <=> embedding) AS rank
        FROM products
        ORDER BY $1 <=> embedding
        LIMIT 40
    )
    UNION ALL
    -- Full-text search subquery
    (
        SELECT
            id,
            description,
            rank() OVER (
                ORDER BY ts_rank_cd(
                    to_tsvector(description), 
                    plainto_tsquery('travel computer')
                ) DESC
            ) AS rank
        FROM products
        WHERE
            plainto_tsquery('english', 'travel computer') @@ 
            to_tsvector('english', description)
        ORDER BY rank
        LIMIT 40
    )
) searches
GROUP BY searches.id, searches.description
ORDER BY score DESC
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

Why 40 Results Per Subquery?

The choice of 40 is strategic:

  1. Default hnsw.ef_search: PostgreSQL's HNSW index defaults to searching 40 candidates
  2. Overlap potential: With 10 final results desired, 40 provides 4x buffer
  3. Performance balance: More results = better fusion but slower queries

Query Execution Flow 

┌─────────────────────────────────────────────────────────────┐
│                    Hybrid Search Query                       │
└─────────────────────────────────────────────────────────────┘
                              │
        ┌─────────────────────┴─────────────────────┐
        │                                           │
        ▼                                           ▼
┌───────────────────┐                   ┌───────────────────┐
│  Vector Search    │                   │  Full-Text Search │
│  (HNSW Index)     │                   │  (GIN Index)      │
│                   │                   │                   │
│  Returns 40 rows  │                   │  Returns 40 rows  │
│  with ranks 1-40  │                   │  with ranks 1-40  │
└─────────┬─────────┘                   └─────────┬─────────┘
          │                                       │
          └───────────────┬───────────────────────┘
                          │
                          ▼
              ┌───────────────────────┐
              │    UNION ALL          │
              │  (80 rows total)      │
              └───────────┬───────────┘
                          │
                          ▼
              ┌───────────────────────┐
              │   GROUP BY id         │
              │   SUM(rrf_score(rank))│
              └───────────┬───────────┘
                          │
                          ▼
              ┌───────────────────────┐
              │   ORDER BY score DESC │
              │   LIMIT 10            │
              └───────────────────────┘
Enter fullscreen mode Exit fullscreen mode

Sample Hybrid Results 

  id   | description                 |         score          
-------+-----------------------------+------------------------
 18548 | ... travel computer ...     | 0.03746498599439775910  ← Top!
  7372 | ... travel computer ...     | 0.01960784313725490196
 12875 | ... computer travel ...     | 0.01960784313725490196
 10578 | ... travel ... computer ... | 0.01960784313725490196
 39214 | ... travel computer ...     | 0.01960784313725490196
 49374 | ... travel computer ...     | 0.01960784313725490196
  3712 | ... travel computer ...     | 0.01960784313725490196
 20763 | ... computer ...            | 0.01923076923076923077
 20894 | ... computer ...            | 0.01886792452830188679
   838 | Computer ...                | 0.01851851851851851852
Enter fullscreen mode Exit fullscreen mode

Analysis of Results

Record 18548 (the "correct" answer):

  • Appeared in both result lists
  • Vector rank: 6 → RRF contribution: 1/(50+6) = 0.0179
  • FTS rank: 1 → RRF contribution: 1/(50+1) = 0.0196
  • Total: 0.0375

Records 7372, 12875, etc.:

  • Appeared in FTS with rank 1 (or tied)
  • Appeared in vector search with lower ranks
  • Combined score: ~0.0196

Records 20763, 20894, 838:

  • Appeared only in vector search (high ranks)
  • No FTS match
  • Score: ~0.019

The key insight: Record 18548's appearance in both lists with strong rankings boosted it to the top, validating the hybrid approach.

10. Performance Analysis with EXPLAIN ANALYZE {#performance-analysis}

The Execution Plan 

EXPLAIN ANALYZE
SELECT ...;  -- Our hybrid search query
Enter fullscreen mode Exit fullscreen mode

Key Plan Output 

Limit  (cost=789.66..789.69 rows=10 width=365) (actual time=8.516..8.519 rows=10 loops=1)
  ->  Sort  (cost=789.66..789.86 rows=80 width=365) (actual time=8.515..8.518 rows=10 loops=1)
        Sort Key: (sum(COALESCE((1.0 / (("*SELECT* 1".rank + 50))::numeric), 0.0))) DESC
        Sort Method: top-N heapsort  Memory: 32kB
        ->  GroupAggregate  (cost=785.53..787.93 rows=80 width=365) (actual time=8.435..8.495 rows=79 loops=1)
              Group Key: "*SELECT* 1".id, "*SELECT* 1".description
              ->  Sort  (cost=785.53..785.73 rows=80 width=341) (actual time=8.430..8.436 rows=80 loops=1)
                    ->  Append  (cost=84.60..783.00 rows=80 width=341) (actual time=0.877..8.414 rows=80 loops=1)
                          ->  Subquery Scan on "*SELECT* 1"  
                                ->  Limit  
                                      ->  WindowAgg  
                                            ->  Index Scan using products_embeddings_hnsw_idx on products  
                                                  Order By: (embedding <=> '<redacted>'::vector)
                          ->  Subquery Scan on "*SELECT* 2"  
                                ->  Limit  
                                      ->  Sort  
                                            ->  WindowAgg  
                                                  ->  Sort  
                                                        ->  Bitmap Heap Scan on products products_1  
                                                              Recheck Cond: ('''travel'' & ''comput'''::tsquery @@ ...)
                                                              ->  Bitmap Index Scan on products_description_gin_idx  
                                                                    Index Cond: (to_tsvector('english'::regconfig, description) @@ ...)

Planning Time: 0.193 ms
Execution Time: 8.553 ms
Enter fullscreen mode Exit fullscreen mode

Performance Breakdown

Component Time Notes
Vector search (HNSW) ~0.9ms Extremely fast with index
Full-text search (GIN) ~7.3ms Bitmap heap scan overhead
Sort + Group + Aggregate ~1.1ms Small result set
Total 8.5ms Excellent for 50K rows

Index Usage Confirmation

The plan confirms both indexes are utilized:

  1. Index Scan using products_embeddings_hnsw_idx — HNSW vector index
  2. Bitmap Index Scan on products_description_gin_idx — GIN full-text index

Scaling Considerations

For production workloads with millions of rows:

Factor Impact Mitigation
HNSW build time Increases linearly Build offline, use maintenance_work_mem
GIN index size ~30% of text size Consider partial indexes
Query latency Sub-linear with HNSW Tune ef_search
Memory HNSW graph in RAM Monitor shared_buffers

11. Production Considerations and Trade-offs {#production-considerations}

When to Use Hybrid Search

Use Case Recommendation
RAG pipelines ✅ Strongly recommended
E-commerce search ✅ Recommended
Document retrieval ✅ Recommended
Real-time autocomplete ⚠️ Consider latency
Simple keyword search ❌ Overkill

Tuning Parameters

HNSW Parameters 

-- Query-time parameter (higher = better recall, slower)
SET hnsw.ef_search = 100;

-- Index-time parameters (require rebuild)
-- m: connections per node (default 16)
-- ef_construction: candidate list size during build (default 64)
Enter fullscreen mode Exit fullscreen mode

Full-Text Search Parameters 

-- Use different dictionaries
to_tsvector('simple', description)  -- No stemming
to_tsvector('english', description) -- English stemming

-- Custom dictionaries for domain-specific terms
CREATE TEXT SEARCH DICTIONARY custom_dict (...);
Enter fullscreen mode Exit fullscreen mode

RRF Parameters 

-- k=50 (default): Balanced
-- k=10: Favor top-ranked results more
-- k=100: Flatter score distribution

SELECT sum(rrf_score(rank, 10)) AS score  -- More aggressive
Enter fullscreen mode Exit fullscreen mode

Memory and Storage

Component Storage (50K rows) Storage (1M rows)
Raw text ~5MB ~100MB
Vector embeddings (384d) ~75MB ~1.5GB
HNSW index ~100MB ~2GB
GIN index ~2MB ~40MB

Hybrid Search vs. Alternatives

Approach Pros Cons
Hybrid (RRF) Simple, effective, no training Requires tuning
Learning to Rank Optimal if trained well Needs labeled data
Weighted Sum Simple Requires score normalization
Cascade Fast May miss results

12. Next Steps and Future Research {#next-steps}

Immediate Improvements

  1. Add reranking: Use a cross-encoder model to rerank top results
  2. Query expansion: Expand queries with synonyms before search
  3. Metadata filtering: Combine with structured filters
  4. Caching: Cache frequent query embeddings

Research Directions

  1. Benchmark on ground-truth datasets: Measure recall@k improvements
  2. Compare FTS algorithms: ts_rank vs ts_rank_cd vs custom
  3. Optimize RRF parameters: Grid search for optimal k
  4. Multi-vector approaches: ColBERT-style late interaction

Production Deployment Checklist 

□ Set up connection pooling (PgBouncer)
□ Configure maintenance_work_mem for index builds
□ Set up monitoring for query latency
□ Implement query result caching
□ Create partial indexes for common filters
□ Set up replication for read scaling
□ Document tuning parameters
□ Create runbooks for common issues
Enter fullscreen mode Exit fullscreen mode

Complete Python Implementation 

import psycopg
from pgvector.psycopg import register_vector
from sentence_transformers import SentenceTransformer

class HybridSearch:
    def __init__(self, connection_string: str, model_name: str = 'multi-qa-MiniLM-L6-cos-v1'):
        self.conn = psycopg.connect(connection_string)
        register_vector(self.conn)
        self.model = SentenceTransformer(model_name)

    def search(self, query: str, limit: int = 10, subquery_limit: int = 40) -> list[dict]:
        # Generate query embedding
        embedding = self.model.encode(query)

        # Execute hybrid search
        with self.conn.cursor() as cur:
            cur.execute("""
                SELECT
                    searches.id,
                    searches.description,
                    sum(rrf_score(searches.rank)) AS score
                FROM (
                    (
                        SELECT id, description,
                               rank() OVER (ORDER BY %s <=> embedding) AS rank
                        FROM products
                        ORDER BY %s <=> embedding
                        LIMIT %s
                    )
                    UNION ALL
                    (
                        SELECT id, description,
                               rank() OVER (
                                   ORDER BY ts_rank_cd(
                                       to_tsvector(description),
                                       plainto_tsquery(%s)
                                   ) DESC
                               ) AS rank
                        FROM products
                        WHERE plainto_tsquery('english', %s) @@ 
                              to_tsvector('english', description)
                        ORDER BY rank
                        LIMIT %s
                    )
                ) searches
                GROUP BY searches.id, searches.description
                ORDER BY score DESC
                LIMIT %s
            """, (embedding, embedding, subquery_limit, query, query, subquery_limit, limit))

            results = cur.fetchall()

        return [
            {"id": r[0], "description": r[1], "score": float(r[2])}
            for r in results
        ]

    def close(self):
        self.conn.close()


# Usage
searcher = HybridSearch("postgresql://user:pass@localhost/dbname")
results = searcher.search("travel computer")
for r in results:
    print(f"[{r['score']:.4f}] {r['description'][:80]}...")
searcher.close()
Enter fullscreen mode Exit fullscreen mode

Conclusion: The Hybrid Advantage

Hybrid search represents a pragmatic evolution in retrieval systems. By combining:

  • Vector search's semantic understanding with
  • Full-text search's lexical precision using
  • Reciprocal Rank Fusion's elegant aggregation

...we achieve retrieval quality that exceeds either method alone.

The PostgreSQL implementation demonstrated here is:

  • Production-ready: Uses battle-tested indexes and query patterns
  • Performant: Sub-10ms queries on 50K rows
  • Scalable: HNSW and GIN indexes handle millions of rows
  • Maintainable: Single database, no external services
  • Cost-effective: No vector database licensing fees

As RAG systems become more prevalent, hybrid search will transition from "nice to have" to "table stakes" for serious AI applications. The techniques shown here provide a solid foundation for building these systems on PostgreSQL—a database you likely already know and trust.

Resources

Top comments (0)