A few months ago, I was debugging a RAG pipeline for an internal engineering repo. A developer typed:
"What is the timeout limit inconfig_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:
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_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))
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.
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: