Hybrid search

Semantic search is good at meaning and weak at exact terms. Keyword search is the opposite. Search a recipe app for "gochujang" and a vector search may return generic spicy dishes, while full-text search finds the three recipes that actually use it. Search for "something to use up stale bread" and the opposite happens.

Hybrid search runs both and merges the results, so a recipe that ranks well on either list, or on both, comes out near the top. Everything runs in one database function in your project's Postgres.

What you need

The recipes table needs both search columns:

• an fts tsvector generated column with a GIN index, as in Full-text search
• an embedding vector(1536) column, ideally with an HNSW index, as in Vector columns with pgvector and Vector indexes

If you created the table from the pgvector page, add the keyword column:

alter table public.recipes
  add column fts tsvector generated always as (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) stored;

create index recipes_fts_idx on public.recipes using gin (fts);

Why merge by rank, not by score

Full-text scores (ts_rank_cd) and vector distances are on unrelated scales, so adding them together gives meaningless numbers. Reciprocal rank fusion (RRF) ignores the raw scores and uses each row's position in each list instead:

score = keyword_weight / (k + keyword_position) + vector_weight / (k + vector_position)

A row missing from one list just gets nothing from that term. The constant k (60 is the value from the original RRF paper) stops the very top positions from dominating. Raising a weight favours that list.

The hybrid search function

Each half first takes its own top candidates with order by ... limit, which is the shape the GIN and HNSW indexes can serve, then numbers them. The final step adds up the RRF terms per recipe.

create or replace function public.hybrid_search_recipes(
  search_text text,
  search_vector extensions.vector(1536),
  result_count int default 10,
  keyword_weight float8 default 1.0,
  vector_weight float8 default 1.0
)
returns table (id bigint, title text, score float8)
language sql
stable
security invoker
set search_path = ''
as $$
  with
  params as (
    select websearch_to_tsquery('english', search_text) as tsq
  ),
  keyword_hits as (
    select hit.id, row_number() over (order by hit.text_rank desc) as pos
    from (
      select r.id, ts_rank_cd(r.fts, p.tsq) as text_rank
      from public.recipes r, params p
      where r.fts @@ p.tsq
      order by text_rank desc
      limit least(result_count, 50) * 4
    ) hit
  ),
  vector_hits as (
    select hit.id, row_number() over (order by hit.distance) as pos
    from (
      select r.id, r.embedding operator(extensions.<=>) search_vector as distance
      from public.recipes r
      where r.embedding is not null
      order by r.embedding operator(extensions.<=>) search_vector
      limit least(result_count, 50) * 4
    ) hit
  ),
  fused as (
    select s.id, sum(s.part) as score
    from (
      select k.id, keyword_weight / (60 + k.pos) as part from keyword_hits k
      union all
      select v.id, vector_weight / (60 + v.pos) as part from vector_hits v
    ) s
    group by s.id
  )
  select r.id, r.title, f.score
  from fused f
  join public.recipes r on r.id = f.id
  order by f.score desc
  limit least(result_count, 50);
$$;

Because it's security invoker, RLS on recipes filters every row, in both halves, to what the caller may see.

Call it from an Edge Function

The query needs an embedding, so call the function from an Edge Function that holds your embeddings API key. Starting from the search-recipes function in Semantic search, swap the RPC call:

const queryEmbedding = await embed(query.trim())

const { data, error } = await supabase.rpc('hybrid_search_recipes', {
  search_text: query.trim(),
  search_vector: queryEmbedding,
  result_count: 10,
})

The browser side stays the same:

const { data } = await supabase.functions.invoke('search-recipes', {
  body: { query: 'gochujang noodles' },
})

Tuning

• Weights: if users mostly type names, ingredients or codes, try keyword_weight: 1.5. If they describe what they want in sentences, raise vector_weight. Test with a list of real queries and the results you'd expect.
• Candidates per list: the function takes four times result_count from each side. More candidates improve recall but cost more time.
• Language: use the same text search configuration ('english' here) in the fts column and in websearch_to_tsquery.
• Empty keyword results: if the search text has only stop words, the keyword half returns nothing and you get pure vector results, which is usually what you want.

A simpler variant: keyword filter, vector order

When exact terms must match, filter by keyword and sort by meaning. As the body of a function that takes search_text and search_vector:

select r.id, r.title
from public.recipes r
where r.fts @@ websearch_to_tsquery('english', search_text)
  and r.embedding is not null
order by r.embedding operator(extensions.<=>) search_vector
limit 10;

This is cheaper than fusion, but it returns nothing when the keyword doesn't appear.

Check the plan

Run the function's inner queries with explain analyze in Studio's SQL editor to confirm the GIN index serves the keyword half and the HNSW index serves the vector half. See Query performance and EXPLAIN.

Related

• Semantic search
• Full-text search
• Retrieval-augmented generation (RAG)