Building a Sub-Second Enterprise RAG Engine with PostgreSQL, pgvector, and the Gemini API

Rohan Khedekar — Sun, 13 Sep 2026 11:38:51 +0000

The AI engineering space is currently obsessed with unbundling the data stack. The moment "Retrieval-Augmented Generation" (RAG) is mentioned, the default industry reflex is to spin up an expensive, dedicated vector database like Pinecone, Weaviate, or Milvus.

But here is the reality for most B2B applications: over-engineering your AI infrastructure introduces unnecessary network latency, complex data synchronization, and exorbitant operational costs. For the vast majority of enterprise use cases, a self-managed PostgreSQL instance—supercharged with pgvector—is faster, cheaper, and inherently more secure because your vector embeddings never leave your primary relational database.

In this article, I will walk you through the exact architecture and codebase of a production-grade document analysis agent. We will look at how to ingest documents, generate embeddings via the Gemini API, and perform sub-second semantic search directly inside PostgreSQL.

Live Demo: Try the live sandbox here: https://smart-document-agent.vercel.app/

1. The Architecture & Ingestion Flow

The core ingestion pipeline is straightforward: parse the document, split the text into manageable chunks, embed those chunks using a state-of-the-art embedding model, and store the resulting high-dimensional vectors alongside the relational metadata.

In our system, we leverage Google's Gemini API for generating embeddings. By utilizing the gemini-embedding-001 model, we convert text chunks into dense 768-dimensional vectors.

Here is the exact Python snippet handling the embedding flow:

from google import genai

def embed_text(text: str) -> list:
    """Generate embedding vector for a text chunk using Gemini."""
    try:
        client = _get_client()
        result = client.models.embed_content(
            model="gemini-embedding-001",
            contents=text,
            config=dict(output_dimensionality=768),
        )
        return result.embeddings[0].values
    except Exception as e:
        logger.error(f"Embedding failed (check API key): {e}", exc_info=True)
        return [0.0] * 768

Once the chunk is vectorized, it is immediately persisted to the PostgreSQL database. This tight coupling of ingestion and storage eliminates the need for an external ETL pipeline to a separate vector store.

2. Database Schema Design (The Technical Meat)

To store these vectors efficiently in PostgreSQL, we utilize the pgvector extension. Our Object-Relational Mapping (ORM) is handled by SQLAlchemy.

Here is the actual database schema used to store the document chunks:

from pgvector.sqlalchemy import Vector
from sqlalchemy import Column, DateTime, ForeignKey, Integer, String, Text
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from .database import Base

class DocumentChunk(Base):
    """Stores text chunks with their embedding vectors for semantic search."""
    __tablename__ = "document_chunks"

    id = Column(Integer, primary_key=True, index=True)
    document_id = Column(Integer, ForeignKey("documents.id", ondelete="CASCADE"))
    session_id = Column(String, ForeignKey("sessions.id", ondelete="CASCADE"), index=True)
    filename = Column(String)
    text = Column(Text)
    embedding = Column(Vector(768))  # Gemini embedding dimension
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    document = relationship("Document", back_populates="chunks")

The "Why" Behind the Design

Relational Tying (document_id & session_id): This is where PostgreSQL shines over dedicated vector DBs. By storing the vector as just another column, we can do hard-filtering (e.g., WHERE session_id = X) before or during the vector similarity search. This guarantees multi-tenant data isolation and makes citation tracking trivial.
The Vector Column: Vector(768) explicitly defines the dimensionality expected from the Gemini model, ensuring data integrity at the database level.

[!TIP]
Indexing for Scale: As your dataset grows beyond a few thousand chunks, performing an exact nearest-neighbor search (KNN) becomes a bottleneck. In production, it is highly recommended to apply an HNSW (Hierarchical Navigable Small World) or IVFFlat index on the embedding column to enable blazingly fast Approximate Nearest Neighbor (ANN) search. HNSW is generally preferred for its superior query speed and recall, though it consumes more memory during index building.

3. The Retrieval & RAG Logic

When a user asks a question, we embed their query using the exact same Gemini model. We then search PostgreSQL for the chunks with vectors most mathematically similar to the query vector.

pgvector supports multiple distance metrics, but Cosine Similarity (using the <=> operator) is the gold standard for text embeddings.

Here is how we execute the vector search using SQLAlchemy's cosine_distance mapping:

def _get_chunk_context(db: DBSession, session_id: str, query_vector: list,
                       unique_filenames: List[str], chunks_per_doc: int = 5) -> str:
    """Retrieve the top-N most relevant chunks per document via cosine similarity."""
    all_results = []
    for fname in unique_filenames:
        per_doc_results = (
            db.query(models.DocumentChunk)
            .filter(
                models.DocumentChunk.session_id == session_id,
                models.DocumentChunk.filename == fname,
            )
            # This translates to the `<=>` Cosine Distance operator in SQL
            .order_by(models.DocumentChunk.embedding.cosine_distance(query_vector))
            .limit(chunks_per_doc)
            .all()
        )
        all_results.extend(per_doc_results)

    return "\n\n".join([f"[Source: {r.filename}]\n{r.text}" for r in all_results])

Once the most relevant text chunks are retrieved, they are injected directly into the Gemini API prompt as context:

    context = _get_chunk_context(db, session_id, query_vector, unique_filenames, chunks_per_doc=5)

    prompt = (
        "You are Smart Document Agent, a professional and friendly document analysis assistant. "
        "Answer based on the provided document context below.\n\n"
        "6. Clearly label which information comes from which source file when citing documents.\n"
        f"RELEVANT EXCERPTS from {len(unique_filenames)} document(s):\n{context}\n\n"
        f"User's Question: {query}"
    )

    response = client.models.generate_content(
        model="gemini-3.5-flash",
        contents=prompt,
    )

4. Benchmarks & Trade-offs

When architecting AI systems, understanding latency profiles is critical.

The Latency Split:
In a typical RAG request on this stack:

Embedding the Query: ~100-200ms (Gemini API network call).
Vector Search (PostgreSQL): < 10ms (if properly indexed with HNSW, vector search is virtually instantaneous).
LLM Generation: 1 to 3 seconds (Gemini 3.5 Flash inference, depending on output token length).

The vector search step is almost never the bottleneck.

A Consultant's Perspective

From an enterprise consulting standpoint, introducing a dedicated vector database into an existing infrastructure often creates more problems than it solves. It requires new security audits, separate backup/restore procedures, complex data synchronization (what happens to vectors when the parent relational record is deleted?), and higher monthly cloud bills.

By leveraging PostgreSQL and pgvector, you consolidate your tech stack. Your semantic search benefits from Postgres's decades of ACID compliance, robust Role-Based Access Control (RBAC), and transactional integrity.

Unless you are indexing hundreds of millions of dense vectors—the territory of hyperscalers—keeping your vector data alongside your relational data is the smartest, most pragmatic architectural decision you can make. It reduces infrastructure complexity, slashes operational costs, and delivers a robust, sub-second RAG engine ready for the enterprise.