Someone reached this site last month asking, word for word, what are the pros and cons of using DuckDB as an LLM cache in a production-grade data app? Short answer: it fits when one process owns the cache, the hard part is the key rather than the database, and the reason to pick it over Redis is that the cache becomes a table you can ask questions of. The long answer follows, including where it's the wrong tool. Whether DuckDB belongs in production at all is its own post; this one is only about the cache.
"LLM cache" is three different caches
Exact-match. Key: a hash of the fully resolved prompt plus the versions below. Value: the response. A hit means this exact call was made before, so the answer is identical by construction. Hit rates are poor for free-text chat and excellent for classification, extraction, batch jobs and retries — the shapes in calling an LLM from inside SQL, where every re-run re-classifies the same rows unless something remembers. This is the tier that pays. (Your API vendor's prompt caching is a different thing: it makes a repeated prefix cheaper but still makes the call.)
Semantic. Embed the prompt, find the nearest stored prompt, serve its answer if the similarity clears a threshold. Every hit is a bet that two different prompts wanted the same answer. A false positive isn't a miss; it's a wrong answer the user sees.
Tool and retrieval results. The search call, the database lookup, the chunks fetched to build the prompt. Deterministic given their inputs and the source's version, run more often than the model, usually the cheapest win. They want a TTL more than a version.
Why DuckDB is plausible here
An exact-match hit is a function call, not a network round trip. On DuckDB 1.5.5 with 8 threads, a keyed lookup over 200,000 rows measured 0.26ms p50, 0.39ms p99 with an index on the key (2ms / 5.7ms without). The rows are columnar, so the cache is also a dataset — hit rate, spend saved and which prompts dominate are one query, which is the strongest argument and gets its own section. The same table takes a FLOAT[1536] column for the semantic tier. And there is nothing to run: a file inside the process you already operate. The memory_limit / temp_directory / threads setup is in running DuckDB on your own infrastructure; the one addition here is that the HNSW index lives outside that memory limit entirely.
This is the first part. The full post — including the rest of the working details — is on my site: DuckDB as an LLM cache: what to cache, what to key on, and where it breaks
Top comments (0)