pgvector on Supabase: Build RAG Without a Vector Database Vendor

pgvector on Supabase turns the Postgres database you already have into a vector store: enable the vector extension, add a vector column for your embeddings, and call one SQL function to find the chunks closest to a question. That is the whole retrieval half of RAG, with no separate vector database to host, pay for or keep in sync with your users and permissions.

Every SQL statement and snippet follows the official docs, checked October 2026: Supabase's guides on vector columns, vector indexes, semantic search, RAG with permissions, compute sizing and API keys, the pgvector README (version 0.8.7), and OpenAI's embeddings guide. The speed figures further down are Supabase's published benchmarks, not tests of ours.

This tutorial assumes you have decided RAG is the right tool. If you are still weighing it, read RAG vs fine-tuning first, and keep our LLM API pricing comparison, the hub for this topic, handy for what the answering model will cost.

What you will have at the end

Two tables, one function, one index and about 50 lines of TypeScript. A signed-in user asks a question and gets an answer drawn only from their own documents, with numbered sources.

Prerequisites

On keys: Supabase is retiring the old anon and service_role keys by the end of 2026 in favour of publishable and secret keys. The secret key bypasses Row Level Security, so it belongs on the server and nowhere else. New to the platform? Our Supabase tutorial covers project setup.

pgvector Supabase setup: the extension and the tables

Step 1: enable pgvector and create the schema

Run this in the SQL Editor, or better, in a migration. One table holds documents, the other holds their chunks and embeddings.

-- 1. Enable pgvector. Supabase keeps extensions in their own schema.
create extension if not exists vector with schema extensions;

-- 2. One row per source document
create table public.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(),
  created_at timestamptz not null default now()
);

-- 3. One row per chunk, with its embedding
create table public.document_sections (
  id bigint primary key generated always as identity,
  document_id bigint not null references public.documents (id) on delete cascade,
  content text not null,
  embedding extensions.vector(1536)
);

create index on public.documents (owner_id);
create index on public.document_sections (document_id);

The number in vector(1536) is the dimension count of your embedding model, and it must match exactly: 1,536 is the default for OpenAI's text-embedding-3-small. Expected result: select extversion from pg_extension where extname = 'vector'; returns a version number, and both tables appear in the Table Editor.

Step 2: lock the tables down

A vector table is an ordinary table, so it needs the same protection as any other. Enable RLS, set the grants, then add a policy that ties each chunk to the owner of its document. This is the pattern from Supabase's RAG with permissions guide.

alter table public.documents enable row level security;
alter table public.document_sections enable row level security;

revoke all on table public.documents, public.document_sections from anon, authenticated;
grant select on table public.documents, public.document_sections to authenticated;
grant select, insert, update, delete on table public.documents, public.document_sections to service_role;

create policy "Users read own documents"
on public.documents for select to authenticated
using (owner_id = (select auth.uid()));

create policy "Users read own sections"
on public.document_sections for select to authenticated
using (
  document_id in (
    select id from public.documents
    where owner_id = (select auth.uid())
  )
);

Expected result: a request with the publishable key and no session returns nothing from either table. If grants and policies are new to you, Supabase RLS explained walks through how the two checks combine.

Embed your documents and store the vectors

Step 3: one embedding call, one insert

Ingestion runs on your server with the secret key. The embeddings endpoint accepts an array, so a document's chunks go in one request and come back in the same order.

import OpenAI from 'openai'
import { createClient } from '@supabase/supabase-js'

const openai = new OpenAI() // reads OPENAI_API_KEY
// Server only: the secret key bypasses Row Level Security
const admin = createClient(process.env.SUPABASE_URL!, process.env.SUPABASE_SECRET_KEY!)

export async function embed(input: string[]) {
  const res = await openai.embeddings.create({ model: 'text-embedding-3-small', input })
  return res.data.map((d) => d.embedding)
}

export async function ingest(ownerId: string, name: string, chunks: string[]) {
  const { data: doc, error } = await admin
    .from('documents')
    .insert({ name, owner_id: ownerId })
    .select('id')
    .single()
  if (error) throw error

  const embeddings = await embed(chunks)
  const rows = chunks.map((content, i) => ({
    document_id: doc.id,
    content,
    embedding: embeddings[i],
  }))
  const { error: insertError } = await admin.from('document_sections').insert(rows)
  if (insertError) throw insertError
}

Expected result: one new row in documents and one row per chunk in document_sections, each with a non-null embedding. At $0.02 per million tokens (checked October 2026), embedding a million tokens of text costs two cents, so the bill here is storage and compute, not the model.

Two alternatives are worth knowing. Supabase Edge Functions have a built-in gte-small model that produces 384-dimension vectors with no external API, and Supabase documents an automatic embeddings pattern that queues the work from a database trigger. Whichever model you choose, use it for both documents and questions: vectors from different models cannot be compared.

Supabase vector search: the match function

Step 4: wrap the similarity query in a function

The Supabase client libraries talk to Postgres through PostgREST, which does not support pgvector's distance operators directly. The documented route is a SQL function called with rpc().

create or replace function public.match_document_sections (
  query_embedding extensions.vector(1536),
  match_threshold float,
  match_count int
)
returns table (id bigint, document_id bigint, content text, similarity float)
language sql stable
set search_path = public, extensions
as $$
  select
    s.id,
    s.document_id,
    s.content,
    1 - (s.embedding <=> query_embedding) as similarity
  from public.document_sections s
  where s.embedding <=> query_embedding < 1 - match_threshold
  order by s.embedding <=> query_embedding asc
  limit least(match_count, 200);
$$;

grant execute on function public.match_document_sections to authenticated;

<=> is cosine distance, so similarity is one minus that. Cosine is the safe default; Supabase notes that if your vectors are normalized, as OpenAI's are, the negative inner product operator <#> is faster. The function runs with the caller's rights, so the RLS policy from step 2 filters its results. Expected result: the function appears under Database, then Functions.

Step 5: call it from your app

Embed the question with the same model, then call the function through a client that carries the user's session.

// userClient carries the signed-in user's session, so RLS applies
const [queryEmbedding] = await embed([question])

const { data: sections, error } = await userClient.rpc('match_document_sections', {
  query_embedding: queryEmbedding,
  match_threshold: 0.3, // tune this on your own data
  match_count: 5,
})
if (error) throw error

Expected result: up to five rows with content and a similarity score, all from documents this user owns. Supabase's own example uses a threshold of 0.78 and says the right value varies by application, so start low, look at real results and raise it. To filter by another column, add a parameter and a where clause inside the function; chaining .eq() after rpc() filters after the ranking and limit have already run.

pgvector embedding index: HNSW, IVFFlat or none

Step 6: add an index when the table is big enough to need one

Without an index, pgvector compares the question against every row. That is exact and perfectly fine for a small table. Once queries slow down, add HNSW.

-- Default choice: HNSW, with the cosine operator class to match <=>
create index on public.document_sections
using hnsw (embedding vector_cosine_ops);

-- Trade speed for recall per session (the default is 40)
set hnsw.ef_search = 100;

-- Alternative: IVFFlat, built only after the table has data
-- create index on public.document_sections
-- using ivfflat (embedding vector_cosine_ops) with (lists = 100);
-- set ivfflat.probes = 10;

For a sense of scale, Supabase publishes benchmarks for both index types on the same hardware. These are its numbers for 100,000 OpenAI embeddings on a Medium instance with 4 GB of RAM:

Memory is the real limit. The same guide sizes 1,536-dimension HNSW workloads at about 15,000 vectors on a Micro instance, 100,000 on a Medium and 500,000 on an XL, and reaches 500,000 on a Medium when the vectors are 384 dimensions. Smaller vectors are the cheapest upgrade available, and OpenAI's embedding models take a dimensions parameter for exactly that.

One interaction with step 2: with an approximate index, filters are applied after the index scan, and that includes your RLS policy. A user who owns a small share of the table can get fewer rows than match_count. From pgvector 0.8.0 you can run set hnsw.iterative_scan = strict_order; so the scan keeps going until it has enough. For strict tenant isolation at scale, the pgvector README suggests partitioning by tenant. If you are wondering where Postgres stops being enough, pgvector vs Qdrant vs Pinecone prices all three at a million vectors.

Answer from the retrieved chunks

Step 7: hand the chunks to the model

Retrieval is done; the rest is a normal model call with the chunks as numbered sources. This example uses the Claude API, but any model works.

import Anthropic from '@anthropic-ai/sdk'

const anthropic = new Anthropic() // reads ANTHROPIC_API_KEY

const sources = sections
  .map((s, i) => '[' + (i + 1) + '] ' + s.content)
  .join('\n\n')

const message = await anthropic.messages.create({
  model: 'claude-sonnet-5-5',
  max_tokens: 1024,
  system: 'Answer only from the numbered sources and cite them like [1]. If they do not contain the answer, say so.',
  messages: [{ role: 'user', content: 'Sources:\n' + sources + '\n\nQuestion: ' + question }],
})

Expected result: an answer that cites [1], [2] and so on, or a plain statement that the sources do not cover the question. That second behaviour is the point of RAG, so test it with a question your documents cannot answer. The AI SaaS Builder program builds this same pattern into a product, with per-customer chunks behind RLS and Claude answering from them with citations.

pgvector with Docker: run it locally and migrate cleanly

For a Supabase project, use the Supabase CLI rather than a bare Postgres container. It runs the whole stack locally, including the auth schema and roles that your policies reference.

npx supabase init
npx supabase start                      # the full stack, pgvector included, in Docker
npx supabase migration new rag_schema   # paste the SQL from steps 1, 2, 4 and 6
npx supabase db reset                   # rebuild the local database from migrations
npx supabase link --project-ref <project-id>
npx supabase db push                    # apply the migration to the hosted project

If you only want Postgres with pgvector, for a script or a non-Supabase app, the pgvector project publishes an image:

docker pull pgvector/pgvector:pg18-trixie
docker run --name pgvector -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d pgvector/pgvector:pg18-trixie

Then run CREATE EXTENSION vector; in the database. Three Supabase-specific notes will save you a failed migration:

• The plain image has no auth schema. auth.users, auth.uid() and the authenticated role exist only in the Supabase stack, so the SQL from step 2 will not run on the bare container.
• Mind the schema. Supabase installs the extension into extensions, which is why the type is written extensions.vector and the function pins its search_path. On plain Postgres the type is just vector.
• Keep grants, RLS and tables in one migration. Supabase's RLS guide says they belong together, and it means a table can never reach production exposed.

Troubleshooting

• Dimension mismatch on insert. The column size and the model output differ. Match vector(n) to the model. Changing models means re-embedding every row.
• Zero rows back. Drop match_threshold to 0 to rule it out, then check that the call carries the user's session. Error 42501 means a missing grant, not a policy.
• Fewer rows after adding HNSW. Results are capped by hnsw.ef_search (40 by default) and reduced further by filters. Raise it, or enable iterative scans.
• Fewer rows after adding IVFFlat. The index was probably built on too little data. Drop it and rebuild when the table is fuller.
• The index is ignored. See the callout above. A small table may also be scanned on purpose, because that is faster.
• Cannot index the column. HNSW and IVFFlat index vector up to 2,000 dimensions. For 3,072-dimension embeddings, index a halfvec cast or shorten the embedding.

For where a vector table fits next to chat history and summaries, see AI agent memory options compared. If you would rather wire this up without code, the n8n agent memory guide uses the same pgvector table from a workflow.

pgvector on Supabase: FAQ