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
Before you start, have the following in place:
- Postgres 15 or newer with the `pgvector` extension available (0.7.0 or later, so that half-precision and binary indexes are on the table).
- An embedding model endpoint you can call from your backend. The example uses a 1536-dimension model, but the pattern is dimension agnostic.
- Node.js 20+ with TypeScript and a Postgres client. The snippets use `postgres.js`, though `pg` works identically.
- A corpus you own policy documents, a knowledge base, product manuals. Anything with structure you can attribute answers back to.
We will build the pipeline in six steps: schema, chunking, embeddings, indexing, retrieval and serving. By the end you will have something you can point production traffic at, plus a clear view of the knobs that matter when quality or latency drifts.
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), -- populated in step 3
-- lexical vector for hybrid search, kept in sync by a trigger
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.
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;
}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.
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);
}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.
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. 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.
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.
export async function answer(query: string, user: { scope: string }) {
const passages = await retrieve(query, user.scope);
// Guardrail: if nothing cleared the retrieval bar, 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. Making the model decline when retrieval returns nothing is the single cheapest reliability improvement in the whole pipeline.
What you built
You now have a retrieval pipeline that lives entirely inside Postgres: transactional ingestion, a tuned HNSW index, hybrid search with lexical fallback, access control enforced in the query, and generation gated behind a real retrieval bar. 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.
Datastore to operate
Postgres holds rows, vectors and access rules
Retrievers, fused
Semantic recall plus exact lexical match
Steps to production
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.