Building Scalable RAG Pipelines with Next.js and pgvector

Every agency client I have spoken to this year wants "ChatGPT, but for our documentation". What they actually need is a retrieval system that finds the right three paragraphs out of forty thousand, and only then an LLM to phrase the answer. This is the architecture I now use for that, built on Next.js and PostgreSQL, after getting it wrong twice.

The first version I shipped for a client knowledge base used a hosted vector database, naive 500-token chunks and a single cosine-similarity query. It demoed well and failed in production: exact product codes were never retrieved (embeddings blur numbers), long policy pages were split mid-sentence, and every question cost two round-trips before the user saw a single token. The version below fixes all three.

The pipeline at a glance

Retrieval-Augmented Generation has two independent halves: an offline ingestion pipeline that turns documents into searchable chunks, and an online query pipeline that turns a question into a grounded answer. Keeping them separate is the single most important architectural decision, because they scale, fail and get tuned differently.

Step 1: Schema design with pgvector

Postgres with the pgvector extension handles the vector side, and its built-in full-text search handles the keyword side. One database, one backup strategy, one set of credentials. For most projects under ten million chunks this is faster to operate than adding Pinecone or Weaviate, and you keep relational joins to your existing tables (users, permissions, tenants).

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id          BIGSERIAL PRIMARY KEY,
  source_url  TEXT NOT NULL,
  title       TEXT NOT NULL,
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  content_hash TEXT NOT NULL UNIQUE          -- skip re-embedding unchanged docs
);

CREATE TABLE chunks (
  id          BIGSERIAL PRIMARY KEY,
  document_id BIGINT REFERENCES documents(id) ON DELETE CASCADE,
  position    INT NOT NULL,                  -- order within the document
  heading     TEXT,                          -- nearest H2/H3, prepended at embed time
  content     TEXT NOT NULL,
  token_count INT NOT NULL,
  embedding   VECTOR(1536) NOT NULL,         -- text-embedding-3-small
  tsv         TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', coalesce(heading,'') || ' ' || content)) STORED
);

-- HNSW gives sub-10ms approximate nearest-neighbour search at this scale.
CREATE INDEX chunks_embedding_idx ON chunks USING hnsw (embedding vector_cosine_ops);
CREATE INDEX chunks_tsv_idx       ON chunks USING gin (tsv);
CREATE INDEX chunks_document_idx  ON chunks (document_id, position);

Two details matter here. The content_hash column lets the ingestion job skip documents that have not changed, which turns a nightly re-index from a $40 embedding bill into a few cents. The generated tsv column means keyword search stays in sync automatically without a second write path.

Step 2: Chunking that respects structure

Fixed-size chunking is the reason most RAG demos disappoint. A 500-token window cuts tables in half and separates a heading from the paragraph that explains it. The approach that consistently works for documentation, blog content and policy documents is heading-aware recursive splitting: split on H2, then H3, then paragraphs, and only fall back to a hard token limit when a single paragraph is too long. Each chunk is then prefixed with its heading path before embedding, so the vector carries context the raw text lacks.

// lib/chunk.ts
import { encode } from 'gpt-tokenizer';

const MAX_TOKENS = 400;   // sweet spot for text-embedding-3-small
const OVERLAP    = 40;    // tokens of overlap between adjacent chunks

export interface Chunk { heading: string; content: string; tokenCount: number; }

export function chunkMarkdown(md: string, title: string): Chunk[] {
  const sections = md.split(/^(?=#{2,3}\s)/m);         // split at H2/H3 boundaries
  const chunks: Chunk[] = [];

  for (const section of sections) {
    const headingMatch = section.match(/^#{2,3}\s+(.+)$/m);
    const heading = headingMatch ? `${title} › ${headingMatch[1].trim()}` : title;
    const body = section.replace(/^#{2,3}\s+.+$/m, '').trim();
    if (!body) continue;

    const paragraphs = body.split(/\n{2,}/);
    let buffer: string[] = [];
    let bufferTokens = 0;

    const flush = () => {
      if (!buffer.length) return;
      const content = buffer.join('\n\n');
      chunks.push({ heading, content, tokenCount: encode(content).length });
      // keep the tail as overlap for the next chunk
      const tail = buffer[buffer.length - 1];
      buffer = encode(tail).length <= OVERLAP ? [tail] : [];
      bufferTokens = buffer.length ? encode(buffer[0]).length : 0;
    };

    for (const p of paragraphs) {
      const t = encode(p).length;
      if (bufferTokens + t > MAX_TOKENS) flush();
      buffer.push(p);
      bufferTokens += t;
    }
    flush();
  }
  return chunks;
}

For WordPress sources I pull posts through the REST API (/wp-json/wp/v2/posts?_fields=id,title,content,link,modified), convert HTML to Markdown with turndown, and feed the result through the same splitter. Because headings survive the conversion, the chunker behaves identically for Markdown files and CMS content. I cover the WordPress side in more detail in my headless WordPress architecture guide.

Step 3: Batch embedding with change detection

Embedding endpoints accept arrays, so never embed one chunk per request. Batch 100 at a time, hash the document, and store the hash so the next run only touches changed content.

// scripts/ingest.ts
import OpenAI from 'openai';
import { createHash } from 'node:crypto';
import { sql } from '@/lib/db';
import { chunkMarkdown } from '@/lib/chunk';

const openai = new OpenAI();

export async function ingestDocument(url: string, title: string, markdown: string) {
  const hash = createHash('sha256').update(markdown).digest('hex');
  const existing = await sql`SELECT id FROM documents WHERE content_hash = ${hash}`;
  if (existing.length) return { skipped: true };

  const [doc] = await sql`
    INSERT INTO documents (source_url, title, content_hash)
    VALUES (${url}, ${title}, ${hash})
    ON CONFLICT (content_hash) DO UPDATE SET updated_at = now()
    RETURNING id`;

  const chunks = chunkMarkdown(markdown, title);

  for (let i = 0; i < chunks.length; i += 100) {
    const batch = chunks.slice(i, i + 100);
    const { data } = await openai.embeddings.create({
      model: 'text-embedding-3-small',
      input: batch.map(c => `${c.heading}\n\n${c.content}`),
    });

    await sql`
      INSERT INTO chunks (document_id, position, heading, content, token_count, embedding)
      SELECT * FROM UNNEST(
        ${Array(batch.length).fill(doc.id)}::bigint[],
        ${batch.map((_, j) => i + j)}::int[],
        ${batch.map(c => c.heading)}::text[],
        ${batch.map(c => c.content)}::text[],
        ${batch.map(c => c.tokenCount)}::int[],
        ${data.map(d => JSON.stringify(d.embedding))}::vector[]
      )`;
  }
  return { skipped: false, chunks: chunks.length };
}

Step 4: Hybrid search with Reciprocal Rank Fusion

This is the step that fixed the "product code SKU-4471 is never found" bug. Dense vectors capture meaning; BM25-style keyword search captures exact tokens. Run both, then merge the rankings with Reciprocal Rank Fusion (RRF), which needs no score normalisation and is robust to the two systems having wildly different score scales.

-- $1 = query embedding, $2 = plain query text, $3 = k (e.g. 20)
WITH dense AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> $1) AS rnk
  FROM chunks ORDER BY embedding <=> $1 LIMIT $3
),
sparse AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY ts_rank_cd(tsv, q) DESC) AS rnk
  FROM chunks, plainto_tsquery('english', $2) q
  WHERE tsv @@ q LIMIT $3
),
fused AS (
  SELECT id, SUM(1.0 / (60 + rnk)) AS score          -- RRF with k = 60
  FROM (SELECT * FROM dense UNION ALL SELECT * FROM sparse) u
  GROUP BY id
)
SELECT c.id, c.heading, c.content, d.source_url, d.title, f.score
FROM fused f
JOIN chunks c    ON c.id = f.id
JOIN documents d ON d.id = c.document_id
ORDER BY f.score DESC
LIMIT 8;

On the client corpus (about 60,000 chunks) this query runs in 12 to 25 ms with the HNSW index. The measured effect on my evaluation set was a jump in hit@5 from 0.71 (dense only) to 0.89 (hybrid). No prompt change produced anything close to that.

Step 5: Streaming answers from a Next.js Route Handler

Users tolerate a two-second wait if they see text appearing. They do not tolerate a blank screen for two seconds followed by a wall of text. Next.js Route Handlers can return a ReadableStream, and the Vercel AI SDK wraps the provider streaming APIs so you do not hand-roll SSE parsing.

// app/api/ask/route.ts
import { streamText } from 'ai';
import { openai } from '@ai-sdk/openai';
import { hybridSearch } from '@/lib/search';
import { unstable_cache } from 'next/cache';

export const runtime = 'nodejs';        // pgvector needs a TCP socket; not Edge

// Cache RETRIEVAL for identical questions; generation stays live.
const cachedSearch = unstable_cache(hybridSearch, ['rag-search'], { revalidate: 3600 });

export async function POST(req: Request) {
  const { question } = await req.json();
  if (typeof question !== 'string' || question.length > 500) {
    return new Response('Bad request', { status: 400 });
  }

  const chunks = await cachedSearch(question, 8);
  const context = chunks
    .map((c, i) => `[${i + 1}] ${c.heading}\n${c.content}\n(source: ${c.source_url})`)
    .join('\n\n');

  const result = streamText({
    model: openai('gpt-4o-mini'),
    system: `You answer questions using ONLY the numbered sources below.
Cite sources inline like [2]. If the sources do not contain the answer, say so plainly.
Never invent product codes, prices or dates.`,
    messages: [{ role: 'user', content: `Sources:\n${context}\n\nQuestion: ${question}` }],
    maxTokens: 600,
    temperature: 0.2,
  });

  return result.toDataStreamResponse({
    headers: { 'X-Sources': JSON.stringify(chunks.map(c => c.source_url)) },
  });
}

On the client, useChat from the AI SDK consumes that stream and renders tokens as they arrive. The custom X-Sources header lets the UI show citation links immediately, before the model has finished writing.

Step 6: Evaluate retrieval before you tune prompts

Before this project I tuned prompts by reading answers and nodding. Now I keep a JSON file of 60 real questions, each mapped to the chunk IDs that should be retrieved, and run this script on every change to chunking, embedding model or search query:

// scripts/eval-retrieval.ts
import questions from './eval-set.json';   // [{ question, expectedChunkIds: number[] }]
import { hybridSearch } from '@/lib/search';

let hits = 0, reciprocalRanks = 0;
for (const q of questions) {
  const results = await hybridSearch(q.question, 5);
  const rank = results.findIndex(r => q.expectedChunkIds.includes(r.id));
  if (rank !== -1) { hits++; reciprocalRanks += 1 / (rank + 1); }
}
console.log(`hit@5 = ${(hits / questions.length).toFixed(2)}`);
console.log(`MRR   = ${(reciprocalRanks / questions.length).toFixed(2)}`);

It runs in under ten seconds and costs nothing, because it never calls the LLM. If hit@5 drops, the generation quality will drop no matter how clever the prompt. I go deeper on prompt-level testing in Prompt Engineering for Developers.

Trade-offs and what I would change at scale

Where to go from here

The system above is around 400 lines of application code plus one SQL migration, and it has replaced two SaaS subscriptions for the client that runs it. The parts worth investing in, in order, are the chunker, the evaluation set and the hybrid query. The LLM is the easiest component to swap and the least important to get perfect on day one.

If you want the same pattern exposed to AI assistants rather than a chat UI, the natural next step is to wrap the search in a tool server. I walk through exactly that in Model Context Protocol Explained. And if you are integrating from a PHP or WordPress backend rather than Next.js, this guide covers the PHP side.