Hybrid search Innovation Release

Suggest edits

Hybrid search combines semantic (vector/embedding) search on knowledge base embeddings with one or more additional filter mechanisms:

  • Relational predicate filtering — filter results by any column in the source table, for example WHERE category = 'vegetarian' or WHERE published_date > '2024-01-01'.

  • BM25 full-text search — rank results by keyword relevance using a BM25 index, ensuring that specific words or phrases appear in the results.

These two approaches can be used independently or together alongside semantic search.

AI pipelines handle knowledge base construction — including embedding generation — as normal. Hybrid search is applied at query time by constructing custom SQL that combines the vector table with your source table.

Prerequisites

pgvector iterative index scans

Requires pgvector 0.8.0 or later (EDB Postgres AI defaults to 0.8.1).

Iterative index scans are the key enabler for combining vector ORDER BY with WHERE filters efficiently. Without them, the vector index pre-fetches a fixed number of candidates before filtering, which means a highly selective filter may return fewer results than your LIMIT requests. With iterative scans enabled, the index keeps producing candidates until your LIMIT is satisfied, even after filtering.

Enable iterative scans for HNSW indexes:

SET hnsw.iterative_scan = relaxed_order;

For IVFFlat indexes:

SET ivfflat.iterative_scan = on;

For very selective filters (few rows match), set a safety limit to prevent the scan from traversing the entire index:

SET hnsw.iterative_scan_max_tuples = 20000;
Important

The vector ORDER BY and the WHERE filter must be in the same query level — not separated by a CTE boundary — for iterative scans to take effect. The query patterns below are structured accordingly.

BM25 (optional)

BM25 full-text filtering requires the vchord_bm25 and pg_tokenizer extensions. These are user-installed and managed. See VectorChord-BM25 for setup details.

Helper functions and metadata

aidb.kb_encode()

Encodes a text query using the embedding model configured for a specific knowledge base, without needing to look up the model name:

SELECT aidb.kb_encode('my_kb', 'summer fashion');

Use this in place of aidb.encode_text('<model>', '<query>') when constructing custom vector queries.

aidb.knowledge_bases view

The aidb.knowledge_bases view (also accessible as aidb.kbs) exposes all the metadata needed to construct hybrid queries:

ColumnDescription
nameKnowledge base name
kb_tableFully qualified vector table name
vector_columnColumn storing the embeddings (value)
key_columnColumn linking embeddings to source rows (source_id)
model_nameEmbedding model used by this KB
distance_operatorDistance metric enum value
distance_operator_sqlSQL operator string (<=>, <->, <#>, <+>)
SELECT name, kb_table, distance_operator_sql
FROM aidb.knowledge_bases;

aidb.generate_retrieval_query()

Returns example SQL queries tailored to a specific knowledge base, showing vector-only, vector-with-source-join, and vector-with-filter patterns based on the KB's actual metadata:

SELECT query_type, example_sql
FROM aidb.generate_retrieval_query('my_kb');

Returns 2–3 rows with query_type labels (vector_search, vector_with_source, vector_filtered) and copy-ready SQL you can adapt.

aidb.rerank_text()

After retrieving candidates, pass them to a reranking model for a final relevance-ordered result:

SELECT aidb.rerank_text('my_reranking_model', 'query text', ARRAY['result1', 'result2', ...]);

Reranking models such as nim_reranking and hf_tei_reranking are supported as a separate post-processing step after retrieval.

Query patterns

Vector + relational filter

Filter semantic search results by any column in the source table. The filter is applied at the same query level as the vector ORDER BY, allowing iterative index scans to produce exactly LIMIT results.

SET hnsw.iterative_scan = relaxed_order;

SELECT s.id, s.description,
       kb.value <=> aidb.kb_encode('my_kb', 'summer fashion') AS distance
FROM my_kb_vector kb
JOIN my_source_table s ON s.id::TEXT = kb.source_id
WHERE s.category_id = 2                          -- relational filter on source table
ORDER BY kb.value <=> aidb.kb_encode('my_kb', 'summer fashion')
LIMIT 10;

Vector + BM25 as a filter

Use a BM25 score threshold as a WHERE condition, then order results by vector distance. This returns the semantically nearest results that also satisfy the keyword match.

SET hnsw.iterative_scan = relaxed_order;

SELECT s.id, s.description,
       kb.value <=> aidb.kb_encode('my_kb', 'summer fashion') AS distance
FROM my_kb_vector kb
JOIN my_source_table s ON s.id::TEXT = kb.source_id
WHERE s.bm25_col <&> to_bm25query('my_bm25_idx', tokenize('shoes', 'bert')) < -1.0  -- BM25 filter
ORDER BY kb.value <=> aidb.kb_encode('my_kb', 'summer fashion')
LIMIT 10;

Vector + BM25 via Reciprocal Rank Fusion

Reciprocal Rank Fusion (RRF) runs semantic and keyword searches independently, then merges and scores the result sets. This avoids over-filtering and surfaces results that rank well in either dimension.

WITH semantic AS (
    SELECT source_id AS id,
           ROW_NUMBER() OVER (ORDER BY value <=> aidb.kb_encode('my_kb', 'shoes')) AS rank
    FROM my_kb_vector
    ORDER BY value <=> aidb.kb_encode('my_kb', 'shoes')
    LIMIT 50
),
fulltext AS (
    SELECT id::TEXT AS id,
           ROW_NUMBER() OVER (ORDER BY bm25_col <&> to_bm25query('my_bm25_idx', tokenize('shoes', 'bert'))) AS rank
    FROM my_source_table
    ORDER BY bm25_col <&> to_bm25query('my_bm25_idx', tokenize('shoes', 'bert'))
    LIMIT 50
)
SELECT COALESCE(s.id, f.id) AS id,
       COALESCE(1.0 / (60 + s.rank), 0) + COALESCE(1.0 / (60 + f.rank), 0) AS rrf_score
FROM semantic s
FULL OUTER JOIN fulltext f ON s.id = f.id
ORDER BY rrf_score DESC
LIMIT 10;

The constant 60 in the RRF formula is the standard rank smoothing factor. Adjust it to control the relative weight of high-rank vs lower-rank results.

Vector + relational + BM25 (all three)

Combine all three filtering mechanisms in a single query:

SET hnsw.iterative_scan = relaxed_order;

SELECT s.id, s.description,
       kb.value <=> aidb.kb_encode('my_kb', 'summer fashion') AS distance,
       s.bm25_col <&> to_bm25query('my_bm25_idx', tokenize('shoes', 'bert')) AS bm25_score
FROM my_kb_vector kb
JOIN my_source_table s ON s.id::TEXT = kb.source_id
WHERE s.category_id = 2                                                                    -- relational filter
  AND s.bm25_col <&> to_bm25query('my_bm25_idx', tokenize('shoes', 'bert')) < -1.0        -- BM25 filter
ORDER BY kb.value <=> aidb.kb_encode('my_kb', 'summer fashion')
LIMIT 10;

Worked examples

Example 1: Relational predicate filtering

This example shows how to combine semantic vector search with a relational WHERE filter to narrow results to a specific category. A pipeline embeds product names into a knowledge base, and queries then retrieve semantically similar results filtered by category.

Set up the source table and pipeline

Create the source table:

CREATE TABLE products (
    id           SERIAL PRIMARY KEY,
    product_name TEXT NOT NULL,
    category     TEXT NOT NULL
);

Populate it with sample data:

INSERT INTO products (product_name, category) VALUES
    ('Hamburger',    'meat'),
    ('Cheeseburger', 'meat'),
    ('Fish n Chips',  'fish'),
    ('Fries',         'vegetarian'),
    ('Burrito',       'meat'),
    ('Pizza',         'meat'),
    ('Sandwich',      'meat'),
    ('Veggie Burger', 'vegetarian'),
    ('Kebab',         'meat');

Create a pipeline to embed product_name into a knowledge base:

SELECT aidb.create_pipeline(
    name               => 'food_products',
    source             => 'products',
    source_key_column  => 'id',
    source_data_column => 'product_name',
    step_1             => 'KnowledgeBase',
    step_1_options     => aidb.knowledge_base_config(
                              model       => 'bert',
                              data_format => 'Text'
                          )
);

Run the pipeline to generate embeddings:

SELECT aidb.run_pipeline('food_products');

Standard semantic query (no filter)

SELECT * FROM aidb.retrieve_text('public.pipeline_food_products', 'fast food', topk => 4);
Output
 key |     value     |      distance      | part_ids | intermediate_steps
-----+---------------+--------------------+----------+--------------------
 8   | Veggie Burger | 0.9955745765192575 | {0}      | []
 6   | Pizza         | 1.0280680840689795 | {0}      | []
 4   | Fries         | 1.0630385212203115 | {0}      | []
 7   | Sandwich      | 1.0903594734917037 | {0}      | []

Custom query with relational filter

SET hnsw.iterative_scan = relaxed_order;

SELECT vector.source_id, source.product_name, source.category
FROM public.pipeline_food_products AS vector
JOIN products AS source ON source.id::TEXT = vector.source_id
WHERE source.category = 'vegetarian'                                                         -- relational filter
ORDER BY vector.value <=> aidb.kb_encode('public.pipeline_food_products', 'fast food')
LIMIT 10;
Output
 source_id | product_name  |  category
-----------+---------------+------------
 8         | Veggie Burger | vegetarian
 4         | Fries         | vegetarian

Only vegetarian items are returned, ordered by semantic relevance to "fast food".

Example 2: BM25 full-text search with RRF

This example shows how to combine semantic vector search with BM25 keyword search using Reciprocal Rank Fusion (RRF) to merge the two result sets. RRF re-ranks results by combining their positions in the semantic and keyword rankings, surfacing items that score well in both. This example builds on the same products table and food_products pipeline from Example 1.

Add a BM25 index to the source table

Enable the pg_tokenizer and vchord_bm25 extensions:

CREATE EXTENSION IF NOT EXISTS pg_tokenizer;
CREATE EXTENSION IF NOT EXISTS vchord_bm25;

Create a tokenizer using the BERT model:

SELECT tokenizer_catalog.create_tokenizer('bert', $$
    model = "bert_base_uncased"
$$);

Add a BM25 vector column to the source table and populate it with tokenized values:

ALTER TABLE products ADD COLUMN embedding_bm25 bm25_catalog.bm25vector;
UPDATE products SET embedding_bm25 = tokenizer_catalog.tokenize(product_name, 'bert');

Create a BM25 index on the new column:

CREATE INDEX products_bm25_idx ON products USING bm25 (embedding_bm25 bm25_catalog.bm25_ops);

Hybrid query with RRF

SET hnsw.iterative_scan = relaxed_order;
SET search_path TO public, tokenizer_catalog, bm25_catalog;

WITH semantic AS (
    SELECT source.id,
           source.product_name,
           source.category,
           ROW_NUMBER() OVER (ORDER BY vector.value <=> aidb.kb_encode('public.pipeline_food_products', 'fast food')) AS rank
    FROM public.pipeline_food_products AS vector
    JOIN products AS source ON source.id::TEXT = vector.source_id
    ORDER BY vector.value <=> aidb.kb_encode('public.pipeline_food_products', 'fast food')
    LIMIT 20
),
keyword AS (
    SELECT id,
           product_name,
           ROW_NUMBER() OVER (ORDER BY embedding_bm25 <&> to_bm25query('products_bm25_idx', tokenize('fast food', 'bert'))) AS rank
    FROM products
    ORDER BY embedding_bm25 <&> to_bm25query('products_bm25_idx', tokenize('fast food', 'bert'))
    LIMIT 20
)
SELECT
    COALESCE(s.id, k.id)                                                         AS id,
    COALESCE(s.product_name, k.product_name)                                     AS product_name,
    s.category,
    s.rank                                                                       AS semantic_rank,
    k.rank                                                                       AS keyword_rank,
    COALESCE(1.0 / (60 + s.rank), 0) + COALESCE(1.0 / (60 + k.rank), 0)        AS rrf_score
FROM semantic s
FULL OUTER JOIN keyword k ON s.id = k.id
ORDER BY rrf_score DESC
LIMIT 10;
__OUPUT__
 id | product_name  |  category  | semantic_rank | keyword_rank |       rrf_score
----+---------------+------------+---------------+--------------+------------------------
  1 | Hamburger     | meat       |             6 |            1 | 0.03154495777446597119
  4 | Fries         | vegetarian |             3 |            4 | 0.03149801587301587302
  6 | Pizza         | meat       |             2 |            6 | 0.03128054740957966765
  8 | Veggie Burger | vegetarian |             1 |            8 | 0.03109932497589199614
  3 | Fish n Chips  | fish       |             7 |            3 | 0.03079838900734423123
  5 | Burrito       | meat       |             5 |            5 | 0.03076923076923076924
  2 | Cheeseburger  | meat       |             9 |            2 | 0.03062178588125292193
  7 | Sandwich      | meat       |             4 |            7 | 0.03055037313432835821
  9 | Kebab         | meat       |             8 |            9 | 0.02919863597612958227

The RRF score combines both rankings: items that rank highly in either semantic or keyword search surface at the top, with items that rank well in both receiving the highest scores.

Setting up user-managed BM25

BM25 is not managed by AI pipelines. You set it up independently on your source table, then reference it in your hybrid queries.

1. Install extensions

CREATE EXTENSION IF NOT EXISTS pg_tokenizer;
CREATE EXTENSION IF NOT EXISTS vchord_bm25;

2. Create a tokenizer

SELECT create_tokenizer('bert', $$
    model = "bert_base_uncased"
$$);

3. Add a bm25vector column and index to your source table

ALTER TABLE my_source ADD COLUMN bm25_tokens bm25vector;
UPDATE my_source SET bm25_tokens = tokenize(description, 'bert');

CREATE INDEX my_source_bm25 ON my_source USING bm25 (bm25_tokens bm25_ops);

4. Keep the index current

New rows must be tokenized before they will appear in BM25 results. Use a trigger or update the column as part of your data ingestion process:

UPDATE my_source SET bm25_tokens = tokenize(description, 'bert') WHERE bm25_tokens IS NULL;

For full tokenizer configuration options, see VectorChord-BM25 and VectorChord-BM25 in pg_extensions.

Volume source limitation

Hybrid search with relational predicate filtering or BM25 is not supported for volume-sourced knowledge bases. Volume sources have no backing Postgres table to join against, so there is no source table to filter or index.

Volume-sourced knowledge bases continue to support standard semantic search via aidb.retrieve_text() and aidb.retrieve_key().

← Prev

Knowledge bases

↑ Up

Knowledge bases

Next →

Vector extensions