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

A developer replaced a cloud vector database with SQLite FTS5 and dense embeddings in a hybrid RAG pipeline, reporting a 10x improvement in retrieval accuracy for exact keywords, error codes, and version numbers. The approach combines BM25 lexical search with cosine similarity using Reciprocal Rank Fusion, cutting infrastructure costs and complexity.

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. 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: ERR CONN REFUSED 0x82 SKU-4982-A def compute rrf rank ... $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 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. unicode61 , trigram, prefix matching . .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. When you run both BM25 and Cosine Similarity, you get two sets of scores: 8.45 , 14.20 , 2.10 . -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: 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. Here is a minimal, complete implementation using standard Python and SQLite. You can copy and run this directly: python 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 hybrid-rag-action I packaged this exact pattern into an open-source tool called Hybrid RAG GitHub Action https://github.com/Cagrik34/hybrid-rag-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: 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 . Let's be realistic. You do need Milvus, Qdrant, or Pinecone if: 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: 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: