Home / Learn / Data & AI / Offline AI / Lesson 12.4
All lessons

Module 12: Private document search (RAG), fully offline · Lesson 12.4 · about 30 min

Vector databases you can run locally

Store your chunks and their embeddings on your own disk. Build the index with plain Python and SQLite, then learn when Chroma, LanceDB, sqlite-vec or DuckDB are worth adding.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 12 · about 30 minutes · read first: 12.3. You’ll be able to build and save a searchable index of a folder of documents, all offline.

A vector database is just a saved table of chunks, where they came from, and their numbers, so each question needs a quick lookup instead of re-reading every file.

1. Why this matters

Embedding a thousand pages takes minutes. You do it once, save the result, and every question afterwards only needs a quick lookup. Where you save it is the “vector database”. For personal and small-office use, it can be one file.

2. What a vector store keeps

One row per chunk: the text, where it came from (file, page), and its vector (the list of numbers from Lesson 12.2). Searching means: embed the question, compare it with every stored vector, return the closest rows.

It is like a library card catalogue. Making the cards takes an afternoon. After that, finding a book takes seconds, and each card says which shelf it is on.

Read a file. The builder opens docs/handbook.pdf. The reader gives back pages: page 12 says "Staff get 20 days of paid vacation a year..."

Cut and embed. Page 12 is cut into 2 chunks of up to 300 words. EmbeddingGemma turns each chunk into 768 numbers.

Save the rows. Each chunk becomes one row: file docs/handbook.pdf, page 12, the text, and its 768 numbers packed into 768 × 4 = 3,072 bytes.

Repeat for every file. A folder of 10,000 chunks makes a table of 10,000 rows; the vectors take 10,000 × 3,072 bytes ≈ 31 MB, in one file, index.db.

Search later. A question is embedded once and compared with all 10,000 vectors: 10,000 × 768 ≈ 7.7 million multiply-adds, about a millisecond on a laptop. The closest rows, with their file and page, come back.

3. The index builder

In your askdocs folder, make a subfolder called docs and put some files in it (.txt and .md for now; Lesson 12.6 adds PDFs, Word, spreadsheets and email). Then save readers.py from Lesson 12.6 (or, for now, a two-line version that reads text files) and this as index_docs.py:

import pathlib
import sqlite3

import numpy as np
import ollama

from chunker import chunk_text
from readers import read_any

EMBED = "embeddinggemma"
DOCS = pathlib.Path("docs")

db = sqlite3.connect("index.db")
db.execute("DROP TABLE IF EXISTS chunks")
db.execute("CREATE TABLE chunks (file TEXT, page INTEGER, text TEXT, vec BLOB)")

for path in sorted(DOCS.rglob("*")):
    if not path.is_file():
        continue
    for page, text in read_any(path):
        pieces = chunk_text(text)
        if not pieces:
            continue
        vectors = ollama.embed(model=EMBED, input=pieces)["embeddings"]
        for piece, vec in zip(pieces, vectors):
            db.execute("INSERT INTO chunks VALUES (?, ?, ?, ?)",
                       (str(path), page, piece, np.array(vec, dtype=np.float32).tobytes()))
    print("indexed", path)

db.commit()
print(db.execute("SELECT COUNT(*) FROM chunks").fetchone()[0], "chunks saved to index.db")

The two-line stand-in for readers.py, until Lesson 12.6:

def read_any(path):
    return [(1, path.read_text(encoding="utf-8", errors="ignore"))] if path.suffix in (".txt", ".md") else []

Run pip install numpy once, then python index_docs.py. SQLite is built into Python, and index.db is an ordinary file you can back up, copy or delete. The script rebuilds the whole index each run, which is simplest; for big folders, skip files whose modified time has not changed.

4. When to use a real vector database

Plain NumPy compares a question with 100,000 vectors in a few milliseconds. Move up when you need more:

Tool Good for Notes
sqlite-vec Keep everything in one SQLite file A small add-on; vector search with SQL
Chroma Quick start, metadata filters Pass your own embeddings so it does not fetch its own model; check its telemetry setting is off
LanceDB Millions of vectors on disk, fast File-based, no server
DuckDB + vss Analysts who already use DuckDB HNSW index inside DuckDB

All of these are open source and run with no server and no internet.

Maths and principle

Principle: exact search compares the question with every vector; approximate search (HNSW, IVF) compares with a smart shortlist.

Equation: for N chunks with d numbers each,

search work≈N×d multiply-adds,memory=N×d×4 bytes

N = 100,000 chunks, d = 768: about 77 million multiply-adds, a few milliseconds on a laptop. Memory ≈ 307 MB.

Analogy: finding the nearest café. Exact: measure the distance to every café in the country. Approximate: jump to your city, then your neighbourhood, then check the few cafés nearby. Much faster, and almost always the same answer. Where it breaks: it is worth it only when N is in the millions; below that, the shortcut costs more to build than it saves.

Try it: 20,000 chunks × 768 numbers × 4 bytes. How many MB? (About 61 MB.)

N
the number of chunks stored, one row each
d
how many numbers are in each vector (768 for EmbeddingGemma)
4 bytes
the space one 32-bit number takes on disk and in memory
exact vs approximate search
exact compares the question with every row; approximate (HNSW) checks a smart shortlist and very occasionally misses the best match
Physical meaning
The index is a card catalogue for your files. Tens of thousands of cards fit in a file smaller than a few photos, and checking every card takes less time than a blink, so most homes and small offices never need anything fancier.

“RAG needs a special vector database server.” For personal and small-office use, a SQLite file and NumPy are plenty: they compare against 100,000 vectors in milliseconds, with no server and no internet. Add Chroma, LanceDB, sqlite-vec or DuckDB when you reach millions of chunks or need filters and frequent updates.

5. Worked example

Made-up numbers. A two-person law office indexes 9,000 pages into 14,000 chunks: 21 minutes on a 16 GB laptop, once. index.db is 52 MB. Search takes 6 ms; the chat model’s answer takes 10 seconds. They never needed more than SQLite and NumPy.

6. Practice

  1. Index a folder of 10 text files and check the chunk count printed at the end.
  2. Open index.db in any SQLite viewer and look at the file and page columns.
  3. Change the script so it skips a file if it is larger than 50 MB.
Answers
  1. Roughly words ÷ 250 per file with the default settings.
  2. You will see one row per chunk; vec shows as binary data.
  3. Add if path.stat().st_size > 50_000_000: continue after the is_file check.

New words: vector store / vector database (a store of vectors plus their text), index (the saved, searchable collection), HNSW (a common fast, approximate search structure).

  • A vector store keeps one row per chunk: text, file, page and vector.
  • Build the index once, save it (one SQLite file is enough), and every question after that is a quick lookup.
  • Memory = N × d × 4 bytes: 10,000 chunks of 768 numbers is about 31 MB.
  • Exact search over a few hundred thousand vectors takes milliseconds; approximate indexes (HNSW) pay off only at millions.
  • Storing file and page next to each vector is what makes citations possible.

Check yourself

1You have 5,000 chunks. Do you need a special vector database?
2What does an approximate index (such as HNSW) trade away?
3Why store the file name and page next to each vector?

Sources

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. Prices and model names change fast: check the official source before you buy.