Most teams approach retrieval backwards. They install a vector database, pick an embedding model, split every document into 512-token chunks, and only then ask whether users needed semantic search in the first place.

That is an expensive way to discover that people mostly search for exact things: an invoice number, an API method, a product code, or the words used in an internal runbook. For those queries, SQLite FTS5 is often a better first move. It keeps the index beside your application, exposes why a result matched, and avoids a second service that can fail independently.

A retrieval ladder showing why lexical search should come before embeddings

The practical question is not whether embeddings are useful. They are. The question is whether your queries have earned them.

When SQLite is enough

Start with an FTS5 virtual table containing the fields people actually search. A minimal version looks like this:

CREATE VIRTUAL TABLE docs USING fts5(title, body, tokenize = 'unicode61');

INSERT INTO docs(rowid, title, body)
VALUES (42, 'Reset the staging password', 'Rotate the credential in the staging vault...');

SELECT rowid, title, snippet(docs, 1, '[', ']', '...', 24)
FROM docs
WHERE docs MATCH 'staging password'
ORDER BY bm25(docs)
LIMIT 8;

This is not a toy trick. SQLite's official FTS5 documentation describes a virtual-table module for searching collections of documents, with Boolean operators, phrase queries, prefix queries, column filters, BM25 ranking, snippets, and highlighting. You can ship a useful search endpoint without adding Elasticsearch, Postgres, a hosted vector store, or an embedding bill.

A recent practical RAG explainer reports full-text retrieval under 10ms in its stated recipe. Treat that as a field report, not a universal promise. Your own latency depends on corpus size, disk, query shape, and whether you are returning large snippets. Still, it gives you the right instinct: measure the simple path before paying for a more elaborate one.

SQLite-first also removes the chunking argument. Full documents stay full documents. You do not have to debate 512 tokens versus 1024, overlap, or semantic boundaries before you have a query set that exposes a real retrieval problem.

Use this path when the user's vocabulary overlaps the documents, exact terms matter, the corpus changes often, or your team wants an index it can debug with SQL. It is particularly good for documentation, support tickets, source code symbols, SKUs, filenames, and internal jargon.

What broke in my first attempt: FTS5 does not understand that “car” and “automobile” can refer to the same thing. It also cannot infer that “How do I rotate a credential?” should match a document titled “Staging secret replacement.” If every test query is conversational, lexical search will look worse than it deserves because the query is badly formed.

When to add embeddings

Add semantic retrieval after a small evaluation set shows a lexical miss. Keep the test set boring and real: write down 20 to 50 questions users actually ask, record the document that should win, and run the same set after every change. Do not evaluate with questions invented by the person who built the index.

The escalation signals are clear:

  • Users use synonyms that never appear in the source text.
  • Questions are conversational and describe a task rather than naming a term.
  • Your documents use unfamiliar internal language and query rewriting cannot bridge it.
  • Exact matches return technically related pages but miss the page that answers the user's intent.

At that point, you do not need a hosted vector database by default. Sentence Transformers documents a local semantic_search function that compares query and corpus embeddings with cosine similarity. Its documented defaults include top_k=10, query_chunk_size=100, and corpus_chunk_size=500000. The last number is a batching setting for processing a large corpus, not a recommendation to put half a million documents in one application table or a quality score.

A small semantic branch can be added beside FTS5:

from sentence_transformers import SentenceTransformer, util

model = SentenceTransformer('sentence-transformers/all-MiniLM-L6-v2')
corpus_embeddings = model.encode(chunks, normalize_embeddings=True)
query_embedding = model.encode(['how do I rotate a staging credential'], normalize_embeddings=True)
hits = util.semantic_search(query_embedding, corpus_embeddings, top_k=10)

For a local setup, Ollama's embeddings documentation provides another route: run an embedding model locally, encode the documents and queries, then store the resulting vectors in the database or a purpose-built index. The important design choice is not the brand of model. It is keeping the retrieval layer replaceable while you learn what your corpus needs.

What broke here: embeddings make a bad chunking strategy harder to see. A semantically similar paragraph can be retrieved even when it lacks the answer, while a short exact-match document gets pushed down. You also inherit model versioning, re-indexing, memory use, and an evaluation problem. Semantic search is a tool for a measured miss, not a substitute for measurement.

A workflow that stays small

Use this order for a new retrieval feature.

First, log the question and the chosen source. Save the raw query, the top eight FTS5 results, the clicked or cited document, and latency. Redact secrets before storing logs. A week of real queries tells you more than a long argument about retrieval theory.

Second, fix query formation before changing retrieval. Normalize case, preserve identifiers, expand a tiny domain glossary, and remove conversational filler. If users say “reset staging secret” while the docs say “rotate credential,” a rewrite layer may solve the problem without embeddings. The practical explainer estimates about $0.001 per query for one GPT-4o-mini rewriting recipe, but treat that number as its example pricing, not a current quote. Calculate your own cost.

Third, add semantic candidates, not a second source of truth. Let FTS5 retrieve exact matches and let embeddings retrieve semantic candidates. Merge the lists, record which path found each result, and inspect disagreements. A hybrid system is easier to reason about when each retriever remains visible.

Fourth, promote only what earns its cost. If the corpus is stable and query volume grows, pre-computed embeddings can make sense. If documents change constantly, embedding everything in advance creates stale results and re-indexing work. If you have fewer than 1,000 queries per day, the practical RAG guide argues that simple approaches are usually sufficient. That is a decision boundary to test, not a law of nature.

Fifth, stop when the misses stop. Do not add a reranker because a diagram says every modern RAG stack has one. Add it when your evaluation set shows that the right answer is present in the candidate pool but ranked too low. Add a vector database when storage, latency, or scale makes your current index the bottleneck. Until then, SQLite is not a compromise. It is a fast feedback loop.

The useful RAG system is the one you can explain when it returns the wrong document. FTS5 gives you that explanation early. Embeddings are worth adding when the failure is genuinely semantic and the data says so. Build the boring version first, keep the test questions, and make the sophisticated version prove that it deserves to exist.

Sources