Building RAG with LangChain and pgvector: A Practical Guide

Introduction

Retrieval-augmented generation (RAG) has become a standard pattern for building large language model (LLM) applications that need access to private or dynamic knowledge. In this guide, we will build a complete RAG pipeline using LangChain and PostgreSQL with the pgvector extension. By storing embeddings in a database you already operate, you avoid the cost and complexity of a dedicated vector store while keeping full SQL power.

Modern LLMs are trained on broad data but lack specific, up-to-date, or proprietary information. RAG closes that gap by retrieving relevant text at query time and placing it into the prompt context. The model then grounds its response in the supplied evidence. This approach reduces hallucination and makes answers verifiable.

LangChain is a popular open-source orchestration library that abstracts document loaders, text splitters, vector stores, and chains. PGVector is the LangChain integration for PostgreSQL pgvector. Together they let a Python developer stand up a RAG service in an afternoon, then harden it for production with familiar database tooling.

In the following sections we explain the underlying concepts, present a reference architecture, walk through a step-by-step build, show production-grade code, compare pgvector with alternatives, and share best practices, security, deployment, and debugging advice. The goal is a single source of truth you can apply to real projects.

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, we must understand the moving parts of a RAG system. The first component is the embedding model. An embedding model converts text into a fixed-length numeric vector that captures semantic meaning. OpenAI provides the text-embedding-3-small and text-embedding-3-large models, which produce high-quality vectors. Alternatively, open-source models like sentence-transformers can be self-hosted.

The second component is the vector store. A vector store persists these embeddings and provides a nearest-neighbor search. pgvector is a PostgreSQL extension that adds a vector data type and indexing methods such as IVFFlat and HNSW. It allows you to store vectors alongside relational metadata in the same table.

The third component is the retrieval strategy. Given a user query, we embed it and search for the most similar document chunks. Similarity is typically measured by cosine distance or inner product. The top-k chunks are returned. Cosine similarity is the dot product of two normalized vectors; it ranges from -1 to 1, where 1 means identical direction.

The fourth component is the generation step. LangChain wraps a chat model (for example GPT-4o or Claude) in a chain that injects the retrieved context into a prompt template. The model then synthesizes an answer constrained by the provided documents. The prompt usually contains a system instruction to only use the context and cite sources.

Chunking is a vital preprocessing step. Long documents are split into overlapping segments of, say, 500 to 1000 tokens. Smaller chunks improve retrieval precision, while overlap preserves context across boundaries. LangChain provides recursive character splitters that respect paragraph and sentence structure. You should also strip boilerplate HTML and normalize whitespace before splitting.

Vector math matters. If you use a 1536-dimension embedding, every chunk becomes a point in 1536-dimensional space. Approximate nearest neighbor (ANN) algorithms like HNSW build graphs to traverse this space quickly. Exact search (brute force) computes distance to every row; it is accurate but slow at scale. Indexes trade a small recall loss for massive speedups.

Finally, evaluation completes the loop. RAG quality depends on both retrieval recall and generation faithfulness. You should measure whether the correct documents are fetched and whether the model cites them accurately. Tools like Ragas compute context precision and answer correctness against a golden set.

LangChain Expression Language (LCEL) is the modern way to compose these steps. Instead of legacy chain classes, you build runnables: retriever | prompt | model | parser. This is declarative and supports streaming, batching, and async. Throughout this article we use current LangChain best practices with LCEL where appropriate.

Architecture Overview

A typical LangChain + pgvector RAG deployment consists of four layers. The ingestion layer reads raw documents (PDF, HTML, markdown), cleans them, splits into chunks, embeds each chunk via the embedding API, and writes rows to PostgreSQL. The storage layer is a PostgreSQL instance with the pgvector extension enabled; a table named document_embeddings holds columns for id, content, metadata, and a vector column.

The API layer is a service (often FastAPI or Node.js) that exposes an endpoint to accept user questions. It uses LangChain retrievers backed by PGVector to fetch context. The orchestration layer builds a prompt with a system message, the retrieved context, and the user question, then calls the LLM.

In production you should separate the embedding job from the query path. Embeddings can be computed in a batch worker, while the online path only does a single embedding per query. Use connection pooling (PgBouncer) to handle concurrent vector searches. For scale, consider read replicas and HNSW indexes.

The conceptual data flow is: Source Documents -> Loader -> Splitter -> Embedding Model -> PGVector Table -> Retriever -> Prompt -> LLM -> Response. Caching of frequent queries at the API layer reduces cost and latency. A Redis cache is common.

You may also add a reranking stage. After initial top-20 vector results, a cross-encoder reranker reorders them by relevance before passing to the LLM. This improves accuracy with minimal overhead. PGVector can return more candidates than final k, and LangChain supports ensemble retrievers.

For multi-tenant SaaS, isolate data by a tenant_id metadata column and enforce it in every query. PostgreSQL row-level security can automatically filter rows based on the session variable. This keeps the RAG logic simple while meeting compliance.

Step-by-Step Guide

We will now build a minimal but complete RAG application. Assume you have PostgreSQL 14+ and Python 3.10+ installed. You also need an OpenAI API key exported as OPENAI_API_KEY.

1. Enable pgvector

Connect to your database as a superuser and run the following SQL. The vector extension is packaged as a dynamic library; once created, it persists across restarts.

CREATE EXTENSION IF NOT EXISTS vector;

Verify by checking the extension list. Then create a table that stores the embedding alongside the textual content and a JSONB metadata field for filtering:

CREATE TABLE document_embeddings (  id SERIAL PRIMARY KEY,  content TEXT,  metadata JSONB,  embedding vector(1536));

The dimension 1536 matches OpenAI text-embedding-3-small. If you use a different model, adjust the number accordingly.

2. Install Python packages

Create a virtual environment and install the required libraries. We use langchain-community for PGVector and langchain-openai for models.

pip install langchain langchain-community langchain-openai pgvector psycopg2-binary tiktoken

3. Ingest documents

Use LangChain's text splitter and PGVector store. The script below loads a text file, splits it, embeds via OpenAI, and writes to Postgres. Replace credentials with your actual connection string.

from langchain_community.document_loaders import TextLoaderfrom langchain_text_splitters import RecursiveCharacterTextSplitterfrom langchain_community.vectorstores import PGVectorfrom langchain_openai import OpenAIEmbeddingsloader = TextLoader('sample.txt')docs = loader.load()splitter = RecursiveCharacterTextSplitter(chunk_size=500, chunk_overlap=50)chunks = splitter.split_documents(docs)embeddings = OpenAIEmbeddings(model='text-embedding-3-small')connection_string = 'postgresql+psycopg2://user:password@localhost:5432/rag'store = PGVector.from_documents(    chunks,    embeddings,    connection_string=connection_string,    collection_name='docs')

4. Query the store

Create a retriever and fetch relevant chunks for a question. The search_kwargs control how many neighbors to return.

retriever = store.as_retriever(search_kwargs={'k': 4})query = 'What is the refund policy?'context = retriever.get_relevant_documents(query)for doc in context:    print(doc.page_content)

5. Build a QA chain

Wrap the retriever in a RetrievalQA chain. The stuff chain type concatenates all context into one prompt. For very large contexts, consider map_reduce or refine.

from langchain.chains import RetrievalQAfrom langchain_openai import ChatOpenAIllm = ChatOpenAI(model='gpt-4o-mini')qa = RetrievalQA.from_chain_type(llm=llm, retriever=retriever, chain_type='stuff')answer = qa.invoke({'query': query})print(answer['result'])

You now have a working RAG loop. In the next sections we harden this for real traffic.

Real-World Examples

Many teams use this stack for internal knowledge bases. A support organization can index thousands of past tickets and product manuals. When a customer asks about a billing issue, the retriever pulls relevant policy pages and the LLM drafts a precise response with citations.

Another example is legal contract analysis. Lawyers upload contracts; the system splits them by clause. An attorney can ask, "Does this contract include a termination for convenience clause?" and receive the exact paragraph plus explanation. Because the data stays in their Postgres, compliance teams approve easily.

In software companies, engineering teams index their codebase documentation and RFCs. New engineers query, "How does our auth service handle token refresh?" and get answers sourced from the actual design docs, reducing onboarding time and Slack interruptions.

Another scenario is e-commerce product discovery. Retailers embed lengthy product descriptions and reviews. Shoppers ask natural language questions like, "Which running shoes are good for flat feet and under $100?" The system combines vector similarity with SQL filters on price and category.

Healthcare organizations use RAG for medical literature search, ensuring answers cite peer-reviewed sources. The audit trail of retrieved chunks helps meet regulatory requirements. These examples show the pattern is domain agnostic.

Production Code Examples

The following snippet shows a more robust ingestion service with environment configuration, batching, and error handling. It uses a connection pool and stores metadata for filtering. Notice the use of os.getenv for secrets and the pre_delete_collection flag set to False to avoid wiping data.

import osfrom langchain_community.document_loaders import DirectoryLoaderfrom langchain_text_splitters import RecursiveCharacterTextSplitterfrom langchain_community.vectorstores import PGVectorfrom langchain_openai import OpenAIEmbeddingsdef build_store():    embeddings = OpenAIEmbeddings(model=os.getenv('EMBEDDING_MODEL', 'text-embedding-3-small'))    conn = os.getenv('DATABASE_URL')    return PGVector(        connection_string=conn,        embedding_function=embeddings,        collection_name=os.getenv('COLLECTION', 'prod_docs'),        pre_delete_collection=False    )def ingest(path):    loader = DirectoryLoader(path, glob='**/*.md')    docs = loader.load()    splitter = RecursiveCharacterTextSplitter(        chunk_size=800,        chunk_overlap=80,        length_function=len    )    chunks = splitter.split_documents(docs)    store = build_store()    store.add_documents(chunks)    return len(chunks)if __name__ == '__main__':    count = ingest('./knowledge')    print(f'Ingested {count} chunks')

For the query API, here is a FastAPI route with caching and timeout control. We use Redis to store answers for an hour, reducing repeated LLM calls. The retriever returns five chunks for better context.

from fastapi import FastAPI, HTTPExceptionfrom langchain_community.vectorstores import PGVectorfrom langchain_openai import OpenAIEmbeddings, ChatOpenAIfrom langchain.chains import RetrievalQAimport os, redisapp = FastAPI()r = redis.Redis.from_url(os.getenv('REDIS_URL'))embeddings = OpenAIEmbeddings()store = PGVector(connection_string=os.getenv('DATABASE_URL'),                 embedding_function=embeddings,                 collection_name='prod_docs')llm = ChatOpenAI(model='gpt-4o-mini', temperature=0)qa = RetrievalQA.from_chain_type(llm=llm,                                 retriever=store.as_retriever(search_kwargs={'k': 5}),                                 chain_type='stuff')@app.get('/ask')def ask(q: str):    if r.exists(q):        return {'answer': r.get(q).decode(), 'cached': True}    try:        res = qa.invoke({'query': q})    except Exception as e:        raise HTTPException(status_code=502, detail='LLM error')    r.setex(q, 3600, res['result'])    return {'answer': res['result'], 'cached': False}

This code demonstrates separation of concerns, caching, and error boundaries expected in production. You should add authentication, request validation, and structured logging before shipping.

Comparison Table

Choosing pgvector versus a dedicated vector database depends on your context. The table below compares key aspects.

If you already run PostgreSQL, pgvector is the fastest path to production without new infrastructure. For ultra-large scale beyond tens of millions of vectors, evaluate dedicated services.

Best Practices

• Use consistent embedding models between ingestion and query. Mismatched dimensions cause silent failures.
• Choose chunk size based on your domain. Technical docs benefit from 500-800 tokens; prose may use larger.
• Store metadata (source, date, author) in JSONB to enable filtered retrieval.
• Create an HNSW index for fast approximate search: CREATE INDEX ON document_embeddings USING hnsw (embedding vector_cosine_ops);
• Version your collection name when you change chunking strategy to avoid mixing old vectors.
• Monitor token usage and cache frequent queries to reduce cost.
• Evaluate retrieval with golden datasets; do not ship without measuring recall.
• Set a similarity score threshold to discard low-relevance chunks before prompting.
• Use hybrid search combining full-text and vector for better precision on keyword-heavy queries.
• Keep your ingestion idempotent; re-running should update not duplicate rows.

Common Mistakes

• Embedding the query with a different model than documents, leading to zero similarity.
• Setting chunk size too large, causing the context window to truncate retrieved content.
• Forgetting to enable the vector extension before creating tables, resulting in type errors.
• Using exact search (no index) in production, which is O(N) and slow.
• Ignoring metadata filtering, forcing the model to sift through irrelevant chunks.
• Not handling API rate limits, causing ingestion jobs to fail midway.
• Mixing embedding dimensions in the same table, breaking the vector operator.
• Logging raw user questions with PII to insecure sinks.

Performance Tips

Vector search is computationally intensive. First, always create an appropriate index. HNSW provides better recall and speed than IVFFlat for most workloads. Second, reduce dimension if acceptable; smaller vectors mean less memory.

Third, use a dedicated connection pool. PGVector opens connections per request; PgBouncer in transaction mode helps. Fourth, precompute embeddings for static documents offline; only embed the query at runtime.

Fifth, limit returned fields. Select only content and metadata, not the full vector, when fetching for the LLM. Sixth, consider a two-stage retrieval: cheap keyword filter then vector search.

Seventh, run VACUUM ANALYZE after large ingestions so the planner has fresh statistics. Eighth, size your Postgres shared buffers and work memory to hold index pages. Ninth, benchmark with realistic data volume before launch.

Security Considerations

RAG systems expose sensitive data if not governed. Apply row-level security in PostgreSQL to restrict which documents a user can retrieve. Use separate collections per tenant.

Never log full prompts with customer data. Rotate OpenAI API keys via secret managers. Use SSL for database connections and verify certificates.

Sanitize user queries to prevent prompt injection that attempts to leak system context. Although pgvector uses parameterized queries, ensure your metadata filters are not built via string concatenation.

Finally, audit the documents ingested; a malicious file could embed instructions to the LLM. Scan and review content before indexing. Apply least-privilege database roles for the application user.

Deployment Notes

For deployment, containerize the ingestion worker and API separately. Use Docker images with pinned Python versions. The database can be a managed Postgres (AWS RDS, GCP Cloud SQL) with pgvector installed; verify extension availability with your provider.

Set environment variables for DATABASE_URL, OPENAI_API_KEY, and REDIS_URL. Run migrations to create the extension and table as part of CI/CD. Scale the API horizontally behind a load balancer; the database connection pool handles concurrency.

For serverless, note that cold starts may slow first embedding. Use provisioned concurrency or a warm pool. Monitor slow query logs for sequential scans on the vector column. If using Kubernetes, set resource requests for memory because vector indexes are RAM hungry.

Debugging Tips

When retrieval returns irrelevant results, first check the embedding dimension and model name in both scripts. Print the vector length. Second, run a raw SQL similarity query to confirm index usage: EXPLAIN ANALYZE SELECT * FROM document_embeddings ORDER BY embedding <=> '[...]' LIMIT 5;

If the LLM ignores context, verify the prompt template includes the context variable. Log the final prompt in development. For ingestion errors, wrap add_documents in try-except and inspect the PostgreSQL logs for constraint violations.

Use LangChain's verbose mode or LangSmith tracing to see each chain step. This reveals whether the retriever or the model is the bottleneck. Enable pg_stat_statements to find slow queries.

FAQ

What PostgreSQL version supports pgvector?

pgvector requires PostgreSQL 11 or later, but we recommend 14+ for better indexing and maintenance. Managed providers like AWS RDS support it on recent versions. Always check your cloud's extension allowlist.

Can I use open-source embedding models?

Yes. LangChain supports sentence-transformers and HuggingFace models. You simply swap the OpenAIEmbeddings class for HuggingFaceEmbeddings, ensuring dimension matches your table. This removes external API dependency.

How many documents can pgvector handle?

pgvector scales to millions of vectors on a single instance with HNSW indexing. Beyond that, consider partitioning or a dedicated vector store. Performance depends on RAM and index settings. Proper indexing is critical at scale.

Is RAG better than fine-tuning?

RAG is better for dynamic, frequently updated knowledge because you update the database instead of retraining. Fine-tuning excels at style and task adaptation. Many systems combine both for optimal results.

How do I keep embeddings in sync with source files?

Implement a hash of file content stored in metadata. On re-ingestion, compare hashes and update only changed chunks. A scheduled worker can crawl sources and refresh. This keeps the index fresh without full rebuilds.

Can I filter by metadata in PGVector?

Yes. PGVector supports a metadata filter parameter that translates to a SQL WHERE clause on the JSONB column. This enables tenant isolation or date filtering. You can combine it with vector search seamlessly.

What is the cost of OpenAI embeddings?

OpenAI charges per token for embeddings; text-embedding-3-small is very cheap (fractions of a cent per million tokens). For large corpora, batch your requests to minimize overhead. Self-hosted models eliminate per-call cost.

How do I evaluate RAG quality?

Use frameworks like Ragas or custom metrics. Measure context recall (did we fetch the right doc?) and answer faithfulness (does the answer match context?). Human review remains valuable for edge cases.

Can pgvector do hybrid search?

Yes. You can combine full-text search (tsvector) with vector similarity in a single SQL query using RRF or weighted scoring. LangChain's PGVector supports optional keyword search, letting you boost exact matches.

Should I use async PGVector?

If your API is async (FastAPI with async routes), use the async PGVector class and async embeddings to avoid blocking the event loop. This improves throughput under concurrent load. Synchronous code is fine for batch jobs.

Conclusion

Building RAG with LangChain and pgvector gives you a powerful, cost-effective knowledge system without leaving the PostgreSQL ecosystem. We covered architecture, code, operations, and pitfalls. Start with a small dataset, measure retrieval quality, then scale with indexes and caching.

If you want to go further, explore our related articles on PostgreSQL performance and vector databases. Subscribe to the RakibAhsan.xyz newsletter for weekly deep dives, and implement your first RAG pipeline this week. The combination of LangChain's ergonomics and PostgreSQL's reliability is a winning stack for modern AI features.