The world of AI development often feels like a frantic game of architectural Jenga. You stack a vector database here, a caching layer there, an orchestration service on top, and pray the whole thing doesn’t topple over. Our team was deep in this game, managing a complex Retrieval-Augmented Generation (RAG) system with all its moving parts. Then, we had a revelation that simplified everything: we realized our entire RAG pipeline is now just a Postgres function, and it has fundamentally changed how we build and deploy AI-powered features.
If you’re tired of managing disparate services, battling network latency, and wrestling with data synchronization issues, this architectural shift might be the breakthrough you’ve been looking for. By leveraging the power of modern PostgreSQL extensions, we collapsed our entire RAG stack into a single, atomic, and surprisingly performant database operation.
The Fragile Complexity of Traditional RAG Architectures
Before our simplification, our RAG setup looked familiar to anyone working in the space. It was a distributed system held together with API calls and hope.
The typical flow involved:
Application Service: A backend service (e.g., Python, Node.js) receives a user's query.
Embedding Generation: The service calls an external API like OpenAI to convert the user's query into a vector embedding.
Vector Database Query: It then sends this embedding to a dedicated vector database (like Pinecone, Weaviate, or Milvus) to find the most similar document chunks.
Context Retrieval: The vector database returns a list of IDs, which our application service then uses to fetch the actual text content from our primary Postgres database.
Created by Andika's AI Assistant
Full-stack developer passionate about building great user experiences. Writing about web development, React, and everything in between.
LLM Prompting: Finally, the service combines the retrieved context with the original query, formats it into a prompt, and sends it to a large language model (LLM) for the final answer.
This multi-step process is functional, but it’s riddled with pain points:
High Latency: Every external API call and network hop between services adds precious milliseconds, resulting in a sluggish user experience.
Operational Nightmare: We were managing and paying for at least three distinct services—our application, our primary database, and our vector database. This meant separate monitoring, scaling, backup, and security policies.
Data Consistency Issues: Keeping the vector database perfectly in sync with our primary Postgres database was a constant source of bugs. A failed ETL job could leave our search results stale or, worse, completely out of sync with the source data.
We knew there had to be a better way. The solution was sitting right in front of us, in the database we were already using.
The Solution: A Database-First RAG Implementation
The core idea was to stop treating our database as a dumb storage layer and start leveraging its powerful computational capabilities. By bringing the entire RAG workflow inside Postgres, we could eliminate network latency, simplify our stack, and ensure transactional data integrity. This database-driven RAG approach is made possible by two key pieces of technology.
Why Postgres? The Power of pgvector
The foundation of our new architecture is pgvector, an open-source extension that adds vector similarity search capabilities directly to PostgreSQL. Instead of shipping our data off to a specialized database, pgvector allows us to store and query embeddings right alongside our primary application data.
This co-location is a game-changer. Suddenly, you can:
Perform lightning-fast hybrid searches that combine vector similarity with traditional SQL WHERE clauses (e.g., "find documents similar to this query, but only for user_id = 123 and created in the last 30 days").
Leverage Postgres's mature ecosystem for backups, replication, and security.
Ensure data and its corresponding embedding are always in sync using atomic transactions.
Orchestrating Logic with PL/pgSQL
The second piece of the puzzle is PL/pgSQL, Postgres's native procedural language. It’s a Turing-complete language that allows you to write complex functions and stored procedures that execute directly on the database server. Using an extension like pg_net, we can even empower our SQL functions to make external HTTP requests—the final key needed to call embedding and LLM APIs.
Anatomy of Our Postgres RAG Function
By combining pgvector and PL/pgSQL, we created a single SQL function that encapsulates the entire RAG process. An application simply calls this function with a user's query and gets the final, AI-generated answer back in a single database call.
Here’s a simplified look at what our Postgres-native RAG function looks like:
CREATEORREPLACEFUNCTION generate_rag_response(user_query TEXT)RETURNSTEXTAS $$
DECLARE query_embedding VECTOR(1536); context_docs TEXT; llm_prompt TEXT; llm_response JSONB; final_answer TEXT;BEGIN-- Step 1: Generate an embedding for the user's query-- This calls an external embedding API via a secure HTTP requestSELECT embedding INTO query_embedding
FROM DBLINK('embedding_service','SELECT embed('''|| user_query ||''')')AS t(embedding vector(1536));-- Step 2: Find the most relevant document chunks using pgvectorSELECT STRING_AGG(content, E'\n\n')INTO context_docs
FROM(SELECT content FROM documents
ORDERBY embedding <=> query_embedding
LIMIT5)AS top_docs;-- Step 3: Construct the final prompt for the LLM llm_prompt :=FORMAT('Using the following context, please answer the question.\n\nContext:\n%s\n\nQuestion:\n%s', context_docs, user_query
);-- Step 4: Call the LLM completion API with the constructed prompt-- This is another secure, external HTTP requestSELECT content INTO llm_response
FROM pg_net.http_post( url :='https://api.openai.com/v1/chat/completions', headers :='{"Content-Type": "application/json", "Authorization": "Bearer ..."}'::JSONB, body := jsonb_build_object('model','gpt-4-turbo','messages', jsonb_build_array(jsonb_build_object('role','user','content', llm_prompt))));-- Step 5: Parse the response and return the answer final_answer := llm_response->'choices'->0->'message'->>'content';RETURN final_answer;END;$$ LANGUAGE plpgsql;
From our application's perspective, the complexity is gone. We just run:
SELECT generate_rag_response('How do I reset my password?');
And get a fully formed answer back. All the intermediate steps are handled within a single, efficient database transaction.
The Tangible Benefits: Performance, Simplicity, and Cost
Migrating to a SQL-based RAG model wasn't just an academic exercise. The results were immediate and impactful.
Drastic Latency Reduction: By eliminating multiple network round trips between services, we saw our end-to-end P95 response times drop by over 50%. Queries that previously took 3-4 seconds now complete in under 1.5 seconds.
Radical Simplicity: Our infrastructure footprint shrank dramatically. We went from managing three services to just one. Our CI/CD pipelines are simpler, our monitoring is consolidated, and our on-call engineers are happier.
Guaranteed Data Integrity: With pgvector, adding a new document and its embedding happens in a single atomic transaction. It’s now impossible for our vector index to be out of sync with our source-of-truth data.
Lower Costs: Consolidating workloads onto a single, powerful Postgres instance allowed us to decommission our dedicated vector database and scale down our application service, resulting in significant monthly savings.
Is This Approach Right for You?
While moving your RAG pipeline into a Postgres function offers compelling advantages, it's not a silver bullet for every scenario.
This architecture shines when:
You are already using Postgres as your primary database.
Low-latency responses are critical for your user experience.
Your team wants to minimize operational overhead and infrastructure complexity.
You need to combine vector search with complex relational filtering.
Consider potential limitations:
Database Load: This approach centralizes computation on your database. You must monitor performance and provision your Postgres instance with adequate CPU, RAM, and IOPS to handle the load.
Extreme Scale: For workloads with tens of billions of vectors, a highly specialized, distributed vector database might still offer performance advantages, though pgvector's HNSW indexing is rapidly closing this gap.
Conclusion: Rethink Your Database's Role
Our journey to a RAG pipeline in a Postgres function was more than a simple refactor; it was a paradigm shift. We stopped seeing our database as a passive data store and started treating it as an active, intelligent data processing engine. The result is a system that is faster, simpler, cheaper, and more reliable than the complex microservice architecture it replaced.
If you're building AI applications, we urge you to explore the powerful capabilities hiding within modern databases. The next time you start architecting a complex RAG system, take a moment and ask yourself: "Could this just be a database function?" The answer might surprise you.
