Build a small RAG pipeline with pgvector and local embeddings

You will build a small retrieval-augmented generation (RAG) pipeline in Python that stores document chunks in PostgreSQL with pgvector and uses local Ollama models for embeddings and answers. It is for developers who want RAG with no hosted vector database and no API keys.

Prerequisites#

• Docker, to run PostgreSQL.
• Python 3.10 or newer. Both psycopg 3.3 and pgvector 0.5 require it.
• Ollama from ollama.com/download (opens in a new tab). The embeddinggemma model needs Ollama v0.11.10 or later. The current release at the time of writing is v0.40.8.
• About 10 GB of free disk space. The gemma4:e2b download alone is several gigabytes.

If Ollama is new to you, start with Run a local LLM with Ollama and call it from your code.

On Linux, install Ollama with the official script:

curl -fsSL https://ollama.com/install.sh | sh

Check that the CLI works:

ollama -v

How the pipeline works#

The pipeline has two paths. The ingest path reads a file, splits it into chunks, embeds each chunk, and stores the text and vector in Postgres. The query path embeds the question, finds the nearest chunks by cosine distance, and passes them to the chat model as context. You write the ingest path in ingest.py and the query path in ask.py.

1. Start PostgreSQL with pgvector#

The pgvector project publishes the pgvector/pgvector image on Docker Hub. It is the official Postgres image with the extension installed. Pin the full tag, 0.8.7-pg18, so every machine runs pgvector 0.8.7 on PostgreSQL 18.

Set a local password, and replace the placeholder with your own value:

export PGPASSWORD="replace-with-a-local-password"

Start the container:

docker run -d --name ragdb \
  -e POSTGRES_PASSWORD="$PGPASSWORD" \
  -e POSTGRES_DB=rag \
  -p 5432:5432 \
  -v ragdb-data:/var/lib/postgresql \
  pgvector/pgvector:0.8.7-pg18

The volume mounts at /var/lib/postgresql, not /var/lib/postgresql/data. The PostgreSQL 18 image keeps its data in /var/lib/postgresql/18/docker, so a volume at the old path would not hold your data.

The image reads POSTGRES_PASSWORD and POSTGRES_DB only when the volume is empty. To change the password later, remove the ragdb-data volume or change the password inside Postgres.

The first start runs initdb, which takes a few seconds. Wait until the server accepts TCP connections:

docker exec ragdb pg_isready -h localhost -U postgres

Repeat the command until it reports accepting connections. During setup the container's entrypoint runs a temporary server that does not listen on TCP, so this check passes only after setup finishes.

2. Pull the models#

ollama pull embeddinggemma
ollama pull gemma4:e2b

embeddinggemma is a 300M-parameter embedding model from Google. It returns 768-dimension vectors and accepts up to 2K tokens of input. gemma4:e2b is the smallest Gemma 4 chat model in the Ollama library. The answer step works with any chat model you have pulled. To use another one, change CHAT_MODEL in ask.py.

Check the vector size with the embed API. The table column in step 4 must match it.

curl -s http://localhost:11434/api/embed \
  -d '{"model": "embeddinggemma", "input": "hello"}' \
  | python3 -c "import json, sys; print(len(json.load(sys.stdin)['embeddings'][0]))"

The command should print 768.

3. Set up the project#

mkdir rag-demo
cd rag-demo
python3 -m venv .venv
source .venv/bin/activate
pip install "psycopg[binary]==3.3.6" "pgvector==0.5.1" "ollama==0.6.3"

Point the scripts at the database with an environment variable:

export DATABASE_URL="postgresql://postgres:${PGPASSWORD}@localhost:5432/rag"

If your password contains characters such as @ or /, URL-encode them in this string.

pgvector 0.5 removed its NumPy dependency. The scripts pass vectors as pgvector.Vector objects built from plain Python lists.

4. Create the schema#

Create schema.sql:

1CREATE EXTENSION IF NOT EXISTS vector;
2
3CREATE TABLE IF NOT EXISTS chunks (
4  id          bigserial PRIMARY KEY,
5  source      text        NOT NULL,
6  chunk_index integer     NOT NULL,
7  content     text        NOT NULL,
8  embedding   vector(768) NOT NULL
9);
10
11CREATE INDEX IF NOT EXISTS chunks_embedding_idx
12  ON chunks USING hnsw (embedding vector_cosine_ops);

Apply it with psql inside the container:

docker exec -i ragdb psql -U postgres -d rag < schema.sql

Notes on the schema:

• You run CREATE EXTENSION vector once per database. It adds the vector type and its distance operators.
• vector(768) rejects vectors of any other length. If you switch to a model with a different vector size, inserts fail with an error instead of mixing incompatible vectors in one table.
• The operator class must match the query operator. vector_cosine_ops supports <=>, which is cosine distance. An index built with vector_l2_ops supports <->, and Postgres does not use it for <=> queries.
• HNSW has no training step, so you can create the index on an empty table. The vector type supports up to 2,000 dimensions in an HNSW index.

5. Chunk, embed, and store documents#

Split text into chunks#

Embedding models have an input limit, and smaller chunks give more precise matches. The chunk_text function in the script below splits on words. It builds windows of 200 words with a 40-word overlap, so text near a boundary appears in both neighboring chunks. A 200-word chunk is a few hundred tokens, well under the 2K limit.

Add task prefixes#

Google trained EmbeddingGemma with task prompts. The EmbeddingGemma model card (opens in a new tab) recommends title: none | text: for documents and task: search result | query: for search queries. The scripts add these prefixes to the input text. Use the same model for indexing and querying.

Write the ingest script#

Create ingest.py:

1import os
2import sys
3from pathlib import Path
4
5import ollama
6import psycopg
7from pgvector import Vector
8from pgvector.psycopg import register_vector
9
10EMBED_MODEL = "embeddinggemma"
11DOC_PREFIX = "title: none | text: "
12BATCH_SIZE = 32
13
14
15def chunk_text(text: str, max_words: int = 200, overlap: int = 40) -> list[str]:
16    words = text.split()
17    step = max_words - overlap
18    chunks = []
19    for start in range(0, max(len(words) - overlap, 1), step):
20        chunk = " ".join(words[start : start + max_words])
21        if chunk:
22            chunks.append(chunk)
23    return chunks
24
25
26def embed_documents(chunks: list[str]) -> list[list[float]]:
27    vectors = []
28    for i in range(0, len(chunks), BATCH_SIZE):
29        batch = [DOC_PREFIX + chunk for chunk in chunks[i : i + BATCH_SIZE]]
30        response = ollama.embed(model=EMBED_MODEL, input=batch)
31        vectors.extend(list(vec) for vec in response.embeddings)
32    return vectors
33
34
35def ingest(conn: psycopg.Connection, path: Path) -> int:
36    chunks = chunk_text(path.read_text(encoding="utf-8"))
37    if not chunks:
38        return 0
39    vectors = embed_documents(chunks)
40    source = str(path)
41    with conn.cursor() as cur:
42        cur.execute("DELETE FROM chunks WHERE source = %s", (source,))
43        cur.executemany(
44            "INSERT INTO chunks (source, chunk_index, content, embedding) "
45            "VALUES (%s, %s, %s, %s)",
46            [
47                (source, i, chunk, Vector(vec))
48                for i, (chunk, vec) in enumerate(zip(chunks, vectors))
49            ],
50        )
51    return len(chunks)
52
53
54def main() -> None:
55    with psycopg.connect(os.environ["DATABASE_URL"]) as conn:
56        register_vector(conn)
57        for name in sys.argv[1:]:
58            count = ingest(conn, Path(name))
59            print(f"{name}: {count} chunk(s)")
60
61
62if __name__ == "__main__":
63    main()

What the script does:

• register_vector(conn) registers the vector type with psycopg. Call it after the extension exists.
• ollama.embed accepts a list and returns one vector per input, in order. Sending 32 chunks per request cuts the number of HTTP calls.
• Vector accepts a list or a NumPy array, so the script converts each embedding to a list with list().
• The DELETE means a re-run replaces a file's chunks instead of duplicating them.
• The with psycopg.connect(...) block commits when it exits without an error.

The Ollama Python client connects to http://127.0.0.1:11434 by default. If Ollama runs somewhere else, set OLLAMA_HOST.

Ingest sample documents#

Create two small files to test with:

1mkdir docs
2cat > docs/runbook.md <<'EOF'
3# Payments service runbook
4
5The payments service runs in the eu-west cluster. Deploys go out every
6Tuesday and Thursday at 14:00 UTC. A deploy freeze starts 48 hours
7before any major sale.
8
9To roll back, rerun the deploy pipeline with the previous release tag.
10A rollback takes about six minutes. Post in the #payments-oncall channel
11before you start.
12EOF
13cat > docs/onboarding.md <<'EOF'
14# New engineer onboarding
15
16Request repository access from your team lead on day one. Laptops ship
17with the dev container image preinstalled. Run the test suite with
18make test before your first pull request.
19EOF

Run the ingest script:

python ingest.py docs/*.md

The script prints a chunk count for each file. Both files are short, so each becomes a single chunk and both lines end with 1 chunk(s).

6. Retrieve and answer#

Create ask.py:

1import os
2import sys
3
4import ollama
5import psycopg
6from pgvector import Vector
7from pgvector.psycopg import register_vector
8
9EMBED_MODEL = "embeddinggemma"
10CHAT_MODEL = "gemma4:e2b"
11QUERY_PREFIX = "task: search result | query: "
12TOP_K = 4
13
14SYSTEM_PROMPT = (
15    "Answer the question using only the provided context. "
16    "Cite sources by their bracket number, like [1]. "
17    "If the context does not contain the answer, say you don't know."
18)
19
20
21def retrieve(conn: psycopg.Connection, question: str, k: int = TOP_K) -> list[tuple]:
22    response = ollama.embed(model=EMBED_MODEL, input=QUERY_PREFIX + question)
23    query_vec = Vector(list(response.embeddings[0]))
24    return conn.execute(
25        """
26        SELECT source, content, 1 - (embedding <=> %(q)s) AS similarity
27        FROM chunks
28        ORDER BY embedding <=> %(q)s
29        LIMIT %(k)s
30        """,
31        {"q": query_vec, "k": k},
32    ).fetchall()
33
34
35def answer(question: str, rows: list[tuple]) -> str:
36    context = "\n\n".join(
37        f"[{i}] (source: {source})\n{content}"
38        for i, (source, content, _) in enumerate(rows, start=1)
39    )
40    response = ollama.chat(
41        model=CHAT_MODEL,
42        messages=[
43            {"role": "system", "content": SYSTEM_PROMPT},
44            {"role": "user", "content": f"Context:\n{context}\n\nQuestion: {question}"},
45        ],
46    )
47    return response.message.content or ""
48
49
50def main() -> None:
51    question = " ".join(sys.argv[1:]) or "How do I roll back a deploy?"
52    with psycopg.connect(os.environ["DATABASE_URL"]) as conn:
53        register_vector(conn)
54        rows = retrieve(conn, question)
55    for i, (source, content, similarity) in enumerate(rows, start=1):
56        print(f"[{i}] {similarity:.3f} {source}: {content[:70]}...")
57    print()
58    print(answer(question, rows))
59
60
61if __name__ == "__main__":
62    main()

The query orders by embedding <=> %(q)s in ascending order with a LIMIT. pgvector needs that shape to use the HNSW index. Ordering by 1 - (embedding <=> ...) DESC would skip the index, so the script computes similarity only in the SELECT list. The named placeholder %(q)s lets you pass the vector once and reference it twice.

The system prompt tells the model to answer only from the retrieved context, to cite sources by number, and to say when it does not know.

Verify it works#

Ask a question the documents answer:

python ask.py "When do payments deploys go out?"

The script prints the retrieved chunks with their cosine similarity, then the answer. The runbook chunk should rank first. The answer should mention Tuesday and Thursday at 14:00 UTC with a [1] citation, though the wording changes between runs.

Ask something the documents do not cover:

python ask.py "What is the staging database hostname?"

The model should say it does not know.

Check what the table holds:

docker exec -i ragdb psql -U postgres -d rag \
  -c "SELECT source, count(*), max(vector_dims(embedding)) AS dims FROM chunks GROUP BY source;"

Confirm that the planner can use the index:

1docker exec -i ragdb psql -U postgres -d rag <<'SQL'
2BEGIN;
3SET LOCAL enable_seqscan = off;
4EXPLAIN SELECT id FROM chunks
5ORDER BY embedding <=> (SELECT embedding FROM chunks LIMIT 1)
6LIMIT 4;
7COMMIT;
8SQL

Look for Index Scan using chunks_embedding_idx in the plan. Without SET LOCAL enable_seqscan = off, Postgres usually picks a sequential scan on a table this small, which is expected.

Troubleshooting#

vector type not found in the database#

register_vector raises psycopg.ProgrammingError with this message when the database has no vector extension. Check that DATABASE_URL points at the rag database, then run schema.sql against it.

expected 768 dimensions, not N#

You switched to an embedding model with a different vector size. Change the vector(N) column to match the new model, then re-embed every document. Vectors from different models are not comparable.

Fewer than k rows after adding a WHERE filter#

HNSW applies filters after the index scan, and hnsw.ef_search defaults to 40 candidates. pgvector 0.8.0 added iterative index scans, which you can turn on:

SET hnsw.iterative_scan = strict_order;

You can also raise the size of the candidate list. A higher value improves recall but slows queries.

SET hnsw.ef_search = 100;

Failed to connect to Ollama#

The Python client raises ConnectionError when it cannot reach the Ollama server. Start the desktop app, or run the server in a separate terminal:

ollama serve

If Ollama runs on another machine, export OLLAMA_HOST before you run the scripts:

export OLLAMA_HOST="http://your-ollama-host:11434"

ResponseError about a missing model#

This error means you have not pulled that model yet. Run ollama pull with the exact names from the scripts, embeddinggemma and gemma4:e2b.

Next steps#

• Use real titles in the document prefix. When a document has a title, the model card recommends title: {title} | text: .
• Try smaller vectors. EmbeddingGemma supports 512, 256 and 128 dimensions, and the Ollama embed API takes a dimensions parameter. Change the column size to match, then re-embed. See the Ollama embed API reference (opens in a new tab).
• Try inner product. Ollama's /api/embed returns unit-length vectors, and the pgvector README recommends inner product for normalized vectors. That means vector_ip_ops in the index and <#> in the query.
• Stream answers with ollama.chat(..., stream=True), which returns an iterator of partial responses.
• Move to a connection pool. The pgvector-python README (opens in a new tab) shows how to call register_vector in a pool's configure callback.
• Tune HNSW build options such as m and ef_construction. The pgvector README (opens in a new tab) documents both. It also covers hybrid search with Postgres full-text search.