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.
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.
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 chunks with numbers each,
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
- Index a folder of 10 text files and check the chunk count printed at the end.
- Open
index.dbin any SQLite viewer and look at thefileandpagecolumns. - Change the script so it skips a file if it is larger than 50 MB.
Answers
- Roughly words ÷ 250 per file with the default settings.
- You will see one row per chunk;
vecshows as binary data. - Add
if path.stat().st_size > 50_000_000: continueafter theis_filecheck.
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
Sources
- SQLite (public domain), https://sqlite.org
- Chroma, open-source vector database (Apache 2.0), https://docs.trychroma.com
- LanceDB (Apache 2.0), https://lancedb.github.io/lancedb/
- sqlite-vec, vector search inside SQLite, https://github.com/asg017/sqlite-vec
- DuckDB vss extension, https://duckdb.org/docs/extensions/vss
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.