Build a Private RAG Pipeline with Ollama and pgvector

Most RAG tutorials quietly route your documents through OpenAI embeddings and Pinecone, which means every chunk you index becomes traffic you pay for and data that leaves your machine. For internal docs, legal notes, or anything you would rather not share with a third party, that is the wrong default.

This tutorial builds a fully local alternative. PostgreSQL with the pgvector extension stores the embeddings, and Ollama runs both the embedding model and the chat model. No API keys, no cloud, nothing leaves your machine.

You end up with around 100 lines of Python that ingests a folder of text files, indexes them, and answers questions with citations back to the source. The pattern is the same one production RAG systems use, just without the managed-service tax.

Why pgvector and not a dedicated vector database

pgvector turns PostgreSQL into a vector store, so your chunks sit next to your relational data. That removes one moving part and one deployment. For corpora up to a few million vectors it is fast enough, and you already know Postgres. Dedicated stores like Qdrant or Milvus start to win on very large corpora, high write throughput, and tuning options like binary quantization. Start with pgvector and move later if a benchmark actually demands it.

Prerequisites

• Docker
• Ollama installed (curl -fsSL https://ollama.com/install.sh | sh on Linux/macOS)
• Python 3.10+

Step 1: Run PostgreSQL with pgvector

The pgvector/pgvector image ships with the extension preinstalled.

# compose.yml
services:
  db:
    image: pgvector/pgvector:pg17
    environment:
      POSTGRES_PASSWORD: rag
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:

docker compose up -d

Step 2: Pull the models

ollama pull nomic-embed-text   # embeddings, 768 dimensions, ~274MB
ollama pull llama3.2           # chat model for generation (3B, runs on CPU)

nomic-embed-text is small and good enough for English and Indonesian text. If you index a lot of Indonesian content, bge-m3 tends to win on multilingual recall but costs about 1GB of disk.

Step 3: Create the schema

docker exec -it $(docker compose ps -q db) psql -U postgres -c "CREATE EXTENSION IF NOT EXISTS vector;"

Or paste this into psql:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
  id bigserial PRIMARY KEY,
  source text NOT NULL,
  chunk_index int NOT NULL,
  content text NOT NULL,
  embedding vector(768)
);

CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops);

The vector(768)' dimension has to match your embedding model, and 768 is what nomic-embed-text` outputs. If you switch models, you change the column and re-ingest. Embeddings from different models do not mix.

The HNSW index is what keeps retrieval fast once the table grows. Without it, Postgres does a full scan on every query.

Step 4: Ingest a folder of documents

pip install psycopg pgvector requests

# ingest.py
import sys
import psycopg
import requests
from pgvector.psycopg import register_vector

OLLAMA = "http://localhost:11434"
EMBED_MODEL = "nomic-embed-text"

DB_DSN = "postgresql://postgres:rag@localhost:5432/postgres"


def embed(texts):
    r = requests.post(f"{OLLAMA}/api/embed", json={"model": EMBED_MODEL, "input": texts})
    r.raise_for_status()
    return r.json()["embeddings"]


def chunk(text, size=800, overlap=100):
    return [text[i:i+size] for i in range(0, len(text) - overlap, size - overlap)]


def ingest(path):
    text = open(path, encoding="utf-8").read()
    chunks = chunk(text)
    with psycopg.connect(DB_DSN) as conn:
        register_vector(conn)
        with conn.cursor() as cur:
            for i, c in enumerate(chunks):
                (vec,) = embed([c])
                cur.execute(
                    "INSERT INTO chunks (source, chunk_index, content, embedding)"
                    " VALUES (%s, %s, %s, %s)",
                    (path, i, c, vec),
                )
    print(f"{path}: {len(chunks)} chunks")


for path in sys.argv[1:]:
    ingest(path)

python ingest.py ./docs/*.txt

The overlap matters. Search words like "caching" or "auth" can fall exactly on a chunk boundary and get lost. A small overlap means those spans appear in two chunks, so retrieval still finds them.

Step 5: Query with semantic search

# query.py
import sys
import psycopg
import requests
from pgvector.psycopg import register_vector

OLLAMA = "http://localhost:11434"
DB_DSN = "postgresql://postgres:rag@localhost:5432/postgres"


def embed(text):
    r = requests.post(f"{OLLAMA}/api/embed", json={"model": "nomic-embed-text", "input": [text]})
    r.raise_for_status()
    return r.json()["embeddings"][0]


def retrieve(query, top_k=5):
    q = embed(query)
    with psycopg.connect(DB_DSN) as conn:
        register_vector(conn)
        with conn.cursor() as cur:
            cur.execute(
                "SELECT source, chunk_index, content FROM chunks"
                " ORDER BY embedding <=> %s LIMIT %s",
                (q, top_k),
            )
            return cur.fetchall()


if __name__ == "__main__":
    for source, idx, content in retrieve(sys.argv[1]):
        print(f"[{idx}] {source}
{content}
")

python query.py "how do I disable rate limiting?"

<=> is cosine distance. Embeddings from Ollama are normalized to unit length, so cosine and L2 give the same ranking here. Cosine is the safer default for a general text store.

Step 6: Generate an answer grounded in the chunks

# ask.py
import sys
import requests
from query import retrieve

OLLAMA = "http://localhost:11434"
CHAT_MODEL = "llama3.2"


def ask(question):
    rows = retrieve(question)
    context = "

".join(f"[{i+1}] {content}" for i, (_, _, content) in enumerate(rows))
    messages = [
        {
            "role": "system",
            "content": (
                "Answer strictly from the provided context. If the context does not "
                "contain the answer, say so. Cite sources as [1], [2], etc."
            ),
        },
        {"role": "user", "content": f"Context:
{context}

Question: {question}"},
    ]
    r = requests.post(
        f"{OLLAMA}/api/chat", json={"model": CHAT_MODEL, "messages": messages, "stream": False}
    )
    return r.json()["message"]["content"]


if __name__ == "__main__":
    print(ask(sys.argv[1]))

python ask.py "what are the rate limits for the API?"

The system prompt is where RAG lives or dies. Forcing the model to answer only from context and to refuse when the answer is absent is the difference between a useful assistant and a confident liar. Never skip those two rules.

Tuning knobs

• Chunk size. 500-1000 characters with 10-15% overlap is a reasonable start. Bigger chunks keep more context but store less precisely. Re-ingest and compare answers on your own test questions.
• top_k. Raise it if answers feel thin, lower it if the model gets distracted by irrelevant chunks.
• Model swap. llama3.2 runs on CPU but slowly on large questions. On a machine with a decent GPU, llama3.1:8b gives noticeably better answers.

When to move off pgvector

pgvector handles a lot. When your corpus clears a million vectors or you need lower recall latency at scale, look at dedicated vector stores (Qdrant, Milvus, Weaviate) or at pgvector's binary quantization and HNSW tuning, which extend the ceiling before you migrate. Do not switch on hype. Switch when a benchmark on your own data shows a real gap.

Next steps

• Add full-text search (tsvector) and blend scores with cosine similarity for hybrid retrieval. Exact keywords like error codes are often found better by full text search than by embeddings.
• Make the ingest step idempotent with an upsert keyed by `(source, chunk_index)' so re-running a file does not duplicate rows.
• Store the question and the retrieved sources so you can trace what the model saw and tune prompts when answers are off.

References

• pgvector GitHub README
• Ollama API docs /api/embed
• nomic-embed-text model page