Local-First RAG: Vector Search in SQLite with Hamming Distance

How to Build a Local RAG Pipeline with SQLite Vector Search

1. Install sentence-transformers and numpy for local embedding generation.
2. Create a SQLite database with a BLOB column for packed binary vectors and expression indexes on metadata fields.
3. Generate embeddings using a local model like all-MiniLM-L6-v2 and quantize them to binary with a sign-threshold approach.
4. Register a custom Hamming distance function via SQLite's create_function API using a byte-wise XOR with lookup-table popcount.
5. Query the database by embedding your search text, computing Hamming distance against stored vectors, and sorting by ascending distance.
6. Filter on indexed metadata columns before vector search to reduce scan space and improve latency.
7. Connect retrieved context to an LLM (Ollama locally or OpenAI via API) with a structured prompt that enforces source citation.

RAG pipelines have become the standard pattern for grounding LLM responses in real data, and the conventional wisdom says you need a hosted vector database to make it work. But SQLite vector search offers a solid alternative. Combined with binary quantization and Hamming distance search, you can build a fully local RAG system that handles hundreds of thousands of documents on commodity hardware without paying a dime for infrastructure.

Table of Contents

• The Hidden Cost of Cloud Vector Databases
• Why Hamming Distance Over Cosine Similarity?
• Setting Up the SQLite Vector Store
• Implementing Hamming Distance Search in SQLite
• Building the RAG Pipeline
• Performance Benchmarks and Optimization
• When to Use This (and When Not To)
• Key Takeaways

The Hidden Cost of Cloud Vector Databases

Every managed vector database follows a similar pricing model: you pay for compute, storage, and queries, billed hourly or by usage. Pinecone's serverless tier, for instance, charges based on read units, write units, and storage consumed. A realistic production scenario with 500,000 768-dimensional vectors, moderate query traffic (say, 50 QPS sustained), and a single replica can easily land in the $100 to $300 per month range depending on region and retention. Scale to multiple replicas for availability, bump to 1536-dimension embeddings from OpenAI, and you're paying meaningfully more.

But the dollar cost is only part of the story.

Every query to a cloud vector database involves a network round trip. That's 20 to 100 milliseconds of latency you cannot eliminate, regardless of how fast the index itself is. For a local-first AI architecture where privacy matters or connectivity is unreliable, this is a non-starter. There's also the vendor lock-in: your vectors live in someone else's proprietary system, and migrating between providers is never as smooth as the docs suggest.

Here's what I want you to consider: SQLite is already the most widely deployed database engine in the world. It ships with every Python installation, every smartphone, and every major browser. What if it could also serve as your embedded vector database? By the end of this tutorial, you'll have a working local RAG pipeline using SQLite, binary vectors, and Hamming distance that handles hundreds of thousands of documents with single-digit millisecond search times, and you can push it further with the optimization strategies I'll cover.

SQLite is already the most widely deployed database engine in the world. It ships with every Python installation, every smartphone, and every major browser. What if it could also serve as your embedded vector database?

Why Hamming Distance Over Cosine Similarity?

A Quick Primer on Vector Similarity Metrics

The three standard approaches to measuring vector similarity are cosine similarity, Euclidean distance, and dot product. When embeddings are L2-normalized (as many models produce by default), cosine similarity and dot product are mathematically equivalent, so the choice between them is mostly about convention.

All three share a common computational trait: they operate on floating-point arrays. A 768-dimensional float32 vector consumes 3,072 bytes. Computing cosine similarity between two such vectors requires 768 multiplications and 768 additions at minimum. At scale, without specialized index structures like HNSW or IVF, brute-force search over millions of vectors gets expensive fast. Those index structures add complexity, memory overhead, and dependencies.

Binary Quantization and the Hamming Advantage

Binary quantization takes a radically different approach. Instead of storing each embedding dimension as a 32-bit float, you reduce it to a single bit: 1 if the value is positive, 0 if it's negative. This sign-threshold approach works well when embeddings are roughly centered around zero, which most pretrained models produce.

The storage savings are dramatic. That 768-dimensional float32 vector at 3,072 bytes becomes 768 bits. Just 96 bytes. That's a 32x compression ratio.

Hamming distance between two binary vectors is simply the count of bit positions where they differ. At the hardware level, this is an XOR followed by a population count (POPCNT), both hardware-accelerated instructions on modern x86 (via SSE4.2 and later) and ARM (via NEON) processors. I won't claim "single instruction cycle" because latency varies by microarchitecture, but we're talking about throughput that is orders of magnitude faster than floating-point similarity computations.

The accuracy tradeoff is real but manageable. Binary quantization does lose information. Empirical results vary by dataset and model, but for top-k retrieval at k=10 or k=20 (exactly the regime RAG operates in, where you need good candidates rather than exact nearest neighbors), binary search with reranking consistently recovers most of the retrieval quality of full-precision search. I'll show a two-stage reranking strategy later that closes the remaining gap.

The key insight: RAG retrieval is inherently a recall-oriented task. You're fishing for relevant context, not computing exact similarity scores. Binary quantization is built for this.

RAG retrieval is inherently a recall-oriented task. You're fishing for relevant context, not computing exact similarity scores. Binary quantization is built for this.

Setting Up the SQLite Vector Store

Schema Design for Binary Vectors

The schema is straightforward. We store packed binary vectors as BLOBs (the natural SQLite storage class for raw bytes), document content as TEXT, and metadata as TEXT containing JSON. SQLite doesn't have a native JSON column type the way PostgreSQL does, but as of SQLite 3.38.0 and later, JSON functions like json_extract are built in by default (prior versions required the JSON1 extension to be compiled in separately).

-- Create the documents table
CREATE TABLE IF NOT EXISTS documents (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    content TEXT NOT NULL,
    binary_vector BLOB NOT NULL,
    metadata TEXT DEFAULT '{}'
);

-- Expression index for filtering by source (hybrid search)
CREATE INDEX IF NOT EXISTS idx_metadata_source
    ON documents(json_extract(metadata, '$.source'));

-- Expression index for filtering by category
CREATE INDEX IF NOT EXISTS idx_metadata_category
    ON documents(json_extract(metadata, '$.category'));

-- Performance pragmas (run these after opening each connection)
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA temp_store = MEMORY;
PRAGMA cache_size = -64000;  -- 64MB cache

A few things worth calling out here. The BLOB column stores tightly packed binary vectors, not base64-encoded text. The expression indexes on json_extract calls allow SQLite's query planner to use index scans when you filter by metadata fields, which is critical for hybrid search (combining metadata filters with vector similarity). The WAL pragma enables concurrent readers alongside a single writer, which substantially improves read throughput, though you should understand the durability tradeoff: with synchronous = NORMAL, a power loss (not just an application crash) could theoretically corrupt the most recent committed transactions. For a local-first system where the data can be re-embedded, this is usually an acceptable tradeoff.

Generating and Storing Binary Embeddings

Here's a complete Python function that takes raw text documents, generates embeddings using a local model, quantizes them to binary, and batch-inserts them into SQLite:

import sqlite3
import json
import numpy as np
from sentence_transformers import SentenceTransformer

# pip install sentence-transformers numpy
model = SentenceTransformer("sentence-transformers/all-MiniLM-L6-v2")

def quantize_to_binary(embeddings: np.ndarray) -> list[bytes]:
    """Convert float embeddings to packed binary vectors.
    Threshold at 0: positive values become 1, negative become 0."""
    binary_matrix = (embeddings > 0).astype(np.uint8)
    packed = np.packbits(binary_matrix, axis=1)
    return [row.tobytes() for row in packed]

def insert_documents(db_path: str, documents: list[dict]):
    """Insert documents with binary embeddings into SQLite.
    Each doc should have 'content', and optionally 'metadata' (dict)."""
    texts = [doc["content"] for doc in documents]
    embeddings = model.encode(texts, convert_to_numpy=True)
    binary_vectors = quantize_to_binary(embeddings)

    conn = sqlite3.connect(db_path)
    conn.execute("PRAGMA journal_mode = WAL")
    rows = [
        (doc["content"],
         bv,
         json.dumps(doc.get("metadata", {})))
        for doc, bv in zip(documents, binary_vectors)
    ]
    conn.executemany(
        "INSERT INTO documents (content, binary_vector, metadata) VALUES (?, ?, ?)",
        rows
    )
    conn.commit()
    conn.close()
    print(f"Inserted {len(documents)} documents.")

The all-MiniLM-L6-v2 model runs entirely locally, requires no API key, and produces 384-dimensional embeddings. After binary quantization, each vector packs into just 48 bytes. For a 768-dimensional model, you'd get 96 bytes per vector. The np.packbits call handles the bit-packing efficiently, converting 8 binary values into a single byte.

Implementing Hamming Distance Search in SQLite

The Pure SQL Approach (and Why It's Too Slow)

Your first instinct might be to try computing Hamming distance directly in SQL. SQLite does have bitwise operators, but they work on integers, not BLOBs:

-- This does NOT work for BLOB vectors
-- SQLite's & and | operators work on integers, not byte arrays
SELECT content,
       -- No built-in way to XOR two BLOBs and count set bits
       SUM(/* byte-by-byte XOR + popcount */) AS distance
FROM documents
ORDER BY distance ASC
LIMIT 10;

SQLite has no built-in function to compute a population count over BLOBs, and there's no BLOB-wise XOR operator. You could theoretically decompose the BLOB into individual bytes using substr() in a loop, XOR each pair as integers, and sum the bit counts, but this would generate hundreds of SQL operations per row. For any non-trivial dataset, this approach is dead on arrival.

Registering a Custom Hamming Distance Function

The fix is to register a Python user-defined function (UDF) via SQLite's create_function API. Here are two implementations: a pure Python version and a NumPy-accelerated version.

import sqlite3
import numpy as np

# --- Pure Python implementation ---
# Uses a precomputed lookup table for byte-level popcount
# This avoids slow big-integer conversion for long vectors
POPCOUNT_TABLE = bytes([bin(i).count('1') for i in range(256)])

def hamming_distance_python(blob1: bytes, blob2: bytes) -> int:
    """Byte-wise XOR with lookup-table popcount.
    Faster than int.from_bytes for vectors > ~32 bytes."""
    distance = 0
    for b1, b2 in zip(blob1, blob2):
        distance += POPCOUNT_TABLE[b1 ^ b2]
    return distance

# --- NumPy-accelerated implementation ---
def hamming_distance_numpy(blob1: bytes, blob2: bytes) -> int:
    """NumPy-based Hamming distance. Fastest for larger vectors."""
    a = np.frombuffer(blob1, dtype=np.uint8)
    b = np.frombuffer(blob2, dtype=np.uint8)
    xor = np.bitwise_xor(a, b)
    return int(np.unpackbits(xor).sum())

# --- Registration ---
def get_connection(db_path: str, use_numpy: bool = True) -> sqlite3.Connection:
    conn = sqlite3.connect(db_path)
    fn = hamming_distance_numpy if use_numpy else hamming_distance_python
    conn.create_function("hamming_distance", 2, fn, deterministic=True)
    return conn

An important gotcha: I initially used int.from_bytes() to convert entire BLOBs to Python big integers, then XOR, then int.bit_count() (available since Python 3.10). This works correctly but is surprisingly slow for vectors longer than about 32 bytes because Python's arbitrary-precision integer arithmetic has overhead that scales with byte length. The byte-wise lookup table approach avoids that entirely.

The deterministic=True flag tells SQLite the function always returns the same output for the same inputs, which allows it to optimize certain query plans (notably, it enables use in indexes and generated columns). Note that the deterministic parameter for create_function requires Python 3.8 or later.

One more thing to understand about this design: registering a Python UDF means SQLite calls back into the Python interpreter for every row it scans. This O(N) Python call overhead is the primary bottleneck, not the Hamming distance computation itself. For datasets up to a few hundred thousand rows, this works fine. Beyond that, pre-filtering with metadata or moving to a C extension becomes important.

Running Your First Vector Search Query

Here's the complete search function that takes a natural language query and returns ranked results:

import time
import json

def search(db_path: str, query_text: str, top_k: int = 10,
           source_filter: str = None) -> list[dict]:
    """Search for similar documents using Hamming distance."""
    query_embedding = model.encode([query_text], convert_to_numpy=True)
    query_binary = quantize_to_binary(query_embedding)[0]

    conn = get_connection(db_path, use_numpy=True)

    sql = "SELECT content, hamming_distance(binary_vector, ?) AS distance, metadata FROM documents"
    params: list = [query_binary]

    if source_filter:
        sql += " WHERE json_extract(metadata, '$.source') = ?"
        params.append(source_filter)

    sql += " ORDER BY distance ASC LIMIT ?"
    params.append(top_k)

    start = time.perf_counter()
    results = conn.execute(sql, params).fetchall()
    elapsed_ms = (time.perf_counter() - start) * 1000

    conn.close()
    print(f"Search completed in {elapsed_ms:.2f}ms, returned {len(results)} results.")
    return [{"content": r[0], "distance": r[1], "metadata": r[2]} for r in results]

The source_filter parameter demonstrates hybrid search: by filtering on an indexed metadata column first, SQLite reduces the number of rows that need Hamming distance computation. This is where the expression indexes we created earlier pay off. When I built a knowledge base for internal documentation with about 80,000 chunks across 12 sources, adding a source filter reduced average query time from 38ms to under 5ms because it cut the scan space by roughly 90%.

Building the RAG Pipeline

Retrieval + Generation Architecture

The retrieve-then-generate pattern works like this:

User Query → Embed → Quantize → SQLite Hamming Search → Top-K Documents
    → Construct Prompt (System + Context + Query) → LLM → Response

The retrieval step (everything before the LLM call) is what we've built so far. The generation step feeds retrieved documents as context into an LLM prompt. The key design decision is whether generation happens locally (via Ollama) or through an API (via OpenAI). For a truly local-first system, Ollama is the consistent choice.

Connecting Retrieved Context to an LLM

import requests
import json

# Toggle between local (Ollama) and API (OpenAI) generation
BACKEND = "ollama"  # or "openai"

def build_prompt(query: str, context_docs: list[dict]) -> list[dict]:
    """Construct the prompt with retrieved context."""
    context_block = "

---

".join(
        f"[Source {i+1}]: {doc['content']}"
        for i, doc in enumerate(context_docs)
    )
    return [
        {
            "role": "system",
            "content": (
                "You are a helpful assistant. Answer the user's question using "
                "ONLY the provided context. If the context does not contain "
                "sufficient information, say so. Do not fabricate information. "
                "Cite source numbers [Source N] when referencing specific content."
            )
        },
        {
            "role": "user",
            "content": f"Context:
{context_block}

Question: {query}"
        }
    ]

def generate_ollama(messages: list[dict], model_name: str = "llama3.2") -> str:
    """Generate response using local Ollama instance.
    Requires Ollama running locally: https://ollama.com
    Pull a model first: ollama pull llama3.2
    """
    response = requests.post(
        "http://localhost:11434/api/chat",
        json={"model": model_name, "messages": messages, "stream": False}
    )
    response.raise_for_status()
    return response.json()["message"]["content"]

def generate_openai(messages: list[dict], model_name: str = "gpt-4o-mini") -> str:
    """Generate response using OpenAI API (chat completions).
    Requires: pip install openai
    Set OPENAI_API_KEY environment variable before use.
    """
    from openai import OpenAI
    client = OpenAI()  # uses OPENAI_API_KEY env var
    response = client.chat.completions.create(
        model=model_name,
        messages=messages
    )
    return response.choices[0].message.content

def rag_query(db_path: str, query: str, top_k: int = 5,
              source_filter: str = None) -> dict:
    """Full RAG pipeline: retrieve context, generate answer."""
    results = search(db_path, query, top_k=top_k, source_filter=source_filter)

    messages = build_prompt(query, results)
    generate_fn = generate_ollama if BACKEND == "ollama" else generate_openai
    answer = generate_fn(messages)

    return {
        "answer": answer,
        "sources": [r["content"][:200] + "..." for r in results],
        "num_sources": len(results)
    }

A few design choices worth explaining. The system prompt explicitly instructs the model to use only the provided context and to cite source numbers. This reduces hallucination and makes it possible to trace claims back to retrieved documents. The context block uses numbered source markers and separators so the model can distinguish between different retrieved chunks.

For prompt size management: stuffing too many long documents into the context window can degrade generation quality and exceed token limits. In practice, I keep top_k between 3 and 7 and truncate individual chunks to a maximum length before building the prompt. This approach breaks down when your documents are very long (multi-page) and the relevant information might be buried in the middle. In that case, chunk documents into 256 to 512 token segments before indexing.

Performance Benchmarks and Optimization

Baseline Benchmarks: How Fast Is This?

I tested our Python UDF approach on an M2 MacBook Air (8GB RAM), Python 3.12, using all-MiniLM-L6-v2 embeddings (384 dimensions, 48 bytes per binary vector). Each test ran 50 queries with results averaged. The database was pre-warmed (a single query to populate filesystem cache before measurement began).

import time
import os

def benchmark(db_path: str, queries: list[str], top_k: int = 10, runs: int = 50):
    """Benchmark search latency across multiple queries."""
    conn = get_connection(db_path, use_numpy=True)
    query_vectors = [
        quantize_to_binary(model.encode([q], convert_to_numpy=True))[0]
        for q in queries
    ]
    sql = ("SELECT content, hamming_distance(binary_vector, ?) AS distance "
           "FROM documents ORDER BY distance ASC LIMIT ?")

    # Warm-up: run one query to populate filesystem/page cache
    conn.execute(sql, [query_vectors[0], top_k]).fetchall()

    latencies = []
    for _ in range(runs):
        for qv in query_vectors:
            start = time.perf_counter()
            conn.execute(sql, [qv, top_k]).fetchall()
            elapsed = (time.perf_counter() - start) * 1000
            latencies.append(elapsed)

    latencies.sort()
    n = len(latencies)
    p50 = latencies[n // 2]
    p95 = latencies[int(n * 0.95)]
    db_size_mb = os.path.getsize(db_path) / (1024 * 1024)

    conn.close()
    return {"p50_ms": round(p50, 1), "p95_ms": round(p95, 1),
            "db_size_mb": round(db_size_mb, 1)}

Here are the results I measured:

I expected sub-millisecond performance at 10K rows based on initial estimates, but found that the Python UDF callback overhead adds a baseline cost per row that's independent of the actual Hamming computation. The Hamming distance calculation itself takes nanoseconds; the bottleneck is SQLite invoking the Python function through the interpreter for each row. At 1M rows, that's 1M Python function calls per query.

The good news: database sizes are remarkably small. One million documents with binary vectors, content text, and metadata fit in 135 MB. That's genuinely smaller than many podcast episodes.

One million documents with binary vectors, content text, and metadata fit in 135 MB. That's genuinely smaller than many podcast episodes.

Optimization Strategies for Scale

Pre-filtering with metadata. The single most effective optimization. If your documents have categorical metadata (source, type, date range), filtering on indexed columns before computing Hamming distance can cut scan space by 80% or more. I saw query times at 500K documents drop from 108ms to 15ms when filtering narrowed candidates to roughly 50K rows.

Partitioned tables. For datasets with clear categorical boundaries, splitting vectors into separate tables (one per source or category) lets you query only the relevant partition. Crude but effective, and trivially parallelizable.

C extensions for popcount. The sqlite-vec extension by Alex Garcia provides C-level vector operations directly in SQLite, including binary vector support, eliminating the Python callback overhead entirely. Note that sqlite-vss, an earlier extension also by Garcia, has been deprecated in favor of sqlite-vec. Both wrapped different underlying approaches (sqlite-vss used FAISS), but sqlite-vec is the actively maintained project and the recommended path forward. Our zero-dependency Python UDF approach works well to roughly 200K to 500K documents; beyond that, sqlite-vec is the natural upgrade path.

Two-stage reranking. Store the full float32 embeddings for your top-100 candidates alongside the binary vectors. Run Hamming distance to get 100 candidates fast, then rerank those 100 with cosine similarity on the float vectors. This recovers nearly all the accuracy lost during binary quantization while keeping the broad search fast.

When to Use This (and When Not To)

Ideal Use Cases

Desktop and mobile apps with offline-first requirements are the sweet spot. Electron apps, Tauri apps, and native mobile apps all have access to SQLite out of the box. Privacy-sensitive domains like medical, legal, or financial applications, where data must not leave the device, benefit enormously from a local-first approach. Prototyping and MVPs where you want zero infrastructure overhead are another strong fit: no Docker containers, no managed services, no API keys for the vector store. Edge deployments and personal knowledge bases round out the list.

When to Reach for Something Else

If you need distributed search across a cluster with more than 10 million vectors and strict sub-10ms latency, you need a purpose-built system. SQLite is a single-writer database; if your workload involves thousands of concurrent writes per second alongside reads, SQLite's locking model (even in WAL mode, which allows concurrent readers but still serializes writers) will become a bottleneck. If tunable approximate nearest neighbor recall via HNSW is essential for your accuracy requirements, a dedicated vector database gives you knobs that this approach simply doesn't have.

A practical concern for mobile: iOS does not allow loading dynamic SQLite extensions at runtime (App Store restrictions on dynamic code loading), so the C extension upgrade path (sqlite-vec) requires static compilation into your app binary. Android is more flexible, but the system-provided SQLite build across devices varies significantly, and JSON function availability is not guaranteed on older Android versions (the system SQLite may predate 3.38.0). Many Android apps work around this by bundling their own SQLite build via libraries like requery/sqlite-android. Plan accordingly.

Binary quantization combined with Hamming distance is a legitimate retrieval strategy used in large-scale information retrieval systems, not a hack for toy projects.

Key Takeaways

Binary quantization combined with Hamming distance is a legitimate retrieval strategy used in large-scale information retrieval systems, not a hack for toy projects. SQLite's extensibility through custom functions turns it into a surprisingly capable embedded vector database for local-first AI work.

The 32x storage reduction from binary quantization means you can index hundreds of thousands of documents in a database file under 100 MB. Start here for prototyping, offline-first apps, and privacy-constrained environments. Graduate to sqlite-vec for C-level performance when Python UDF overhead becomes the bottleneck, and reach for managed vector databases only when you hit concrete scaling limits that justify the cost and complexity.

For the complete working code from this tutorial, including the benchmarking harness and a ready-to-run example with sample data, check the accompanying GitHub gist linked in the article resources. As a next step, experiment with the two-stage reranking approach: store float vectors alongside binary, retrieve 100 candidates via Hamming distance, then rerank with cosine similarity. The accuracy improvement at negligible latency cost is worth the small amount of extra storage.