Semantic Search for a Knowledge Base with Supabase pgvector

Supabase pgvector semantic search works like this: store an embedding (a list of numbers representing meaning) for each knowledge-base article in a vector column, index it with HNSW, and at query time embed the user's text and return the articles with the highest cosine similarity above a threshold. It finds "Wi-Fi keeps dropping" when the article is titled "Intermittent wireless connectivity", which keyword search misses. Add keyword search alongside (hybrid search), scope the query to the caller's organization, and it runs comfortably on Supabase's free plan.

Why semantic search for a help desk

Knowledge bases fail when people can't find the answer that already exists. Keyword search requires the searcher to use the author's words; requesters rarely do. Semantic search matches meaning:

Shown while someone is typing a new request, good matches resolve some requests before they're filed — see ticket deflection with a self-service knowledge base.

Step 1: enable pgvector and add a column

pgvector is available as a Postgres extension on Supabase. Enable it once, then add a column sized to your embedding model's output:

create extension if not exists vector with schema extensions;
 
alter table public.knowledge_entries
  add column embedding extensions.vector(768);

Choosing dimensions

Google's Gemini embedding models output 3,072 dimensions by default and support smaller sizes through Matryoshka representation learning, with 768, 1,536 and 3,072 recommended. LetRelay stores 768: a quarter of the storage of 3,072, and well under pgvector's indexing limit — the pgvector README states vectors with up to 2,000 dimensions can be indexed (4,000 with halfvec). For short help articles, 768 has been plenty.

Pick once and keep it: embeddings from different models or dimensions aren't comparable, and Google notes that newer and older Gemini embedding models produce incompatible spaces — switching means re-embedding everything.

Step 2: generate embeddings on the server

Embedding calls need an API key, so they belong in a Route Handler, Server Action or background job — never in the browser. When an article is created or edited:

1. Build the text to embed: title + body (strip Markdown noise).
2. Call the embeddings API with the right task type if the model supports one (document vs query).
3. Store the vector.

If the embedding call fails, save the article anyway with embedding = null and retry later. The article is still findable by keyword search; semantic search catches up. That's the same rule as all AI in LetRelay: degrade gracefully.

Step 3: index it

Without an index, every search compares the query against every row. pgvector offers two approximate index types. Its README summarizes the trade-off: HNSW has better query performance (speed–recall trade-off) but slower builds and more memory; IVFFlat builds faster and uses less memory, with lower query performance.

For a knowledge base — small, read-heavy, updated occasionally — HNSW is the right default:

create index on public.knowledge_entries
  using hnsw (embedding extensions.vector_cosine_ops);

pgvector's defaults are m = 16, ef_construction = 64, and ef_search = 40 at query time. Leave them until you have a measured recall problem.

Step 4: the search function

Cosine distance is pgvector's <=> operator; similarity is 1 - distance. Wrap the query in a SQL function so the app calls one RPC:

create or replace function public.match_knowledge(
  query_embedding extensions.vector(768),
  match_threshold double precision,
  match_count integer
)
returns table (id uuid, title text, similarity double precision)
language sql stable
as $$
  select ke.id, ke.title, 1 - (ke.embedding <=> query_embedding) as similarity
  from public.knowledge_entries ke
  where ke.embedding is not null
    and 1 - (ke.embedding <=> query_embedding) > match_threshold
  order by ke.embedding <=> query_embedding
  limit least(match_count, 10);
$$;

Order by the distance expression itself so Postgres can use the HNSW index; filter by threshold so weak matches aren't shown. LetRelay uses a similarity threshold of 0.72 by default for "similar articles", tuned by looking at real queries — too low shows irrelevant articles, too high shows nothing.

The multi-tenant trap

The function above has no security definer, so it runs with the caller's permissions and row-level security filters knowledge_entries to their organization. That's the safe version.

LetRelay's original version was security definer (to reach the index without evaluating policies per row) and accepted an organization id parameter so the background AI pipeline could say which organization to search. Our 2026 security audit found the problem: in Supabase, functions in the public schema are callable through the API, so any signed-in user could pass another organization's id and read its knowledge base. The fix scoped the function to the caller's own organization whatever they pass, restricted who may execute it, and capped match_count.

The general rule: a security definer function must derive the tenant from the caller, never from an argument — or be executable only by the service role. More in the multi-tenant RLS guide.

Step 5: hybrid search

Semantic search is weak on exact identifiers: error codes, product names, ticket references (RLY-1042), acronyms. Keyword search is strong on exactly those. Combine them:

1. Run a full-text search (to_tsvector / websearch_to_tsquery) and a vector search.
2. Merge with reciprocal rank fusion — score each result by 1 / (k + rank) in each list and sum.
3. Return the top few.

Supabase's docs include a hybrid-search function you can adapt. For LetRelay's in-app assistant we found a lexical retriever with synonyms and typo tolerance got the right guide page first 79% of the time on 47 test questions; adding embedding similarity raised a blind test set's top-1 accuracy from 53% to 80%. Measure on your own questions — the mix that wins depends on your content.

Measuring search quality

"It seems to work" isn't a measurement. Build a small golden set: 50–100 real questions people asked, each with the article that answers it. Then track:

• Top-1 accuracy — the right article is the first result.
• Top-3 accuracy — it's in the first three (what people actually look at).
• Empty rate — questions that return nothing above the threshold. Some should (no article exists); too many means the threshold is too high.

Include the messy cases: typos, abbreviations, other languages, very short queries ("vpn"), and questions whose answer isn't in the knowledge base at all — the correct result there is nothing. Run the set whenever you change the model, dimensions, threshold or the text you embed (title only, title + body, title + summary). Changes that feel better in a demo often lose a few points on the full set.

Where to show results

Semantic search is most valuable at three points in a help desk:

1. While a request is being typed — show two or three matching articles under the form, after a short pause in typing.
2. In the agent's view of a request — similar past requests and articles, to answer faster and consistently.
3. In an assistant — as grounding for answers, so the model only answers from articles it was given.

Show the article title and a one-line excerpt, not a similarity score. People judge relevance from words, not numbers.

Keeping embeddings fresh

• Re-embed when an article's title or body changes (a trigger can null the embedding; a job refills it).
• Store which model produced each embedding, so a model change can re-embed in batches.
• Don't embed on every keystroke while someone types a request; debounce by a few hundred milliseconds and require a minimum length.

Cost and free-tier fit

Vectors are small: 768 four-byte floats is about 3 KB per article before index overhead. A thousand articles is a few megabytes of Supabase's 500 MB free database. Embedding API calls are the other cost; Gemini's free tier covers modest volumes, and caching query embeddings for identical strings avoids repeat calls.

FAQ

What is pgvector?

An open-source Postgres extension that adds a vector data type, distance operators (<-> for L2, <=> for cosine, <#> for negative inner product) and approximate indexes (HNSW and IVFFlat) for similarity search.

HNSW or IVFFlat for a knowledge base?

HNSW for most knowledge bases: better query performance at the cost of slower index builds and more memory, which matters little for a few thousand articles.

How many dimensions should embeddings have?

Enough for your content and within index limits. 768 works well for short help articles, uses a quarter of the storage of 3,072, and stays under pgvector's 2,000-dimension index limit for vector.

Do I still need keyword search?

Yes, for exact terms like error codes and ticket references. Hybrid search combines both and is usually better than either alone.

Sources

• pgvector on GitHub
• Supabase — Vector columns
• Supabase — Semantic search
• Supabase — Hybrid search
• Google — Gemini embeddings
• PostgreSQL — Full text search