From SQLite to LLM Reranking: Seven Design Decisions for a Rust RAG Service

Introduction: The Dilemma and Way Out for Single-Machine RAG Services

Developers building local RAG systems have likely gone through the same struggle: wanting a purely local document retrieval service, only to find SQLite insufficient and needing to integrate LanceDB; after solving vector search, having to bring in Ollama for reranking; then discovering unstable retrieval quality and adding pgvector, configuring Elasticsearch… Finally, at deployment time, the container image is absurdly large and operational costs far exceed expectations.

The rag-task-service project was built to address this pain point: can we use the simplest SQLite + hash embedding, ready to go out of the box; while also leaving interfaces so that those who need it can smoothly migrate to production-grade backends?

The answer is: yes, and the design decisions behind it are worth examining closely.

This article will walk through seven key design decisions — from the storage layer to the retrieval layer, from concurrency control to reranking fallback — comparing the "intuitive approach" with the "actual approach" for each, and explaining why the latter is better.

TL;DR

• Zero external dependencies to run the whole RAG pipeline: the bundled .env enables LanceDB by default (a local file, zero external services) with hash embedding and SQLite WAL, so after cloning you can run make run directly; both semantic embeddings and the pgvector backend are optional
• Embeddings use FNV-1a rather than Rust's DefaultHasher — stable across versions, so persisted vector indexes don't break after a restart due to hash-algorithm drift
• Hybrid retrieval (the default hybrid) runs BM25 keyword recall and vector recall in parallel, then fuses the two rankings with RRF (k=60); it clearly outperforms pure vector search for short Chinese queries and proper-noun-heavy text
• LLM reranking is an optional robustness enhancement: on timeout or error it automatically falls back to the original RRF order, so search always returns results
• The persistence backend is switchable: LanceDB performs well within the low tens of thousands of chunks (17k chunks hybrid retrieval at ~54ms); switching to pgvector pushes BM25 scoring down into SQL and uses HNSW indexes, achieving ~1.5s hybrid retrieval at 340k chunks

1. Why SQLite WAL Instead of Running in Memory

But after actually getting started, I discovered an implicit problem with this type of storage: most of them only provide simple Key-Value interfaces, while the core requirement in RAG scenarios is indexed text querying, which is essentially a variant of relational querying.

SQLite is different. Its SQL interface natively supports full-text search (FTS5) and vector similarity search, and WAL (Write-Ahead Logging) mode solves the performance bottleneck of concurrent reads.

The database is opened via tokio_rusqlite, providing a non-blocking asynchronous interface, so HTTP request threads won't be blocked waiting for database I/O.

This trigger is one of my favorite design decisions. Event logs in a RAG system (document uploads, chunking, embedding tasks) that grow without bound will severely impact query performance. Using a trigger to automatically trim to 200 rows after each INSERT satisfies monitoring needs while avoiding scattered manual cleanup logic throughout the business code.

SQLite WAL mode supports multiple readers and a single writer simultaneously, which is exactly the optimal storage model for a "write-rarely, read-often" RAG service.

2. Optimistic Concurrency Control: How Version Numbers Prevent Silent Overwrites

What happens if two requests simultaneously update the same task record?

The intuitive approach is "last writer wins" — whoever's request arrives last overwrites the previous one. This is fine in most CRUD scenarios, but in a RAG task pipeline, task state transitions are critical: a document may be in the middle of chunking, embedding, and writing to the vector database. If another request silently overwrites it, the entire pipeline falls into an inconsistent state.

My approach is to introduce an expected_version field and use HTTP PATCH requests for optimistic lock validation.

If the version doesn't match, the database layer returns a 409 Conflict, and the client can choose to retry (re-read the latest state then update) or give up. The cost of this mechanism is minimal — just one extra integer field and one comparison operation — but it buys consistency guarantees for task state.

Compared to row-level locking: row locks block concurrent requests and become a bottleneck under high concurrency; optimistic locking lets concurrent requests fail fast, with the client deciding how to handle conflicts, which aligns better with RESTful principles.

3. Why FNV-1a for Hash Embedding Instead of DefaultHasher

Hash Embedding is a technique that maps text to low-dimensional vectors: split the text into bigrams (2-gram), hash each bigram, then use the hash values as vector indices and accumulate into the corresponding dimensions.

Intuitively, using Rust's standard library DefaultHasher is the most convenient choice — one line of code, zero dependencies. But DefaultHasher has a fatal flaw: it does not guarantee cross-version consistency. Different Rust versions may use different default hash algorithms, meaning vectors persisted to SQLite today could have different hash values after restarting the service tomorrow, rendering the entire vector index useless.

RAG scenarios have extremely high requirements for hash stability — once a document is stored, no matter how many times the service restarts, the same text must map to the same vector.

// Test assertion: two calls on the same text produce the same vector
let v1 = fnv1a_bigram("hello world", 128);
let v2 = fnv1a_bigram("hello world", 128);
assert_eq!(v1, v2);

This isn't over-engineering — it's a baseline requirement for RAG systems. Unstable embeddings mean retrieval is just spinning its wheels.

4. RRF Fusion: Why Hybrid Retrieval Chooses "Reciprocal Rank" Over Weighted Scoring

Hybrid retrieval is a classic RAG architecture: use BM25 for keyword recall and vector similarity for semantic recall, then merge the results from both paths.

The intuitive approach is to simply add BM25 scores and vector similarity scores together. But there's a problem: BM25 scores and vector similarity scores have completely different scales. BM25 scores might fall in the range of 0–10, while cosine similarity falls in the range of 0–1. Adding them directly means whichever dominates depends on whatever parameters you happened to tune, with no theoretical basis.

My choice is RRF (Reciprocal Rank Fusion). RRF doesn't care about raw scores, only about rankings:

The core advantage of RRF is that it naturally normalizes. No matter how the raw score distributions of the two retrieval paths look, as long as the rankings are meaningful, the fused result is meaningful. k=60 is the empirical value proposed by Cohen et al. in the original paper and performs stably in practice.

More importantly, RRF lets you independently tune both retrieval paths without worrying about score alignment. No matter how aggressively you tune BM25 for top-20, or how conservatively you tune vector retrieval for top-50, RRF can reasonably fuse them.

5. Why the Vector Database and BM25 Share the Same SQLite Backend

Most RAG tutorials suggest using SQLite for text storage and a dedicated vector database (like Chroma, Weaviate) for vectors. But in a single-machine scenario, this choice introduces unnecessary complexity.

rag-task-service takes the approach of letting BM25 (via SQLite's FTS5 extension) and vector retrieval (via SQLite's custom vector functions) share the same backend. After document chunking, text content is written to the FTS5 table, hash embeddings are written to the vector table, and the two are linked by document ID.

The benefit is extremely simple deployment: just one SQLite file, and backup, migration, and recovery are all one command. The downside is — if the data volume reaches the millions, SQLite's performance indeed falls short of dedicated vector databases.

But my judgment is: for single-machine RAG scenarios, millions of documents is already the ceiling, and SQLite is more than sufficient at this scale.

6. Why LLM Reranking Needs an Automatic Fallback Mechanism

With hybrid retrieval, isn't the result quality good enough? It is, but it can be better.

The idea behind LLM reranking is: first use hybrid retrieval to fetch the top-K candidate documents, then have the LLM re-rank those documents based on the query, outputting the final ranking. Compared to rule-based sorting, LLMs can better understand query intent and document relevance.

But LLM reranking has an obvious problem: it depends on external services (such as Ollama), and external services may be unavailable, slow to respond, or return erroneous results.

My approach is to design a reranking pipeline with automatic fallback. Normal flow: hybrid retrieval → LLM rerank → return. Fallback flow: hybrid retrieval → directly sort by RRF score → return.

// Pseudocode describing the fallback logic
async fn rank_and_return(query: &str, candidates: Vec<Doc>) -> Vec<Doc> {
    match llm_rerank(&query, &candidates).await {
        Ok(ranked) => ranked,
        Err(e) => {
            log::warn!("LLM rerank failed, falling back to RRF: {}", e);
            rrf_sort(&candidates)
        }
    }
}

Fallback isn't "not doing it well" — it's "engineering robustness." LLM services occasionally restart, network jitter occurs, timeouts happen — these are the norm in production. A good feature that can't fall back becomes a bad feature when things break.

7. Axum Route Design: Why Organize by Resource Instead of by Function

There are many schools of thought for RESTful API route design. Some organize by function (/search, /embed, /rerank), others by resource (/tasks, /tasks/{id}/results).

I chose the latter. The reason is simple: the core entity of a RAG system is the "task" — one task corresponds to one document upload and retrieval request. All operations revolve around the task: uploading documents, checking status, fetching retrieval results, triggering reranking.

POST   /tasks              # Create a retrieval task
GET    /tasks              # List tasks
GET    /tasks/{id}         # View task details
PATCH  /tasks/{id}         # Update task (with optimistic lock)
POST   /tasks/{id}/upload  # Upload a document to a task
GET    /tasks/{id}/results # Get retrieval results
POST   /tasks/{id}/rerank  # Trigger LLM reranking

Organizing routes by resource has several benefits:

First, the URLs themselves express the system's domain model — developers understand it at a glance. Second, Axum route handlers can be grouped by resource, with each handler focused on CRUD for a single entity, keeping the code structure clean. Third, if you need to add permission control in the future, adding it at the resource granularity is easier to reason about than at the function granularity.

---

What gives me the most satisfaction with this project isn't that it runs — it's that every decision has a clear "why." SQLite WAL solves concurrency, FNV-1a solves stability, RRF solves scale mismatch, fallback solves robustness — these aren't snap decisions, but the optimal solutions that remained after continuously eliminating wrong options in practice. I hope the thinking behind these decisions can also give you some inspiration as you build your own local RAG system.

Frequently Asked Questions

Project Repository

• GitHub repository: https://github.com/erishen/rag-task-service

Related Articles in this Series

This article is part of the "Practical Project Teardown (PSE)" series. Other articles in the series:

• Building a PSE Tri-Role Closed Loop with AutoGen: A Retriable, Traceable Agent Collaboration Framework — Planner / Specialist / Evaluator tri-role collaboration emphasizing retriability and traceability
• AutoGen PSE Architecture Deep Dive: Building Reliable Multi-Agent Collaboration with the Planner/Specialist/Evaluator Pattern — A teardown from the message bus to the lifecycle
• When LLMs Start Deceiving Themselves: A Multi-Agent Framework Guarding Article Credibility with Regular Expressions — Programmatic verification plus multi-agent guarding against LLM hallucination
• LangGraph PSE: Explicitly Modeling PSE Tri-role Collaboration with State Machines — Explicitly modeling tri-role collaboration with state machines
• Preventing Agents from Lying at the Source: Building a RAG-Grounded PSE Multi-Agent Framework with LlamaIndex Workflow — LlamaIndex Workflow orchestrating a RAG-grounded multi-agent system