Single-VM RAG proof of concept with Ollama and pgvector

#1. Install the pieces

# Ollama (local models)
curl -fsSL https://ollama.com/install.sh | sh
ollama pull llama3.1:8b
ollama pull nomic-embed-text

# PostgreSQL + pgvector
sudo apt-get install -y postgresql-16 postgresql-16-pgvector
sudo -u postgres psql -c "CREATE DATABASE rag;"
sudo -u postgres psql -d rag -c "CREATE EXTENSION vector;"

python3 -m venv ~/rag && source ~/rag/bin/activate
pip install psycopg[binary] pgvector requests pypdf

A CPU-only VM works for testing (it's slow). A small GPU (16–24 GB) makes 8B models responsive.

#2. Schema

CREATE TABLE chunks (
  id        bigserial PRIMARY KEY,
  source    text NOT NULL,
  chunk_no  int  NOT NULL,
  content   text NOT NULL,
  embedding vector(768)          -- nomic-embed-text produces 768 dimensions
);
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops);

#3. Ingest

# ingest.py
import sys, requests, psycopg
from pypdf import PdfReader
from pgvector.psycopg import register_vector

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

def chunks(text, size=1200, overlap=200):
    i = 0
    while i < len(text):
        yield text[i:i + size]
        i += size - overlap

with psycopg.connect("dbname=rag user=postgres") as conn:
    register_vector(conn)
    for path in sys.argv[1:]:
        text = "\n".join(p.extract_text() or "" for p in PdfReader(path).pages)
        for n, c in enumerate(chunks(text)):
            conn.execute("INSERT INTO chunks (source, chunk_no, content, embedding) VALUES (%s,%s,%s,%s)",
                         (path, n, c, embed(c)))

python ingest.py docs/*.pdf

#4. Ask

# ask.py
import sys, requests, psycopg, numpy as np
from pgvector.psycopg import register_vector
from ingest import embed

q = sys.argv[1]
with psycopg.connect("dbname=rag user=postgres") as conn:
    register_vector(conn)
    rows = conn.execute("SELECT source, content FROM chunks ORDER BY embedding <=> %s LIMIT 5",
                        (np.array(embed(q)),)).fetchall()

context = "\n\n".join(f"[{s}]\n{c}" for s, c in rows)
prompt = (f"Answer using only the context below. Cite the source in brackets. "
          f"If the answer is not in the context, say you don't know.\n\nContext:\n{context}\n\nQuestion: {q}")

r = requests.post("http://localhost:11434/api/chat",
                  json={"model": "llama3.1:8b", "stream": False,
                        "messages": [{"role": "user", "content": prompt}]})
print(r.json()["message"]["content"])

python ask.py "What is our RPO for Tier 1 databases?"

#5. Evaluate the PoC honestly

Write 20–30 real questions with known answers. Score each answer: correct, partly correct, or wrong, and whether the source was cited. That gives a baseline to improve on (chunk size, top-k, a better embedding model, hybrid search).

#6. Before production

Note

Ollama is great for a PoC and for desktops. For multi-user production serving, vLLM or a managed model endpoint gives much better throughput under concurrency.