GyaanStack. Blog

Articles / ragprojectspython

Build a fully local RAG app with Ollama and sqlite-vec

A private RAG app that never leaves your laptop. Ollama for embeddings and answers, sqlite-vec for search, RAM sizing with real numbers, and num_ctx traps.

By Raghunath · · 19 min read

Some documents can't go to a hosted API. HR policy, salary bands, a customer list, a contract that isn't signed yet. If the question-answering tool for those files has to call someone else's server, the legal team will say no, and they will be right.

This post builds a retrieval-augmented generation (RAG) app that runs entirely on one laptop. Ollama serves both the embedding model and the chat model, sqlite-vec stores the vectors inside a single SQLite file, and the glue is about 110 lines of Python. No API key, no cloud account, and nothing leaves localhost.

The pipeline is the easy part. The parts that go wrong on a laptop get more space here: which model fits in your RAM, what Ollama's default context length does to a long prompt, why the embedding model needs a prefix you will forget, and how to pick the line where the app refuses to answer. Every number below came from a run on my machine, an Apple M5 with 24 GB of unified memory, using Ollama 0.40.16, the ollama Python package 0.6.3 and sqlite-vec 0.1.9 on Python 3.15.2. Treat the speeds as estimates for similar hardware, not benchmarks.

If you want the RAG stages in general (chunking strategies, hybrid search, reranking, metrics), the RAG cheat sheet covers them. This post sticks to the local setup.

What runs where#

   your files (.md)                     one SQLite file (rag.db)
        |                               +-----------------------+
        v                               | chunks: id, heading,  |
   split by "## " heading  ---------->  |   text, sha256 hash   |
        |                               | vecs (vec0): id ->    |
        v                               |   768 floats, cosine  |
   Ollama /api/embed  --------------->  +-----------------------+
   nomic-embed-text                                ^
   ("search_document: ...")                        | KNN, k = 3
                                                   |
   question --> Ollama /api/embed ("search_query: ...")
                        |
                        v
            top distance > 0.43 ? ---- yes ---> "Not in the documents."
                        | no
                        v
            Ollama /api/chat, qwen3:4b-instruct, num_ctx 8192
                        |
                        v
                 answer with [1] [2] citations

Two Ollama models stay loaded side by side. The embedding model is small (ollama ps showed 335 MB for nomic-embed-text), so it costs almost nothing to keep it resident next to the chat model.

Setup#

Install Ollama from ollama.com/download, then pull one embedding model and one chat model:

ollama pull nomic-embed-text
ollama pull qwen3:4b-instruct
python3 -m venv .venv
.venv/bin/pip install ollama sqlite-vec

For embeddings, Ollama's own embeddings guide uses embeddinggemma in its example, and the library also has qwen3-embedding. I used nomic-embed-text because it is 274 MB on disk and returns 768-dimension vectors, and because it has one quirk worth showing, which comes up in the embeddings section. Whatever you pick, set DIM in the code to its output size.

For the vector store I started with Chroma, the usual choice in tutorials. On Python 3.15, pip install chromadb gave no error but resolved to version 0.3.23, a release years old, because no 1.x release could be installed alongside its dependencies there (pip install 'chromadb>=1.0' failed with ResolutionImpossible; the current release is 1.5.9 on PyPI). If you use Chroma, check pip list after installing, or use Python 3.12 or 3.13. I switched to sqlite-vec, a SQLite extension that adds a vec0 virtual table for vector search. It installs as a small wheel, and the whole index ends up in one file you can copy, back up or delete.

The test documents#

Three short Markdown files, a travel policy, a leave policy and a security handbook, each split into ## sections. A section looks like this:

## Hotels
The nightly hotel limit is 9,000 rupees in metro cities and 6,000 rupees elsewhere. Pay at checkout with the corporate card and upload the itemised bill within seven days.

Small, invented documents are a deliberate choice. You know every fact in them, so you can tell at a glance when an answer is wrong. Swap in your real files once the eval at the end passes.

The app#

Here is the full rag.py. Read it top to bottom; the sections after it explain the choices.

"""Local RAG: Ollama for embeddings and answers, sqlite-vec for the index."""
import hashlib, pathlib, sqlite3, sys
import ollama, sqlite_vec

EMBED_MODEL = "nomic-embed-text"
CHAT_MODEL = "qwen3:4b-instruct"
DB_PATH = "rag.db"
DIM = 768            # nomic-embed-text output size
MAX_DISTANCE = 0.43  # above this, refuse instead of answering (picked from eval.py)


def connect():
    db = sqlite3.connect(DB_PATH)
    db.enable_load_extension(True)
    sqlite_vec.load(db)
    db.enable_load_extension(False)
    db.execute("CREATE TABLE IF NOT EXISTS chunks(id INTEGER PRIMARY KEY, source TEXT, heading TEXT, text TEXT, hash TEXT UNIQUE)")
    db.execute(f"CREATE VIRTUAL TABLE IF NOT EXISTS vecs USING vec0(embedding float[{DIM}] distance_metric=cosine)")
    return db


def embed(texts, kind):
    # nomic-embed-text was trained with task prefixes; leaving them out costs recall silently
    prefix = "search_query: " if kind == "query" else "search_document: "
    out = ollama.embed(model=EMBED_MODEL, input=[prefix + t for t in texts], truncate=False)
    return out["embeddings"]


def split_markdown(path):
    """One chunk per '## ' section, with the document title and heading kept on top."""
    title, heading, buf, chunks = "", "", [], []
    for line in path.read_text().splitlines():
        if line.startswith("# "):
            title = line[2:].strip()
        elif line.startswith("## "):
            if buf:
                chunks.append((heading, " ".join(buf)))
            heading, buf = f"{title} > {line[3:].strip()}", []
        elif line.strip():
            buf.append(line.strip())
    if buf:
        chunks.append((heading, " ".join(buf)))
    return chunks


def ingest(folder):
    db = connect()
    added = skipped = 0
    for path in sorted(pathlib.Path(folder).glob("*.md")):
        for heading, text in split_markdown(path):
            body = f"{heading}\n{text}"
            h = hashlib.sha256(body.encode()).hexdigest()
            if db.execute("SELECT 1 FROM chunks WHERE hash=?", (h,)).fetchone():
                skipped += 1
                continue
            cur = db.execute("INSERT INTO chunks(source, heading, text, hash) VALUES (?,?,?,?)", (path.name, heading, text, h))
            [vec] = embed([body], "document")
            db.execute("INSERT INTO vecs(rowid, embedding) VALUES (?, ?)", (cur.lastrowid, sqlite_vec.serialize_float32(vec)))
            added += 1
    db.commit()
    print(f"added {added} chunks, skipped {skipped} unchanged")


def retrieve(db, question, k=3):
    [q] = embed([question], "query")
    return db.execute(
        "SELECT c.id, c.heading, c.text, v.distance FROM vecs v JOIN chunks c ON c.id = v.rowid "
        "WHERE v.embedding MATCH ? AND k = ? ORDER BY v.distance",
        (sqlite_vec.serialize_float32(q), k)).fetchall()


class LocalLLM:
    """The only place the model is called. Swap this class to use another backend."""
    def __init__(self, model=CHAT_MODEL, num_ctx=8192):
        self.model, self.num_ctx = model, num_ctx

    def answer(self, prompt):
        r = ollama.chat(model=self.model, messages=[{"role": "user", "content": prompt}],
                        options={"num_ctx": self.num_ctx, "temperature": 0})
        return r.message.content, r


PROMPT = """Answer the question in one or two full sentences, using only the numbered sources below.
After each fact, add the number of the source it came from in brackets, for example:
"Hotels are capped at 6,000 rupees outside metros [3]."
If the sources do not contain the answer, reply exactly: Not in the documents.

{sources}

Question: {question}"""


def ask(question, llm=None, verbose=True):
    db = connect()
    hits = retrieve(db, question)
    if not hits or hits[0][3] > MAX_DISTANCE:
        return "Not in the documents.", hits
    sources = "\n\n".join(f"[{i}] {h}\n{t}" for i, (_, h, t, _) in enumerate(hits, 1))
    text, r = (llm or LocalLLM()).answer(PROMPT.format(sources=sources, question=question))
    if verbose:
        tps = r.eval_count / (r.eval_duration / 1e9)
        print(f"  prompt {r.prompt_eval_count} tok, answer {r.eval_count} tok, {tps:.0f} tok/s, total {r.total_duration/1e9:.1f}s")
    return text, hits


if __name__ == "__main__":
    if sys.argv[1] == "ingest":
        ingest(sys.argv[2])
    else:
        question = " ".join(sys.argv[2:])
        answer, hits = ask(question)
        for _, h, _, d in hits:
            print(f"  {d:.3f}  {h}")
        print(answer)

A few design choices in there:

  • One chunk per section, heading on top. Each chunk starts with Travel policy > Hotels. Without the heading, a chunk that says "6,000 rupees elsewhere" has no subject, and both the embedding and the model have to guess what it is about. Fixed-size character chunks with overlap work too; the cheat sheet compares the options.
  • A content hash per chunk. Re-running ingest only embeds what changed. On a laptop, embedding is the slow step once the corpus gets into thousands of pages, so skipping unchanged chunks matters more than it does on a GPU server.
  • LocalLLM is the only class that talks to the chat model. To try another local model, pass a different name. To compare against a hosted model on non-sensitive test data, write a class with the same answer(prompt) method and pass it to ask(question, llm=...). Nothing else changes.
  • Refusal happens before the model sees the question, based on vector distance. More on that threshold in the eval section.

Run it#

.venv/bin/python rag.py ingest docs
.venv/bin/python rag.py ingest docs
.venv/bin/python rag.py ask "How many leave days can I carry over into next year?"

Real output, including the second ingest skipping everything:

added 9 chunks, skipped 0 unchanged
added 0 chunks, skipped 9 unchanged
> How many leave days can I carry over into next year?
  prompt 217 tok, answer 20 tok, 45 tok/s, total 0.9s
  0.221  Leave policy > Annual leave
  0.240  Leave policy > Sick leave
  0.320  Leave policy > Parental leave
You can carry over up to 10 unused leave days into the next year [1].
> Can I fly business class to London?
  prompt 232 tok, answer 20 tok, 44 tok/s, total 0.7s
  0.283  Travel policy > Booking flights
  0.464  Travel policy > Per diem
  0.472  Travel policy > Hotels
Business class needs written approval from a vice president before booking [1]. Not in the documents.
> What is the hotel limit in Pune?
  prompt 232 tok, answer 24 tok, 43 tok/s, total 0.8s
  0.229  Travel policy > Hotels
  0.382  Travel policy > Per diem
  0.463  Travel policy > Booking flights
The hotel limit in Pune is 6,000 rupees because Pune is outside metro cities [1].
> What is the dress code on Fridays?
  0.459  Travel policy > Per diem
  0.478  Travel policy > Hotels
  0.490  Travel policy > Booking flights
Not in the documents.

Retrieval got the right section first every time, and the dress code question never reached the model. Two of the answers still have problems, and both are typical of a 4B model:

  1. The London answer is correct, then tacks on "Not in the documents." The model is hedging because the policy doesn't mention London by name. If you parse answers in code, check whether the refusal string is the whole reply, not whether it appears somewhere in it.
  2. The Pune answer is a confident inference the documents don't support. The policy says "metro cities" and never lists them, and the model decided on its own that Pune is not one. A bigger model makes this mistake less often, but the real fix is in the documents: if a policy depends on a list, put the list in the corpus.

A shorter prompt that said only "answer using the sources and cite them" got a bare [1] from the 4B model and nothing else. Adding one example sentence of the expected format fixed it. Small models follow examples far better than abstract instructions.

Embeddings: the prefix you will forget#

The nomic-embed-text model card says the input must start with a task prefix: search_document: for chunks you index and search_query: for questions. Nothing errors if you leave it out. To see what it changes, I re-ingested without prefixes and ran the same eval (described below):

Setup Recall@1 Worst answerable distance Best unanswerable distance
With prefixes 9/9 0.396 0.459
Without prefixes 9/9 0.472 0.495

On nine easy questions, recall didn't move. The distance scale did. Without prefixes, most distances rose, and the gap between "has an answer" and "doesn't" shrank from 0.063 to 0.023. The beer question moved from 0.396 to 0.472, so the 0.43 refusal threshold tuned on the prefixed index would have refused it. So any change to the embedding setup (model, prefix, dimensions) means re-embedding the corpus and re-tuning the threshold.

The second embedding trap is truncation. Ollama loaded nomic-embed-text with a 2,048-token context (ollama ps shows it), and the /api/embed reference says truncate defaults to true. I sent it a 3,000-word string both ways:

truncate=False  ->  400 the input length exceeds the context length
default         ->  ok, prompt_eval_count 2048

With the default, the end of a long chunk silently vanishes from its vector, and questions about that part never match. The code passes truncate=False so an oversized chunk fails loudly at ingest time, when you can still split it.

Choosing a model for your RAM#

Memory goes to the weights and to the KV cache, which grows with the context length.

Weights. Ollama's default tags use Q4_K_M quantization (check with ollama show <model>). From the sizes ollama list reports, that comes to about 0.62 GB per billion parameters: qwen3:4b-instruct has 4.0B parameters and is 2.5 GB, qwen2.5:7b has 7.6B and is 4.7 GB. So a quick estimate is weights ≈ 0.6 × billions of parameters, in GB.

KV cache. Per token, the cache holds a key and a value for every layer and KV head. For Qwen3-4B-Instruct, the model config lists 36 layers, 8 KV heads and a head size of 128. At 16 bits per number:

2 (K and V) x 36 layers x 8 heads x 128 dims x 2 bytes = 147,456 bytes per token
4,096 tokens  x 147,456  = 0.60 GB
8,192 tokens  x 147,456  = 1.21 GB
32,768 tokens x 147,456  = 4.83 GB

ollama ps agreed with the math. The loaded model was 3.2 GB at a 4,096-token context and 3.9 GB at 8,192, a 0.7 GB step (the extra 0.1 GB is compute buffers). Note the 32K row: a 4B model's cache at 32K is almost twice its weights.

Putting it together for laptops, as estimates at Q4_K_M with an 8K context, keeping 6 to 8 GB free for the OS, the browser and your editor:

Laptop RAM Comfortable chat model Example tags
8 GB 1B to 2B llama3.2:1b, qwen3:1.7b
16 GB 4B, a 7B to 8B fits with little else open qwen3:4b-instruct, qwen2.5:7b
24 to 32 GB up to about 12B gemma3:12b

On Apple Silicon the GPU shares that RAM. On a PC with a separate graphics card, the limit that matters is the card's VRAM. When a model doesn't fit, ollama ps shows a CPU/GPU split in the PROCESSOR column instead of 100% GPU, and speed drops sharply.

Speed: what to expect#

I ran one RAG-sized prompt (about 1,040 tokens of context plus a question) through three models with num_ctx 8192, after a warm-up call so load time isn't counted. The numbers come from Ollama's own timing fields (prompt_eval_duration, eval_duration):

qwen3:1.7b         prompt 1044 tok at  1956 tok/s | answer 56 tok at   77 tok/s | 1.3s
qwen3:4b-instruct  prompt 1036 tok at  1027 tok/s | answer 45 tok at   41 tok/s | 2.1s
gemma3:12b         prompt 1034 tok at   324 tok/s | answer 45 tok at   15 tok/s | 6.2s

These are single runs on one machine, so treat them as rough. Two patterns hold more generally. Reading the prompt is far faster than writing the answer, so adding retrieved context costs less time than you might fear: at about 1,000 tokens per second, three extra chunks add well under a second on the 4B model. And generation speed falls roughly in line with model size, so a 12B model writes about a third as fast as a 4B one. For an internal Q&A tool, 40 tokens per second already outpaces reading speed. 15 is usable for short answers and painful for long ones.

The first call after a model unloads also pays the load time. In my first test, load_duration was 1.8 seconds of a 2.3-second call. The Ollama FAQ says models stay loaded for 5 minutes by default; set OLLAMA_KEEP_ALIVE or the keep_alive request field if your users ask questions further apart than that.

num_ctx: the setting that breaks long prompts#

Ollama does not use a model's full context length by default. The context length docs say the default is 4K tokens with under 24 GiB of VRAM, 32K between 24 and 48 GiB, and 256K above that. My 24 GB Mac got 4,096 (shown in the CONTEXT column of ollama ps), even though qwen3:4b-instruct supports 262,144.

To see what happens when a prompt is too long, I sent a 6,136-token prompt with the answer in the first line, once with the default and once with num_ctx set:

for opts in [{}, {"num_ctx": 8192}]:
    try:
        r = ollama.chat(model="qwen3:4b-instruct", messages=[{"role": "user", "content": prompt}],
                        options={"temperature": 0, **opts})
    except ollama.ResponseError as err:
        print(f"{str(opts):20} error {err.status_code}: {err.error}")
        continue
    print(f"{str(opts):20} prompt_eval_count={r.prompt_eval_count:5}  answer={r.message.content.strip()!r}")
{}                   error 400: request (6136 tokens) exceeds the available context size (4096 tokens), try increasing it
{'num_ctx': 8192}    prompt_eval_count= 6136  answer='4417'

On this Ollama version, an oversized chat request fails with a 400 instead of quietly dropping text. That's the safer behaviour, but it means a RAG app that works in testing with short chunks can start failing in use when someone asks a question that pulls three long sections. Set num_ctx explicitly per request (as LocalLLM does), or for every model with OLLAMA_CONTEXT_LENGTH=8192 ollama serve, and size it from your worst case: k chunks × your largest chunk + prompt template + answer length. The FAQ also warns that parallel requests multiply the context memory, since each concurrent request (OLLAMA_NUM_PARALLEL) gets its own slice.

Test it before you trust it#

Before pointing this at real files, write down questions with known answers, plus questions the documents cannot answer, and check retrieval alone. eval.py holds twelve cases: nine answerable, phrased the way a person would ask ("I left my laptop in a cab, what now?"), and three that should be refused.

CASES = [  # (question, expected heading, or None if the docs do not answer it)
    ("How many leave days roll over to next year?", "Leave policy > Annual leave"),
    ("Can I get my beer at dinner reimbursed?", "Travel policy > Per diem"),
    ("I left my laptop in a cab, what now?", "Security handbook > Laptops"),
    # ... six more answerable cases
    ("What is the dress code on Fridays?", None),
    ("Who won the cricket world cup in 2011?", None),
    ("Does the office have a gym?", None),
]

db = rag.connect()
hit1 = hit3 = 0
answerable, unanswerable = [], []
for q, want in CASES:
    hits = rag.retrieve(db, q)
    top = hits[0][3]
    if want is None:
        unanswerable.append(top)
        print(f"  none  {top:.3f}  {q}")
        continue
    answerable.append(top)
    heads = [h for _, h, _, _ in hits]
    hit1 += heads[0] == want
    hit3 += want in heads
    print(f"  {'ok  ' if heads[0] == want else 'MISS'}  {top:.3f}  {q}")

n = len(answerable)
print(f"\nrecall@1 {hit1}/{n}   recall@3 {hit3}/{n}")
print(f"answerable top distance: max {max(answerable):.3f}")
print(f"unanswerable top distance: min {min(unanswerable):.3f}")
  ok    0.228  How many leave days roll over to next year?
  ok    0.230  Do I need a doctor's note for three days off sick?
  ok    0.232  How long is paternity leave?
  ok    0.301  What is the hotel budget in Mumbai?
  ok    0.396  Can I get my beer at dinner reimbursed?
  ok    0.208  Is premium economy allowed on a 7 hour flight?
  ok    0.371  I left my laptop in a cab, what now?
  ok    0.296  Can I paste a customer list into ChatGPT?
  ok    0.296  What is the minimum password length?
  none  0.459  What is the dress code on Fridays?
  none  0.504  Who won the cricket world cup in 2011?
  none  0.463  Does the office have a gym?

recall@1 9/9   recall@3 9/9
answerable top distance: max 0.396
unanswerable top distance: min 0.459

The two printed distances bracket the refusal threshold. Every answerable question scored 0.396 or less, every unanswerable one 0.459 or more, so I set MAX_DISTANCE to 0.43, between them. The margin is small, and the beer question came closest to the line, because the question says beer and the policy says alcohol. With twelve questions, this proves only that the plumbing works. Grow the set to fifty or more real questions from the people who will use the tool, re-run it after every change to chunking or models, and expect the two ranges to overlap. When they do, choose which mistake you can live with: wrongly refusing, or sending weak context to the model and hoping its own "Not in the documents" rule catches it.

Retrieval checks don't cover the answer itself. The Pune answer passed retrieval and was still wrong. To grade answers too, add an expected fact per question and check the reply for it, or use a judge model as described in LLM-as-a-judge in Python.

Where to go from here#

  • PDFs instead of Markdown. Splitting PDFs by page, and quoting the exact sentence with its page number, is covered in the PDF question-answering bot. Its retrieval step can be replaced with the retrieve function above.
  • Hybrid search. Vector search alone misses exact strings like policy codes and part numbers. Add an SQLite FTS5 full-text table next to vec0 in the same file and fuse the two rankings; the cheat sheet has a runnable reciprocal rank fusion example.
  • Agents over the same corpus. If one question needs several lookups, the local multi-agent research assistant runs a planner, researchers and a reviewer with LangGraph on the same Ollama setup.

Write the eval file before you add more documents. Twelve cases were enough to set the threshold, expose the prefix problem and catch the Pune answer.

Sources#