Tools

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

Back to Tutorials

The Retrieval Failure That Changed the Architecture

A few months ago, I was debugging a RAG pipeline over an internal engineering repository. A developer asked: "What is the timeout limit in config_v2.py for worker pool #4?"

The system used a popular managed vector database and current cosine-similarity embeddings. It confidently returned five passages about worker thread best practices, microservice resiliency, and thread-pool scaling patterns. None contained the answer.

The relevant evidence was a single-line comment beside the setting:

WORKER_POOL_4_TIMEOUT = 45

This was not primarily a model problem. It was a retrieval mismatch. The question contained several tokens that mattered exactly: a filename, a configuration key, a version marker, and an identifier. Semantic similarity found text that was conceptually related to worker pools. Full-text search could find the literal terms.

That distinction is the reason SQLite FTS5 deserves serious consideration in small and medium RAG systems. It is not a universal replacement for vector search. It is an embedded full-text retrieval engine that can be a better first retrieval layer when users ask about names, symbols, paths, error codes, configuration keys, tickets, or other lexical identifiers.

The "10x better" claim in this title should be read as a workload-specific result, not a general benchmark. For exact repository questions, replacing a semantic-only retriever with lexical search can feel dramatically better. For paraphrased questions about broad concepts, vectors still have a useful role.

What SQLite FTS5 Actually Does

SQLite FTS5 is SQLite's full-text search extension. It maintains a searchable text index over rows and returns matches for words and phrases found in the indexed content. In a RAG pipeline, the indexed rows can represent files, sections, code blocks, document pages, or chunks.

A practical record usually keeps both searchable text and source metadata:

  • Repository or collection identifier
  • File path or document name
  • Chunk identifier
  • Line range or page number
  • Raw chunk text
  • Language, branch, or version metadata
  • Optional heading or symbol name

The FTS index is then used to retrieve candidate chunks for a query. The application takes those candidates, applies any required metadata filters, and passes the highest-value context to the language model.

FTS5 is lexical rather than semantic. A query for WORKER_POOL_4_TIMEOUT is strong when the indexed text contains that identifier. A query for "how long can worker four wait before timing out?" may be less reliable if the source only contains the symbolic constant and no matching natural-language terms.

That limitation is also its strength. FTS5 does not silently transform a rare identifier into a broad concept. It gives you an inspectable match against the words that were actually indexed. Search behavior can be debugged by looking at the query, the indexed row, and the ranking output rather than treating an embedding distance as an opaque relevance signal.

FTS5 commonly supports relevance ordering through the BM25 ranking function. BM25 is a lexical ranking approach that considers term frequency and document-frequency behavior. It is useful for ordering results within one search index, but raw BM25 scores should not be assumed to be directly comparable across separate indexes or documents. When combining independent result lists, ranks are generally safer to combine than uncalibrated scores.

Why It Worked Better for Code

Code and configuration repositories contain a high density of exact strings. Examples include:

  • config_v2.py
  • WORKER_POOL_4_TIMEOUT
  • HTTP_429
  • --max-retries
  • tenant_id
  • Exception class names
  • Feature flags
  • Database table names
  • Commit references and issue identifiers

Embedding models represent meaning. They are not inherently optimized for preserving the retrieval value of every punctuation mark, identifier fragment, path segment, or number. A question containing worker pool #4 can be semantically close to many documents about worker pools while remaining far from the one line that contains the exact setting.

FTS5 gives those literals a direct retrieval path. The filename can match. The constant can match. The number can match. A distinctive comment can match. This is especially valuable when the answer is short and surrounded by a large amount of generic technical prose.

Chunking also matters. If ingestion creates very large chunks, the relevant line may be technically present but difficult to rank above unrelated text in the same chunk. If ingestion creates tiny fragments with no path or line metadata, retrieval can find the value but the generation step may lack enough context to explain it. A useful implementation keeps chunks small enough for focused retrieval while retaining source location and nearby context.

Pros and Cons

Pros

Low operational overhead. SQLite is embedded in the application process. A small RAG service can store its corpus, metadata, and full-text index in one database file rather than operating a separate retrieval service.

Excellent exact-match behavior. File names, identifiers, flags, error codes, and literal configuration values are natural inputs for lexical search.

Easy inspection and debugging. The index, source records, queries, filters, and returned rows can be examined using ordinary SQLite tooling and application logs.

Low infrastructure cost. For a local tool, internal service, or bounded corpus, there may be no reason to pay for a managed vector database or maintain a distributed search deployment.

Good privacy characteristics. Data can remain on the device or inside the application's deployment boundary. This is useful for private repositories and edge deployments, although the rest of the RAG stack still determines whether data leaves the environment.

Flexible hybrid design. SQLite can provide lexical retrieval while a small cached vector store provides semantic candidates. The two result lists can be merged with Reciprocal Rank Fusion, or RRF, which combines rank contributions rather than requiring comparable score scales.

Cons

No semantic understanding by itself. Synonyms, paraphrases, and conceptually related language are not its main strength. A user can ask a good question using words that never appear in the source and receive weak results.

Not a distributed vector platform. SQLite is not designed to provide the horizontal scaling, multi-node operation, or massive vector collections associated with distributed systems such as Milvus and other dedicated vector databases.

Concurrency and deployment require thought. A database file is simple to ship, but multiple application instances writing to the same file across machines is a different problem from one process reading a local database. Replication, shared storage, ingestion jobs, backups, and file locking need explicit operational decisions.

Index maintenance is part of your application. You must decide how to detect changed files, remove deleted content, rebuild indexes, preserve metadata, and avoid serving stale chunks. A managed service does not eliminate these questions, but it may provide more indexing infrastructure around them.

Search quality depends on tokenization and chunking. Paths, underscores, punctuation, generated files, minified assets, and programming-language syntax can behave differently from ordinary prose. Test the tokenizer and query behavior against your actual repository instead of assuming that a text-search demo represents code search.

It does not solve answer grounding. Better retrieval reduces one failure mode. It does not prevent an LLM from inventing an answer, ignoring a retrieved line, or mixing evidence from incompatible versions. The prompt and answer validation still need source citations and abstention behavior.

Setup Experience

The setup is attractive because the architecture is small. A basic implementation has four stages.

1. Ingest source records

Walk the approved repository or document collection, exclude irrelevant generated content, and create records with stable identifiers. Store the original path and location alongside the chunk. For code, line ranges and symbol names are more useful than a generic document title.

Do not discard the raw source after creating the index. Retrieval bugs are much easier to diagnose when you can compare the returned indexed text with the original file.

2. Create the full-text index

Create an FTS5 virtual table over the fields that should be searchable. The exact schema depends on whether content is stored directly in the index or maintained in a related ordinary SQLite table. The important design choice is to make source metadata recoverable for every result.

Indexing only the body text is a common mistake. Include path, filename, headings, symbol names, and other high-value identifiers when they are part of the retrieval question. Keep metadata that should be filtered, such as branch or tenant, in ordinary columns where possible so application code can enforce access boundaries.

3. Query and rank candidates

At query time, run the user's search terms through the FTS index and order candidates using the index's relevance ranking. Add hard filters before context construction. For example, a repository assistant should not retrieve a result from a branch or tenant the user is not authorized to inspect.

Query normalization is worth testing. Users may include punctuation, quoted identifiers, natural-language filler, or a mixture of symbols and prose. A robust system can generate more than one lexical query: one preserving distinctive identifiers and another containing meaningful natural-language terms. The system can then merge or deduplicate candidates.

4. Build grounded context

Return the source path, line range, and text with each result. Include enough neighboring context to make a one-line match interpretable, but keep the context bounded. The generation prompt should require the model to cite the source and say that the answer is not found when the evidence is insufficient.

For changing repositories, incremental ingestion is essential. Re-index changed files, remove deleted files, and associate records with a commit, branch, or snapshot identifier. Without version-aware metadata, an exact match can still produce a wrong answer from an old configuration.

The Hybrid Architecture I Would Use

The strongest general design is not "FTS5 everywhere." It is a retrieval router with lexical search as a first-class path.

For identifier-heavy questions, query FTS5 directly and give its exact matches substantial weight. For conceptual questions, run semantic retrieval as well. When both are useful, combine their ranked outputs with RRF. A common hybrid search approach uses the intuition that each result list votes by rank, with a contribution shaped like 1 / (k + rank) and k = 60. The point is not that this constant is universally optimal; it is that rank fusion avoids pretending that unrelated lexical and vector scores share a scale.

A practical flow looks like this:

User question
    -> query classification or parallel retrieval
    -> SQLite FTS5 candidates
    -> optional vector candidates
    -> metadata authorization and version filters
    -> deduplication and rank fusion
    -> optional reranking
    -> bounded cited context
    -> language model

Classification can begin with simple signals rather than a model. A question containing a path, code identifier, error code, quoted string, or numeric setting is a good lexical candidate. A question asking for an explanation or comparison may benefit from semantic retrieval. Running both in parallel is often simpler than building a fragile classifier, provided latency and embedding cost are acceptable.

For a corpus of roughly one hundred PDFs, another issue appears: merging results from separate document-level searches. BM25 scores from separate indexes are not portable. RRF is useful because it merges rankings without score normalization. This same idea applies when combining lexical and semantic result lists, although the quality still depends on candidate generation and deduplication.

Alternatives

A managed vector database

A managed vector database is appropriate when the corpus and traffic justify a separate retrieval service, when semantic search is central, or when the team needs hosted scaling and operational features. Its cost is not limited to the subscription. You also own embedding generation, re-embedding on model changes, metadata design, network latency, access control, observability, and debugging of poor retrieval.

It remains a sensible component for large knowledge bases and high-throughput distributed applications. The failure in the opening example is an argument against semantic-only retrieval, not against vector databases in every workload.

ChromaDB for local development

ChromaDB is an in-process option for local development. It can reduce the migration distance between a prototype and a vector-oriented design, but it does not replace lexical search for exact identifiers. A local vector store and SQLite FTS5 can also coexist.

Qdrant, Weaviate, or Milvus

These are candidates when a self-hosted or distributed vector service is required. Milvus, for example, is described as a cloud-native distributed system supporting large-scale collections and multiple index types and similarity metrics. That capacity is valuable at the relevant scale, but it adds deployment and operational complexity that a small internal repository assistant may not need.

SQLite plus cached vectors

A middle path stores FTS5 data and a limited set of cached vectors alongside application metadata. This keeps the deployment compact while preserving semantic retrieval for the cases where lexical matching is insufficient. It is a good fit when the corpus is bounded and the main requirement is reliable, inspectable retrieval rather than multi-billion-vector scale.

Practical Verdict

SQLite FTS5 is a strong default for small and medium private corpora, engineering repositories, local assistants, and internal tools where exact names and identifiers determine whether retrieval succeeds. It is cheap, inspectable, easy to package, and often substantially better than a vector-only pipeline for configuration and code questions.

I would not replace a distributed vector database solely because one benchmark question failed. I would first measure retrieval recall on a representative test set containing exact identifier questions, paraphrased questions, cross-document questions, versioned content, and access-controlled content. Compare lexical-only, vector-only, and hybrid retrieval before judging the architecture.

The practical recommendation is to make FTS5 a first-class retrieval channel. Start with SQLite when the corpus is bounded and the deployment is simple. Add cached vectors or a dedicated vector service when semantic recall, collection size, concurrency, or operational requirements justify them. The right question is not whether embeddings or full-text search are fashionable. It is which retrieval method preserves the evidence your users actually ask for.

FAQ

Is SQLite FTS5 a replacement for a vector database?

Not universally. FTS5 replaces a vector database only when lexical retrieval meets the workload's needs. It is particularly effective for exact identifiers, paths, symbols, error codes, configuration keys, and literal text. It is weaker for paraphrases and questions whose vocabulary differs substantially from the source.

Can FTS5 search code repositories effectively?

Yes, when the ingestion and chunking strategy preserve filenames, paths, symbols, line ranges, and relevant source text. You should test tokenization against your languages and repository conventions. Generated files and very large chunks can reduce practical quality.

Should I use FTS5 and embeddings together?

Often. Use FTS5 for exact and identifier-heavy queries, vectors for semantic candidates, and a rank-fusion method such as RRF when combining results. Keep the source metadata and authorization filters in the application path regardless of which retriever returned a chunk.

Does SQLite eliminate RAG operational work?

No. It removes a separate retrieval service for suitable deployments, but you still need ingestion, incremental updates, deletion handling, backups, version tracking, access control, prompt grounding, and retrieval evaluation. The smaller infrastructure footprint does not remove correctness responsibilities.

When should I keep a managed vector database?

Keep it when you need distributed scale, high concurrent traffic, hosted operations, or semantic retrieval across a very large and diverse corpus. Even then, consider adding lexical retrieval for identifiers and exact terms instead of relying on embeddings alone.

Is the claimed 10x improvement guaranteed?

No. It describes a workload-specific experience involving exact configuration retrieval. There is no universal multiplier. Measure recall and answer accuracy on your own questions; a hybrid system may outperform either lexical-only or vector-only retrieval.