RAG on Supabase pgvector: no vector database required

Every AI-chatbot architecture diagram seems to include a dedicated vector database. Ours doesn't — and it serves paying customers in production every day. This post is the honest engineering story: how the pipeline works, the actual numbers, and the point where our approach would stop scaling (because it does have one).

The stack, in one paragraph

When a customer connects their website, we crawl it, split the content into 1,000-character chunks with 200-character overlap, embed each chunk with OpenAI's text-embedding-3-small (1,536 dimensions), and store the vectors in a pgvector column — in the same Supabase Postgres database that holds users, bots, and conversations. At question time we embed the query, run a cosine-similarity search (top 5, minimum similarity 0.3) scoped to that bot's chunks, and hand the matches to the language model as context. That's the whole retrieval layer.

The contrarian part: exact KNN, no ANN index

We don't use an HNSW or IVFFlat index. Every retrieval is an exact nearest-neighbor scan over the bot's chunks. That sounds wrong to anyone who's read a vector-database benchmark — until you look at the actual workload:

• Our whole production knowledge base is currently ~5,000 chunks across ~1,300 sources — and retrieval is always scoped to one bot, so a single query scans a few hundred to a few thousand vectors, not millions.
• At 1,536 dimensions, an exact scan over a few thousand vectors is arithmetic a modern CPU shrugs at. We measured the full round-trip — query embedding already in hand, over the public internet from a laptop: median 237ms, best 163ms. To be clear about what that measures: it's dominated by network, which is exactly the point — even with the laziest possible benchmark, the exact scan never becomes the number you notice.
• Exact KNN has perfect recall by definition. Every approximate index trades recall for speed — and in customer support, the chunk you fail to retrieve is the answer your customer doesn't get. At our scale the speed trade buys nothing, so paying recall for it would be pure loss.

There's also a practical reason: managed Postgres providers limit which knobs you can turn (we can't tune hnsw.ef_search per-query on Supabase, for instance). An index you can't tune is a black box in the middle of your retrieval quality — we'd rather have the boring, predictable scan.

What Postgres gives you for free

The "just use Postgres" argument isn't only about avoiding another bill. Keeping vectors next to the business data means:

• No sync pipeline. The classic vector-DB failure mode is drift: a source got deleted but its vectors didn't. Our chunks live in the same database as the sources, tied by foreign keys — delete the source, the chunks go with it, transactionally.
• Row-level security is enabled on the vector table like any other, guarding dashboard and admin reads. (Honesty note: the public chat path runs under the service role, which bypasses RLS — there, per-bot isolation is an explicit bot_id filter inside the search function. RLS is the safety net, not the hot path.)
• One backup, one migration story, one thing to monitor. For a small team, every component you don't run is a component that can't page you.

Where retrieval actually gets hard (hint: not the database)

Running this in production taught us that retrieval quality problems live above the vector search:

• Users don't write queries; they write fragments. "price?", "0445116013", a sentence in Turkish with a product code in the middle. We run a query-rewrite step before embedding, so the thing we search with is a well-formed question even when the input isn't.
• Top-5 can be five copies of the same paragraph. Crawled sites repeat themselves (headers, product boilerplate). We diversify results so the context window carries five different pieces of information, not one piece five times.
• Similarity scores need calibration, not vibes. With text-embedding-3-small, cosine similarities cluster in a narrow band — a match at 0.42 is strong, one at 0.32 is weak. We calibrated our confidence thresholds against that real distribution instead of textbook 0.8/0.9 values that this embedding model essentially never produces.
• The model must be allowed to say "I don't know." When nothing clears the threshold, our bot doesn't improvise — it returns a configurable fallback and logs the question as a knowledge gap for the owner to fill. A retrieval layer with perfect recall still can't retrieve what was never written down.

When we'd change our answer

Honesty section: exact KNN stops being the right call when a single bot's corpus reaches the high tens of thousands of chunks — a scan over 100k+ vectors per query starts costing real milliseconds, and an HNSW index earns its recall trade. Our largest single bot is roughly two orders of magnitude below that line. When a customer crosses it, the fix is adding one index to one column — not migrating databases. That's the position we optimized for: the boring architecture with a known upgrade path, instead of the scalable one with a permanent operational tax.

If you're building RAG for workloads like ours — many small, isolated corpora rather than one giant one — you probably don't need a vector database either. You need good chunking, query rewriting, honest thresholds, and a bot that admits what it doesn't know.

You can interrogate the result: our production bots are live on real businesses right now. Ask them something their websites don't answer, and watch them decline to make it up — that refusal is most of what this post was about. Then start free and point one at your own site, or read what RAG means for your support quality from the buyer's side.