Your RAG Pipeline Doesn't Need a Vector Database

You're building a RAG system over internal HR docs, medical records, or a client's legal contracts. You reach for Pinecone or pgvector on a managed Postgres, wire up OpenAI embeddings, and ship. Six weeks later legal asks where the data lives, your per-query cost is $0.004 and climbing, and your "air-gapped" deployment story has a hole in it the size of an HTTPS connection to api.openai.com . The problem isn't RAG. The problem is that most RAG tutorials assume you need a managed vector store and a hosted embedding API. For corpora under ~1M chunks on a single machine, you don't. SQLite with the sqlite-vec extension, plus a local embedding model via Ollama, gives you a fully offline pipeline that fits in a single file, runs in ~50ms per query on a laptop, and leaks nothing. This article walks through the actual code: chunking, embedding, storage, retrieval, and the parts that will bite you. Why local embeddings beat the API for sensitive docs Three concrete reasons, in order of how often they actually matter: - Data residency. Sending a paragraph of a merger agreement to text-embedding-3-small is a data transfer to OpenAI. If your contract says "data never leaves the customer VPC," you're already in violation. Local embeddings make this a non-issue. - Cost at volume. text-embedding-3-small is $0.02 per 1M tokens. Cheap per call, but re-embedding a 500MB corpus every time you change your chunking strategy gets old fast.nomic-embed-text via Ollama is free after the one-time model download. - Latency floor. A round trip to OpenAI is 80-200ms before you've done anything. A local embedding on an M2 Mac is 5-15ms. For interactive search, that's the difference between "instant" and "noticeable." The trade-off is real: text-embedding-3-large (3072 dims) still beats nomic-embed-text (768 dims) on MTEB by a few points. On domain-specific corpora, that gap often shrinks. Test on your data before assuming. Setup # Ollama serves the embedding model locally on :11434 ollama pull nomic-embed-text # sqlite-vec is a loadable extension, not a fork of SQLite pip install sqlite-vec ollama schema.sql : -- vec0 is a virtual table; the embedding column has fixed dimensionality -- because sqlite-vec stores vectors in a packed binary format, not JSON. CREATE VIRTUAL TABLE IF NOT EXISTS chunks USING vec0( embedding float[768], +text TEXT, +source TEXT, +chunk_index INTEGER ); -- Metadata filtering needs a regular index alongside the virtual table. CREATE INDEX IF NOT EXISTS idx_source ON chunks(source); The + prefix marks auxiliary columns - they're stored but not indexed for vector search. You can filter on them in WHERE clauses. Chunking Chunking is where most RAG pipelines quietly fail. Fixed 512-token windows split sentences, split code blocks, and split table rows. Overlap helps but doubles your storage. import re def chunk_text(text: str, target_chars: int = 1800, overlap: int = 200) -> list[str]: """Split on paragraph boundaries first, then sentences, then hard-wrap. Why not just split on tokens? Because token counts don't align with semantic boundaries - a 512-token window will happily cut a numbered list in half. Char-based targets are crude but the boundary logic below keeps units intact. """ paragraphs = re.split(r"\n\s*\n", text.strip()) chunks, buf = [], "" for para in paragraphs: # If adding this paragraph overshoots, flush and start fresh. # Only carry overlap when the buffer is already substantial. if len(buf) + len(para) > target_chars and buf: chunks.append(buf.strip()) buf = buf[-overlap:] if overlap else "" buf += para + "\n\n" if buf.strip(): chunks.append(buf.strip()) # Any chunk still oversized gets sentence-split as a last resort. final = [] for c in chunks: if len(c) target_chars and sub: final.append(sub.strip()) sub = "" sub += s + " " if sub.strip(): final.append(sub.strip()) return final Two things to tune: target_chars should match your embedding model's context. nomic-embed-text handles 8192 tokens, but retrieval quality degrades on long inputs - 1500-2000 chars is the sweet spot in my testing. And overlap should be roughly one sentence, not 20% of the chunk. Embedding and insertion import sqlite3, sqlite_vec, ollama, struct def embed(texts: list[str]) -> list[list[float]]: # Batch through Ollama's /api/embed; one HTTP call per batch, not per text. resp = ollama.embed(model="nomic-embed-text", input=texts) return resp["embeddings"] def ingest(db_path: str, source: str, text: str): conn = sqlite3.connect(db_path) conn.enable_load_extension(True) sqlite_vec.load(conn) conn.enable_load_extension(False) chunks = chunk_text(text) # Batch size 32 keeps memory bounded and Ollama's queue happy. for i in range(0, len(chunks), 32): batch = chunks[i:i+32] vectors = embed(batch) conn.executemany( "INSERT INTO chunks(embedding, text, source, chunk_index) " "VALUES (?, ?, ?, ?)", [ (struct.pack(f"{len(v)}f", *v), t, source, i + j) for j, (t, v) in enumerate(zip(batch, vectors)) ], ) conn.commit() conn.close() The struct.pack step is not optional. sqlite-vec expects raw little-endian float32 bytes, not a JSON array or a Python list. Passing a list silently produces garbage results or an error depending on version - always pack. Retrieval def search(db_path: str, query: str, k: int = 5, source_filter: str | None = None): conn = sqlite3.connect(db_path) conn.enable_load_extension(True) sqlite_vec.load(conn) conn.enable_load_extension(False) qvec = embed([query])[0] qbytes = struct.pack(f"{len(qvec)}f", *qvec) # KNN in sqlite-vec uses the MATCH operator with k = ?. # The distance is L2 by default; see below for cosine. sql = """ SELECT text, source, chunk_index, distance FROM chunks WHERE embedding MATCH ? AND k = ? """ params = [qbytes, k] if source_filter: # Post-filter is fine at small k, but see the gotcha below. sql = sql.replace("k = ?", "k = ? AND source = ?") params.append(source_filter) return conn.execute(sql, params).fetchall() Call it: for text, src, idx, dist in search("docs.db", "what is the PTO carryover policy?", k=5): print(f"[{dist:.3f}] {src}#{idx}: {text[:120]}...") Wiring retrieval into generation def answer(db_path: str, question: str) -> str: hits = search(db_path, question, k=5) context = "\n\n---\n\n".join(h[0] for h in hits) prompt = ( "Answer using ONLY the context below. If the answer isn't present, " "say so. Cite chunk indices.\n\n" f"Context:\n{context}\n\nQuestion: {question}" ) resp = ollama.chat( model="llama3.1:8b", messages=[{"role": "user", "content": prompt}], ) return resp["message"]["content"] llama3.1:8b at Q4_K_M runs at ~40 tok/s on an M2 Pro and is good enough for extractive QA. Step up to qwen2.5:14b or llama3.3:70b if you have the VRAM and the questions require real reasoning across chunks. Gotchas Cosine vs L2. sqlite-vec defaults to L2 distance. If you want cosine, normalize your vectors before insertion and before query - then L2 and cosine rank identically. nomic-embed-text does not return normalized vectors. Post-filtering kills recall. WHERE embedding MATCH ? AND k = 5 AND source = 'hr.pdf' retrieves 5 nearest overall, then filters. If your corpus is 90% legal and 10% HR, you'll often get zero HR hits. Either pre-filter with a separate query or over-fetch (k = 50 ) and truncate. There's an open issue on this in the sqlite-vec repo; the workaround is a two-stage query. sqlite-vec is pre-1.0. The API has changed between 0.0.x and 0.1.x. Pin your version. It also doesn't yet support ANN indexes - every query is a brute-force scan. At 100K vectors × 768 dims that's ~30ms. At 1M it's ~300ms and climbing. Batch embedding can OOM Ollama. Sending 500 texts in one call will spike memory. 32-64 is safe on most machines. No incremental re-embedding. Change your chunker and you re-embed everything. Store the chunker version in a metadata table so you can detect drift. When not to use this - Corpus > ~1M chunks. Brute-force scan becomes the bottleneck. Move to pgvector with HNSW, Qdrant, or LanceDB. - You need hybrid search at scale. BM25 + vector fusion is table stakes for good retrieval. SQLite's FTS5 works, but combining scores well is fiddly; Elasticsearch or Vespa do it better. - Multi-tenant with per-tenant isolation requirements. A single SQLite file is hard to slice. One file per tenant works up to a few hundred tenants, then it's a mess. - You already have Postgres. pgvector is one extension away and gives you transactions, replication, and HNSW. Don't add a second datastore for novelty. - Embedding quality is the bottleneck, not infra. If text-embedding-3-large beats your local model by 15 points on your eval set, the privacy win isn't worth the retrieval loss - negotiate a BAA or self-host a bigger model instead. For everything else - internal wikis, personal knowledge bases, single-tenant document Q&A, air-gapped deployments - a 200-line Python file and a .db you can scp is the right amount of infrastructure. Reference: sqlite-vec docs, Ollama embeddings API, MTEB leaderboard. Top comments (0)