pgvector

You shipped a RAG feature on pgvector because the math was simple: one Postgres extension, one `vector` column, one `<=>` operator, and retrieval lives in the database you already run. Then production traffic arrived. The agent keeps returning the right paragraph from the wrong document. It surfaces a draft article that was never published. It cites a product variant that was discontinued in March. The cosine distance is correct every time. The answer is still wrong, because the query had a structural component your embeddings can't see.

Sanity Context is Sanity's agent-facing product. Its primary surface today is Context MCP, a hosted, read-only MCP endpoint that exposes schema reads, GROQ queries, reference traversal, and optional semantic search across a Sanity dataset. Knowledge Bases is the second surface, for unstructured sources like PDFs, support exports, and websites. It does not replace your Postgres. It sits next to it and handles the part of retrieval that is editorial and structured rather than a pile of undifferentiated chunks.

This article is honest about the build-it-yourself pgvector and Supabase stack: what it does well, where it quietly breaks, and the specific failure mode (structure-aware retrieval over governed content) where a different tool earns its place in the stack.

The pgvector + Supabase stack, and why developers reach for it

The appeal is real and worth naming before we poke holes in it. pgvector adds a `vector` type and three distance operators to Postgres: `<->` for L2, `<=>` for cosine, and `<#>` for inner product. Supabase ships it as a one-click extension, gives you `pgvector` behind the same connection string as your application tables, and adds an HNSW index so approximate nearest-neighbor search stays fast as the table grows. You write one `ORDER BY embedding <=> $1 LIMIT 10` and you have retrieval. No second system to provision, no sync job, no eventual-consistency window between your source of truth and your index.

For a corpus that is genuinely unstructured and self-contained, this is the right tool. Support transcripts, log lines, scraped documentation, machine-generated content at volume: chunk it, embed it, store the vector next to the text, and let cosine distance do the work. The data lives in the same transaction boundary as everything else, so a single migration keeps your embeddings consistent with your rows.

The trouble starts when the corpus is not a flat pile of text. The moment your documents have authors, publication states, categories, effective dates, or references to other documents, the query a user actually asks stops being purely semantic. "What did our security team publish about SSO last quarter" is two predicates (team, date range) and one semantic match. pgvector handles the third. The first two are your problem to bolt on, and that bolt-on is where most build-it-yourself stacks accumulate their bugs.

-- enable once
create extension if not exists vector;

create table documents (
  id bigserial primary key,
  title text,
  body text,
  embedding vector(1536)
);

create index on documents
  using hnsw (embedding vector_cosine_ops);

-- the query your agent runs every turn
select id, title, body
from documents
order by embedding <=> $1
limit 10;

Where pure vector search returns the wrong document

Here is the failure mode in slow motion. A user asks your agent, "What's our current refund policy for enterprise plans?" You embed the question, run the `<=>` search, and get back ten chunks ranked by cosine distance. Three of them are real refund-policy text. But the top result is an old policy document that was superseded last year and never deleted, only marked `status = 'archived'` in a column your vector query never looks at. The embedding of the archived text is just as close to the question as the live version. Distance has no opinion about publication state.

The naive fix is a metadata filter: add `where status = 'published'` to the query. That works until the filter and the index fight each other. With an HNSW index, Postgres builds the approximate-nearest-neighbor graph over the whole table, then filters the results. If only a small fraction of rows match your `where` clause, the index can return ten neighbors that all get filtered out, and you silently get fewer than ten results, or zero. This is the well-known post-filtering problem, and the usual workarounds (partial indexes per status, or `iterative` scan modes in newer pgvector) each carry their own maintenance cost.

Stack a few of these predicates together (status, plan tier, locale, effective date) and you are writing increasingly defensive SQL, tuning index strategies per filter combination, and still getting brittle results. The embeddings were never the hard part. The structure around them is.

Combining structured filters and embeddings: how far you can push it in SQL

You can get a long way in raw SQL, and you should know exactly how far before reaching for anything else. The honest pattern for hybrid retrieval in pgvector is: put your structural predicates in the `where` clause, combine a keyword signal with a semantic signal in the `order by`, and weight them. Postgres gives you `ts_rank` from full-text search for the keyword side and `1 - (embedding <=> $1)` for the semantic side. You normalize both into a 0-1 range and add them with a tunable alpha.

This is real hybrid search, and for a structured corpus it beats pure vector distance most of the time, because keyword match catches exact product names, SKUs, and error codes that embeddings smear together. The cost is that you now own the scoring function, the normalization, the alpha tuning, and the index strategy for both the GIN full-text index and the HNSW vector index. None of it is hard in isolation. All of it is yours to maintain, debug, and re-tune every time the content model changes.

The deeper limit is reference traversal. "Find articles by authors on the security team" requires a join from documents to authors to teams. "Find the canonical version of any document that has a newer revision" requires the query to understand a self-reference. You can express these as joins, but now your retrieval logic is a growing pile of SQL that encodes your editorial model, living far away from where editors actually manage that model.

select id, title, body,
  (0.5 * ts_rank(to_tsvector('english', body),
                 plainto_tsquery('english', $2))
   + 0.5 * (1 - (embedding <=> $1))) as score
from documents
where status = 'published'
  and plan_tier = 'enterprise'
  and effective_date <= now()
order by score desc
limit 10;

The routing decision: which content belongs where

Before reaching for another tool, draw the line clearly, because the answer is not "move everything off pgvector." The right split is by content type and who owns it.

High-volume, machine-generated, or genuinely flat corpora belong in pgvector and Supabase. Log analytics, embedding caches, scraped pages you never edit by hand, per-user document piles in the millions: a dedicated vector store inside your own Postgres is the correct call. There is no editorial workflow, no human reviewing publication state, no reference graph worth modeling. Keep it where it is.

Structured, governed content is the other case, and it is where Sanity Context fits. If your documents have a schema, if humans approve them before they go live, if they reference each other, and if "which version is current" is an editorial decision rather than a timestamp, then the structure is the product and your SQL is a lossy copy of it. Sanity Context exposes that content to your agent through Context MCP, a hosted read-only MCP endpoint, so the agent queries the real content model (with publication state, references, and types intact) instead of a denormalized embedding table you have to keep in sync.

For unstructured sources that still need governance (policy PDFs, support-database exports, marketing sites), the second surface, Knowledge Bases, turns that mess into ordered documents with a table of contents the agent can navigate. The routing rule: structured content goes through GROQ retrieval, unstructured-but-governed content goes through Knowledge Bases, and flat machine data stays in pgvector.

Hybrid retrieval as one GROQ query, not a hand-rolled scorer

The pattern you hand-rolled in SQL (structural predicates, plus a keyword signal, plus an optional semantic signal, combined into a single score) is expressed in GROQ as one query. The structural filters stay where they belong, as predicates inside the `*[ ... ]` brackets. The keyword and semantic signals go inside a `score()` pipeline, and `order(_score desc)` ranks the result.

The operators are specific, so use them exactly: `text::query($queryText)` with `match` for the BM25 keyword signal, `text::semanticSimilarity($queryText)` for the optional semantic signal (it takes the query text, not an embedding field), `boost()` to weight a clause, and `score()` to combine them into `_score`. Note what you are not doing: you are not normalizing two ranges by hand, you are not tuning an alpha in application code, and you are not maintaining two indexes whose drift you have to reason about. The structural predicate (`status == "published"`) is a real filter on the real content model, not a post-filter fighting an ANN graph.

Via Context MCP your agent gets these as schema-aware tools without writing the query at all. The MCP server advertises the dataset's types, so the agent can resolve "enterprise refund policy" against the actual `policy` document type with its `planTier` and `status` fields, traverse the reference to the canonical version, and return governed content. Writes never happen here, the endpoint is read-only by design, which is exactly the property you want when an autonomous loop is doing the querying.

*[_type == "policy" && status == "published" && planTier == "enterprise"]
  | score(
      boost(title match text::query($queryText), 3),
      body match text::query($queryText),
      text::semanticSimilarity($queryText)
    )
  | order(_score desc)[0...10]
  { _id, title, body, _score }

Wiring it into an agent: keep Supabase, add the MCP endpoint

You do not rip out pgvector to do this. The realistic production shape is a split: Supabase keeps the flat, high-volume vectors and your application tables, and the agent attaches Context MCP as an additional tool source for the governed content. Most agent frameworks (the Vercel AI SDK, Mastra, LangGraph) accept an MCP server as a first-class tool provider, so the integration is a connection, not a rewrite.

If you want full control of the query rather than the auto-generated MCP tools, the second path is a thin custom tool that runs a typed GROQ query through the client. You write the hybrid query once, expose it as a tool the model can call, and you still get publication-state filtering and reference traversal for free from the content model. The example below uses `next-sanity` because most readers are deploying on Next.js, but the same `@sanity/client` call works in any Node, Bun, or Deno runtime over the HTTP GROQ API.

The operational win is the part that does not show up in a benchmark: when an editor unpublishes a policy in the Studio, the agent stops returning it on the next query, with no re-embedding job, no sync worker, and no stale-vector window. The content model is the source of truth, and "current" is whatever the editorial team says it is. That is the gap a hand-rolled pgvector index cannot close, because the index has no concept of who approved what.

import { createClient } from 'next-sanity'
import { tool } from 'ai'
import { z } from 'zod'

const sanity = createClient({
  projectId: process.env.SANITY_PROJECT_ID!,
  dataset: 'production',
  apiVersion: '2024-10-01',
  useCdn: false,
})

export const retrievePolicy = tool({
  description: 'Search published enterprise policy content',
  parameters: z.object({ queryText: z.string() }),
  execute: async ({ queryText }) => {
    const query = `*[_type == "policy" && status == "published" && planTier == "enterprise"]
      | score(
          boost(title match text::query($queryText), 3),
          body match text::query($queryText),
          text::semanticSimilarity($queryText)
        )
      | order(_score desc)[0...10]{ _id, title, body, _score }`
    return sanity.fetch(query, { queryText })
  },
})

An honest verdict

pgvector and Supabase are a good default and a bad universal answer. For flat, high-volume, machine-owned text where retrieval really is "find the nearest chunk," keeping vectors in your own Postgres is the cheapest correct option, and adding another system would be over-engineering. Be honest with yourself about whether your corpus is actually that, though, because most product content is not.

The tell that you have outgrown the build-it-yourself stack is not performance. HNSW is fast. The tell is that your retrieval bugs are increasingly structural: wrong publication state, stale versions, missing reference joins, defensive `where` clauses fighting your index, and a scoring function you re-tune every content-model change. Those are symptoms of encoding an editorial model in SQL that lives somewhere else. At that point the work you are doing is content modeling, and you are doing it in the wrong place.

Sanity is the Content Operating System for the AI era, the intelligent backend for teams building AI content operations at scale, and Sanity Context is the agent-facing product that exposes that governed content model to your loop. The pragmatic move is not migration, it is composition: Supabase for the flat vectors, Context MCP for structured governed content, and Knowledge Bases for the unstructured sources that still need a human in the approval path. Route by content type, keep each tool doing the one thing it is genuinely good at, and stop asking cosine distance to know what "published" means.