pgvector on Supabase: Building a RAG Pipeline on the Free Tier

pgvector turns a Supabase project into a vector store without adding a second system to your stack. Enable one extension, add a column, create an index, and the same Postgres that holds your users and content can answer similarity queries. This post walks through building a retrieval-augmented generation (RAG) pipeline on that foundation, and accounts honestly for how far the free tier carries you before the numbers stop working.

If the concepts are new, our explainer on retrieval-augmented generation covers why grounding a model in your own data beats fine-tuning, the piece on vector databases explains what similarity search is doing, and our What Is Supabase? pillar covers the platform.

The Five Moving Parts

A RAG pipeline is smaller than it sounds. Five steps, in order:

• Chunk: split documents into passages small enough to embed and specific enough to retrieve.
• Embed: send each chunk to an embedding model and get back a fixed-length array of floats.
• Store: write the chunk text and its vector into a Postgres table.
• Retrieve: embed the question, then find the nearest stored vectors by distance.
• Generate: paste the retrieved passages into the prompt and let the model answer from them.

Supabase handles storage and retrieval. The embedding and generation calls go out to a model provider.

Enabling the Extension and Designing the Table

Vectors in Supabase are provided by pgvector, an open-source Postgres extension. Enable it from the Database section of the dashboard under Extensions, or in SQL:

create extension if not exists vector with schema extensions;

create table documents (
  id bigint generated by default as identity primary key,
  source_id text not null,
  content text not null,
  embedding extensions.vector(1536)
);

The number in parentheses is the dimension count, and it has to match your embedding model exactly. OpenAI’s text-embedding-3-small returns 1536 dimensions and text-embedding-3-large returns 3072, and both accept a dimensions parameter that shortens the output (OpenAI embeddings guide). Open models run smaller: gte-small, used in Supabase’s own examples, produces 384.

Smaller is worth taking seriously. Each vector value costs 4 * dimensions + 8 bytes, so a 1536-dimension embedding is about 6.0 KB, since each vector costs four bytes per dimension plus eight against 1.5 KB for a 384-dimension one. Supabase’s 2023 benchmark found a 384-dimension model beating a 1536-dimension one by 78 percent on queries per second at matched accuracy, on roughly half the RAM (Supabase engineering blog, August 2023). On a constrained instance, dimension count is the biggest lever you control.

Keep the chunk text in the same row as its vector, and add the metadata columns you will filter on later, such as a document ID or a tenant ID. Turn on Row Level Security before the table sees real data: any table in the public schema is reachable through the auto-generated REST API with the published anon key, as covered in the Supabase Auth deep dive.

Chunking Decides What Retrieval Can Find

Chunking is the step teams skip and then blame the model for. The chunk is the unit of retrieval: too large buries the relevant sentence in noise, too small loses the context that made it meaningful.

Neither the pgvector nor the Supabase documentation prescribes a size, because the right answer depends on your corpus. Splitting on document structure beats splitting on a fixed character count: break at headings, then at paragraphs, and fall back to a hard token limit only when a section runs long. Passages of 200 to 500 tokens with a small overlap are a reasonable starting point for prose documentation. Test against real questions before committing, because re-embedding a corpus later costs money and time.

Choosing an Index: HNSW or IVFFlat

Without an index, pgvector does exact nearest-neighbour search: perfect recall, full table scan, latency that grows with the row count. An index switches you to approximate search, trading a little accuracy for a lot of throughput.

pgvector supports exactly two index types, and Supabase recommends HNSW by default (Supabase vector indexes documentation).

• HNSW: a multilayer proximity graph. Better speed-to-recall than IVFFlat, slower to build, uses more memory. Crucially, it can be created on an empty table, because there is no training step, and it does not need a rebuild as rows are added.
• IVFFlat: divides vectors into lists and searches the lists nearest the query. Faster to build and lighter on memory, but weaker query performance, and it must be built after the table holds representative data because it trains on that data.

That last difference decides most cases. An IVFFlat index built on a near-empty table gives poor recall, and a growing corpus eventually needs a rebuild. HNSW has no training step, so it suits an incremental ingest pipeline. Build parameters default to m = 16 and ef_construction = 64, with search controlled by hnsw.ef_search (default 40). Raise them for recall and accept slower builds and queries.

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

Pick IVFFlat when the instance is memory-starved, the data is static, and build time matters more than query latency. Otherwise start with HNSW. Skipping the index entirely on a few thousand rows is defensible too: a sequential scan gives full accuracy and is not RAM bound.

Generating and Storing Embeddings

The write path is ordinary application code:

const { data } = await openai.embeddings.create({
  model: 'text-embedding-3-small',
  input: chunk.content,
})

await supabase.from('documents').insert({
  source_id: chunk.sourceId,
  content: chunk.content,
  embedding: data[0].embedding,
})

Batch the calls, because providers charge per token and per round trip. Store a hash of the chunk text so a re-ingest can skip unchanged content instead of re-embedding everything. And never mix embeddings from two models in one column: distances between them are meaningless.

The Similarity Search Query

Three distance operators matter for text embeddings stored in a vector column, each with a matching index operator class, though pgvector ships more than three in total:

Cosine distance is the safe default for text embeddings. If your model returns normalised vectors, inner product computes the same ranking with less work, which is why Supabase recommends it in that case. Whichever you choose, the index has to be built for that operator; an index created with vector_cosine_ops does nothing for a query written with <->.

Supabase client libraries reach Postgres through PostgREST, which does not support pgvector operators, so the query goes in a database function called over RPC:

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

Note the order by. It sorts on the raw distance operator ascending, not on the derived similarity column. Sorting on the computed column, or descending, makes the planner ignore the index and fall back to a scan. This is the most common reason a query that looks correct runs like it has no index at all.

Filtering brings a second subtlety. With an approximate index, the filter applies to the candidates the index returns, so a selective where clause can leave fewer rows than your LIMIT. From pgvector 0.8.0, iterative index scans keep scanning until enough results are found, controlled by hnsw.iterative_scan, off by default.

Wiring Retrieval into the Model Call

Retrieval and generation stay decoupled. Embed the question with the model used for ingest, call the function, build the prompt:

const { data: matches } = await supabase.rpc('match_documents', {
  query_embedding: questionEmbedding,
  match_threshold: 0.78,
  match_count: 8,
})

const context = matches.map((m) => m.content).join('nn')
const answer = await model.messages.create({
  messages: [{ role: 'user', content: `Context:n${context}nnQuestion: ${question}` }],
})

Two knobs matter. match_threshold is the floor below which a passage is not worth retrieving, and it is application specific; 0.78 is the value in Supabase’s documentation example, not a universal constant. match_count controls how much text lands in the prompt, and every retrieved passage is billed as input, which is where the economics of input and output tokens bite. Retrieving 20 chunks when 5 would answer the question is a recurring, avoidable cost.

What the Free Tier Actually Covers

Verified on the Supabase pricing page as of July 26, 2026, the free plan gives each project 500 MB of database storage on shared CPU with 500 MB of RAM, 5 GB of egress, 1 GB of file storage, unlimited API requests, 50,000 monthly active users, and 500,000 Edge Function invocations. You get two active projects, no automatic backups, one day of log retention, and community support. Free projects pause after one week of inactivity.

Run the storage math before you plan around it. At 1536 dimensions, each vector is roughly 6.2 KB, so 500 MB holds roughly 85,000 embeddings before you count the chunk text, the primary key, index structures, and everything else in the database. A realistic ceiling is somewhere in the tens of thousands of chunks. Drop to 384 dimensions and the same budget stretches roughly four times further.

Memory is the tighter constraint. Supabase’s compute sizing benchmarks start at the Micro instance: 1 GB of RAM, measured at 15,000 vectors of 1536 dimensions with an HNSW index. The free instance has half that memory. An HNSW index that does not fit in RAM still works, but it pages against disk and latency climbs.

When You Have to Upgrade

The free tier is a real development and demo environment, and for an internal knowledge base of a few thousand documents it can be the production one too. Four things push you off it: a corpus past roughly 10,000 chunks at 1536 dimensions, since memory binds well before disk does, a latency target a shared 500 MB instance cannot hold, an inactivity pause you cannot tolerate, or the absence of backups on data you cannot re-derive. The last is usually decisive for anything customer-facing.

The next step is the Pro plan at $25 per month: 8 GB of disk per project, seven days of daily backups, no inactivity pause, and $10 in compute credits covering one Micro instance. Compute scales separately from there, and vector workloads hit RAM limits long before disk limits, so budget for instance size rather than the storage line.

Frequently Asked Questions