Setting the file. One moment.
Subchapter 7.2
references/hybrid-search.mdMarkdown4 KBView on GitHub
Use hybrid search when either semantic similarity or exact vocabulary can identify a relevant document. Lakebase Search does not provide a built-in hybrid function: run vector and BM25 retrieval separately, then combine their results with a fusion strategy suited to the workload.
Lakebase Search requires Postgres 16 or later. Hybrid search uses both extensions:
CREATE EXTENSION IF NOT EXISTS lakebase_vector CASCADE;
CREATE EXTENSION lakebase_vector installs pgvector through CASCADE; lakebase_text has no extension dependency. Both rely on preloaded libraries that Neon enables by default. If the project customized its preloaded-library list, confirm both libraries remain enabled.
Prepare a table with both vector and text-search columns:
CREATE TABLE documents (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
title text NOT NULL,
body text NOT NULL,
embedding vector(1536),
body_tsv tsvector GENERATED ALWAYS AS
(to_tsvector('english', body)) STORED
);Replace 1536 with the embedding model’s dimension. Use the same model and preprocessing for stored-document and query embeddings, and choose a PostgreSQL text-search configuration appropriate for the corpus.
Create and validate each retriever independently before combining them. Follow Vector search and Full-text search for their indexes, query operators, and tuning.
Reciprocal Rank Fusion (RRF) is the approach in the Lakebase Search get-started guide and a useful default because it combines ranks instead of incomparable raw distances and scores. It is not the only option: weighted rank fusion, normalized score fusion, or a reranker may fit applications with different relevance signals.
For rank r and constant k, each retriever contributes 1 / (k + r). The documented starting point uses 40 candidates per retriever and k = 60; tune both for the corpus and workload.
Bind the query embedding as $1, query text as $2, and final result count as $3:
WITH vector_ranked AS (
SELECT id, RANK() OVER (ORDER BY distance) AS rank
FROM (
SELECT id, embedding <=> $1::vector AS distance
FROM documents
ORDER BY distance
FETCH FIRST 40 ROWS WITH TIES
) AS vector_candidates
),
keyword_ranked AS (
SELECT id, RANK() OVER (ORDER BY score) AS rank
FROM (
SELECT
id,
body_tsv <@> to_bm25query(
to_tsvector('english', $2),
'documents_body_bm25'::regclass
) AS score
FROM documents
ORDER BY score
FETCH FIRST 40 ROWS WITH TIES
) AS keyword_candidates
)
SELECT
d.id,
d.title,
COALESCE(1.0 / (60 + v.rank), 0) +
COALESCE(1.0 / (60 + k.rank), 0) AS rrf_score
FROM documents AS d
LEFT JOIN vector_ranked AS v ON v.id = d.id
LEFT JOIN keyword_ranked AS k ON k.id = d.id
WHERE v.id IS NOT NULL OR k.id IS NOT NULL
ORDER BY rrf_score DESC, d.id
LIMIT $3;RANK() gives tied retrieval scores the same rank. Sort by rrf_score descending and use the stable ID as a final tie-breaker.
FETCH FIRST ... ROWS WITH TIES keeps every candidate tied at the cutoff, so RANK() receives the complete boundary tie group. The candidate set can therefore exceed 40 rows. lakebase_bm25.default_limit defaults to 1000; increase it only when the BM25 candidate set needs to exceed that value.
lakebase_bm25.default_limit above the BM25 candidate target and allow room for boundary ties.lakebase_bm25.prefilter improves the filtered query.