In my previous blog on the pgEdge Vectorizer and RAG Server, I showed how to build a semantic search pipeline using dense vector embeddings. The RAG Server already did hybrid search, combining vector similarity with BM25 keyword matching, but BM25 was handled at the application layer, not inside PostgreSQL.

That changes now. The pgedge-vectorizer extension adds BM25 sparse vector generation directly into the vectorizer, running inside PostgreSQL alongside the dense embeddings. Results are fused using reciprocal rank fusion (RRF). In this blog I will show you what this means in practice.

I am using the same Rocky Linux ARM64 VM and testdb configuration from the previous blog: two pgEdge nodes, n1 on port 5432 and n2 on port 5433, using Ollama with nomic-embed-text. If you have not read that blog yet, start there first.

Why Hybrid Search?

To understand what this feature does, it helps to understand the three pieces behind it.

Dense Vector Search

When you run a semantic search, the extension converts your query and data into a list of numbers called a vector. These numbers capture meaning. Data that matches your query ends up with similar numbers, even if the words are completely different.

That is why a query like "how do I get back into my account?" finds an article titled "Account Recovery" even though the query uses none of the same phrasing. The embedding model understands that getting back into your account and account recovery describe the same thing.

Dense search is powerful for conceptual queries. Where it struggles is exact technical terms. Search for "wal_level = logical" and the model may rank a general replication article above the one that specifically explains that parameter, because it treats all replication content as semantically similar.

BM25

BM25 takes the opposite approach. It does not understand the meaning at all. It counts words.

Specifically it scores documents based on two things: how often your query terms appear in a document, and how rare those terms are across your entire knowledge base. A common word like "the" gets almost no weight. A rare technical term like "wal_level" that appears in only a handful of documents becomes a very strong signal when it shows up in both your query and a document.

BM25 is the ranking algorithm behind Elasticsearch and Apache Solr, and has been a standard baseline in information retrieval for decades. It is extremely good at exact term matching. Ask it a conceptual question and it fails completely. Ask it for a specific parameter name and it finds it reliably.

Reciprocal Rank Fusion

After you run both, you have two ranked lists. The same article might be ranked #1 by dense search and #3 by BM25. Another article might be #2 in dense and #1 in BM25.

RRF combines these lists without trying to normalize the raw scores, which are in completely different units and scales. Instead it only looks at positions. Each article gets a score based on where it appears in each list, and those scores are added together.

The result is simple: an article that both methods independently ranked near the top wins. An article that only one method found still has a chance, just a lower one. No complex weighting, no tuning required to get started.

How Hybrid Search Brings This Together

Think of dense search and BM25 as two colleagues ranking the same set of articles for you. The first understands meaning but sometimes misses exact terminology. The second is a keyword expert with no sense of meaning at all. RRF says: if both searches independently rank the same article highly, that article is almost certainly what you need.

For PostgreSQL documentation and technical knowledge bases, where users mix conceptual questions with precise terminology like "spock apply worker" or "LSN lag", this combination works really well. Conceptual queries go to the dense colleague. Exact term queries go to the keyword colleague. Most real queries get help from both.

The practical value of doing this inside PostgreSQL is that you do not maintain two separate systems. The background worker computes both the dense embedding and the BM25 sparse vector for every chunk, keeps them in sync as your data changes, and the search function handles the fusion. You write one query and get the benefit of both.

Installation

Installation is straightforward - clone the repo and build.

One thing to watch out for on Rocky Linux: if you have a system PostgreSQL installed, its pg_config ends up first on PATH - you need to point make at the pgEdge PATH explicitly.

export PATH=/home/pgedge/projects/pgedge/n1/pgedge/pg16/bin:$PATH
git clone https://github.com/pgEdge/pgedge-vectorizer.git
cd pgedge-vectorizer
make

sudo also resets PATH, so pass it through explicitly when installing:

# Install for n1
sudo env PATH=$PATH make install
# Install for n2
sudo env PATH=$PATH make install PG_CONFIG=/home/pgedge/projects/pgedge/n2/pgedge/pg16/bin/pg_config

Then create the extension in testdb on each node:

CREATE EXTENSION IF NOT EXISTS vector; 

CREATE EXTENSION IF NOT EXISTS pgedge_vectorizer;

Configuration

Add pgedge_vectorizer to shared_preload_libraries on both nodes:

shared_preload_libraries = 'pg_stat_statements, snowflake, spock, pgedge_vectorizer'

Then add the vectorizer settings. The databases and provider lines are required; without them the extension will not do anything useful.

# Required
pgedge_vectorizer.databases = 'testdb'
pgedge_vectorizer.provider  = 'ollama'
pgedge_vectorizer.model     = 'nomic-embed-text'
pgedge_vectorizer.api_url   = 'http://localhost:11434'

# Enable hybrid search
pgedge_vectorizer.enable_hybrid = true
# Optional BM25 tuning -- defaults work well for most cases
pgedge_vectorizer.bm25_k1 = 1.2   # term-frequency saturation (0.0-3.0)
pgedge_vectorizer.bm25_b  = 0.75  # length normalization (0.0-1.0)

Because shared_preload_libraries changed, restart both nodes:

pg_ctl restart -D /path/to/n1/data/pg16
pg_ctl restart -D /path/to/n2/data/pg16

What Changes in the Chunk Table

I am using the same kb_articles table we used in the previous blog. After enabling hybrid mode and running enable_vectorization(), the chunk table now has a sparse_embedding column alongside the dense one:

\d kb_articles_content_chunks

 Column           | Type        | Description
 -----------------+-------------+---------------------------
 id               | bigint      | chunk primary key
 source_id        | bigint      | FK to kb_articles.id
 chunk_index      | integer     | position within document
 content          | text        | the chunk text
 embedding        | vector(768) | dense semantic embedding
 sparse_embedding | sparsevec   | BM25 sparse representation

A companion kb_articles_content_chunks_idf_stats table is created automatically to track per-term IDF weights. It updates automatically as new chunks arrive; you do not need to manage it directly.

Seeing the Difference

I will add three articles with specific technical terms, exactly the kind of content where dense-only search can struggle:

INSERT INTO kb_articles (title, content, category) VALUES
  ('Replication Lag Troubleshooting',
   'If your standby is falling behind, check pg_replication_slots for
    stuck slots. High replication lag is often caused by long-running
    transactions on the primary or a slow apply worker. Use
    pg_stat_replication to monitor sent_lsn vs replay_lsn.', 'replication'),
  ('Configuring wal_level for Logical Replication',
   'To enable logical replication, set wal_level = logical in
    postgresql.conf. This is required before creating a publication.
    After changing wal_level you must restart PostgreSQL.', 'configuration'),
  ('Spock Apply Worker Overload',
   'When the spock apply worker falls behind, you may see LSN skipping
    in the logs. Check spock.subscription and pg_replication_slots.
    Increasing num_apply_workers can help under heavy write loads.', 'replication');

Give the background workers a moment to process the embeddings, then run the tests.

Test 1: Semantic Query

Now, let's test the search with a question where the user and document use completely different words:

SELECT a.title,
       ROUND((c.embedding <=> pgedge_vectorizer.generate_embedding(
           'my cluster is falling behind'))::numeric, 4) AS distance
FROM kb_articles_content_chunks c
JOIN kb_articles a ON a.id = c.source_id
ORDER BY distance LIMIT 3;
 title                                | distance
 -------------------------------------+----------
 Spock Apply Worker Overload          |   0.4571
 Replication Lag Troubleshooting      |   0.4590
 Configuring wal_level for Logical... |   0.6026

Dense search handles this well. "Falling behind" is semantically close to both replication lag and apply worker overload, even though the words are completely different.

Test 2: Exact Technical Term

Now search for a specific configuration parameter:

SELECT a.title,
       ROUND((c.embedding <=> pgedge_vectorizer.generate_embedding(
           'wal_level = logical'))::numeric, 4) AS distance
FROM kb_articles_content_chunks c
JOIN kb_articles a ON a.id = c.source_id
ORDER BY distance LIMIT 3;
 title                                | distance
 -------------------------------------+----------
 Configuring wal_level for Logical... |   0.2377
 Replication Lag Troubleshooting      |   0.4892
 Spock Apply Worker Overload          |   0.5017

With nomic-embed-text the right article ranks first here too. Now run the same query with hybrid search and look at what the two signals are doing:

SELECT a.title, h.dense_rank, h.sparse_rank,
       ROUND(h.rrf_score::numeric, 4) AS rrf_score
FROM pgedge_vectorizer.hybrid_search(
    p_source_table := 'kb_articles'::regclass,
    p_query        := 'wal_level = logical',
    p_limit        := 3
) h
JOIN kb_articles a ON a.id::text = h.source_id
ORDER BY rrf_score DESC;
 title                                | dense_rank | sparse_rank | rrf_score
 -------------------------------------+------------+-------------+----------
 Configuring wal_level for Logical... |     1      |      2      |   0.0163
 Replication Lag Troubleshooting      |     2      |      3      |   0.0161
 Spock Apply Worker Overload          |     3      |      1      |   0.0160

The right article stays at the top. With p_alpha at the default 0.7, the dense signal carries more weight, which is why the dense ranking drives the final order. The dense_rank and sparse_rank columns show you what each signal is doing independently, which is useful for debugging: if the right article is not coming up, check whether its sparse_rank is unexpectedly low and whether your query terms actually appear in the document text. Note that sparse_rank values are most meaningful on larger corpora where term frequencies have stabilized across many documents. On a small dataset the IDF weights are still developing and rankings between low-scoring documents can be non-intuitive.

Test 3: Mixed Query

Most real queries mix conceptual intent with specific terminology. This one does both: LSN is a precise PostgreSQL term (Log Sequence Number) that BM25 will match exactly, while "spock apply worker is overloaded" describes a concept that dense search understands semantically. Neither signal alone would rank this as well as the two combined.

SELECT a.title, h.dense_rank, h.sparse_rank,
       ROUND(h.rrf_score::numeric, 4) AS rrf_score
FROM pgedge_vectorizer.hybrid_search(
    p_source_table := 'kb_articles'::regclass,
    p_query        := 'LSN skipping when spock apply worker is overloaded',
    p_limit        := 3
) h
JOIN kb_articles a ON a.id::text = h.source_id
ORDER BY rrf_score DESC;
 title                                | dense_rank | sparse_rank | rrf_score
 -------------------------------------+------------+-------------+----------
 Spock Apply Worker Overload          |     1      |      1      |   0.0164
 Replication Lag Troubleshooting      |     2      |      3      |   0.0161
 Configuring wal_level for Logical... |     3      |      2      |   0.0159

When dense and sparse rank the same article first, you can be fairly confident it is the right one.

Tuning the Balance

When hybrid search combines the two ranked lists, it has to decide how much to trust each one. You can tip that balance in your favor with p_alpha.

  • p_alpha = 1.0 means trust dense search completely and ignore BM25.

  • p_alpha = 0.0 means trust BM25 completely and ignore dense search.

The default value of p_alpha = 0.7 leans toward dense search but still lets BM25 influence the result.

For a general help center where users ask conceptual questions like "how do I cancel my subscription" or "why is my account locked", the default 0.7 works well. For a PostgreSQL documentation search where users frequently type exact parameter names or function signatures, shifting p_alpha to 0.3 or 0.4 gives BM25 more weight and surfaces for more reliable exact matches.

You do not need to set this globally. You can pass it per query. If your application can detect that a query looks like an exact technical term, say it contains underscores or looks like a config parameter, you can shift p_alpha down for just that query:

FROM pgedge_vectorizer.hybrid_search(
    p_source_table := 'kb_articles'::regclass,
    p_query        := 'wal_level = logical',
    p_limit        := 3,
    p_alpha        := 0.3   -- shift toward keyword matching

For most use cases, start with the default and only adjust once you have real queries to test against.

Running on Node 2

A few things to know about running this on a pgEdge cluster.

enable_vectorization() needs to be run on each node to register the background workers and triggers locally. The chunk table and IDF stats table replicate to the other node automatically via Spock, and the chunk table is added to the replication set so embeddings replicate too. When a new article is inserted on n1, the background workers on n1 generate the embedding, write it to the chunk table, and Spock replicates that chunk to n2. The same happens in reverse for inserts on n2. Both nodes always have the same embeddings and can serve hybrid search queries independently. If n1 goes down, n2 continues answering queries without interruption.

Spock bypasses triggers on rows arriving via replication to avoid processing them twice. This is why reprocess_chunks() is needed after running enable_vectorization() on n2: any rows that already existed on n2 before the chunk trigger was created will not have embeddings yet. Running reprocess_chunks() queues them for processing. For all rows inserted after enable_vectorization() runs on both nodes, everything works automatically.

SELECT pgedge_vectorizer.reprocess_chunks('kb_articles_content_chunks');

Once that completes, hybrid search works identically on n2:

 title                                | dense_rank | sparse_rank | rrf_score
 -------------------------------------+------------+-------------+----------
 Configuring wal_level for Logical... |     1      |      2      |   0.0163
 Replication Lag Troubleshooting      |     2      |      3      |   0.0161
 Spock Apply Worker Overload          |     3      |      1      |   0.0160

Wrapping Up

One Grand Unified Scheme (GUC) variable to enable it, no changes to enable_vectorization(), and the background workers handle the rest. The three tests above show where it makes a real difference: exact configuration parameters, technical keywords, and mixed queries that combine conceptual intent with precise terminology. On a pgEdge cluster, embeddings generated on either node replicate automatically to the other via Spock, giving you full active-active search availability with no extra steps.

The dense_rank and sparse_rank columns in the output are worth watching - they will tell you which signal drove each result, which is useful when you are tuning or debugging search quality.

Ahsan Hadi is a Database Evangelist at pgEdge with 20+ years of database experience, 15+ of those with PostgreSQL.