Build RAG Apps with LlamaIndex and pgvector

Retrieval Augmented Generation (RAG) has emerged as the most practical pattern for building AI applications that need access to custom data — and combining LlamaIndex with pgvector offers one of the most production-ready stacks available today. This tutorial walks you through building a complete RAG pipeline from scratch, covering everything from environment setup to query optimization.

Unlike experimental setups that rely on in-memory vector stores, this approach uses PostgreSQL as your vector database, making it suitable for real-world deployments where persistence, scalability, and familiarity matter.

Key Takeaways

• LlamaIndex simplifies RAG development by abstracting document loading, chunking, indexing, and querying into a clean API
• pgvector extends PostgreSQL with vector similarity search, eliminating the need for a separate vector database
• This stack supports documents up to millions of chunks with proper indexing
• Total setup time is approximately 30-45 minutes for a working prototype
• The combination is significantly cheaper than managed vector database services like Pinecone ($70+/month) for small-to-mid-scale projects
• You can deploy the entire stack on a single $20/month cloud server for development

Why LlamaIndex and pgvector Make a Powerful Combination

RAG applications solve a fundamental limitation of large language models: they don't know about your private data. By retrieving relevant documents and injecting them into the LLM's context window, RAG bridges the gap between general AI capabilities and domain-specific knowledge.

LlamaIndex, developed by Jerry Liu's team, has grown into the leading open-source framework for building RAG systems. It currently has over 37,000 GitHub stars and supports more than 160 data connectors. The framework handles the entire RAG pipeline — from ingesting PDFs, web pages, and databases to chunking, embedding, storing, and querying documents.

pgvector is a PostgreSQL extension that adds vector similarity search capabilities directly to the world's most popular open-source relational database. Instead of spinning up a separate Pinecone, Weaviate, or Qdrant instance, you store embeddings right alongside your traditional data. This means fewer moving parts, simpler infrastructure, and the full power of SQL for metadata filtering.

Compared to using a standalone vector database, pgvector reduces operational complexity by roughly 40-50% since most engineering teams already run PostgreSQL in production.

Setting Up Your Development Environment

Before writing any code, you need 3 core components installed: Python 3.10+, PostgreSQL 15+ with the pgvector extension, and an OpenAI API key for embeddings and LLM inference.

Installing Dependencies

Start by creating a virtual environment and installing the required Python packages:

• llama-index — the core framework (version 0.10+ recommended)
• llama-index-vector-stores-postgres — the pgvector integration module
• psycopg2-binary — PostgreSQL adapter for Python
• python-dotenv — for managing environment variables securely
• sqlalchemy — ORM layer used by LlamaIndex's PostgreSQL integration

Run the following in your terminal: pip install llama-index llama-index-vector-stores-postgres psycopg2-binary python-dotenv sqlalchemy

Configuring PostgreSQL with pgvector

Once PostgreSQL is running, enable the pgvector extension on your target database. Connect via psql and execute CREATE EXTENSION IF NOT EXISTS vector;. This single command unlocks vector storage and similarity search.

Create a dedicated database for your RAG application. A clean separation ensures your vector data doesn't interfere with existing production tables.

Building the RAG Pipeline Step by Step

The core RAG workflow in LlamaIndex follows 5 stages: load, chunk, embed, store, and query. Each stage is modular, meaning you can swap components without rewriting the entire pipeline.

Step 1 — Loading Documents

LlamaIndex's SimpleDirectoryReader handles most common file formats out of the box. Point it at a folder containing your PDFs, text files, or Markdown documents. For a typical knowledge base of 50-100 documents, loading takes under 10 seconds.

The framework automatically extracts text and preserves basic metadata like filenames and page numbers. For more complex sources — Notion, Confluence, Slack, or Google Drive — LlamaIndex offers dedicated data connectors through its LlamaHub ecosystem.

Step 2 — Chunking and Embedding

Chunking splits your documents into smaller segments that fit within the LLM's context window. LlamaIndex defaults to a chunk size of 1,024 tokens with a 20-token overlap, which works well for most use cases.

For technical documentation, consider reducing chunk size to 512 tokens to improve retrieval precision. For narrative content like reports, 1,024-2,048 tokens preserves better context.

Embeddings convert each chunk into a high-dimensional vector. OpenAI's text-embedding-3-small model produces 1,536-dimensional vectors at a cost of $0.02 per 1 million tokens — making it extremely affordable. For 10,000 document chunks, embedding costs roughly $0.05.

Step 3 — Storing Vectors in pgvector

This is where the pgvector integration shines. Configure the PGVectorStore with your database connection details, specifying the table name, embedding dimension (1,536 for OpenAI's small model), and the distance metric (cosine similarity is the default and recommended choice).

LlamaIndex automatically creates the necessary tables and indexes. The StorageContext wraps the vector store, and calling VectorStoreIndex.from_documents() handles the entire ingest pipeline — chunking, embedding, and storing — in a single function call.

For datasets exceeding 100,000 vectors, add an IVFFlat or HNSW index to pgvector. HNSW indexes offer better recall (typically 95-99%) with slightly higher memory usage, while IVFFlat indexes are more memory-efficient for very large datasets.

Step 4 — Querying Your Data

Create a query engine from your index with a single method call. When a user submits a question, LlamaIndex automatically embeds the query, performs similarity search against pgvector, retrieves the top-k most relevant chunks (default is 2), and sends them to the LLM along with the original question.

The LLM — typically GPT-4o or GPT-3.5-turbo — synthesizes a natural language answer grounded in your retrieved documents. This dramatically reduces hallucination compared to asking the LLM directly.

Optimizing RAG Performance for Production

A basic RAG pipeline gets you 70-80% of the way there. Production-quality systems require additional optimization across several dimensions.

Retrieval Quality Improvements

• Increase top-k from 2 to 5-8 for complex queries that require synthesizing multiple sources
• Add metadata filtering to narrow results by document type, date, or category before vector search
• Implement hybrid search combining pgvector's semantic search with PostgreSQL's full-text search (tsvector) for keyword-sensitive queries
• Use re-ranking with a cross-encoder model like cross-encoder/ms-marco-MiniLM-L-6-v2 to reorder results by relevance after initial retrieval

Infrastructure Considerations

• Connection pooling — use PgBouncer or SQLAlchemy's built-in pooling to handle concurrent queries efficiently
• Batch ingestion — for large document sets (10,000+ files), use async ingestion pipelines to avoid timeout issues
• Caching — LlamaIndex supports response caching, which can reduce LLM API costs by 30-50% for repeated queries
• Monitoring — integrate with LlamaIndex's built-in observability tools or third-party platforms like Arize Phoenix to track retrieval quality

Comparing This Stack to Alternatives

The LlamaIndex + pgvector combination isn't the only option for building RAG applications. Here's how it compares to popular alternatives:

LangChain + Pinecone remains the most commonly cited stack in tutorials. However, Pinecone's managed service starts at $70/month for the Standard tier, and LangChain's abstraction layer adds complexity that many developers find unnecessary for straightforward RAG use cases.

LlamaIndex + ChromaDB is excellent for prototyping. ChromaDB runs entirely in-memory or on local disk, making it fast to set up. However, it lacks the durability and scalability guarantees of PostgreSQL for production deployments.

Custom pipelines with raw OpenAI + pgvector offer maximum control but require significantly more code. LlamaIndex reduces boilerplate by approximately 60-70%, letting you focus on application logic rather than plumbing.

What This Means for Developers and Teams

Enterprise teams already running PostgreSQL can add RAG capabilities without introducing new infrastructure. This reduces vendor lock-in and simplifies compliance, especially in regulated industries like finance and healthcare where data residency matters.

Solo developers and startups benefit from the low cost. A complete RAG application serving hundreds of queries per day can run on a $20/month DigitalOcean or AWS Lightsail instance, with OpenAI API costs adding perhaps $5-15/month depending on usage.

The skills required — Python, SQL, and basic understanding of embeddings — are already common in most engineering teams. There's no need to learn a new query language or manage unfamiliar infrastructure.

Looking Ahead — The Future of RAG Architecture

RAG is evolving rapidly. Advanced patterns like agentic RAG — where the system dynamically decides which tools and data sources to query — are gaining traction in 2024-2025. LlamaIndex already supports agent-based workflows through its ReActAgent and OpenAIAgent classes.

pgvector 0.7.0, released in early 2024, introduced significant performance improvements including parallel index builds and better memory management. Future versions are expected to support quantized vectors, which could reduce storage requirements by 4-8x.

The convergence of LLMs, vector databases, and orchestration frameworks is making RAG accessible to a much broader audience. What once required a specialized ML engineering team can now be built by any backend developer in an afternoon.

For teams looking to get started, the combination of LlamaIndex and pgvector represents the sweet spot of simplicity, cost-effectiveness, and production readiness. Start with a small document set, validate the retrieval quality, and scale from there.