NLQueries GitHub

Blog  / 

Semantic caching for text-to-SQL: cheaper, faster answers to repeat questions

Published . Written for nlqueries-core 0.4.

cache performance cost security

A semantic cache for an LLM stores what the model produced for a question and reuses it when a question with the same meaning comes back. For text-to-SQL that pays twice: the repeat answer skips a model call, which is most of the cost and most of the wait, and the hundredth person asking for last month's revenue gets the same SQL as the first. It is also a store of SQL that runs on your database with no model in front of it, so getting it wrong costs more than a stale answer. This post describes the semantic cache in nlqueries-core 0.4: how a hit is found, what a hit skips and what it does not, what each entry is bound to, and what makes it stop matching.

What a hit skips, and what it doesn't

The cache stores the SQL and the answer text, never the result rows. On a hit, the model is not called and the stored answer is replayed, but the stored SQL is run again, so the rows you see are current. A cache hit saves the LLM call, not the database query.

Before a stored statement runs, it is checked again against the same SQL policy as a freshly generated one: a single read-only query, or it is not executed. The replay path is the only one that sends SQL to your database without a generation step, so it gets the same gate.

Three ways to match a question

A lookup tries three tiers in order:

  • Tier 0, an exact match. The question is normalised: lowercased, whitespace collapsed, and punctuation dropped except <, >, =, !, -, . and %. Those are kept because they change the question: total > 100 and total < 100 are different questions, and so are 1.5 and 15. This tier needs no embedding.
  • Tier 1, a stored answer by meaning. The question is embedded and compared with stored questions. A match needs cosine similarity of at least 0.97 (NLQ_CACHE_ANSWER_THRESHOLD), which is close to a rewording, not a related question.
  • Tier 2, a SQL template. Dates, months, amounts, quoted strings and numbers are masked in the question, and the masked form is compared with stored templates at 0.90 or more (NLQ_CACHE_TEMPLATE_THRESHOLD). On a match, the values from the new question are bound into the stored SQL's placeholders, and the bound statement is checked before use. So a question that differs from a stored one only in a date or a number can reuse its SQL with the new value bound in. A template is stored only when the question had such values and its SQL had literals to replace.

One caution for 0.4: a tier 2 hit also replays the stored answer text, and that text was written about the original question's rows. Ask for the top 10 after someone asked for the top 5, and the ten fresh rows can arrive under a sentence about five. The rows are right; read them, not the sentence. In the next release, a template hit shows only the rebound SQL and its rows.

A follow-up is looked up under the question it resolved to, not the text typed. "And for Germany?" means something different in every conversation, so the same follow-up in two conversations does not share an entry.

Tiers 1 and 2 find their match by vector, and a vector is not signed (more on signing below). So in 0.4, a hit from either tier also has its stored question embedded again and compared with the question asked. An entry moved onto another question's vector fails that check. It costs one embedding per hit.

What an entry is bound to

Each agent has its own cache collection in Qdrant, cache_<agent>. Within it, every entry is signed with HMAC-SHA256, using a key held outside Qdrant, over its own contents and the context it was produced in:

  • the agent;
  • the connector, as the connector's ID and a hash of the connectors file;
  • the SQL dialect;
  • the knowledge base, as a hash of its file;
  • the version of the SQL policy.

An entry that does not verify for the context of the current question is a miss, not an error. That covers an entry altered in Qdrant, an entry replayed against another agent or connector, and an unsigned one. Someone who can write to Qdrant but cannot read the key cannot add an entry that will be served.

The key is generated on first use and kept in the state directory, unless you set NLQ_CACHE_SIGNING_KEY or NLQ_CACHE_SIGNING_KEY_FILE. Every process that shares one Qdrant has to share that key, or each treats the others' entries as misses. A key shorter than 32 bytes stops the MCP server at startup, and anywhere else it turns the cache off with an error in the log. See configuration.

What makes an entry stop matching

Nobody has to remember to invalidate anything. An entry stops matching when its context does:

  • The knowledge base changes. A new table, a changed column, new sample values or a new description changes the file's hash. Every entry signed against the old file then misses, which is what you want: its SQL was written for a schema that is no longer the one the model sees. An export-kb run that writes exactly the same file changes nothing.
  • The connectors file changes. Its hash is part of the binding, so editing it, or registering another connector, makes every agent's entries miss once.
  • An upgrade changes the SQL policy. Entries made under an older policy version miss, and new ones are written under the current rules.
  • The entry is over 24 hours old. Expired entries are skipped on read, and a write sweeps them from the collection at most once an hour (NLQ_CACHE_PRUNE_INTERVAL_SECONDS).

To empty an agent's cache by hand:

nlqueries cache list shop      # cached questions and their SQL
nlqueries cache stats shop
nlqueries cache clear shop     # drops the agent's cache collection

What is never stored

Some results are refused at write time, before anything is embedded:

  • SQL that did not pass validation. A prose refusal stored as SQL and re-run on every hit is the failure this rule exists for.
  • Document and hybrid answers. They restate excerpts as they read when the answer was written, and a replay would outlive an edited document for the whole day.
  • Empty answers, and answers that are the system reporting its own error.
  • Questions over 500 characters (NLQ_CACHE_MAX_QUESTION_CHARS). A padded prompt injection tends to be long, and questions that long are rarely asked twice anyway.

Who can read whose answers

Every entry in an agent's cache can be served to anyone who may query that agent. With tier 0 that only ever means the same question. Tiers 1 and 2 can serve one person's answer to someone else's differently worded question, which is the point of them, and also the only way a poisoned entry could reach someone who did not write it. For an agent where that trade is not worth making, set NLQ_CACHE_ANSWER_TIERS=0. Repeat questions are still cached, and the near misses are generated fresh.

Answers are stored in Qdrant in plain text unless you set NLQ_CACHE_ENCRYPTION=true, which seals them with AES-256-GCM. Questions, SQL and vectors are stored as before.

Where it applies in 0.4

You need a reachable Qdrant; the cache turns itself on when it finds one. Qdrant setup covers running it with an API key. Embedding runs locally: start nlqueries embed-server start once, or each process loads the model itself.

The cache serves nlqueries query and the MCP server's query tool. ask does not use it.

One limit to know about in 0.4: entries are written by a background task after the answer is sent. A long-running process, such as the MCP server or your own application using the async API, completes that write. A one-off nlqueries query run reads the cache but exits before its write runs, so questions asked only from the CLI do not fill it. That is fixed for the next release. Until then, cache stats on an agent used only from the CLI shows an empty cache, and that is why.

In short

  1. A hit skips the model, not the database: the SQL runs again.
  2. Entries are signed for their agent, connector, dialect, knowledge base and policy, so a changed context makes them miss.
  3. A day is the longest an entry lives.
  4. NLQ_CACHE_ANSWER_TIERS=0 keeps exact repeats only, for sensitive agents.

The cache commands are in the CLI reference. To try it, start the Docker Compose stack, which includes Qdrant, and ask the same question twice over MCP. For how the SQL is checked before any of this, see Why we validate SQL before it runs.

All posts · RSS feed · Documentation