Retrieval pipeline
The pipeline this tutorial builds runs a document through a chunker, into Postgres with pgvector, through a hybrid retriever, and finally to the generation model: document, chunker, pgvector, retriever, LLM. The pgvector stage is where retrieval quality and latency are actually won or lost, which is why it gets the bulk of this tutorial's attention. The result, once wired together, is hybrid retrieval feeding grounded generation.
Most retrieval demos fall over the moment they meet a real corpus. A notebook that answers three curated questions is a very different thing from a service that fields thousands of queries a day against documents that change under you. This tutorial walks through the pipeline we reach for on enterprise engagements: retrieval augmented generation built on Postgres and the pgvector extension, with nothing exotic in the stack. If your organisation already runs Postgres, you already run most of a capable vector database.
We favour Postgres here for a reason worth stating plainly. Keeping your embeddings, your source rows and your access controls in one transactional store removes an entire class of consistency bugs. There is no separate vector service to keep in sync, no dual-write to reconcile at 2am, and no second set of credentials to rotate. You get joins, transactions and your existing backups for free. The trade is that you have to understand the index, so we will spend real time on it.
Prerequisites
Use this as a working baseline. The production column marks controls the tutorial does not implement. We will build the pipeline in six steps: schema, chunking, embeddings, indexing, retrieval and serving.
Swipe or scroll the table horizontally to see every column.
| Component | Tutorial baseline | Before live traffic |
|---|---|---|
| Runtime | Node.js 24 LTS with TypeScript and a Postgres client. The snippets use postgres.js. | Use the same supported 24.x patch line in development, CI and production. Pin and maintain dependencies. |
| Database | A supported PostgreSQL major version. PostgreSQL 15 remains suitable where already in use. | Use TLS, least-privilege database roles, tested backups and retention controls. |
| Vector extension | A maintained pgvector release compatible with the index and vector type you choose. | Test filtered retrieval recall against real entitlements and tune the index for the workload. |
| Embeddings | A backend-accessible model endpoint, with the model and output dimension pinned. | Keep content and credentials server-side. Validate responses, timeouts and data-handling conditions. |
| Source corpus | Documents you are authorised to ingest and cite, with stable source references. | Maintain provenance, classification, entitlement mapping and a deletion and re-indexing path. |
1. Design the schema
Start with the extension and two tables. One holds the source documents, the other holds the chunks and their vectors. Keeping documents and chunks separate lets you re-chunk without re-ingesting, and lets you attribute every retrieved passage back to a stable source for citations and access checks.
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE documents (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
source_uri text NOT NULL,
title text NOT NULL,
-- who is allowed to see this document, checked at query time
visibility text NOT NULL DEFAULT 'internal',
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE chunks (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
document_id bigint NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
ordinal int NOT NULL, -- position within the document
content text NOT NULL,
token_count int NOT NULL,
embedding vector(1536), -- replace 1536 with your model's output dimension
-- lexical vector, populated during ingestion
tsv tsvector
);
CREATE INDEX chunks_tsv_idx ON chunks USING gin (tsv);The ON DELETE CASCADE matters more than it looks. When a source document is withdrawn, every chunk and every embedding derived from it disappears in the same transaction. Stale answers from deleted documents are one of the most common and most damaging RAG failures, and a foreign key removes the whole category.
Do not store raw text only in your vectors
A vector is a lossy projection. You cannot show it to a user, cite it, or audit it. Always keep the original content alongside the embedding so every answer can be traced to text a human can read. Teams that discard the source to save space lose the ability to explain any answer the system gives.
2. Chunk the corpus
Chunking is where most retrieval quality is won or lost, and it gets far less attention than embedding-model choice. Chunks that are too large dilute the signal, so the relevant sentence is drowned out by surrounding filler and the vector points at an average of everything. Chunks that are too small lose the context that makes a passage meaningful. The reliable middle ground is to split on natural boundaries, headings and paragraphs, then pack up to a token budget with a small overlap so a thought is never cut clean in half.
interface Chunk {
content: string;
tokenCount: number;
}
// Split on blank lines first, then greedily pack paragraphs up to a
// target budget with a small overlap so context survives the cut.
export function chunk(
text: string,
countTokens: (s: string) => number,
target = 400,
overlap = 60,
): Chunk[] {
const paras = text.split(/\n{2,}/).map((p) => p.trim()).filter(Boolean);
const chunks: Chunk[] = [];
let buf: string[] = [];
let tokens = 0;
for (const para of paras) {
const t = countTokens(para);
if (tokens + t > target && buf.length) {
const content = buf.join("\n\n");
chunks.push({ content, tokenCount: tokens });
// carry the tail forward as overlap
const tail = buf.slice(-1);
buf = tail;
tokens = countTokens(tail.join("\n\n"));
}
buf.push(para);
tokens += t;
}
if (buf.length) chunks.push({ content: buf.join("\n\n"), tokenCount: tokens });
return chunks;
}Rule of thumb
Aim for chunks in the 300 to 500 token range for prose, with 10 to 15 percent overlap. Measure, do not guess: hold out a set of real questions, vary the target size, and keep the setting that maximises retrieval recall on that set. The best chunk size is a property of your documents, not a universal constant.
Count tokens with the same tokeniser your embedding model uses, not a word count. A budget expressed in words drifts badly across languages and across content with code, tables or long identifiers, and it is exactly that content where retrieval tends to matter most.
3. Generate embeddings
With chunks in hand, embed them in batches and write the vectors back. Batch the calls: embedding one chunk at a time will dominate your ingestion time and your bill. Do the writes inside a transaction per document so a partially embedded document never becomes visible to search. Keep the model credential and document content on the backend: do not accept either from a browser or expose them in client logs.
import postgres from "postgres";
const sql = postgres(process.env.DATABASE_URL!);
async function embedDocument(documentId: number, chunks: Chunk[]) {
// embed in batches; the provider call is the slow part, so keep it wide
const vectors = await embedBatch(chunks.map((c) => c.content));
await sql.begin(async (tx) => {
for (let i = 0; i < chunks.length; i++) {
await tx`
INSERT INTO chunks (document_id, ordinal, content, token_count, embedding, tsv)
VALUES (
${documentId}, ${i}, ${chunks[i].content}, ${chunks[i].tokenCount},
${JSON.stringify(vectors[i])}::vector,
to_tsvector('english', ${chunks[i].content})
)
`;
}
});
}
// embedBatch calls your model endpoint and returns number[][]
async function embedBatch(inputs: string[]): Promise<number[][]> {
const res = await fetch(process.env.EMBEDDINGS_URL!, {
method: "POST",
headers: {
"content-type": "application/json",
authorization: `Bearer ${process.env.EMBEDDINGS_KEY}`,
},
body: JSON.stringify({ input: inputs }),
});
if (!res.ok) throw new Error(`embeddings failed: ${res.status}`);
const json = await res.json();
return json.data.map((d: { embedding: number[] }) => d.embedding);
}Normalise once, and only once
If you index with cosine distance, your model output must be L2-normalised for the maths to mean what you think. Some providers return normalised vectors already; others do not. Normalising twice quietly degrades recall in a way that is very hard to spot after the fact. Confirm your model behaviour once, write it down, and pick your distance operator to match.
4. Build the vector index
A sequential scan over a few thousand vectors is fine. Past that, you need an approximate index or query latency climbs linearly with the corpus. For most workloads HNSW is the right default: it gives excellent recall at low latency and, unlike the alternative, does not need a representative sample of your data present before you build it.
-- Build after a meaningful volume of rows exists.
-- vector_cosine_ops must match the distance operator you query with (<=>).
CREATE INDEX chunks_embedding_idx
ON chunks
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
-- Raise recall at query time by widening the search frontier.
-- Set per session or per transaction, not globally.
SET hnsw.ef_search = 100;The two build parameters trade index size and build time against recall. Higher m and ef_construction give better recall and a larger, slower-to-build index. At query time, ef_search is your live dial: raise it when recall matters more than milliseconds, lower it when the reverse is true. Tune it against real queries rather than adopting a number from a blog post, including this one.
Index after the bulk load
Building the HNSW index before a large ingest means paying to maintain it on every insert. For a first load, insert the rows, then build the index once. For steady-state trickle updates the incremental cost is negligible, so leave it in place.
5. Hybrid retrieval
Pure vector search is strong on paraphrase and weak on exact tokens: product codes, error strings, acronyms, surnames. Those are precisely the terms enterprise users search for. The fix is to run vector and lexical search together and fuse the results, which recovers the exact-match cases without giving up semantic recall.
// Reciprocal rank fusion: robust, needs no score calibration between
// the two retrievers, and is stable across very different query types.
export async function retrieve(query: string, visibleTo: string, k = 8) {
const queryVector = (await embedBatch([query]))[0];
const rows = await sql`
WITH semantic AS (
SELECT c.id, row_number() OVER (
ORDER BY c.embedding <=> ${JSON.stringify(queryVector)}::vector
) AS rank
FROM chunks c
JOIN documents d ON d.id = c.document_id
WHERE d.visibility = ${visibleTo}
ORDER BY c.embedding <=> ${JSON.stringify(queryVector)}::vector
LIMIT 40
),
lexical AS (
SELECT c.id, row_number() OVER (
ORDER BY ts_rank_cd(c.tsv, plainto_tsquery('english', ${query})) DESC
) AS rank
FROM chunks c
JOIN documents d ON d.id = c.document_id
WHERE d.visibility = ${visibleTo}
AND c.tsv @@ plainto_tsquery('english', ${query})
LIMIT 40
)
SELECT ch.id, ch.content, ch.document_id,
COALESCE(1.0 / (60 + s.rank), 0) + COALESCE(1.0 / (60 + l.rank), 0) AS score
FROM chunks ch
LEFT JOIN semantic s ON s.id = ch.id
LEFT JOIN lexical l ON l.id = ch.id
WHERE s.id IS NOT NULL OR l.id IS NOT NULL
ORDER BY score DESC
LIMIT ${k}
`;
return rows;
}Note the visibility filter carried through both arms of the query. Resolve that scope from the authenticated server-side session or a trusted policy decision, not from a request field the caller can choose. Access control belongs in the retrieval SQL, not in a post-filter after the model has already seen the text. Because everything lives in one Postgres query, the permission check and the search are a single atomic operation: a user can never be served a passage they are not entitled to, even for a moment.
Filter before the model, never after
Retrieving broadly and then dropping unauthorised passages in application code is a data-leak waiting to happen. The moment restricted text enters the prompt it can surface in the answer, in logs, or in a cache. Push every access predicate into the query itself.
6. Serve it safely
The final step assembles retrieved chunks into a prompt and calls the generation model. Two habits separate a demo from a service. First, always pass citations through: attach the document_id and ordinal to each passage so the answer can link back to its sources and a reviewer can verify it. Second, instrument the retrieval step itself. Log which chunks were retrieved for each query, not only the final answer, because when quality drops the cause is almost always in retrieval rather than generation, and you cannot fix what you cannot see.
Production boundary
Before this serves real users, keep four boundaries non-negotiable: derive access scope from the authenticated server session and enforce it in the retrieval query; keep model credentials and source content off the browser; treat retrieved text as untrusted data, never as permission to call a tool; and evaluate an answerability threshold on held-out questions before the model is allowed to answer. The example below covers the first two and an empty-result guard. Tool access, thresholding, monitoring and incident response are separate production work.
export async function answer(query: string, session: Session) {
const visibleTo = await resolveVerifiedScope(session);
const passages = await retrieve(query, visibleTo);
// Guardrail: if retrieval returned no candidates, do not invent an answer.
if (passages.length === 0) {
return { text: "I could not find anything relevant in the sources.", sources: [] };
}
const context = passages
.map((p, i) => `[${i + 1}] (doc ${p.document_id}) ${p.content}`)
.join("\n\n");
const text = await generate({
system:
"Answer only from the numbered context. Cite sources as [n]. " +
"If the context does not contain the answer, say so plainly.",
user: `Context:\n${context}\n\nQuestion: ${query}`,
});
return { text, sources: passages.map((p) => p.document_id) };
}The empty-result guardrail is not a nicety. An answer confidently assembled from nothing is worse than no answer, because it costs a user their trust and it costs you the incident. It is not a relevance threshold: this query still returns its best candidates. Set and evaluate a retrieval acceptance rule for your corpus before treating a result as answerable.
What you built
You now have a working retrieval baseline that lives entirely inside Postgres: transactional ingestion, a tuned HNSW index, hybrid search with lexical fallback, access control enforced in the query, and an empty-result guard before generation. It is boring in the best sense. There is one datastore to operate, one backup to trust, and one place to reason about who can see what. Production readiness still depends on the boundaries and evaluation work above.
Datastore to operate
Postgres holds rows, vectors and access rules
Retrievers, fused
Semantic recall plus exact lexical match
Steps to a working baseline
Schema through to a guarded serving path
From here the work is measurement, not architecture. Assemble a held-out set of real questions with known-good sources, then move the dials this tutorial exposed: chunk size and overlap, ef_search, the fusion weighting, the retrieval cut-off. Change one at a time and watch recall on that set. The pipeline is the easy part. The discipline of measuring it against your own corpus is what keeps it honest as the documents, and the questions, keep changing underneath you.