Minimal RAG with PostgreSQL and pgvector and a local model (Python)
Store embeddings in PostgreSQL with pgvector, search them with an HNSW index and cosine distance, and answer with a local Ollama model that cites its sources. Tested in CI without a model.

Retrieval-augmented generation (RAG) is a simple idea with a fancy name: before you ask a model a question, you find the few passages of your own documents that are most likely to contain the answer, and you put them in the prompt. The model answers from your text instead of from memory, and can say which document it used.
You do not need a dedicated vector database to start. If you already run PostgreSQL, the pgvector extension adds a vector column type, distance operators and approximate nearest-neighbour indexes. Documents, embeddings and your normal data share one database, its backups and its permissions.
This guide builds the smallest RAG that still does things properly: chunking, embeddings from a local model, an HNSW index, a distance cut-off, and an answer with numbered sources. Versions checked on 31 October 2026: psycopg 3.3.6 and pgvector 0.5.1 (the Python adapter) on PyPI, the Docker image pgvector/pgvector:0.8.7-pg18 (pgvector 0.8.7 on PostgreSQL 18), and Ollama 0.40.5 running mxbai-embed-large for embeddings and llama3.2:1b for answers.
How the pieces fit
Indexing runs when a document changes: split it into chunks, turn each chunk into a vector, store both. Answering runs per question: embed the question with the same model, fetch the closest chunks, send them with the question to a chat model.
| Step | What runs | In this example |
|---|---|---|
| Chunk | Your code | Paragraphs packed into chunks of up to 1,200 characters |
| Embed | Embedding model | mxbai-embed-large through Ollama's POST /api/embed |
| Store and search | PostgreSQL + pgvector | vector(1024) column, HNSW index, <=> cosine distance |
| Answer | Chat model | llama3.2:1b through Ollama's POST /api/chat |
Everything runs locally: no document leaves your machine and no API key is needed.
Start PostgreSQL with pgvector
The pgvector project publishes Docker images with the extension already compiled. For local work:
docker run -d --name pgvector -e POSTGRES_PASSWORD=postgres -p 5432:5432 pgvector/pgvector:0.8.7-pg18
pip install "psycopg[binary]==3.3.6" pgvector==0.5.1
ollama pull mxbai-embed-large && ollama pull llama3.2:1b
Create the table and the index
The extension must be enabled once per database. The Python adapter then registers the vector type with psycopg, so you can pass vectors as query parameters. The order matters: register_vector looks up the type, so it fails if the extension does not exist yet.
import psycopg
from pgvector import Vector
from pgvector.psycopg import register_vector
def connect(dsn: str) -> psycopg.Connection:
conn = psycopg.connect(dsn, autocommit=True)
conn.execute("CREATE EXTENSION IF NOT EXISTS vector")
register_vector(conn) # after CREATE EXTENSION: it looks up the vector type
return conn
def create_schema(conn: psycopg.Connection, dim: int) -> None:
conn.execute(f"""
CREATE TABLE IF NOT EXISTS chunks (
id bigserial PRIMARY KEY,
source text NOT NULL,
content text NOT NULL,
embedding vector({int(dim)}) NOT NULL
)""")
conn.execute("CREATE INDEX IF NOT EXISTS chunks_embedding_idx "
"ON chunks USING hnsw (embedding vector_cosine_ops)")
Three choices are hidden in these lines.
The dimension is fixed by the model. mxbai-embed-large returns 1024 numbers per text, so the column is vector(1024). Change the embedding model and you must create a new column and embed everything again: vectors from different models cannot be compared.
The distance is chosen with the operator class. pgvector offers L2 distance (<->), negative inner product (<#>), cosine distance (<=>) and L1 distance (<+>), and an index serves only the distance it was built for. Cosine distance is the usual choice for text embeddings because it ignores the length of the vectors and compares only their direction; the index is therefore built with vector_cosine_ops and the query must use <=>.
HNSW is approximate. Without an index, pgvector searches every row exactly, which is fine for a few thousand chunks. An HNSW index is much faster on large tables but can miss a row an exact scan would return. Its knobs are m (default 16) and ef_construction (64) at build time and hnsw.ef_search (40) at query time: higher values give better recall and cost speed. The vector type can be indexed up to 2,000 dimensions.
Chunk and embed the documents
Embedding models have a small context window: mxbai-embed-large reads 512 tokens and, by default, Ollama truncates longer input without telling you. A whole document therefore cannot become one vector. Split it into pieces that each make sense alone. Paragraph boundaries are a good default:
def chunk_text(text: str, max_chars: int = 1200) -> list[str]:
"""Split on blank lines, then pack paragraphs into chunks of at most max_chars.
A paragraph longer than max_chars becomes a chunk of its own: split it further if
your embedding model has a small context window."""
chunks, current = [], ""
for para in (p.strip() for p in re.split(r"\n\s*\n", text)):
if not para:
continue
if current and len(current) + 2 + len(para) > max_chars:
chunks.append(current)
current = para
else:
current = f"{current}\n\n{para}" if current else para
if current:
chunks.append(current)
return chunks
1,200 characters of English is roughly 250 to 300 tokens, inside the 512-token window. Other languages and code use more tokens per character (see our guide on counting tokens), so measure.
Ollama's embed endpoint accepts a list of texts in one request and returns one vector per text, in the same order. A plain HTTP call is enough:
def ollama_embedder(model: str = EMBED_MODEL, base_url: str = OLLAMA_URL) -> Embedder:
"""Embeddings from Ollama's POST /api/embed (several texts per request)."""
def embed(texts: Sequence[str]) -> list[list[float]]:
return _post_json(f"{base_url}/api/embed", {"model": model, "input": list(texts)})["embeddings"]
return embed
Indexing a document deletes its old chunks and inserts the new ones in one transaction, so a search never sees half a document and re-indexing an edited file does not leave duplicates:
def index_document(conn: psycopg.Connection, source: str, text: str, embed: Embedder) -> int:
"""Replace the chunks of one document. Returns the number of chunks stored."""
chunks = chunk_text(text)
vectors = embed(chunks)
with conn.transaction():
conn.execute("DELETE FROM chunks WHERE source = %s", (source,))
with conn.cursor() as cur:
cur.executemany(
"INSERT INTO chunks (source, content, embedding) VALUES (%s, %s, %s)",
[(source, c, Vector(v)) for c, v in zip(chunks, vectors)])
return len(chunks)
The embedding call happens before the transaction starts on purpose: a slow model should not keep a database transaction open.
Search by cosine distance
The search embeds the question with the same model; the ORDER BY embedding <=> %s LIMIT k shape lets PostgreSQL use the HNSW index:
def search(conn: psycopg.Connection, query: str, embed: Embedder, k: int = 4,
query_prefix: str = "") -> list[tuple[str, str, float]]:
"""The k chunks closest to the query by cosine distance (0 = same direction)."""
qvec = Vector(embed([query_prefix + query])[0])
return conn.execute(
"SELECT source, content, embedding <=> %s AS distance "
"FROM chunks ORDER BY embedding <=> %s LIMIT %s",
(qvec, qvec, k)).fetchall()
The query_prefix exists because some embedding models are trained to embed questions and documents differently. The model card of mxbai-embed-large shows search queries prefixed with Represent this sentence for searching relevant passages: , and documents without a prefix. Read the card of the model you choose: getting this wrong does not raise an error, it just makes the results quietly worse.
With the three sample documents in the repository (a refund policy, VPN instructions and an on-call rota), the distances we measured with mxbai-embed-large were:
| Question | refunds.md | oncall.md | vpn.md |
|---|---|---|---|
| How long do customers have to ask for a refund? | 0.216 | 0.520 | 0.686 |
| What is the capital of France? | 0.794 | 0.748 | 0.779 |
The right document stands out. Just as important is the second row: a question no document can answer still gets three "nearest" chunks. A nearest neighbour always exists; it is not necessarily relevant.
Answer only from relevant passages
That is why the answering step drops chunks above a distance threshold, and does not call the model at all when nothing is left. The prompt numbers the passages and asks the model to cite them:
def build_messages(question: str, hits: list[tuple[str, str, float]]) -> list[dict]:
context = "\n\n".join(f"[{i}] ({src})\n{content}" for i, (src, content, _) in enumerate(hits, 1))
system = ("Answer using only the numbered context passages. Cite them like [1]. "
"If the answer is not in the context, say you don't know.")
return [{"role": "system", "content": system},
{"role": "user", "content": f"Context:\n{context}\n\nQuestion: {question}"}]
def ask(conn, question: str, embed: Embedder, k: int = 4, max_distance: float = 0.5,
chat_model: str = CHAT_MODEL, base_url: str = OLLAMA_URL, query_prefix: str = "") -> dict:
hits = [h for h in search(conn, question, embed, k, query_prefix) if h[2] <= max_distance]
if not hits:
return {"answer": "I don't know: no relevant passage found.", "sources": []}
res = _post_json(f"{base_url}/api/chat", {"model": chat_model, "stream": False,
"messages": build_messages(question, hits)})
return {"answer": res["message"]["content"], "sources": [h[0] for h in hits]}
The threshold of 0.5 comes from the table above and is valid only for this model and these documents. Distances from different embedding models are not on the same scale, so pick the value by running a few dozen real questions, with known answers, and looking where relevant and irrelevant chunks separate.
The threshold also improved the answer. With a looser one, all three documents went into the prompt and the 1B model replied that the context did not mention a refund deadline; with only the refund policy, it answered "Customers can ask for a full refund within 30 days of purchase." For small models, fewer and better passages beat a longer context. To show the answer word by word in a web page, combine this with our guide on streaming to the browser with SSE.
Test it in CI without a model
Embedding models are too large to download on every CI run. The tests therefore use a real PostgreSQL with pgvector, but replace the model with a deterministic fake: each word is hashed into one of dim positions (256 in the tests), and the vector is normalised to length 1. Texts that share words get similar vectors. It knows nothing about meaning, which is exactly what you want in a test: the results are the same on every run.
def fake_embedder(dim: int = 1024) -> Embedder:
"""Deterministic test embeddings: hashed bag of words, normalised to length 1.
Texts that share words get similar vectors. Good for tests, useless for real meaning."""
def embed(texts: Sequence[str]) -> list[list[float]]:
out = []
for text in texts:
v = [0.0] * dim
for word in re.findall(r"\w+", text.lower()):
h = int.from_bytes(hashlib.sha256(word.encode()).digest()[:8], "big")
v[h % dim] += 1.0
norm = math.sqrt(sum(x * x for x in v)) or 1.0
out.append([x / norm for x in v])
return out
return embed
The hash is SHA-256 rather than Python's hash(), which changes between processes. In GitHub Actions, PostgreSQL runs as a service container (pgvector/pgvector:0.8.7-pg18) next to the job, and a tiny HTTP server stands in for Ollama, so the tests also check the exact requests sent to /api/embed and /api/chat:
test_chunking_packs_paragraphs ... ok
test_hnsw_index_exists ... ok
test_no_relevant_passage_skips_the_model ... ok
test_ollama_client_and_ask ... ok
test_reindex_replaces_old_chunks ... ok
test_search_finds_the_right_document ... ok
Bag-of-words vectors give larger distances than a real model (0.74 for the best match instead of 0.22), so the tests pass their own max_distance: the threshold belongs to the model, not to the code.
Plugging in another embedding model
The rest of the code only needs a function that turns a list of texts into a list of vectors. For another Ollama model, pass its name to ollama_embedder; for a hosted embedding API, wrap the provider's SDK call in the same signature. In both cases:
- set the column to the new dimension and re-embed every chunk; never mix vectors from two models in one column;
- check the model card for query and document prefixes;
- measure the distance threshold again;
- remember that with a hosted API your documents leave your network. If they contain personal data or secrets, remove them before indexing; our log and payload redactor shows what kind of data tends to hide in text.
Embedding calls are HTTP calls with latency and limits: batch them, and use our API capacity sizing tool to estimate how many concurrent requests the embedding and chat backends must handle at peak.
Limits of this minimal version
- HNSW recall. Results can change after you add the index, and a query with a
WHEREfilter can return fewer thankrows, because the filter is applied after the index scan. The pgvector README describes iterative index scans for this case. - Paragraph chunking is a starting point. Tables, code and long paragraphs need their own rules.
- Vector search misses exact terms such as error codes; many systems add PostgreSQL full-text search (hybrid search).
- A small model can still answer wrongly with the right passage. Keep sources visible and evaluate answers on a fixed set of questions before changing model, prompt or threshold.
- Access control is your job. Filter chunks by permission in the SQL query, before the model sees them.
The complete code, the sample documents and the tests are in the example folder, and they run in GitHub Actions on every change.
The complete, tested example for this article is in our public repository: adamant-solutions/ai-dev-examples/rag-pgvector