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:

1. 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.
2. 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.
3. 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 * 2:
            final.append(c)
        else:
            sentences = re.split(r"(?<=[.!?])\s+", c)
            sub = ""
            for s in sentences:
                if len(sub) + len(s) > 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.