Building a Production-Ready RAG Pipeline with Node.js and PostgreSQL pgvector

Introduction

Retrieval-augmented generation, commonly abbreviated as RAG, has rapidly become the default architectural pattern for teams that want to combine the fluent reasoning of large language models with the factual accuracy of proprietary data. In a world where models are trained on static snapshots of the internet, RAG provides a live bridge to your documents, tickets, wikis, and databases. This article presents a complete, production-ready RAG pipeline with Node.js and PostgreSQL using the pgvector extension. We will move from first principles to deployable code, ensuring you understand not only the how but also the why.

Many engineers believe that implementing RAG requires adopting a specialized vector database such as Pinecone, Weaviate, or Milvus. While those tools are powerful, they introduce a new datastore, a new operational surface, and a synchronization problem. If your organization already runs PostgreSQL—and most do—the pgvector extension lets you store embeddings right alongside your relational data. You get transactions, backups, role-based access control, and a mature query planner without leaving the ecosystem you trust.

This guide is written for backend and full-stack developers comfortable with JavaScript or TypeScript, Node.js, and basic SQL. By the end, you will have a working ingestion script, a query API, and a clear roadmap for taking the system to production.

Table of Contents

• Core Concepts
• Architecture Overview
• Step-by-Step Guide
• Real-World Examples
• Production Code Examples
• Comparison Table
• Best Practices
• Common Mistakes
• Performance Tips
• Security Considerations
• Deployment Notes
• Debugging Tips
• FAQ
• Conclusion

Core Concepts

Before writing a single line of code, we need a shared vocabulary. A RAG system is only as good as its representations and retrieval logic.

Embeddings and Vector Spaces

An embedding is a fixed-length array of floating-point numbers produced by a neural network. Modern embedding models map semantically similar texts to nearby points in a high-dimensional space. For example, the sentences "How do I reset my password?" and "I forgot my login credentials" will have vectors with small cosine distance. The dimensionality typically ranges from 384 to 3072 depending on the model.

Distance Metrics

pgvector supports several distance operators: <=> for cosine distance, <#> for taxicab distance, and <|> for inner product. In practice, cosine similarity is the most common choice for text because it is invariant to vector magnitude. We can compute cosine similarity as 1 - (a <=> b).

Chunking Strategies

Large documents must be split before embedding. Naive splitting by character count breaks sentences; smarter strategies use sentence boundaries or recursive character splitting. A typical chunk size is 500–1000 tokens with a 10–20% overlap. The right size depends on your embedding model's max input and the granularity needed for accurate retrieval.

Index Types in pgvector

Exact nearest-neighbor search is O(n) and becomes slow at scale. pgvector provides two approximate index types. IVFFlat (Inverted File Flat) partitions the vector space into lists and searches only nearby lists. HNSW (Hierarchical Navigable Small World) builds a layered graph that delivers higher recall with faster queries, at the cost of longer build time and more memory. For most production RAG systems, HNSW is the recommended default.

Architecture Overview

Our reference architecture comprises four cooperating components, all orchestrated by Node.js services:

1. Document Ingestion Worker: Reads files or database records, cleans and chunks text, calls the embedding API, and writes rows to PostgreSQL.
2. PostgreSQL with pgvector: Stores chunks, their embeddings, and metadata such as source, tenant_id, and created_at.
3. Retrieval Service: Accepts a user query, embeds it, executes a similarity search with optional metadata filters, and returns the top-k contexts.
4. Generation API: An Express (or NestJS) endpoint that composes a prompt with the retrieved contexts and calls a chat completion model to produce the final answer.

This design keeps a single source of truth. There is no separate vector store to sync, and you can join retrieved chunks with other business tables in the same transaction. A caching layer (Redis) can sit in front of the retrieval service to memoize frequent questions.

Step-by-Step Guide

We will now stand up the system locally. Assume you have Node.js 20+ and Docker installed.

1. Start PostgreSQL with pgvector

docker run -d --name pgvector -e POSTGRES_PASSWORD=secret -p 5432:5432 ankane/pgvector:latest

2. Enable Extension and Create Schema

CREATE EXTENSION IF NOT EXISTS vector;CREATE TABLE documents (  id SERIAL PRIMARY KEY,  content TEXT NOT NULL,  embedding VECTOR(1536),  source TEXT,  tenant_id INTEGER,  created_at TIMESTAMPTZ DEFAULT NOW());

3. Scaffold the Node.js Project

mkdir rag-service && cd rag-servicenpm init -ynpm install pg openai dotenv express

4. Environment Configuration

Create a .env file with DATABASE_URL and OPENAI_API_KEY. Never commit this file.

5. Implement Ingestion and Query Logic

The following sections show the full TypeScript modules. You can run ingestion as a script and start the API with ts-node or compile with tsc.

Real-World Examples

RAG is not a toy. Here are three deployments that deliver measurable value:

Customer Support Copilot: A SaaS company ingests Zendesk articles and past tickets. When a support agent types a query, the system returns the most relevant articles and drafts a reply. Average handling time drops by 30%.

Enterprise Search: A consulting firm indexes thousands of PDF reports. Employees ask natural-language questions and receive answers with source citations, replacing keyword search that frequently missed synonyms.

Compliance Monitoring: A fintech stores regulatory texts and internal policies. The RAG pipeline flags whether a new product description aligns with stored compliance clauses.

Production Code Examples

Below is a robust implementation split into modules.

import { Pool } from 'pg';import OpenAI from 'openai';import dotenv from 'dotenv';dotenv.config();const pool = new Pool({ connectionString: process.env.DATABASE_URL });const openai = new OpenAI({ apiKey: process.env.OPENAI_API_KEY });export async function embedText(text: string): Promise<number[]> {  const res = await openai.embeddings.create({    model: 'text-embedding-3-small',    input: text,  });  return res.data[0].embedding;}export async function insertDocument(content: string, source: string, tenantId?: number) {  const embedding = await embedText(content);  await pool.query(    'INSERT INTO documents (content, embedding, source, tenant_id) VALUES ($1, $2, $3, $4)',    [content, embedding, source, tenantId ?? null]  );}export async function searchSimilar(query: string, k = 5, tenantId?: number) {  const embedding = await embedText(query);  const params: any[] = [embedding, k];  let filter = '';  if (tenantId) {    filter = 'WHERE tenant_id = $3';    params.push(tenantId);  }  const { rows } = await pool.query(    `SELECT content, source, 1 - (embedding <=> $1) AS score     FROM documents ${filter}     ORDER BY embedding <=> $1     LIMIT $2`,    params  );  return rows;}

import express from 'express';import { searchSimilar } from './rag';import OpenAI from 'openai';const app = express();app.use(express.json());const openai = new OpenAI();app.post('/ask', async (req, res) => {  try {    const { question, tenantId } = req.body;    if (!question) return res.status(400).json({ error: 'question required' });    const contexts = await searchSimilar(question, 5, tenantId);    const contextText = contexts.map(c => `[${c.source}] ${c.content}`).join('');    const completion = await openai.chat.completions.create({      model: 'gpt-4o-mini',      messages: [        { role: 'system', content: 'You answer strictly using the provided context. Cite sources.' },        { role: 'user', content: `Context:${contextText}Question: ${question}` }      ],    });    res.json({ answer: completion.choices[0].message.content, sources: contexts.map(c => c.source) });  } catch (err) {    res.status(500).json({ error: 'Internal error' });  }});app.listen(3000, () => console.log('RAG API on :3000'));

Comparison Table

Best Practices

• Select chunk size empirically; 512 tokens with 64 overlap is a strong starting point.
• Always store metadata (source, tenant, timestamp) to enable filtered retrieval and audits.
• Use a single pg Pool instance; never create a client per request.
• Version embedding models in a config table; re-index when you upgrade.
• Evaluate retrieval with recall@k before tuning the LLM prompt.
• Add a human feedback loop to capture thumbs up/down on answers.
• Cache embeddings for identical texts to save API cost.

Common Mistakes

• Skipping text normalization, causing the same concept to have different vectors.
• Neglecting to create an index, leading to full table scans on every query.
• Stuffing too many chunks into the prompt and truncating the context window.
• Hardcoding secrets in source code or committing the .env file.
• Ignoring duplicate or near-duplicate content that biases retrieval.
• Using Euclidean distance when cosine is more appropriate for text.

Performance Tips

• Create an HNSW index: CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);
• Batch embedding requests: the OpenAI API accepts arrays of inputs.
• Run EXPLAIN ANALYZE to confirm the planner uses the index.
• Set work_mem high enough for index builds.
• Use a CDN or Redis to cache answers for identical questions.
• Precompute embeddings for static documents during off-peak hours.

Security Considerations

• Keep API keys in a secrets manager, not in environment files checked into git.
• Always use parameterized queries to eliminate SQL injection risks.
• Apply row-level security or tenant_id filters to prevent cross-tenant leakage.
• Scrub personally identifiable information before sending text to embedding APIs.
• Rate limit and authenticate the /ask endpoint to prevent cost-based attacks.
• Encrypt the PostgreSQL volume at rest and enforce TLS in transit.

Deployment Notes

Containerize the Node.js service. A minimal Dockerfile uses node:20-alpine, copies package files, runs npm ci, and starts with node dist/server.js. For the database, use a managed Postgres that supports pgvector (e.g., AWS RDS PostgreSQL 15+, Google Cloud SQL, or Supabase). Run schema migrations at deploy time. Configure horizontal replication for read-heavy retrieval, and monitor slow queries.

Debugging Tips

• Log the exact SQL and the top retrieved chunk IDs for each question.
• Manually inspect distances: SELECT content, embedding <=> '[...]' FROM documents LIMIT 5;
• If latency is high, verify the index exists with \d documents in psql.
• Write unit tests with mocked embeddings to validate prompt assembly.
• Use the OpenAI playground to test the prompt with pasted contexts.

FAQ

What embedding dimension should I use?

Match the column to your model. text-embedding-3-small outputs 1536, while older models like ada-002 also output 1536. Open-source MiniLM outputs 384. Using the wrong dimension causes insertion errors.

Can pgvector handle millions of rows?

Yes. With HNSW indexing and adequate memory, pgvector serves millions of vectors with sub-100ms latency. For beyond 100 million, evaluate dedicated engines.

How do I update embeddings when source text changes?

Re-embed the modified chunk and update by primary key. Automate this with a queue: when a document is edited, publish an event that triggers re-embedding.

Is RAG better than fine-tuning?

RAG excels at fresh, verifiable data and is cheaper to update. Fine-tuning bakes style or domain tone into the model. Many systems use both: fine-tune for tone, RAG for facts.

Can I use local open-source embedding models?

Yes. You can run ONNX models via transformers.js in Node.js or call a Python microservice. The output vector is stored identically in pgvector.

How do I evaluate retrieval quality?

Build a golden set of question-to-document pairs. Measure recall@k and MRR. Only after retrieval is solid should you tune the generation prompt.

Which LLM is best for the generation step?

It depends on cost and latency needs. GPT-4o-mini is a strong default; Claude Haiku and Llama 3 8B are good alternatives. Ensure the context window fits your top-k chunks.

Do I need a separate vector database?

Not for most use cases. pgvector reduces architecture complexity. If you need multi-region replication of vectors alone, a dedicated store may help.

How do I prevent the model from hallucinating?

Instruct the model to answer only from context and return "I don't know" when absent. Cite sources and validate with post-processing checks.

Can I filter by metadata during search?

Absolutely. Add a WHERE clause on source or tenant_id before the ORDER BY. pgvector still uses the index for the vector part.

Conclusion

You now have a comprehensive blueprint for a RAG pipeline with Node.js and PostgreSQL pgvector. We covered the math, the architecture, the code, and the operational realities. The combination of familiar relational infrastructure and modern embeddings is a pragmatic, cost-effective path to shipping AI features. Review the internal guides on Docker deployment and vector databases, then start building your own retrieval-augmented assistant this week. If you found this useful, subscribe to the RakibAhsan.xyz newsletter for more deep dives.