RAG on pgvector: three attempts, two silent failures

the answer that was almost right

Two weeks after retrieval went live, someone dropped a screenshot into the team channel. They'd asked about the refund window and got back a confident paragraph from the shipping policy. Not a hallucination. A real chunk, correctly cited, from the right document set — just the wrong part of it.

That's the thing about retrieval nobody puts on the tin. It doesn't throw. It returns eight rows every time, three of them are usually fine, and unless you go looking you'll never find out the other five were noise.

This is the build log for the retrieval layer behind two production backends, one of them openboxzone.com, in the order it happened. Including both times I was wrong.

why pgvector and not a hosted vector database

I looked at the hosted options for about a day and then stopped, for a reason that has nothing to do with benchmarks.

Every chunk belongs to a workspace, a document, and a scope. The agent isn't allowed to see documents outside the scope its run was invoked with — a hard rule, enforced in the same layer that decides which tools it can call. If the vectors live in a different system from the rows, that filter becomes a second-class citizen: you either over-fetch and filter in application code, or you duplicate the authorization model into a metadata filter language that isn't SQL and can't join.

Then the boring half. Postgres was already there, already backed up, already in the migration pipeline. A second store means a second set of credentials and a delete path that has to succeed in two places or you're serving embeddings for documents that no longer exist.

Corpus size settled it. The larger of the two is about 38,000 chunks. That's small. pgvector on a modest RDS instance doesn't notice 38,000 vectors. Under a million, a dedicated vector database is mostly solving a problem you don't have yet and adding one you definitely do.

attempt one: Prisma will not help you here

Prisma has no vector type. There's been an open issue about it longer than I've been using Prisma. What you get is this:

model DocumentChunk {
  id             String   @id @default(uuid()) @db.Uuid
  documentId     String   @map("document_id") @db.Uuid
  workspaceId    String   @map("workspace_id") @db.Uuid
  ordinal        Int
  content        String
  embeddedText   String   @map("embedded_text")
  tokenCount     Int      @map("token_count")
  embeddingModel String?  @map("embedding_model")
  embedding      Unsupported("vector(768)")?
  createdAt      DateTime @default(now()) @map("created_at")

  document Document @relation(fields: [documentId], references: [id], onDelete: Cascade)

  @@index([workspaceId, documentId])
  @@map("document_chunk")
}

Unsupported means exactly what it says. The column exists so migrations know about it, and it does not exist on the generated client type. You can't select it, write it, or filter on it. Prisma also refuses to let the client create a row when an Unsupported column is non-nullable with no default, which is why it's optional up there — right anyway, since chunks exist for a while before they're embedded.

So every read and write against that column drops to raw SQL. Writing means serializing to pgvector's text format and casting:

async storeEmbedding(chunkId: string, vector: number[], model: LlmModel): Promise<void> {
  const literal = `[${vector.join(',')}]`;

  await this.prisma.$executeRaw`
    UPDATE document_chunk
       SET embedding = ${literal}::vector,
           embedding_model = ${model.slug}
     WHERE id = ${chunkId}::uuid
  `;
}

The extension goes into a migration by hand — CREATE EXTENSION IF NOT EXISTS vector; at the top of the first one that touches the column. Prisma's shadow database needs it too, so the Postgres image in CI needs pgvector installed or migrate dev fails with an error that never mentions the shadow database. That cost me an afternoon.

Then migrate diff started proposing to drop the index on every later migration, because it can't see an index it doesn't understand. Keep index DDL out of the schema and own it in hand-written migrations.

The first version worked. No index at all, just the column and an ORDER BY. In dev, against 900 chunks, p95 retrieval was around 40ms, and I shipped it.

what broke: 38,000 sequential comparisons

Production had 38,000 chunks. p95 retrieval went to 1.9 seconds, and since retrieval runs inside the agent's context-building step, the first token of any answer was six seconds out.

Obvious in hindsight. Without an index, pgvector does exact search, which is a sequential scan computing a 768-dimensional distance per row. Correct, and linear. Linear was fine at 900.

attempt two: IVFFlat, and a parameter I guessed

I reached for IVFFlat because it's what the README leads with and the build is fast. It partitions vectors into lists clusters; a query scans only the nearest probes of them.

CREATE INDEX document_chunk_embedding_ivf
    ON document_chunk
 USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 38);

The rule of thumb is rows/1000 for a small corpus. p95 dropped to about 120ms. Ship it.

what broke: the centroids stopped meaning anything

IVFFlat computes its centroids from the rows that exist at the moment you build the index. We were ingesting continuously. The index got built during the backfill against roughly 4,000 chunks, the corpus grew nearly ten times that over the next month, and nobody rebuilt anything, because nothing tells you to.

Recall degraded slowly. Same latency, same eight rows, quietly worse ones.

Second silent failure in this build, and at that point I stopped treating it as bad luck. Retrieval has no natural error surface. Every layer of it — index, embedding model, chunker — fails by returning something plausible.

attempt three: HNSW

HNSW builds a navigable graph incrementally. New rows get inserted into the graph. There's no snapshot of the data distribution to go stale and no reindex job for someone to forget about.

CREATE INDEX CONCURRENTLY document_chunk_embedding_hnsw
    ON document_chunk
 USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

	IVFFlat	HNSW
build time, 38k rows	11s	4m 20s
index size	~120 MB	~310 MB
p95 query	~120ms	~28ms
goes stale as data grows	yes	no
query-time knob	probes	ef_search

The build is twenty times slower and the index is nearly three times bigger. At this corpus size I don't care about either. That's a good trade for an index that doesn't rot.

Two things in that DDL matter more than the numbers.

vector_cosine_ops is an operator class, and the planner will only use the index for the operator that class supports. Query with <-> against a vector_cosine_ops index and Postgres silently ignores the index and sequential-scans. Correct results, slowly, with no warning anywhere. Run EXPLAIN ANALYZE against your real retrieval query and confirm you see Index Scan using ..._hnsw before you believe any latency number you've measured.

And CONCURRENTLY, because four minutes of ACCESS EXCLUSIVE on a hot table is an outage.

the operator

<=> is cosine distance, meaning 1 - cosine_similarity. Smaller is closer, so it's always ORDER BY ... ASC, and a similarity threshold is 1 - (a <=> b) > 0.75, which is easy to get backwards at 11pm.

Cosine rather than L2 because I don't want ranking to depend on vector magnitude. With perfectly unit-normalized embeddings the two give the same ordering and the choice is cosmetic — but "the provider normalizes its output" is a property nobody puts in a contract, and it can change between model versions.

The query, roughly as it runs today:

const literal = `[${queryVector.join(',')}]`;

const hits = await this.prisma.$queryRaw<VectorHit[]>`
  SELECT c.id,
         c.document_id AS "documentId",
         c.content,
         c.embedding <=> ${literal}::vector AS distance
    FROM document_chunk c
    JOIN document d ON d.id = c.document_id
   WHERE c.workspace_id = ${workspaceId}::uuid
     AND c.embedding IS NOT NULL
     AND d.deleted_at IS NULL
     AND d.scope = ANY(${scopes}::text[])
   ORDER BY c.embedding <=> ${literal}::vector
   LIMIT ${limit}
`;

Yes, the distance expression appears twice. I keep it explicit instead of ordering by the alias — same plan either way, but I want it obvious to the next reader which expression the index exists for. hnsw.ef_search needs its own statement, and Prisma won't take two in one tagged template, so it's a $transaction with a SET LOCAL in front.

The WHERE clause hides one more trap. An HNSW scan walks the graph and then the filter is applied to what comes back, so a narrow filter over a wide corpus returns fewer rows than you asked for. A workspace with 200 chunks inside a 38,000-chunk table got two results for LIMIT 8, and the agent answered from two chunks as though that were everything. hnsw.iterative_scan = 'relaxed_order' per transaction fixed it.

the dimension bug

This is the one I'd undo if I could.

Documents were embedded with Gemini text-embedding-004 at its native 768 dimensions. Months later, during a config change to wire a second provider, the query path started resolving to a different embedding model — one that emits 1536 dimensions, configured down to 768, because 768 is what the column wanted.

Every layer said yes. 768 equals 768, so the write passed. <=> computes happily between any two vectors of matching dimension. The index worked. Latency didn't move. And the results were wrong in the way that's hardest to see: two models, two vector spaces, a cosine distance between them that means nothing, and top-k over meaningless numbers still hands you eight plausible-looking rows.

I found it because of that refund screenshot, and only then because I finally sat down and built a labelled set — 60 questions, each with the chunk that should come back. Recall@5 was 0.55. It had been 0.82.

The fix was three things. Re-embed the whole corpus with one model — 18 minutes, batched 100 at a time, cheaper than lunch. Store embedding_model on every chunk row and refuse to run a vector search when the query model doesn't match what the corpus was built with. And put the declared dimension on the model record, checked at write time:

if (vector.length !== model.embeddingDim) {
  throw new EmbeddingDimensionMismatchError({
    model: model.slug,
    expected: model.embeddingDim,
    received: vector.length,
  });
}

That guard would not have caught this bug. Both sides were 768. The corpus-model check is the one that would have, and it exists because the dimension check turned out to be the easy half of the problem.

Recall@5 today is 0.84.

chunking is where the recall actually lives

Everything above bought me about 90 milliseconds. Chunking bought me twenty points of recall, and I did it last.

Where it landed:

• Split on structure before size. Headings, list boundaries, paragraph breaks. A table never gets split across chunks; if it's too big it becomes its own chunk and eats the budget.
• Pack to a token budget, not a character count. About 500 tokens with 80 of overlap. Character counts drift badly across languages and code blocks.
• Prefix every chunk with its document title and heading path before embedding. That alone moved recall@5 by nine points. A chunk reading "must be requested within 14 days of delivery" is close to unretrievable alone; the same text with Returns and Refunds > Eligibility in front of it lands.

That's why the table has both content and embedded_text. The embedding sees the prefixed version, the model sees the clean one. Breadcrumbs read as document text make for worse answers — I noticed because a summary came back describing the heading path as a section of the policy.

the lexical fallback, on purpose

The agent must not break because retrieval is unavailable. The retriever is called from inside the plan step of a loop that's already running, and a throw there kills a run someone is watching.

What's actually taken vector search out: a local Postgres without the extension, the embedding provider rate-limiting a burst backfill, a workspace whose chunks hadn't finished embedding, and one config typo pointing at a model with no API key.

async retrieve(q: RetrieveQuery): Promise<RetrievalResult> {
  try {
    const vector = await this.embeddings.embedQuery(q.text, q.model);
    const hits = await this.vectorSearch(q, vector);
    if (hits.length > 0) return { strategy: 'vector', hits };
  } catch (err) {
    if (!isRecoverableRetrievalError(err)) throw err;
    this.logger.warn(
      { err, workspaceId: q.workspaceId },
      'vector retrieval unavailable, falling back to lexical',
    );
  }

  return { strategy: 'lexical', hits: await this.lexicalSearch(q) };
}

Lexical is Postgres full text — websearch_to_tsquery and ts_rank_cd over a stored tsvector, with the same workspace and scope predicates, because the authorization rules don't get to be best-effort.

Recall@5 on lexical alone is about 0.51 against 0.84 for vector. Clearly worse. Also clearly not zero, and the run finishes.

The part I'd argue for hardest is strategy in the return type. It goes into the run transcript, and it's a metric — fallback rate sits on a dashboard with an alert on it. A fallback nobody can see is just an outage with better manners.

what I'd do differently

Write the evaluation set first. Sixty questions and their expected chunks took an afternoon, and both silent failures here — stale IVFFlat centroids, mismatched embedding model — would have surfaced in a day instead of a month. I built it as a forensic tool after the second incident. It should have been the first commit.

What I still don't have a good answer for is multi-turn queries. "What about the other one?" embeds to nothing useful. The agent currently rewrites the question into a standalone one during its plan step, which works maybe seven times in ten and fails, once again, as a slightly-off answer rather than an error. Reranking is the obvious next thing to try. I haven't, mostly because I want the eval set a lot bigger before I trust it to tell me whether a reranker helped.