Hybrid Search Without Elasticsearch: The Complete Guide to pg_search + pgvector

For a while, there was a period when it was impossible to escape the formula "search = Elasticsearch." With every new project, spinning up an additional EC2 cluster next to RDS, writing ETL pipelines whenever data changed, and managing security policies in two places felt like the natural way of things. But starting in late 2025, the mood in the community shifted. TigerData published It's 2026, Just Use Postgres, and ParadeDB announced pg_search V2, marking the full emergence of the trend that all three pillars of Elasticsearch — BM25, vector search, and RRF — have been natively implemented as PostgreSQL extensions.

This post covers how to implement hybrid search on a single PostgreSQL stack by combining two extensions: pg_search and pgvector. The core idea is expressing in a single SQL statement the structure where vectors catch synonyms that keyword search misses, and BM25 catches proper nouns that vector search gets wrong. Reports from TREC-COVID and BEIR-style benchmarks support this approach, showing roughly a 15–25 percentage point improvement in nDCG@10 over pure vector search (ParadeDB — Hybrid Search Guide, Instaclustr — pgvector Hybrid Search).

Core Concepts

BM25: The Algorithm Elasticsearch Uses, Now in PostgreSQL

BM25 (Best Matching 25) is a probability-based ranking algorithm that builds on TF-IDF. Because it jointly considers term frequency (TF), inverse document frequency (IDF), and document length normalization, it does not treat "a keyword appearing once in a short document" the same as "appearing ten times in a long document." It is the same algorithm Elasticsearch adopts as its default ranking algorithm.

pg_search is a PostgreSQL extension built by ParadeDB on top of Tantivy — a Rust-based full-text search library that borrows Lucene's design — via pgrx. It creates a BM25 index with CREATE INDEX ... USING bm25 syntax and queries it with the @@@ operator. According to Neon benchmarks, indexing one million rows is approximately 50 seconds faster than native PostgreSQL tsvector, and ranking speed is roughly 20 times faster (Neon — Postgres FTS vs Elasticsearch vs pg_search).

-- Install extensions
CREATE EXTENSION pg_search;
CREATE EXTENSION vector;
 
-- Example products table
CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        TEXT,
    description TEXT,
    category    TEXT,
    embedding   vector(1536)  -- Based on OpenAI text-embedding-3-small
);
 
-- Create BM25 index
-- key_field must be set to the unique identifier column
CREATE INDEX products_search_idx ON products
USING bm25 (id, name, description, category)
WITH (key_field = 'id');

key_field must be the column that serves as the table's primary key, and you must explicitly list the text columns to include in the BM25 index. This is easy to miss on first use.

pgvector: The Engine for Semantic Search

pgvector adds the ability to store high-dimensional vectors and compute similarity scores in PostgreSQL. It provides three operators — cosine similarity (<=>), L2 distance (<->), and inner product (<#>) — and is used for semantic search to find documents that are meaningfully close.

-- Create HNSW index (based on cosine similarity)
CREATE INDEX products_embedding_idx ON products
USING hnsw (embedding vector_cosine_ops);

Converting text into vectors is the job of an embedding model. OpenAI's text-embedding-3-small is widely used for general-purpose English embeddings. For multilingual environments including Korean, BAAI/BGE-M3 (supporting over 1,000 languages) or Cohere Embed v4 are good choices.

Why You Need Both

For example, when searching for "contract termination clause," BM25 ranks documents that contain exactly "contract," "termination," and "clause" near the top. However, it misses different expressions with the same meaning, such as "agreement cancellation provision." Conversely, vector search is good at finding semantically similar documents, but can produce irrelevant results for keywords where exact matching matters more than meaning — such as product codes like "SKU-12345" or acronyms like "IMF." Using both together lets them compensate for each other's weaknesses.

RRF: Merging Different Scores into a Single Ranking

BM25 scores and cosine similarity are on completely different scales. BM25 is positive but unbounded, while cosine similarity ranges from -1 to 1. Simply adding these two scores together would always let the one with the larger scale win.

RRF (Reciprocal Rank Fusion) avoids this problem by using ranks rather than scores. Each document's RRF score is computed as 1 / (k + rank) (k is typically 60), and the scores from both approaches are summed. Since only rank is reflected — the top document gets 1/61 ≈ 0.0164, second gets 1/62 ≈ 0.0161, tenth gets 1/70 ≈ 0.0143 — no scale normalization is needed (ParadeDB — What is Reciprocal Rank Fusion?).

RRF score = 1/(60 + BM25_rank) + 1/(60 + vector_rank)

k=60 may seem like a magic number, but it serves to suppress the contribution of lower-ranked documents. Lowering k concentrates weight on top ranks; raising it gives more contribution to lower ranks.

Practical Application

Basic Hybrid Search Pattern

Let's start with the overall flow.

The core is the RRF pattern using CTEs (Common Table Expressions) and FULL OUTER JOIN.

-- The query embedding is generated in the application layer and passed as a parameter
-- Function names are based on pg_search 0.15+. Newer versions use paradedb.score(id),
-- so always check the official docs for the version you are using.
-- https://docs.paradedb.com/documentation/guides/hybrid
WITH bm25_results AS (
    SELECT
        id,
        ROW_NUMBER() OVER (ORDER BY paradedb.score_bm25(id) DESC) AS bm25_rank
    FROM products
    WHERE description @@@ 'wireless headphones'
    ORDER BY paradedb.score_bm25(id) DESC
    LIMIT 50
),
vector_results AS (
    SELECT
        id,
        ROW_NUMBER() OVER (ORDER BY embedding <=> $1) AS vec_rank
    FROM products
    ORDER BY embedding <=> $1
    LIMIT 50
)
SELECT
    COALESCE(b.id, v.id)                          AS id,
    COALESCE(1.0 / (60 + b.bm25_rank), 0) +
    COALESCE(1.0 / (60 + v.vec_rank), 0)          AS rrf_score
FROM bm25_results b
FULL OUTER JOIN vector_results v ON b.id = v.id
ORDER BY rrf_score DESC
LIMIT 10;

The query embedding vector goes in place of $1. The reason for using FULL OUTER JOIN is to include documents that appeared in one search but not the other. COALESCE(..., 0) treats the score of documents absent from one result set as 0. If the ORDER BY in the bm25_results CTE is omitted, LIMIT 50 will silently pull an arbitrary 50 rows and RRF will be computed against the wrong candidate set, so it must always be specified explicitly.

E-Commerce Product Search

When searching for "blue running shoes," BM25 catches products whose name or category contains exactly "running shoes," while vector search also surfaces products described as "jogging shoes" or "sports sneakers." A major advantage here is that standard PostgreSQL WHERE clauses can be combined naturally.

-- Hybrid search restricted to in-stock items priced at or below 50,000 KRW
WITH bm25_results AS (
    SELECT
        id,
        ROW_NUMBER() OVER (ORDER BY paradedb.score_bm25(id) DESC) AS bm25_rank
    FROM products
    WHERE description @@@ 'running shoes'
      AND stock > 0
      AND price <= 50000
    ORDER BY paradedb.score_bm25(id) DESC
    LIMIT 50
),
vector_results AS (
    SELECT
        id,
        ROW_NUMBER() OVER (ORDER BY embedding <=> $1) AS vec_rank
    FROM products
    WHERE stock > 0
      AND price <= 50000
    ORDER BY embedding <=> $1
    LIMIT 50
)
SELECT
    p.id,
    p.name,
    p.price,
    ranked.rrf_score
FROM (
    SELECT COALESCE(b.id, v.id) AS id,
           COALESCE(1.0 / (60 + b.bm25_rank), 0) +
           COALESCE(1.0 / (60 + v.vec_rank), 0) AS rrf_score
    FROM bm25_results b
    FULL OUTER JOIN vector_results v ON b.id = v.id
) ranked
JOIN products p ON p.id = ranked.id
ORDER BY ranked.rrf_score DESC
LIMIT 20;

With Elasticsearch you would need a separate filter query DSL, but here a standard WHERE clause does the job. PostgreSQL's Row-Level Security is also applied automatically, so there is no need to implement per-user access control in a separate layer.

RAG-Based Internal Document Search

Hybrid search is especially useful when building a RAG pipeline in an LLM application. You can handle everything with PostgreSQL alone, without dedicated vector databases like Pinecone or Weaviate.

import openai
import psycopg2
from pgvector.psycopg2 import register_vector
 
def hybrid_search(query: str, conn, top_k: int = 5) -> list[dict]:
    # Register once per connection to bind list/np.ndarray directly.
    register_vector(conn)
 
    # 1. Generate query embedding
    response = openai.embeddings.create(
        model="text-embedding-3-small",
        input=query
    )
    query_embedding = response.data[0].embedding  # list[float]
 
    # 2. Run hybrid search (based on pg_search 0.15+)
    sql = """
    WITH bm25_results AS (
        SELECT
            id,
            ROW_NUMBER() OVER (ORDER BY paradedb.score_bm25(id) DESC) AS bm25_rank
        FROM documents
        WHERE content @@@ %s
        ORDER BY paradedb.score_bm25(id) DESC
        LIMIT 50
    ),
    vector_results AS (
        SELECT
            id,
            ROW_NUMBER() OVER (ORDER BY embedding <=> %s) AS vec_rank
        FROM documents
        ORDER BY embedding <=> %s
        LIMIT 50
    )
    SELECT
        d.id,
        d.title,
        d.content,
        ranked.rrf_score
    FROM (
        SELECT
            COALESCE(b.id, v.id) AS id,
            COALESCE(1.0 / (60 + b.bm25_rank), 0) +
            COALESCE(1.0 / (60 + v.vec_rank), 0) AS rrf_score
        FROM bm25_results b
        FULL OUTER JOIN vector_results v ON b.id = v.id
    ) ranked
    JOIN documents d ON d.id = ranked.id
    ORDER BY ranked.rrf_score DESC
    LIMIT %s;
    """
 
    with conn.cursor() as cur:
        cur.execute(sql, (query, query_embedding, query_embedding, top_k))
        rows = cur.fetchall()
 
    return [
        {"id": r[0], "title": r[1], "content": r[2], "score": r[3]}
        for r in rows
    ]

On BEIR-family benchmarks, the hybrid + RRF approach is reported to improve nDCG@10 by approximately 15–25 percentage points over pure vector search (ParadeDB — Hybrid Search Guide). There are also cases where adding a cross-encoder reranker on the top 20 results yields an additional 3–7 percentage points (DEV — Building Hybrid Search for RAG). Note that nDCG@10 and precision@k are different metrics, so the "precision 62%→84%" figure cited in the introduction (Instaclustr) should not be directly compared to nDCG numbers.

Multilingual Content Search

There are things to watch out for when handling Korean or Japanese. BM25 assumes whitespace-based tokenization, but Korean has irregular spacing and requires morpheme segmentation. For instance, "running shoes" written as one word versus two separate words would be processed as different tokens.

In such cases, combining two strategies works well:

1. Preprocessing: Apply morpheme segmentation using kiwi or mecab before storing in PostgreSQL
2. Increase vector weight: Give vector scores a higher weight when merging rankings

-- Multilingual environment: a variant with 2x weight on vector rank (weighted RRF)
-- Note that this is a linear combination approach, not standard RRF.
SELECT
    COALESCE(b.id, v.id)                             AS id,
    1.0 * COALESCE(1.0 / (60 + b.bm25_rank), 0) +
    2.0 * COALESCE(1.0 / (60 + v.vec_rank), 0)      AS score
FROM bm25_results b
FULL OUTER JOIN vector_results v ON b.id = v.id
ORDER BY score DESC
LIMIT 10;

The moment you apply weights, this approach is no longer pure RRF but is closer to weighted RRF or a linear combination. Sharing this distinction accurately within your team will make subsequent tuning easier. BAAI/BGE-M3 is an open-source embedding model supporting over 1,000 languages and works well for scenarios like finding Korean documents with an English query, or vice versa.

Pros and Cons Analysis

Comparison

Common Mistakes in Practice

1. Omitting ORDER BY in the BM25 CTE

If you only have LIMIT 50 without ORDER BY, an arbitrary 50 rows are returned, and while the query appears to run fine, RRF is actually computed against a random candidate set. Because no error is raised and the wrong results come out silently, this is the hardest trap to catch. Always write it as ORDER BY paradedb.score_bm25(id) DESC LIMIT N.

2. Setting the candidate LIMIT too small

If the candidate set for BM25 and vector is too narrow, good documents may not make it in at all after the FULL OUTER JOIN. It is common practice to use 50–100 candidates for each and then pull the top 10–20 as the final result.

3. Hardcoding k=60 unconditionally

k=60 is a widely cited default, but the optimal value varies by domain. It is better to measure search quality metrics such as nDCG and MRR while tuning the k value and weights.

4. Trying to install pg_search on AWS RDS

Standard AWS RDS does not allow custom extension installation. To use pg_search, you need to use a service like Neon or ParadeDB Cloud, or install it directly on an EC2 or Aurora Machine Learning instance. Supabase support must be verified individually through official documentation.

5. Using a Korean BM25 index with no configuration

If tokenization is done only by whitespace, "searchengineoptimization" and "search engine optimization" are recognized as entirely different tokens. For Korean columns, a realistic strategy is to apply morpheme segmentation in advance or to increase the vector search weight for those fields.

6. Running without an HNSW index on the vector column

Performing a vector search in pgvector without an index results in a sequential scan. Query performance degrades noticeably once data exceeds tens of thousands of rows, so always create an HNSW or IVFFlat index.

Operations Checklist

• Is an HNSW or IVFFlat index created on the vector column?
• Is a pg_search BM25 index created on text columns? (Using only the default tsvector degrades ranking quality)

Closing Thoughts

To summarize:

• BM25 handles keyword precision, vectors handle semantic understanding, and they compensate for each other's weaknesses.
• RRF safely sums two scores on different scales using rank-based fusion. Once you apply weights, you must recognize that it is no longer standard RRF but weighted RRF or a linear combination.
• The pg_search + pgvector combination enables hybrid search on a single PostgreSQL stack without Elasticsearch.
• nDCG@10 improves by approximately 15–25 percentage points over pure vector search, and cases of 60–90% infrastructure cost reduction have been reported. (nDCG and precision are different metrics, so it is safer not to directly compare these figures with the "precision" numbers cited in the introduction.)

If you want to get started, here is the recommended sequence:

1. Spin up a PostgreSQL instance with pg_search and pgvector enabled on Neon or ParadeDB Cloud. Setting up the installation environment is the most tedious hurdle; with managed services, the extensions are already ready.
2. Add a BM25 index and an HNSW index to your existing table, and run the CTE + RRF pattern above on a small dataset first. Adjusting k values and weights while looking at the results will help you find the right configuration for your domain.
3. If you are running Elasticsearch, run the same queries on both sides and compare result quality. Measuring metrics like nDCG or MRR lets you decide on migration based on data.

References

• Hybrid Search in PostgreSQL: The Missing Manual — ParadeDB
• ParadeDB Official Docs — Create Index (BM25)
• Hybrid Search — ParadeDB Guide
• The pg_search extension — Neon Docs
• Comparing Native Postgres, ElasticSearch, and pg_search for Full-Text Search — Neon
• Elasticsearch's Hybrid Search, Now in Postgres (BM25 + Vector + RRF) — TigerData
• BM25 in PostgreSQL: Full-Text Search Without Elastic — TigerData
• It's 2026, Just Use Postgres — TigerData
• Hybrid search with PostgreSQL and pgvector — Jonathan Katz
• Building Hybrid Search for RAG: pgvector + RRF — DEV Community
• Hybrid search with Postgres Native BM25 and VectorChord — VectorChord Blog
• What is Reciprocal Rank Fusion? — ParadeDB
• pgvector Hybrid Search: Benefits, Use Cases, and Quick Tutorial — Instaclustr
• Replacing Elasticsearch with Postgres Using BM25 Hybrid Search and RRF — SoftwareSeni
• GitHub — paradedb/paradedb
• GitHub — pgvector/pgvector
• Postgres Vector Search Compared 2026: pgvector vs pgvectorscale vs ParadeDB vs Lantern