Build a Production-Ready LangChain RAG Pipeline with PostgreSQL pgvector

Introduction

Retrieval-Augmented Generation, commonly called RAG, has become one of the most practical patterns for building large language model applications that need access to private, fresh, or domain-specific knowledge. Instead of expecting a model to memorize every fact, a RAG system retrieves relevant documents at query time and feeds them into the prompt context. This approach reduces hallucination and makes the system auditable because you can show which sources informed the answer.

In this article we focus on building a production-ready LangChain RAG pipeline with PostgreSQL pgvector. LangChain provides the orchestration layer, document loaders, text splitters, embedding abstractions, and retrieval chains. PostgreSQL with the pgvector extension gives you a familiar, transactional, and highly available vector store that lives right next to your relational data. For many teams this combination eliminates the need to operate a separate specialized vector database while still delivering low-latency semantic search.

We will cover core concepts, a reference architecture, a step-by-step build, production code, a comparison with alternative vector stores, and the operational best practices you need before shipping to real users. By the end you should be able to stand up a LangChain RAG pipeline that ingests documents, stores embeddings in pgvector, and answers questions with cited context.

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 code, it is important to understand the building blocks of a LangChain RAG pipeline. The first building block is the embedding model. An embedding model converts text into a fixed-length numeric vector that captures semantic meaning. OpenAI, Cohere, and open-source models such as sentence-transformers can produce these vectors. In a pgvector store, each document chunk is represented as a vector of floats, often 1536 dimensions for OpenAI text-embedding-3-small or 768 for many open-source models.

The second building block is the vector store. pgvector is a PostgreSQL extension that adds a vector data type and indexing support. It allows you to store vectors in a regular table and query them using distance operators such as cosine distance, L2 distance, or inner product. Because it is just PostgreSQL, you can join vector results with standard relational filters like tenant_id or document_type.

The third building block is the retriever. LangChain defines a retriever interface that returns relevant documents for a query. The PGVector store implements this interface by embedding the query, running a similarity search in PostgreSQL, and returning the closest rows. The retriever can be enhanced with metadata filtering, hybrid search combining keyword and vector relevance, and re-ranking.

The fourth building block is the generation step. After retrieval, the pipeline packs the retrieved chunks into a prompt template, sends it to a chat model such as GPT-4o or an open-source model, and returns a grounded answer. The prompt typically instructs the model to use only the provided context and to cite sources.

Finally, evaluation is a core concept for production systems. A RAG pipeline should be measured on retrieval precision and answer faithfulness. You can log the retrieved document IDs, the cosine scores, and the generated answer to review quality over time.

Architecture Overview

A typical LangChain RAG pipeline with pgvector contains three logical layers: ingestion, storage, and serving. The ingestion layer loads raw documents from sources such as PDF, HTML, or a content management system. LangChain document loaders and text splitters convert these sources into smaller chunks that fit the embedding model token limit. Each chunk is embedded and written to PostgreSQL.

The storage layer is a PostgreSQL instance with the pgvector extension enabled. A table holds the chunk text, metadata, and the embedding vector. An index such as IVFFlat or HNSW accelerates nearest-neighbor search. Because the data lives in PostgreSQL, you can apply row-level security, backups, and replication using standard tooling.

The serving layer is an API process that receives user questions. It embeds the question, queries pgvector for similar chunks, and passes the top results into a LangChain retrieval chain. The chain calls the LLM and returns the answer with source references. In production you may separate the ingestion worker from the query API for independent scaling.

The diagram below is conceptual but maps directly to code. Documents flow left to right: source, loader, splitter, embedder, pgvector, retriever, LLM, response. Each stage can be monitored, retried, and versioned independently.

Step-by-Step Guide

This section walks through a minimal but production-minded setup. We assume you have a PostgreSQL instance version 14 or later and Python 3.10 or later.

1. Enable pgvector

Connect to your database as a superuser and run the extension command. On most managed PostgreSQL providers the extension is available in the add-on marketplace; on a self-hosted Ubuntu server you can install the package and create the extension.

CREATE EXTENSION IF NOT EXISTS vector;SELECT extname, extversion FROM pg_extension WHERE extname = 'vector';

2. Install Python dependencies

Use a virtual environment and install LangChain packages plus the pgvector driver. The examples below use the OpenAI embeddings interface, but you can swap in any LangChain embedding class.

python -m venv venv source venv/bin/activate pip install langchain langchain-openai langchain-postgres pgvector sqlalchemy python-dotenv

3. Configure environment variables

Store secrets in a .env file. Never hard-code API keys. The connection string uses the postgresql+psycopg scheme for SQLAlchemy compatibility.

OPENAI_API_KEY=sk-your-key DATABASE_URL=postgresql+psycopg://app_user:secret@localhost:5432/rag_db COLLECTION_NAME=docs

4. Ingest documents

Load a sample text, split it, embed it, and insert it into pgvector using the PGVector wrapper from langchain-postgres. The wrapper creates the table and index automatically on first use.

from dotenv import load_dotenvload_dotenv()from langchain_openai import OpenAIEmbeddingsfrom langchain_postgres import PGVectorfrom langchain_text_splitters import RecursiveCharacterTextSplitterfrom langchain_core.documents import Documentembeddings = OpenAIEmbeddings(model='text-embedding-3-small')connection = os.environ['DATABASE_URL']collection = os.environ['COLLECTION_NAME']store = PGVector(    embeddings=embeddings,    collection_name=collection,    connection=connection,    use_jsonb=True,)sample_text = 'PostgreSQL is a powerful open-source relational database. pgvector adds vector similarity search.'splitter = RecursiveCharacterTextSplitter(chunk_size=500, chunk_overlap=50)chunks = splitter.split_text(sample_text)docs = [Document(page_content=c, metadata={'source': 'intro'}) for c in chunks]store.add_documents(docs)

5. Query the pipeline

Create a retriever and a question-answering chain. The chain embeds the question, searches pgvector, and generates a grounded answer.

from langchain_openai import ChatOpenAIfrom langchain.chains import RetrievalQAretriever = store.as_retriever(search_kwargs={'k': 4})llm = ChatOpenAI(model='gpt-4o-mini', temperature=0)qa = RetrievalQA.from_chain_type(llm=llm, retriever=retriever)result = qa.invoke({'query': 'What does pgvector add to PostgreSQL?'})print(result['result'])

Real-World Examples

A LangChain RAG pipeline with pgvector fits many scenarios. In customer support, companies index product manuals and past tickets. When a user asks about a billing error, the system retrieves the relevant policy and generates a precise response with a link to the source article.

In an internal knowledge base, organizations ingest Confluence pages, Notion exports, and HR documents. Employees ask natural language questions and receive answers that respect document permissions through metadata filters such as department or clearance level.

In legal tech, firms embed case law and contracts. Lawyers query the system for clauses similar to a draft agreement. Because pgvector stores metadata, the firm can filter by jurisdiction and date range while still using semantic search.

In each case the architecture remains the same; only the loaders, chunking strategy, and metadata schema change. This consistency is why LangChain plus PostgreSQL is a strong default for teams already using relational databases.

Production Code Examples

The snippet below shows a more complete moduleStructured for production use. It separates configuration, ingestion, and serving, and includes metadata filtering and basic error handling. It uses the langchain-postgres PGVector integration which manages the table schema.

import osfrom typing import Listfrom dotenv import load_dotenvfrom langchain_openai import OpenAIEmbeddings, ChatOpenAIfrom langchain_postgres import PGVectorfrom langchain_text_splitters import RecursiveCharacterTextSplitterfrom langchain_core.documents import Documentfrom langchain.chains import RetrievalQAload_dotenv()class RAGPipeline:    def __init__(self):        self.embeddings = OpenAIEmbeddings(model='text-embedding-3-small')        self.connection = os.environ['DATABASE_URL']        self.collection = os.environ.get('COLLECTION_NAME', 'docs')        self.store = PGVector(            embeddings=self.embeddings,            collection_name=self.collection,            connection=self.connection,            use_jsonb=True,        )        self.splitter = RecursiveCharacterTextSplitter(            chunk_size=800, chunk_overlap=80        )        self.llm = ChatOpenAI(model='gpt-4o-mini', temperature=0)    def ingest(self, text: str, source: str) -> None:        chunks = self.splitter.split_text(text)        docs: List[Document] = [            Document(page_content=c, metadata={'source': source})            for c in chunks        ]        self.store.add_documents(docs)    def ask(self, question: str, tenant_id: str = None) -> str:        filter_arg = {'source': tenant_id} if tenant_id else None        retriever = self.store.as_retriever(            search_kwargs={'k': 5, 'filter': filter_arg}        )        qa = RetrievalQA.from_chain_type(            llm=self.llm, retriever=retriever        )        return qa.invoke({'query': question})['result']if __name__ == '__main__':    pipe = RAGPipeline()    pipe.ingest('PostgreSQL pgvector enables cosine similarity search.', 'demo')    print(pipe.ask('What does pgvector enable?'))

For a serving API you can wrap the class in FastAPI. Use a connection pool and run ingestion as a background worker. The key production concern is to avoid embedding the same text twice; maintain a content hash in metadata to make ingestion idempotent.

Comparison Table

Choosing a vector store depends on your operational constraints. The table below compares pgvector with popular alternatives. All are capable; the difference is primarily around hosting, scaling, and integration with existing data.

If you already run PostgreSQL with replicas and backups, pgvector is often the lowest-friction choice. It keeps your vectors and relational data in one transactional boundary.

Best Practices

Chunk size matters. Too small and you lose context; too large and the retriever returns noisy matches. A common starting point is 500 to 1000 characters with 10 to 20 percent overlap. Evaluate retrieval quality on a sample of real questions.

Use stable embedding models. If you change the embedding model version, the vector space changes and old vectors become incompatible. Version your collections and re-embed when you upgrade.

Store rich metadata. Tenant ID, document type, language, and timestamp allow you to filter at query time. This improves relevance and supports multi-tenant isolation.

Add a re-ranker when precision is critical. A lightweight cross-encoder can reorder the top 20 results from pgvector before they reach the LLM.

Log everything. Store the query, retrieved chunk IDs, distances, model used, and latency. This data powers evaluation and debugging.

Common Mistakes

One mistake is skipping text splitting and embedding entire documents. Large chunks exceed token limits and dilute similarity scores. Another is using the wrong distance metric; pgvector defaults differ by version, so explicitly set cosine or L2 in your index.

Developers often forget to create an index, causing full table scans as data grows. They also hard-code API keys in source code, creating security risks. Another frequent error is not handling embedding API failures, which can stall ingestion.

Finally, many teams treat RAG as fire-and-forget. Without evaluation, retrieval drift goes unnoticed and users lose trust. Schedule periodic reviews of answer quality.

Performance Tips

Create an HNSW index for low-latency queries when your dataset is up to a few million rows. For larger datasets, IVFFlat with a tuned lists parameter works well if you can accept approximate search.

Batch embed documents. The OpenAI embeddings API accepts multiple inputs per request, reducing network overhead. Use a worker pool to parallelize ingestion.

Use connection pooling in the API layer. PGVector opens a connection per request by default if you do not configure SQLAlchemy pool size. Set pool_pre_ping to avoid stale connections.

Cache frequent queries. If many users ask the same question, store the answer with a short TTL to reduce LLM cost and latency.

Security Considerations

Protect your OpenAI key with environment variables and a secrets manager. Rotate keys if you suspect exposure.

Be careful with PII. Embeddings can inadvertently encode sensitive data. If a document contains personal information, consider redaction before embedding or use a private embedding model hosted in your own network.

Use PostgreSQL row-level security or metadata filters to ensure users only retrieve documents they are authorized to see. Never build a retriever filter by concatenating user input into a SQL string; rely on the parameterized queries provided by LangChain wrappers.

Rate limit your API. A RAG endpoint that calls an external LLM can become a cost amplifier if abused. Add authentication and per-user quotas.

Deployment Notes

Run PostgreSQL as a managed service or a tuned self-hosted cluster with regular backups. Enable the vector extension in the migration scripts so environments are reproducible.

Containerize the ingestion worker and API separately. The worker needs the embedding API key and write access to the vector table. The API needs read access and the LLM key.

Use Alembic or another migration tool to manage schema changes. Although PGVector creates tables automatically, explicit migrations are safer for production and let you add relational columns.

Scale the API horizontally behind a load balancer. The worker can scale based on queue depth if you place ingestion jobs in a message broker such as Redis or RabbitMQ.

Debugging Tips

If retrieval returns irrelevant chunks, log the raw cosine distances. A high distance means the embedding space is not capturing the intent. Try a different model or chunk size.

If you see dimension mismatch errors, verify that the collection was created with the same embedding model. Dropping and re-creating the collection solves stale schema issues.

Use PostgreSQL EXPLAIN ANALYZE on the similarity query to confirm the index is used. A sequential scan indicates the index is missing or the operator class is wrong.

When the LLM ignores context, inspect the prompt template. Ensure the retrieved text is actually inserted and not truncated by token limits.

FAQ

What is a LangChain RAG pipeline?

A LangChain RAG pipeline is an orchestrated workflow where LangChain loads and splits documents, generates embeddings, stores them in a vector database such as pgvector, retrieves relevant chunks for a query, and passes them to a language model to produce a grounded answer.

Why use PostgreSQL pgvector instead of a dedicated vector database?

pgvector lets you keep vectors and relational data in one transactional store. If your team already operates PostgreSQL, you avoid new infrastructure, benefit from existing backups and security, and can join vector search with SQL filters.

Can I use open-source embedding models with this pipeline?

Yes. LangChain supports many embedding providers. You can replace OpenAIEmbeddings with HuggingFaceEmbeddings or a local sentence-transformers model. Just ensure the vector dimension matches your pgvector column.

How do I update documents after they change?

Maintain a content hash or version in metadata. On update, delete old chunks for that document ID and re-ingest the new text. This keeps the index consistent without duplicates.

How many documents can pgvector handle?

pgvector scales to millions of vectors on a single instance with proper indexing. For very large workloads, consider sharding, HNSW tuning, or a dedicated vector store. Performance depends on RAM, index type, and query patterns.

Is RAG better than fine-tuning?

RAG is usually better for factual, frequently changing knowledge because it retrieves fresh data at query time. Fine-tuning bakes knowledge into weights and is better for style or task adaptation. Many production systems combine both.

How do I evaluate answer quality?

Use frameworks like LangChain evaluators or Ragas. Measure context relevance, faithfulness to retrieved text, and answer correctness against a labeled set. Logging retrieved chunk IDs helps manual review.

Can I filter results by tenant in a multi-tenant app?

Yes. Store a tenant_id in metadata and pass a filter to the retriever. pgvector supports metadata filtering through the LangChain wrapper, and you can enforce stricter isolation with PostgreSQL row-level security.

What LLM should I use for generation?

Any chat model supported by LangChain works. OpenAI GPT-4o or GPT-4o-mini are common for quality and cost balance. Open-source models like Llama 3 via Ollama or vLLM are viable for self-hosted setups.

Conclusion

Building a LangChain RAG pipeline with PostgreSQL pgvector is a pragmatic path to production-grade AI features. You reuse familiar relational infrastructure while gaining semantic search and grounded generation. Start with the step-by-step guide, apply the best practices, and harden the deployment before serving real traffic.

If you want to go further, review our related articles on Dockerizing Node.js Applications and PostgreSQL Performance Optimization linked above. Implement a small pilot, measure retrieval quality, and iterate. The pattern is stable, extensible, and ready for real users.