Vector search

Semantic search over embeddings is an order by embedding <=> query limit k. In a multi-tenant app, RLS adds where org_id = ... to that query, and an HNSW index can't apply it while scanning: it finds the k nearest rows across all tenants, then RLS drops the ones the caller can't see. A tenant with a small share of the data often gets fewer than k results, or none.

pgvector 0.8 fixed this with iterative index scans: with hnsw.iterative_scan on, the scan continues until k visible rows are found. The vector-search SQL module writes one search function per table with it set, and db.$search calls it.

SetupCopy Anchor Link

List the embedding columns:

export default defineConfig({
  vectorSearch: {
    chunks: "embedding", // cosine distance
    "docs.pages": { column: "embedding", distance: "inner_product" }, // or 'l2'
  },
  sql: { modules: ["vector-search"] },
});

better-supabase sql sync
supabase db schema declarative sync -f vector_search

For chunks this writes:

create or replace function public.search_chunks(query extensions.vector, k integer default 10)
returns setof public.chunks
language plpgsql stable security invoker
set search_path = ''
as $$
declare
  previous_scan text := current_setting('hnsw.iterative_scan', true);
begin
  perform set_config('hnsw.iterative_scan', 'strict_order', true);
  return query select t.* from public.chunks t
  where t.embedding is not null
  order by t.embedding operator(extensions.<=>) query
  limit least(greatest(k, 1), 1000);
  perform set_config('hnsw.iterative_scan', coalesce(previous_scan, 'off'), true);
end;
$$;

The function turns on hnsw.iterative_scan in its body and restores the caller's value afterwards, instead of in a set clause. A set clause for a pgvector setting needs the pgvector library loaded in the session that runs create function, and a migration session that hasn't used a vector yet fails with permission denied to set parameter "hnsw.iterative_scan" for any role that isn't a superuser, which includes postgres on Supabase.

The functions use the schema pgvector is installed in. sql sync reads it from the create extension ... vector ... schema statement in your schema files or migrations, and falls back to extensions, the Supabase default. Set sql.modules.vector-search.options.schema when pgvector is installed another way:

sql: {
  modules: { "vector-search": { options: { schema: "public" } } },
},

The function is security invoker, so the caller's RLS policies run inside the scan. It is granted to authenticated and service_role; grant it to anon yourself for public search. Create the index with the operator class that matches the distance:

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

SearchingCopy Anchor Link

const embedding = await embed(question); // number[]
const result = await ctx.db.$search("chunks", {
  vector: embedding,
  k: 8,
  select: ["id", "content", "documentId"],
});

• Rows come back nearest first, typed from the generated schema, in your configured casing. select and include work as in findMany.
• where filters the k rows the search returns, so it can return fewer. Put filters that should shape the search (the tenant, a document set) in RLS.
• vector is a number[] or pgvector text ('[0.1,0.2]'). It is sent in a POST body: embeddings are too long for a URL. So a search counts toward write rate limits and goes to the primary with read replicas.
• It runs over PostgREST and over better-supabase/postgres alike. A table without a search function fails with a hint to add it to vectorSearch.

ScoresCopy Anchor Link

score: true adds $score to each row: the similarity (1 - distance for cosine, 1 / (1 + distance) for l2, the inner product for inner_product), or the fused and boosted score below. Every entry also gets search_<table>_scores, which returns the ids and scores; db.$search reads it, then the rows through the table, so RLS and where apply to both. It needs a one-column primary key, id unless key names another.

const rows = await db
  .$search("chunks", { vector, k: 8, score: true })
  .orThrow();
rows[0].$score; // number

Hybrid ranking, boosts, filters and orderCopy Anchor Link

An entry can take more options:

vectorSearch: {
  knowledge_chunks: {
    column: "embedding",
    type: "halfvec", // the column is halfvec(1536)
    hybrid: { tsvector: "content_tsv", config: "english" },
    boost: "t.retrieval_priority",
    prefilter: ["collection_id", "organization_id"],
  },
},

With any of hybrid, boost, prefilter, predicate or order, the functions take filter jsonb and text_query text as well, and rank four times k candidates from each side before they keep k:

const rows = await db
  .$search("knowledgeChunks", {
    vector: embedding,
    text: question,
    k: 8,
    filter: { collection_id: ids, organization_id: [orgId, null] },
    score: true,
  })
  .orThrow();

On a hybrid entry, vector can be left out (or null) with text: the search then ranks by full-text alone. That keeps search working when the embedding call fails or times out:

const embedding = await embed(question).catch(() => null);
const rows = await db
  .$search("knowledgeChunks", { vector: embedding, text: question, k: 8 })
  .orThrow();

filter is keyed by database column name. A value matches the column; a list matches any of its values, and null in it matches rows where the column is null, such as shared rows without an organization.

strict_order returns rows in exact distance order. For large tables where approximate order is enough, change the setting in the function to relaxed_order, and raise hnsw.max_scan_tuples if a tenant's rows are very sparse.

ExampleCopy Anchor Link

The Next.js example stores a 3-dimensional notes.embedding and lists the notes nearest to the latest one on its customers page (src/features/notes/note-queries.ts). It needs no embedding model: the query vector is a stored embedding.

Custom executorsCopy Anchor Link

db.$search reads through SelectOp.source. An executor opts in with functionSources: true; testExecutor then checks that it reads the function rather than the table. See Interfaces.