Combining Keyword and Vector Search

This recipe shows how to rank text by its keywords and by its meaning at the same time. A search by meaning alone can miss an exact term, such as a product name or an error code, that a keyword search finds at once; hybrid search runs both and merges the two rankings. The script vectorizes a table of three rows, waits for the background worker, and then calls pgedge_vectorizer.hybrid_search_simple() with a question.

These examples are for pgEdge Vectorizer 1.1.

The script prints the following output:

Chunks with keyword vectors: 3
Question: Which database stores tables?
Top match (row 3): PostgreSQL stores data in tables and answers questions written in SQL.

The vector half of the ranking depends on the embedding model and on the build of the model. The rows in this example cover subjects far enough apart, and share enough keywords with the question, that the top match should not change, but the order of the other rows can. The output above came from Ollama's nomic-embed-text model with the digest 0a109f422b47.

Preparing the Ingredients

The script needs a PostgreSQL server with the vector and pgedge_vectorizer extensions, and an embedding model that the server can reach. The Vectorizing a Table recipe lists the server settings that the examples use. Hybrid search also needs the following setting, which tells the background worker to compute a sparse BM25 vector for each chunk alongside its embedding:

pgedge_vectorizer.enable_hybrid = on

The setting must apply to the background worker as well as to the session that searches, so set it in postgresql.conf and reload the configuration, or set it on the server's command line, rather than in the session alone. Chunks that the worker embedded before the setting took effect have no sparse vector until pgedge_vectorizer.reprocess_chunks() queues them again.

Configuring psql

The script prints plain lines, and it hides the NOTICE messages that the Vectorizer functions raise. A failed step must not stop the script before the cleanup at the end, so the script turns off ON_ERROR_STOP until the cleanup starts. The following code shows the settings:

-- Print plain lines, and hide the NOTICE messages that the Vectorizer
-- functions raise as they work.
\pset format unaligned
\pset tuples_only on
SET client_min_messages = warning;

-- Keep going after a failed step, so that the cleanup at the end of the
-- script always runs; the script fails at the end instead.
\set ON_ERROR_STOP off

Vectorizing the Rows

The script creates a table with a primary key, inserts three rows, and calls enable_vectorization() for the text column, which creates the cookbook_hybrid_search_content_chunks table and queues each chunk for the background worker. The following code creates and vectorizes the table:

-- Three rows about unrelated subjects, so that the right answer is clear
-- whichever build of the embedding model is in use.
CREATE TABLE cookbook_hybrid_search (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    content text NOT NULL
);

INSERT INTO cookbook_hybrid_search (content) VALUES
    ('Knead the bread dough for ten minutes, then leave it to rise in a warm kitchen.'),
    ('Jupiter is the largest planet in the solar system, with dozens of moons.'),
    ('PostgreSQL stores data in tables and answers questions written in SQL.');

-- The function returns void, so the query selects no columns, which
-- prints nothing.
SELECT FROM pgedge_vectorizer.enable_vectorization(
    'cookbook_hybrid_search', 'content');

Waiting for the Embeddings

The script waits in a DO block that checks the queue once a second. The block stops when no item for the chunk table is pending or in progress, and raises an error if an item fails or the wait passes five minutes; a model that is not yet in memory can take some time to answer its first request. The worker computes each chunk's embedding and sparse vector together, so one wait covers both. The following code waits for the worker:

-- Wait until the background worker has embedded every queued chunk of the
-- table, and fail if an item fails or the wait takes over five minutes. A
-- cold embedding model can take a while to answer its first request.
DO $$
DECLARE
    deadline timestamptz := clock_timestamp() + interval '300 seconds';
    failure text;
BEGIN
    LOOP
        SELECT error_message INTO failure
        FROM pgedge_vectorizer.failed_items
        WHERE chunk_table = 'cookbook_hybrid_search_content_chunks'
        LIMIT 1;
        IF FOUND THEN
            RAISE EXCEPTION 'embedding failed: %', failure;
        END IF;

        EXIT WHEN NOT EXISTS (
            SELECT 1
            FROM pgedge_vectorizer.queue_status
            WHERE chunk_table = 'cookbook_hybrid_search_content_chunks'
              AND status IN ('pending', 'processing'));

        IF clock_timestamp() > deadline THEN
            -- Report the latest error that the worker recorded for the
            -- table; a request that hangs records none.
            SELECT error_message INTO failure
            FROM pgedge_vectorizer.queue
            WHERE chunk_table = 'cookbook_hybrid_search_content_chunks'
              AND error_message IS NOT NULL
            ORDER BY next_retry_at DESC NULLS LAST, id DESC
            LIMIT 1;
            RAISE EXCEPTION 'timed out waiting for the embeddings: %',
                coalesce(failure, 'no error recorded; check that '
                    || 'pgedge_vectorizer.databases lists this database '
                    || 'and that pgedge_vectorizer.api_url reaches the '
                    || 'embedding provider');
        END IF;
        PERFORM pg_sleep(1);
    END LOOP;
END
$$;

Checking the Keyword Vectors

The worker stores the sparse BM25 vector of each chunk in the sparse_embedding column of the chunk table, and leaves the column empty when enable_hybrid is off. The following code counts the chunks that have a sparse vector:

-- The worker computes a sparse BM25 vector for each chunk, as well as its
-- embedding, only when pgedge_vectorizer.enable_hybrid is on.
SELECT 'Chunks with keyword vectors: ' || count(sparse_embedding)
FROM cookbook_hybrid_search_content_chunks;

Searching by Keyword and Meaning

The hybrid_search_simple() function takes the source table rather than the chunk table, and looks up the chunk table in the extension's registry. The function ranks the chunks by the cosine distance of their embeddings from the question's embedding, and separately by their BM25 keyword score, and then merges the two lists with Reciprocal Rank Fusion (RRF). Each result carries the source row's primary key as text, the chunk, and the rrf_score, which is highest for the best match. The pgedge_vectorizer.hybrid_search() function takes the same arguments with two more, for the weight of the vector ranking and the RRF constant, and also returns each chunk's rank in both lists.

The script keeps the question in a psql variable, and psql writes the value into the query as a quoted string literal, escaping any quotes inside it. The following code runs the search:

-- psql quotes the variable as a string literal where :'question' appears,
-- escaping any quotes inside it.
\set question 'Which database stores tables?'
\echo Question: :question

-- hybrid_search_simple() finds the chunk table from the source table,
-- ranks the chunks by keyword (BM25) and by vector distance, and returns
-- them in order of the combined Reciprocal Rank Fusion (RRF) score, best
-- first. The third argument limits the number of results.
SELECT 'Top match (row ' || source_id || '): ' || chunk
FROM pgedge_vectorizer.hybrid_search_simple(
    'cookbook_hybrid_search', :'question', 3)
ORDER BY rrf_score DESC
LIMIT 1;

Cleaning Up

The disable_vectorization() function drops the triggers and, with drop_chunk_table, the chunk table and its table of keyword statistics, and it removes the table's pending items from the queue. Completed items stay in the queue; the clear_completed(0) function would remove them at once, but for every vectorized table in the database, so the script deletes only the items for its own chunk table. The script then checks psql's LAST_ERROR_SQLSTATE variable, and raises an error if an earlier step failed, so that psql exits with a non-zero status. The following code removes everything that the script created:

-- Stop at the first error from here on, so that a failed cleanup fails
-- the script.
\set ON_ERROR_STOP on

-- Drop the triggers and the chunk table, and then remove the table's
-- completed queue items, which disable_vectorization() leaves behind.
SELECT FROM pgedge_vectorizer.disable_vectorization(
    'cookbook_hybrid_search', 'content', drop_chunk_table => true);
DELETE FROM pgedge_vectorizer.queue
WHERE chunk_table = 'cookbook_hybrid_search_content_chunks';
DROP TABLE cookbook_hybrid_search;

-- Fail the script if any earlier step failed; psql keeps the SQLSTATE of
-- the most recent error, or 00000 if there was none.
SELECT :'LAST_ERROR_SQLSTATE' <> '00000' AS failed \gset
\if :failed
DO $$ BEGIN RAISE EXCEPTION 'a step of the recipe failed'; END $$;
\endif

Running the Example

The script is available in the cookbook repository. Set the PG* environment variables for your server, and then run the following command from the recipe's sql directory:

psql -X -q -v ON_ERROR_STOP=1 -f main.sql

Viewing the Complete Program

The following code shows the complete script:

-- Ellie's Postgres Cookbook
-- Combining keyword and vector search with pgEdge Vectorizer, which merges
-- a BM25 keyword ranking with a vector ranking of the same chunks.
--
-- Copyright (c) 2026, pgEdge, Inc.
-- This code is released under the PostgreSQL License.

-- Print plain lines, and hide the NOTICE messages that the Vectorizer
-- functions raise as they work.
\pset format unaligned
\pset tuples_only on
SET client_min_messages = warning;

-- Keep going after a failed step, so that the cleanup at the end of the
-- script always runs; the script fails at the end instead.
\set ON_ERROR_STOP off

-- Three rows about unrelated subjects, so that the right answer is clear
-- whichever build of the embedding model is in use.
CREATE TABLE cookbook_hybrid_search (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    content text NOT NULL
);

INSERT INTO cookbook_hybrid_search (content) VALUES
    ('Knead the bread dough for ten minutes, then leave it to rise in a warm kitchen.'),
    ('Jupiter is the largest planet in the solar system, with dozens of moons.'),
    ('PostgreSQL stores data in tables and answers questions written in SQL.');

-- The function returns void, so the query selects no columns, which
-- prints nothing.
SELECT FROM pgedge_vectorizer.enable_vectorization(
    'cookbook_hybrid_search', 'content');

-- Wait until the background worker has embedded every queued chunk of the
-- table, and fail if an item fails or the wait takes over five minutes. A
-- cold embedding model can take a while to answer its first request.
DO $$
DECLARE
    deadline timestamptz := clock_timestamp() + interval '300 seconds';
    failure text;
BEGIN
    LOOP
        SELECT error_message INTO failure
        FROM pgedge_vectorizer.failed_items
        WHERE chunk_table = 'cookbook_hybrid_search_content_chunks'
        LIMIT 1;
        IF FOUND THEN
            RAISE EXCEPTION 'embedding failed: %', failure;
        END IF;

        EXIT WHEN NOT EXISTS (
            SELECT 1
            FROM pgedge_vectorizer.queue_status
            WHERE chunk_table = 'cookbook_hybrid_search_content_chunks'
              AND status IN ('pending', 'processing'));

        IF clock_timestamp() > deadline THEN
            -- Report the latest error that the worker recorded for the
            -- table; a request that hangs records none.
            SELECT error_message INTO failure
            FROM pgedge_vectorizer.queue
            WHERE chunk_table = 'cookbook_hybrid_search_content_chunks'
              AND error_message IS NOT NULL
            ORDER BY next_retry_at DESC NULLS LAST, id DESC
            LIMIT 1;
            RAISE EXCEPTION 'timed out waiting for the embeddings: %',
                coalesce(failure, 'no error recorded; check that '
                    || 'pgedge_vectorizer.databases lists this database '
                    || 'and that pgedge_vectorizer.api_url reaches the '
                    || 'embedding provider');
        END IF;
        PERFORM pg_sleep(1);
    END LOOP;
END
$$;

-- The worker computes a sparse BM25 vector for each chunk, as well as its
-- embedding, only when pgedge_vectorizer.enable_hybrid is on.
SELECT 'Chunks with keyword vectors: ' || count(sparse_embedding)
FROM cookbook_hybrid_search_content_chunks;

-- psql quotes the variable as a string literal where :'question' appears,
-- escaping any quotes inside it.
\set question 'Which database stores tables?'
\echo Question: :question

-- hybrid_search_simple() finds the chunk table from the source table,
-- ranks the chunks by keyword (BM25) and by vector distance, and returns
-- them in order of the combined Reciprocal Rank Fusion (RRF) score, best
-- first. The third argument limits the number of results.
SELECT 'Top match (row ' || source_id || '): ' || chunk
FROM pgedge_vectorizer.hybrid_search_simple(
    'cookbook_hybrid_search', :'question', 3)
ORDER BY rrf_score DESC
LIMIT 1;

-- Stop at the first error from here on, so that a failed cleanup fails
-- the script.
\set ON_ERROR_STOP on

-- Drop the triggers and the chunk table, and then remove the table's
-- completed queue items, which disable_vectorization() leaves behind.
SELECT FROM pgedge_vectorizer.disable_vectorization(
    'cookbook_hybrid_search', 'content', drop_chunk_table => true);
DELETE FROM pgedge_vectorizer.queue
WHERE chunk_table = 'cookbook_hybrid_search_content_chunks';
DROP TABLE cookbook_hybrid_search;

-- Fail the script if any earlier step failed; psql keeps the SQLSTATE of
-- the most recent error, or 00000 if there was none.
SELECT :'LAST_ERROR_SQLSTATE' <> '00000' AS failed \gset
\if :failed
DO $$ BEGIN RAISE EXCEPTION 'a step of the recipe failed'; END $$;
\endif

Next Steps

The following resources provide more information about the topics in this recipe:

The Searching Vectorized Text recipe searches by meaning alone, with the pgvector distance operator.
The Splitting Text into Chunks recipe shows how the extension divides a document before it embeds the chunks.
The pgEdge Vectorizer documentation describes the hybrid search functions and their settings.