Adamant Tools

Adamant Tools › Blog

  • RAG
  • Embeddings
  • PostgreSQL
  • pgvector
  • Python
  • Ollama

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.

Published 7 min readGitHubLeggi in italiano

Minimal RAG with PostgreSQL and pgvector and a local model (Python)
Foto: Franklsf95, CC BY-SA 3.0, Wikimedia Commons

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:

bash
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.

python
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:

python
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:

python
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:

python
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:

python
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:

python
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.

python
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:

text
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 WHERE filter can return fewer than k rows, 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.

Full code on GitHub
The complete, tested example for this article is in our public repository: adamant-solutions/ai-dev-examples/rag-pgvector

About the author

Written by the Adamant Solutions engineering team: senior engineers working on APIs, identity and enterprise integration, certified on WSO2 and Spring. Code is tested before publication; if you spot an error, write to administrata@adamant-solutions.com.