Supabase pgvector for RAG: The Complete Production Setup

Most RAG tutorials get you to a working similarity search in 20 minutes and then leave you to figure out why it's slow, expensive, and returning the wrong chunks in production. This is the version that fills the gap — what I set up in FounderRadar to make retrieval actually reliable.

Why pgvector in Supabase instead of a dedicated vector database#

The argument for a dedicated vector DB (Pinecone, Qdrant, Weaviate) is retrieval performance at enormous scale. The argument against it, for a early-stage SaaS, is that you've now got a second database to operate, pay for, keep in sync, and reason about. If you're already on Supabase, your document metadata, user records, and embeddings can live in the same Postgres instance — with the same auth, the same backups, and the same RLS policies.

pgvector is not the right tool if you're indexing millions of high-dimensional vectors and need sub-10ms retrieval at that scale. For the 99% case — tens to hundreds of thousands of chunks, queries that take under 100ms — it's completely fine, and the operational simplicity is a genuine advantage.

Setting up the extension and table#

Enable pgvector in your Supabase dashboard (Extensions → pgvector) or via SQL:

create extension if not exists vector;

Then create your documents table. The shape I've settled on:

create table documents (
  id          uuid primary key default gen_random_uuid(),
  content     text not null,
  embedding   vector(1536),
  metadata    jsonb default '{}',
  source      text,
  created_at  timestamptz default now()
);

A few decisions embedded here: vector(1536) matches OpenAI's text-embedding-3-small and text-embedding-ada-002 output dimensions. If you're using a different model (Anthropic doesn't publish embeddings; Cohere and Mistral have different dimensions), adjust accordingly. The metadata jsonb column is where I store things like the source document's title, page number, chunk index, and any other fields that filter retrieval — keeping them in Postgres means you can filter before or after the vector search using normal SQL.

Indexing strategy#

Without an index, pgvector does an exact nearest-neighbour scan — correct, but O(n) in the number of vectors. Once you have more than a few thousand rows, you need an index.

pgvector supports two index types: IVFFlat and HNSW. HNSW is the better default today — faster query times, no need to specify the number of lists upfront, and better recall at the cost of slower build times and more memory.

create index on documents using hnsw (embedding vector_cosine_ops);

Use vector_cosine_ops for cosine similarity (what most embedding models are designed for), vector_l2_ops for L2/Euclidean distance, or vector_ip_ops for inner product. Cosine is almost always what you want.

One thing to get right: build the index after bulk-inserting your initial data, not before. Inserting rows one by one into an HNSW index is slow. Insert in bulk, then create the index.

The similarity search function#

Wrap your search in a Postgres function so you can call it from your application without raw SQL in your app code, and so you can apply RLS rules cleanly:

create or replace function match_documents(
  query_embedding  vector(1536),
  match_threshold  float default 0.7,
  match_count      int default 5
)
returns table (
  id        uuid,
  content   text,
  metadata  jsonb,
  similarity float
)
language sql stable
as $$
  select
    id,
    content,
    metadata,
    1 - (embedding <=> query_embedding) as similarity
  from documents
  where 1 - (embedding <=> query_embedding) > match_threshold
  order by embedding <=> query_embedding
  limit match_count;
$$;

Call it from your app via Supabase's rpc():

const { data, error } = await supabase.rpc('match_documents', {
  query_embedding: embedding,
  match_threshold: 0.7,
  match_count: 5,
});

The <=> operator is cosine distance; 1 - distance gives cosine similarity. The threshold of 0.7 is a starting point — tune it by looking at actual retrieval results for your content. Too low and you get irrelevant chunks; too high and you miss relevant ones.

Chunking decisions#

Chunking is where RAG pipelines either work or don't, and it's mostly a domain problem rather than a technical one.

The decisions that matter:

• Chunk size: 512–1024 tokens is a common range. Smaller chunks retrieve more precisely but lose context; larger chunks retain context but retrieve noisily. For structured documents (docs, knowledge base articles), I lean toward 512 with 10% overlap. For free-form text (transcripts, emails), larger chunks often work better.
• Overlap: Always overlap adjacent chunks by 10–20%. A question that falls on a chunk boundary shouldn't be unanswerable because you sliced exactly there.
• Semantic vs fixed-size chunking: Fixed-size with overlap is fine for most content. If you have a document with distinct sections (a legal contract, a technical spec), chunking at section boundaries and embedding the section title as context is worth the extra effort.
• Metadata per chunk: Store the parent document ID, chunk index, and the title/heading that chunk lives under. That context is cheap to include and makes retrieval results far easier to debug.

Embedding in bulk vs on-demand#

For a corpus you control (your documentation, your product knowledge base), embed everything upfront in a batch job and store the vectors. For user-uploaded content, embed on upload — trigger an async job when a document lands, chunk it, embed each chunk, and store.

Don't embed at query time for the knowledge base. The only thing you embed at query time is the user's query itself, which is one call to the embedding model per search — cheap.

Cache embedding calls where you can. If users often ask similar questions, the query embedding is the same and you can cache the embedding (and even the results) at the application layer.

Production gotchas#

Dimension mismatch: If you switch embedding models mid-project, your old vectors and new vectors are incompatible — you can't compare them. Track which model produced each embedding in your metadata column, and re-embed when you change models.

RLS on the documents table: If your RAG content is multi-tenant (each user's uploaded documents are private), apply RLS to the documents table and make sure your match_documents function runs with security invoker so RLS policies are respected by the calling user's role.

Index drift: Very high insert rates on an HNSW index can cause drift. vacuum analyze documents periodically; in Supabase's managed environment this is handled, but be aware of it on self-hosted.

FAQ#

Which embedding model should I use? OpenAI's text-embedding-3-small is the pragmatic choice — cheap, well-documented, 1536 dimensions. If you need multilingual support, consider Cohere's multilingual embed model.

How many chunks should I retrieve per query? Start at 5. That's usually enough context for the LLM to answer well without overloading the context window. Retrieve more only when you've confirmed 5 isn't sufficient for your use case.

Can I filter by metadata before the vector search? Yes — add a where metadata->>'source' = $1 clause to the search function. Pre-filtering on metadata reduces the effective search space and improves speed. Just make sure the filtered column is indexed with a standard Postgres B-tree index.

Is pgvector fast enough for my use case? For under a million vectors with HNSW, almost certainly yes. Run explain analyze on your search queries in production — if they're under 100ms, you're fine.