A Supabase pgvector RAG app is four things: a table with a vector column, a SQL function that returns the nearest chunks, a security policy so users only retrieve their own documents, and an index once the table grows. The model call is the easy part. Most RAG bugs I see live in those four SQL pieces.
This post builds them in order, and every SQL result below came from a local run. I did not have a Supabase project or API keys in this session, so I ran the SQL on a throwaway local Postgres 17 with pgvector 0.8.7, and the TypeScript parts are labelled unrun.
What do you need before you start?
- A Supabase project (or any Postgres with pgvector).
- An embedding model. Pick one and never mix: Supabase's docs say embeddings from different models give meaningless distances. See the embedding models comparison for options.
- The dimension count of that model. Every
vector(N)below uses N = 3 only so the demo output fits on screen. Replace it with your model's real size.
If you want the concepts first, read how RAG works. If you want to run Postgres yourself instead, the pgvector on a VPS guide covers Docker, backups and restores.
Step 1: create the tables
Supabase's docs enable the extension into the extensions schema and reference the type as extensions.vector. The tables below follow its "RAG with permissions" layout: documents own sections, and sections hold the chunk text plus the embedding. (Checked against the Supabase docs on 2026-10-31.)
create extension if not exists vector with schema extensions;
create table documents (
id bigint primary key generated always as identity,
name text not null,
owner_id uuid not null references auth.users (id) default auth.uid()
);
create table document_sections (
id bigint primary key generated always as identity,
document_id bigint not null references documents (id) on delete cascade,
content text not null,
embedding extensions.vector(384)
);
Two choices worth copying. on delete cascade means deleting a document removes its chunks, so you never leak orphans into search. And the owner lives on documents, not on every chunk, so changing ownership is one row update.
My local run used a plain vector(3) and a stub auth.uid() function, because the real auth schema only exists on Supabase. The stub reads the request.jwt.claim.sub setting, which is what Supabase's RAG-with-permissions page says the real one does. Treat the local run as a test of the SQL logic, not of Supabase itself.
Step 2: write the match function
PostgREST, the layer behind supabase-js, does not expose pgvector's operators, so Supabase's docs wrap the query in a function and call it with rpc(). Here is the shape I used, adapted from their match_documents example:
create or replace function match_sections (
query_embedding extensions.vector(384),
match_threshold float,
match_count int
)
returns table (id bigint, content text, similarity float)
language sql stable
as $$
select s.id, s.content, 1 - (s.embedding <=> query_embedding) as similarity
from document_sections s
where s.embedding <=> query_embedding < 1 - match_threshold
order by s.embedding <=> query_embedding asc
limit least(match_count, 200);
$$;
Note the order by. It sorts by the distance expression, not by the similarity alias. Supabase's vector columns page warns that sorting by the computed similarity can cause the index to be ignored. I checked that on 50,000 random 64-dimension vectors (plan trimmed):
-- order by embedding <=> q, with an HNSW index
Limit
-> Index Scan using items_hnsw on items
Execution Time: 0.523 ms
-- order by 1 - (embedding <=> q) desc
Limit
-> Sort
-> Seq Scan on items
Same table, same data, and the second form can never use the index. Without any index, the plain distance query was a sequential scan of 50,000 rows at 9.5 ms on my laptop. That is already fine for a small app, which is why you should not rush to add an index (see step 4).
The threshold means: return only chunks whose cosine distance is under 1 - match_threshold. Supabase's examples use 0.78 and say to choose it for your own data. There is no universal good value, so log scores for real questions and look at where wrong matches start.
Step 3: lock it down with row level security
If several users upload documents, similarity search must not cross the boundary. Because pgvector is ordinary Postgres, row level security (RLS) does this for you:
alter table document_sections enable row level security;
create policy "own sections" on document_sections
for select to authenticated using (
document_id in (
select id from documents where owner_id = (select auth.uid())
)
);
I seeded two users, Alice (two chunks) and Bob (one chunk), all about refunds. Then I ran the same match_sections('[1,0,0]', 0.5, 5) call as three different identities. Real output:
=== as table owner (bypasses RLS) ===
1 | Alice: refunds take 5 days | 1
3 | Bob: refunds take 30 days | 0.9938837488013375
=== as alice ===
1 | Alice: refunds take 5 days | 1
=== as bob ===
3 | Bob: refunds take 30 days | 0.9938837488013375
The table owner (the role you use in the SQL editor or with a service key) sees everything. Alice and Bob see only their own rows, and the function needed no where user_id = ... of its own. That matters, because the next developer who writes a new query cannot forget the filter. Supabase's docs make the same argument for RLS over hand-written filters.
Two traps follow from this:
- Do not mark the function
security definer. The default is security invoker, which runs as the caller and respects the policies. A definer function runs as its owner and would behave like the first block above. - Never put the service role key in the browser. It bypasses RLS by design. Use it only in server code that does its own authorisation, and prefer the user's own token for retrieval.
Step 4: add an HNSW index, and know what it breaks
Supabase's HNSW page calls HNSW the default choice, and says you can create it straight after the table, unlike IVFFlat which should be built after data exists:
create index on document_sections
using hnsw (embedding extensions.vector_cosine_ops);
Pick the operator class that matches your query operator: vector_cosine_ops for <=>, vector_l2_ops for <->, vector_ip_ops for <#>. A mismatch means the index is silently unused.
Now the part tutorials skip. An approximate index returns its top candidates, and your where filter is applied afterwards. I added a tenant column to the 50,000-row test table (100 tenants, so a filter keeps 1 percent) and asked for the 10 nearest rows in one tenant:
-- default settings
select count(*) from (... where tenant = 7 order by embedding <=> q limit 10)
count
-------
0
-- set hnsw.iterative_scan = relaxed_order;
count
-------
10
Zero results. The query was correct and the data was there; the index handed back its candidate list (hnsw.ef_search, 40 by default per the pgvector README, checked 2026-10-31) and none of them belonged to tenant 7. Supabase's docs describe this: from pgvector 0.8.0 you can turn on iterative scans with hnsw.iterative_scan (strict_order or relaxed_order), default off, bounded by hnsw.max_scan_tuples (default 20,000). Set it per transaction or per role if your queries filter heavily. Note that a per-user RLS policy is also a filter, so the same failure can show up for a user who owns a small slice of a big table.
If your table is small, skip the index and keep exact search. Sequential scan is exact and 9.5 ms at 50,000 rows in my test (different hardware will differ, so measure your own).
Step 5: ingest documents (unrun TypeScript)
This part needs an embedding API and a Supabase project, so it is a template I have not run. The structure is the point: split, embed in a batch, insert under the user's own session.
// UNRUN template. embed() is whichever embedding client you chose.
async function ingest(supabase: SupabaseClient, name: string, text: string) {
const { data: doc, error } = await supabase
.from("documents")
.insert({ name })
.select("id")
.single();
if (error) throw error;
const chunks = splitIntoChunks(text, { maxChars: 1200, overlap: 150 });
const vectors = await embed(chunks); // one batched call, same model as queries
const rows = chunks.map((content, i) => ({
document_id: doc.id,
content,
embedding: vectors[i],
}));
const { error: insertError } = await supabase.from("document_sections").insert(rows);
if (insertError) throw insertError;
}
The chunk size and overlap numbers are placeholders, not recommendations. Tune them by checking whether the answer to a real question sits inside a single retrieved chunk.
If you insert a vector with the wrong length, Postgres refuses it. My local error: expected 3 dimensions, not 2. That is the error you get when you change embedding models and forget to migrate the column.
Step 6: retrieve and answer (unrun)
// UNRUN template.
const queryEmbedding = await embedOne(question);
const { data: sections } = await supabase.rpc("match_sections", {
query_embedding: queryEmbedding,
match_threshold: 0.7, // tune on your data
match_count: 6,
});
const context = (sections ?? []).map((s) => s.content).join("\n---\n");
// Pass `context` to your model with an instruction to answer only from it,
// and to say so when the context does not contain the answer.
Treat retrieved text as untrusted input. A document a user uploads can contain instructions, and the model will see them. Read prompt injection explained before letting a RAG answer trigger any action. For better recall on exact terms like product codes, add keyword search: the hybrid search guide shows the idea, and Supabase documents its own hybrid search page.
Checklist before you ship
- Same embedding model for documents and queries; dimension matches the column.
order byuses the distance expression; the index operator class matches the operator.- RLS enabled on every table the function reads; function is security invoker.
- Tested with two real users, plus one empty-result user.
- Heavily filtered queries tested with
hnsw.iterative_scan, and result counts checked. - Service role key only on the server.
What I did not verify
- I did not run anything on Supabase itself. The managed platform's extension versions, limits and pricing may differ from my local pgvector 0.8.7; check your project's version with
select * from pg_extension where extname = 'vector'. - The supabase-js and embedding code is unrun.
- Timings are from one laptop, on random vectors. Random vectors have no real cluster structure, so recall and speed on your data will differ.
- The
auth.uid()function andauthenticatedrole in my test were stand-ins that mimic Supabase's documented behaviour.
