Building RAG and Vector Search in Node.js with PostgreSQL and pgvector

Artificial Intelligence and Large Language Models (LLMs) have fundamentally shifted how we build applications. However, LLMs are trained on static datasets and lack context regarding your proprietary business data. Retrieval-Augmented Generation (RAG) solves this by fetching relevant contextual data from your database and injecting it into the LLM prompt at inference time.

Traditionally, implementing semantic search required spinning up specialized vector databases like Pinecone, Weaviate, or Milvus. But if your application already uses PostgreSQL, you don’t need a new database. With the pgvector extension, PostgreSQL transforms into a powerful, scalable vector database capable of exact and approximate nearest neighbor searches.

In this guide, we will build a complete RAG pipeline and semantic search backend in Node.js using PostgreSQL, pgvector, and the OpenAI API.

Prerequisites and Environment Setup

To follow along, ensure you have the following installed:

• Node.js (v18+ recommended)
• PostgreSQL (v15+ recommended)
• An OpenAI API Key

Setting up the Node.js Project

Initialize a new Node.js project and install the necessary dependencies:

mkdir pgvector-rag-node
cd pgvector-rag-node
npm init -y
npm install pg dotenv openai
npm install --save-dev typescript @types/node @types/pg
npx tsc --init

Create a .env file in your root directory:

PORT=3000
DATABASE_URL=postgresql://postgres:password@localhost:5432/vector_demo
OPENAI_API_KEY=your_openai_api_key_here

Enabling pgvector in PostgreSQL

First, connect to your PostgreSQL instance using your preferred client (e.g., psql or DBeaver) and create the database:

CREATE DATABASE vector_demo;

Connect to vector_demo and enable the pgvector extension:

-- Enable the extension (requires pgvector to be installed on your server)
CREATE EXTENSION IF NOT EXISTS vector;

If you are using Docker, you can quickly spin up a pre-configured Postgres instance with pgvector using this command:

docker run --name pgvector-db -e POSTGRES_PASSWORD=password -e POSTGRES_DB=vector_demo -p 5432:5432 -d ankane/pgvector

Designing the Database Schema

We need a table to store our documents, metadata, and the generated vector embeddings. OpenAI’s text-embedding-3-small model outputs vectors with 1538 dimensions (though it can be scaled down; we will use the standard 1538-dimension format or 1536 dimensions depending on the model variation—let’s use 1536 for text-embedding-3-small).

Execute the following SQL script to create our documents table:

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    metadata JSONB,
    embedding VECTOR(1536)
);

-- Create an HNSW index for fast approximate nearest neighbor search
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

Note: The HNSW (Hierarchical Navigable Small World) index is essential for production environments with millions of records. It trades a tiny amount of recall accuracy for massive speedups in vector similarity searches.

Connecting Node.js to PostgreSQL

Let’s write our database connection module using the pg package. Create a file named db.ts:

import { Pool } from 'pg';
import dotenv from 'dotenv';

dotenv.config();

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

export async function query(text: string, params?: any[]) {
  const start = Date.now();
  const res = await pool.query(text, params);
  const duration = Date.now() - start;
  console.log('Executed query', { text, duration, rows: res.rowCount });
  return res;
}

Generating Embeddings and Storing Data

Next, we’ll write a service to interface with the OpenAI API to turn text strings into vector arrays, and then save them into PostgreSQL. Create ragService.ts:

import OpenAI from 'openai';
import { query } from './db';

const openai = new OpenAI({
  apiKey: process.env.OPENAI_API_KEY,
});

/**
 * Generates an embedding vector for a given text string using OpenAI.
 */
export async function generateEmbedding(text: string): Promise<number[]> {
  const response = await openai.embeddings.create({
    model: 'text-embedding-3-small',
    input: text,
    encoding_format: 'float',
  });

  return response.data[0].embedding;
}

/**
 * Inserts a document and its vector embedding into PostgreSQL.
 */
export async function insertDocument(content: string, metadata: object = {}): Promise<number> {
  const embedding = await generateEmbedding(content);
  
  // pgvector accepts arrays formatted as string literals like '[0.1, 0.2, ...]' 
  // or passed directly via pg driver if serialized properly.
  const vectorString = `[${embedding.join(',')}]`;

  const result = await query(
    `INSERT INTO documents (content, metadata, embedding) VALUES ($1, $2, $3) RETURNING id;`,
    [content, metadata, vectorString]
  );

  return result.rows[0].id;
}

Performing Vector Similarity Search

pgvector supports three main distance operators:

• <->: Euclidean distance
• <#>: Negative inner product (for normalized vectors)
• <=>: Cosine distance (most common for semantic search)

Let’s write a function to search our database for documents semantically similar to a user’s query:

export interface SearchResult {
  id: number;
  content: string;
  metadata: any;
  similarity: number;
}

/**
 * Searches for the top K most similar documents using cosine distance.
 */
export async function searchSimilarDocuments(queryText: string, matchCount: number = 5): Promise<SearchResult[]> {
  const queryEmbedding = await generateEmbedding(queryText);
  const vectorString = `[${queryEmbedding.join(',')}]`;

  // Using 1 - cosine_distance to calculate similarity score (closer to 1.0 is more similar)
  const sql = `
    SELECT 
      id, 
      content, 
      metadata, 
      1 - (embedding <=> $1) AS similarity
    FROM documents
    ORDER BY embedding <=> $1
    LIMIT $2;
  `;

  const result = await query(sql, [vectorString, matchCount]);
  return result.rows;
}

Implementing the RAG Pipeline

With retrieval fully functional, we can now complete the RAG loop:

1. User submits a natural language question.
2. We embed the question and fetch the most relevant chunks from Postgres.
3. We inject those chunks as context into a system prompt.
4. We pass the enriched prompt to an LLM to generate an accurate, grounded answer.

Add the RAG generation function to ragService.ts:

/**
 * Answers a user query by retrieving relevant context and querying an LLM.
 */
export async function answerWithRAG(userQuery: string): Promise<string> {
  console.log(`Processing query: "${userQuery}"`);

  // Step 1: Retrieve relevant context chunks
  const relevantDocs = await searchSimilarDocuments(userQuery, 3);
  
  const context = relevantDocs
    .map((doc, idx) => `[Document ${idx + 1}]:\n${doc.content}`)
    .join('\n\n');

  console.log(`Retrieved ${relevantDocs.length} context documents.`);

  // Step 2: Construct prompt with context
  const systemPrompt = `
You are a helpful AI assistant. Use the provided context chunks below to answer the user's question accurately.
If you do not know the answer based on the context, state clearly that you cannot find the answer in the provided documents.

Context:
${context}
`;

  // Step 3: Call LLM
  const completion = await openai.chat.completions.create({
    model: 'gpt-4o-mini',
    messages: [
      { role: 'system', content: systemPrompt },
      { role: 'user', content: userQuery },
    ],
    temperature: 0.2,
  });

  return completion.choices[0].message.content || 'No response generated.';
}

Putting It All Together

Let’s build an entry point (index.ts) to seed some sample data, run a vector search, and execute our full RAG pipeline:

import { insertDocument, searchSimilarDocuments, answerWithRAG } from './ragService';
import { pool } from './db';

async function main() {
  try {
    console.log('Seeding knowledge base...');

    await insertDocument(
      'PostgreSQL is an advanced, enterprise-class open-source relational database management system supporting both relational and non-relational data structures.',
      { category: 'database', source: 'docs' }
    );

    await insertDocument(
      'pgvector is an open-source vector similarity search extension for PostgreSQL. It supports IVFFlat and HNSW indexing for high-performance vector retrieval.',
      { category: 'database', source: 'extension-docs' }
    );

    await insertDocument(
      'Retrieval-Augmented Generation (RAG) is a technique used in AI to improve LLM responses by fetching authoritative context from external databases.',
      { category: 'ai', source: 'ml-guide' }
    );

    console.log('\n--- Running Semantic Search Test ---');
    const searchResults = await searchSimilarDocuments('How does pgvector store vectors?', 2);
    searchResults.forEach((res, i) => {
      console.log(`Result ${i + 1} (Score: ${res.similarity.toFixed(4)}): ${res.content}`);
    });

    console.log('\n--- Running RAG Pipeline Test ---');
    const answer = await answerWithRAG('What is RAG and why is it useful?');
    console.log('\nLLM Answer:\n', answer);

  } catch (error) {
    console.error('Error in pipeline execution:', error);
  } finally {
    await pool.end();
  }
}

main();

Compile and run your TypeScript code:

npx tsc
node index.js

Performance Optimization Tips for Production

When scaling your Node.js and pgvector application to production workloads, keep these best practices in mind:

1. Tune HNSW Parameters: When creating indexes, configure m (max connections per node) and ef_construction (size of the dynamic candidate list during construction):

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

2. Connection Pooling: Node.js apps handle concurrent requests efficiently through connection pools (pg.Pool). Ensure your pool size matches your database workload capacity.
3. Chunking Strategy: Break large documents (PDFs, markdown files) into smaller paragraphs (e.g., 500-1000 tokens) before generating embeddings to ensure high retrieval precision.
4. Asynchronous Ingestion: Generate embeddings in background worker queues (like BullMQ) rather than blocking web request threads during document uploads.

Conclusion

By pairing Node.js with PostgreSQL and pgvector, you eliminate the operational overhead of managing a standalone vector database. You get ACID compliance, robust relational querying, and state-of-the-art vector similarity search all under one roof. Whether you are building internal enterprise search or customer-facing AI agents, this architecture scales gracefully and keeps your stack delightfully simple.