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.
Retrieval-Augmented Generation has become the dominant paradigm for building AI applications that need to access external knowledge. The pattern is deceptively simple:
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.
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 |
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 |
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.
Before diving into implementation, let's establish a clear mental model of what each search method actually does.
Vector search converts text into high-dimensional embeddingsβnumerical representations that capture semantic meaning. Similar meanings produce similar vectors, enabling:
The similarity is typically measured using cosine distance, where smaller values indicate greater similarity.
cosine_distance = 1 - cosine_similarity
PostgreSQL's full-text search uses:
The result is a lexeme-based index that enables fast, precise keyword matching.
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 β
βββββββββββββββββββ
To follow along, you'll need:
psycopg (PostgreSQL adapter)pgvector (Python integration)faker (test data generation)sentence_transformers (embedding generation)
brew install pgvector
sudo apt install postgresql-15-pgvector
git clone https://github.com/pgvector/pgvector.git
cd pgvector && make && make install
pip install psycopg[binary] pgvector faker sentence-transformers
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);
$$;
Why 384 dimensions? The multi-qa-MiniLM-L6-cos-v1 model produces 384-dimensional embeddings. This is a deliberate choice balancing:
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
fake = Faker()
sentences = [fake.sentence(nb_words=50) for _ in range(50_000)]
print(f"Generated {len(sentences)} sentences")
print(f"Sample: {sentences[0][:100]}...")
model = SentenceTransformer('multi-qa-MiniLM-L6-cos-v1')
print("Generating embeddings...")
embeddings = model.encode(sentences, show_progress_bar=True)
print(f"Embedding shape: {embeddings.shape}") # Should be (50000, 384)
conn = psycopg.connect(
dbname="your_database",
user="your_user",
password="your_password",
host="localhost",
port="5432",
autocommit=True
)
register_vector(conn)
cur = conn.cursor()
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()
Performance Tip: The COPY command is orders of magnitude faster than individual INSERT statements for bulk . For 50,000 rows, this approach typically completes in seconds rather than minutes.
-- 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);
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:
tsvector column
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:
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
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.
from sentence_transformers import SentenceTransformer
model = SentenceTransformer('multi-qa-MiniLM-L6-cos-v1')
query_embedding = model.encode('travel computer')
js
SELECT
id,
description,
rank() OVER (ORDER BY $1 <=> embedding) AS rank
FROM products
ORDER BY $1 <=> embedding
LIMIT 10;
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
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
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.
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;
plainto_tsquery('english', 'travel computer'): Converts plain text to a tsquery
'travel' & 'comput' (stemmed, AND-connected)
to_tsvector('english', description): Converts document to searchable form
@@ operator: Tests if tsquery matches tsvector
ts_rank_cd(): Cover density ranking
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
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.
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.
RRF_score(d) = Ξ£ (1 / (k + rank_i(d)))
Where:
d = documentk = smoothing constant (typically 50-60)rank_i(d) = rank of document d in result list i
Scale-independent: Combines rankings, not raw scores
Robust: Outliers in one list don't dominate
Simple: No training required
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);
$$;
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
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.
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;
The choice of 40 is strategic:
hnsw.ef_search
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
β 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 β
βββββββββββββββββββββββββ
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
Record 18548 (the "correct" answer):
Records 7372, 12875, etc.:
Records 20763, 20894, 838:
The key insight: Record 18548's appearance in both lists with strong rankings boosted it to the top, validating the hybrid approach.
EXPLAIN ANALYZE
SELECT ...; -- Our hybrid search query
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
| 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 |
The plan confirms both indexes are utilized:
Index Scan using products_embeddings_hnsw_idx β HNSW vector indexBitmap Index Scan on products_description_gin_idx β GIN full-text index
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 |
| Use Case | Recommendation |
|---|---|
| RAG pipelines | β Strongly recommended |
| E-commerce search | β Recommended |
| Document retrieval | β Recommended |
| Real-time autocomplete | β οΈ Consider latency |
| Simple keyword search | β Overkill |
-- 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)
-- 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 (...);
-- 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
| 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 |
| 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 |
ts_rank vs ts_rank_cd vs custom
β‘ 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
python
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]:
embedding = self.model.encode(query)
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()
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()
Hybrid search represents a pragmatic evolution in retrieval systems. By combining:
...we achieve retrieval quality that exceeds either method alone.
The PostgreSQL implementation demonstrated here is:
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.