# Vector search > Nearest-neighbour search with pgvector that respects RLS and still returns k rows per tenant. Source: https://bettersupabase.com/docs/blocks/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](https://github.com/pgvector/pgvector#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. ## Setup [#setup] List the embedding columns: ```ts title="better-supabase.config.ts" export default defineConfig({ vectorSearch: { chunks: "embedding", // cosine distance "docs.pages": { column: "embedding", distance: "inner_product" }, // or 'l2' }, sql: { modules: ["vector-search"] }, }); ``` ```bash better-supabase sql sync supabase db schema declarative sync -f vector_search ``` For `chunks` this writes: ```sql 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: ```ts title="better-supabase.config.ts" 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: ```sql create index on public.chunks using hnsw (embedding extensions.vector_cosine_ops); ``` ## Searching [#searching] ```ts 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](/docs/blocks/sql#rate-limiting-writes) and goes to the primary with [read replicas](/docs/guides/read-replicas). * It runs over PostgREST and over [`better-supabase/postgres`](/docs/auth/postgres) alike. A table without a search function fails with a hint to add it to `vectorSearch`. ## Scores [#scores] `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__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. ```ts const rows = await db .$search("chunks", { vector, k: 8, score: true }) .orThrow(); rows[0].$score; // number ``` ## Hybrid ranking, boosts, filters and order [#hybrid-ranking-boosts-filters-and-order] An entry can take more options: ```ts title="better-supabase.config.ts" 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"], }, }, ``` | Option | What it does | | ----------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `type` | `halfvec` for half-precision columns; `vector` by default | | `hybrid` | ranks the `tsvector` column against `text` with `websearch_to_tsquery` and fuses both rankings by reciprocal rank (`k`, 60 by default) | | `boost` | an expression over the row `t` the score is multiplied by, such as a priority column | | `prefilter` | columns `filter` narrows before ranking, so filters shape the scan instead of trimming the `k` rows | | `key` | the primary key column the scores function returns; `id` by default | | `predicate` | a SQL condition over the row `t` every candidate meets before ranking: ranges and related rows, such as `t.expires_at > now()` or an `exists` on a parent that isn't disabled | | `boostMode` | `multiply` (default) or `add`, how `boost` combines with the score | | `order` | a SQL `order by` list over `t` that breaks score ties, such as `t.created_at desc` | 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`: ```ts 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: ```ts 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. ## Example [#example] 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 executors [#custom-executors] `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](/docs/extending/interfaces#executor).