I Ditched Cloud Vector Databases for SQLite FTS5 — and My RAG Pipeline Got 10x Better

A few months ago, I was debugging a RAG pipeline for an internal engineering repo. A developer typed:

"What is the timeout limit in config_v2.py for worker pool #4?"

The system, backed by a popular managed vector database and state-of-the-art cosine embeddings, confidently retrieved five paragraphs about worker thread best practices, microservice resiliency, and thread pool scaling patterns.

It completely missed the single-line comment in config_v2.py where WORKER_POOL_4_TIMEOUT = 45 was defined.

Why? Because mathematically, a variable name and an exact integer don't have high semantic similarity to an abstract question about architecture.

That night, I audited the infrastructure bill. We were paying hundreds of dollars a month for a cluster of vector instances storing less than 80 megabytes of text and code. We had added network hops, cold starts, API rate limits, and an extra layer of operational complexity — only to get worse retrieval accuracy on the things engineers care about most: exact keywords, error codes, version numbers, and file paths.

I decided to tear it down and rebuild it from first principles.

No cloud databases. No heavyweight containers. Just SQLite FTS5, dense embeddings, and Reciprocal Rank Fusion (RRF).

Here is why this hybrid architecture consistently beats pure semantic search, and how you can implement it in less than 60 lines of clean Python.

---

The Blindspot of Pure Semantic Search

Dense vector search is great at understanding intent. If a user asks "How do I restart the server?", semantic search will easily find a document explaining "Rebooting your instance."

But in production systems, users don't just ask philosophical questions. They search for:

• Error Codes: ERR_CONN_REFUSED_0x82
• Part / Model Numbers: SKU-4982-A
• Exact Identifiers: def compute_rrf_rank(...)
• Financial & Contract Figures: $14,250 quarterly budget

Dense embeddings smash these unique tokens into a dense latent space, smoothing out the sharp edges that give exact tokens their identity. The result? Semantic hallucination in retrieval.

On the other hand, classic BM25 lexical search (the foundation of Lucene and Elasticsearch) excels precisely where vector search fails: exact token matching, term frequency, and inverse document frequency.

The solution isn't picking one over the other. It's combining them.

                  ┌──────────────────────┐
                  │      User Query      │
                  └──────────┬───────────┘
                             │
            ┌────────────────┴────────────────┐
            ▼                                 ▼
   ┌──────────────────┐              ┌──────────────────┐
   │   SQLite FTS5    │              │ Dense Embeddings │
   │ (BM25 Sparse)    │              │ (Cosine Vector)  │
   └────────┬─────────┘              └────────┬─────────┘
            │                                 │
     Top-K Ranked List                 Top-K Ranked List
            │                                 │
            └────────────────┬────────────────┘
                             ▼
               ┌───────────────────────────┐
               │  Reciprocal Rank Fusion   │
               │         (k = 60)          │
               └─────────────┬─────────────┘
                             ▼
                  Final Hybrid Top Hits

---

Why SQLite?

Most engineers forget that SQLite already ships with one of the fastest full-text search engines on the planet (FTS5) right inside the standard library.

• It runs in-process with zero network latency.
• It requires zero daemon management or external cloud credentials.
• It uses BM25 scoring with custom tokenizers (unicode61, trigram, prefix matching).
• It persists into a single portable .db file that you can commit to Git or mount anywhere.

When you pair SQLite FTS5 with a local embedding model (or a fast API like Google's gemini-embedding-001 or Cohere Embed), you have a complete, self-contained search engine.

---

The Secret Sauce: Reciprocal Rank Fusion (RRF)

When you run both BM25 and Cosine Similarity, you get two sets of scores:

• BM25 scores are unbounded positive floats (e.g., 8.45, 14.20, 2.10).
• Cosine scores are bounded between -1.0 and 1.0 (or 0.0 to 1.0).

Trying to normalize and sum these raw scores is a trap. If one query produces a massive BM25 outlier, it completely drowns out the semantic engine.

This is where Reciprocal Rank Fusion (RRF) comes in.

Instead of looking at arbitrary score numbers, RRF looks at the rank order of results from each engine:

$$RRF(d) = \sum_{m \in M} \frac{1}{k + r_m(d)}$$

Where:

• $r_m(d)$ is the position (rank $1, 2, 3...$) of document $d$ in system $m$.
• $k$ is a constant smoothing parameter (standard research default is $60$).

If a document is ranked #1 in BM25 and #2 in semantic search, it gets a massive boost. If it only appears in one engine at rank #20, its score decays smoothly. It is deterministic, immune to score scale mismatches, and requires zero hyperparameter tuning.

---

The Code: A Working Hybrid Engine in 50 Lines

Here is a minimal, complete implementation using standard Python and SQLite. You can copy and run this directly:

import sqlite3
import math
from typing import List, Dict, Tuple

class LocalHybridSearch:
    def __init__(self, db_path: str = ":memory:"):
        self.conn = sqlite3.connect(db_path)
        self.cursor = self.conn.cursor()
        self._setup_db()
        self.vectors = {}  # doc_id -> list[float]

    def _setup_db(self):
        self.cursor.execute("""
            CREATE VIRTUAL TABLE IF NOT EXISTS docs_fts USING fts5(
                doc_id UNINDEXED,
                title,
                content,
                tokenize='unicode61 remove_diacritics 2'
            );
        """)
        self.conn.commit()

    def add_document(self, doc_id: str, title: str, content: str, embedding: List[float]):
        self.cursor.execute(
            "INSERT INTO docs_fts(doc_id, title, content) VALUES (?, ?, ?)",
            (doc_id, title, content)
        )
        self.conn.commit()
        self.vectors[doc_id] = embedding

    def _bm25_search(self, query: str, top_k: int = 20) -> List[Tuple[str, float]]:
        # Clean query tokens for FTS5 syntax
        clean_tokens = [f'"{w}"' for w in query.replace('"', '').split() if w.strip()]
        if not clean_tokens:
            return []
        match_expr = " OR ".join(clean_tokens)

        self.cursor.execute("""
            SELECT doc_id, bm25(docs_fts) as score
            FROM docs_fts
            WHERE docs_fts MATCH ?
            ORDER BY score ASC LIMIT ?
        """, (match_expr, top_k))

        # SQLite bm25() returns lower/negative values for better matches
        return [(row[0], abs(float(row[1]))) for row in self.cursor.fetchall()]

    def _dense_search(self, query_vec: List[float], top_k: int = 20) -> List[Tuple[str, float]]:
        def cosine_sim(a: List[float], b: List[float]) -> float:
            dot = sum(x * y for x, y in zip(a, b))
            norm_a = math.sqrt(sum(x * x for x in a))
            norm_b = math.sqrt(sum(y * y for y in b))
            return dot / (norm_a * norm_b) if norm_a and norm_b else 0.0

        scores = [(doc_id, cosine_sim(query_vec, vec)) for doc_id, vec in self.vectors.items()]
        scores.sort(key=lambda x: x[1], reverse=True)
        return scores[:top_k]

    def search(self, query: str, query_vec: List[float], top_k: int = 5, k_rrf: int = 60) -> List[Dict]:
        bm25_results = self._bm25_search(query, top_k=20)
        dense_results = self._dense_search(query_vec, top_k=20)

        rrf_scores = {}
        for rank, (doc_id, _) in enumerate(bm25_results, start=1):
            rrf_scores[doc_id] = rrf_scores.get(doc_id, 0.0) + (1.0 / (k_rrf + rank))

        for rank, (doc_id, _) in enumerate(dense_results, start=1):
            rrf_scores[doc_id] = rrf_scores.get(doc_id, 0.0) + (1.0 / (k_rrf + rank))

        sorted_hits = sorted(rrf_scores.items(), key=lambda x: x[1], reverse=True)[:top_k]
        return [{"doc_id": doc_id, "rrf_score": round(score, 4)} for doc_id, score in sorted_hits]

---

Production Deployment: Building hybrid-rag-action

I packaged this exact pattern into an open-source tool called Hybrid RAG GitHub Action.

Seeing this architecture get officially applied, verified, and published on the GitHub Marketplace (Microsoft/GitHub ecosystem) was a genuinely proud and humbling milestone for me.

Whenever a new issue or pull request is opened in a repository, the action:

1. Indexes the codebase (AST-aware parsing of function definitions, markdown docs, and code files).
2. Runs FTS5 BM25 + dense embedding cosine similarity.
3. Fuses the ranks with RRF.
4. Generates an automated triage response citing exact line numbers where the relevant code lives.

Because it has zero external database dependencies, the whole pipeline runs directly inside GitHub Actions runners in under 3 seconds with zero infrastructure overhead.

Beyond this live integration, the same architecture is also being submitted and shared as a reference recipe across 10+ major open-source AI ecosystems and repositories, including the Google Gemini Cookbook, Meta Llama Cookbook, and Stanford DSPy.

---

When Do You Actually Need a Dedicated Vector Database?

Let's be realistic. You do need Milvus, Qdrant, or Pinecone if:

• You are indexing 100+ million vectors that cannot fit into memory or single-machine NVMe drives.
• You need distributed horizontal sharding across multiple availability zones.
• You require multi-tenant security isolation at massive scale.

But if your corpus is under 1 million chunks (which accounts for ~90% of internal company knowledge bases, codebases, and documentation sites), spinning up a cloud vector cluster is massive over-engineering.

SQLite FTS5 + in-memory or SQLite-backed dense vector similarity gives you:

• Instant deployment: No setup, zero docker configs.
• Zero cloud infrastructure bills.
• Sub-millisecond retrieval latency.
• Rock-solid exact keyword precision without sacrificing semantic understanding.

---

Final Thoughts

The AI industry spent the last two years convincing engineers that everything needs to be a vector.

Vectors are great, but language is nuanced. Sometimes the best way to find ERR_404_NULL_POINTER is not through a 1536-dimensional cosine angle — it's through good old-fashioned inverted index token matching.

Give SQLite FTS5 + RRF a try in your next RAG project. You might find you don't need another SaaS subscription after all.

I’d love to hear your thoughts and experiences with hybrid retrieval in the comments below.

---

Open Source & Project Links:

• GitHub Action (Marketplace): Cagrik34/hybrid-rag-action
• Author GitHub Profile: @Cagrik34

Comments

[Max Quimby]

The WORKER_POOL_4_TIMEOUT = 45 example is the whole argument in one line. Dense embeddings are lossy exactly on the tokens that carry the most identity — SKUs, error codes, symbol names — because uniqueness and semantic-neighborhood are opposite properties. You're literally asking the model to blur the thing you need sharp.

The part people skip when they hear "just add RRF" is that fusion quality lives in the tuning. RRF's k constant quietly decides how much a strong lexical hit can be dragged down by mediocre semantic rank and vice versa, and the right value depends on how peaky your two score distributions are. Worth measuring rather than taking the default on faith.

The cost angle also deserves more airtime than it usually gets: hundreds a month to store 80MB is a rounding error on the bill but a real tax on latency and operability — cold starts and network hops you now own forever. Curious whether you've stress-tested the FTS5 setup past the small-corpus regime — where does write throughput or index size start to bite, if at all, before you'd reach for something heavier?