DuckDB as an LLM cache: what to cache, what to key on, and where it breaks

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.

The single-writer rule decides the architecture

DuckDB's concurrency documentation has two rules. Read-write mode: one process reads and writes the file. Read-only mode: many processes can read, but then no process writes. So there is no "one live writer, many live readers on the same file" — read-only readers exist only while nothing holds the file for writing, and a cache is written on nearly every miss.

Three ways to live with that, in order of preference:

1. One process owns the cache. A single-worker service, a queue consumer, a batch pipeline. Threads inside it are fine: each takes con.cursor(), and appends never conflict. Most of the genuine fit lives here.

2. Batch the writes inside that process. A single-row INSERT carrying an embedding cost about 60ms through the Python client (mostly parameter binding, see below); 500 rows registered as an Arrow table and written with one INSERT … SELECT took 39ms in total, 0.08ms per row. Queue misses, flush every second. A prompt arriving twice inside one flush window goes to the model twice — a dict of in-flight keys fixes that, and INSERT OR IGNORE makes the duplicates harmless.

3. Many processes: Parquet partitions, one writer. Each app process reads the cache from immutable Parquet via read_parquet('cache/**/*.parquet') plus its own in-memory table of recent misses, and ships those misses to one writer that appends a partition per flush. Visibility lag equals the flush interval — a miss in worker A stays a miss in worker B until then.

If you need many writers with immediate cross-process visibility, use Redis or Postgres. That isn't a DuckDB weakness to engineer around; it's the wrong shape, and the diagnostic is the one from the SaaS warehouse post: how many processes need to write at once? DuckDB's docs now point at the Quack remote protocol (beta as of 1.5.2, expected to mature with 2.0 in autumn 2026) and at DuckLake with a Postgres catalog for multi-process writes. I haven't run either as a cache, so I still plan for one writer.

The schema and the key

import duckdb, hashlib, json, queue, threading, time
import pyarrow as pa

MODEL = "claude-sonnet-5"
TEMPLATE_VERSION = "support-reply/v7"
DIM = 1536

con = duckdb.connect("/var/lib/app/llm_cache.duckdb")   # exactly one process opens this file
con.execute(f"""
CREATE TABLE IF NOT EXISTS llm_cache (
    cache_key         VARCHAR PRIMARY KEY,   -- sha256 of every field that changes the answer
    model             VARCHAR,
    template_version  VARCHAR,
    params_hash       VARCHAR,               -- temperature, tools, output schema
    context_hash      VARCHAR,               -- the retrieved chunks the model actually saw
    prompt            VARCHAR,               -- resolved prompt, not the user's question
    response          VARCHAR,
    prompt_tokens     INTEGER,
    completion_tokens INTEGER,
    latency_ms        INTEGER,
    embedding         FLOAT[{DIM}],          -- NULL until the semantic tier earns it
    created_at        TIMESTAMP DEFAULT now()
);
CREATE TABLE IF NOT EXISTS cache_hits (cache_key VARCHAR, hit_at TIMESTAMP DEFAULT now());
""")

def cache_key(prompt: str, params: dict, context: list[str]) -> tuple[str, str, str]:
    h = lambda s: hashlib.sha256(s.encode()).hexdigest()
    params_hash  = h(json.dumps(params, sort_keys=True))[:16]
    context_hash = h("\x1e".join(context))[:16]
    key = h("\x1e".join([MODEL, TEMPLATE_VERSION, params_hash, context_hash, prompt]))
    return key, params_hash, context_hash

pending_rows: queue.Queue = queue.Queue()
pending_hits: queue.Queue = queue.Queue()

def complete(prompt: str, params: dict, context: list[str]) -> str:
    key, params_hash, context_hash = cache_key(prompt, params, context)
    c = con.cursor()                              # one cursor per thread; keep it in a threading.local
    hit = c.execute("SELECT response FROM llm_cache WHERE cache_key = ?", [key]).fetchone()
    if hit:
        pending_hits.put({"cache_key": key})
        return hit[0]
    t0 = time.perf_counter()
    r = call_model(MODEL, prompt, **params)       # your SDK call
    pending_rows.put({
        "cache_key": key, "model": MODEL, "template_version": TEMPLATE_VERSION,
        "params_hash": params_hash, "context_hash": context_hash,
        "prompt": prompt, "response": r.text,
        "prompt_tokens": r.usage.input, "completion_tokens": r.usage.output,
        "latency_ms": int((time.perf_counter() - t0) * 1000),
    })
    return r.text

def flusher():                                    # one write transaction per second, not per request
    w = con.cursor()
    while True:
        time.sleep(1)
        if rows := [pending_rows.get() for _ in range(pending_rows.qsize())]:
            w.register("batch", pa.Table.from_pylist(rows))
            w.execute("INSERT OR IGNORE INTO llm_cache BY NAME SELECT * FROM batch")
        if hits := [pending_hits.get() for _ in range(pending_hits.qsize())]:
            w.register("hits", pa.Table.from_pylist(hits))
            w.execute("INSERT INTO cache_hits BY NAME SELECT * FROM hits")

threading.Thread(target=flusher, daemon=True).start()

The read path is one indexed lookup. The write path is append-only, batched, and deduplicated by the primary key. Nothing is ever UPDATEd in place — DuckDB is not an OLTP store, and a cache doesn't need it to be one.

Invalidation is the part people get wrong

A cache serves whatever its key says it should. If the key is hash(prompt), then the day you upgrade the model, rewrite the template or re-index the knowledge base, the cache keeps answering from the system you retired — silently, indefinitely, with a hit rate that looks great on the dashboard. Nothing errors. I'd bet on this failure before any of the DuckDB-specific ones.

The rule: anything that would change the answer goes into the key. Model id, template version, generation parameters, tool or output-schema version, retrieved context, and the resolved prompt. Then invalidation stops being an event. A new model id produces new keys that miss naturally; the old rows sit there until the writer ages them out (DELETE … WHERE created_at < now() - INTERVAL 30 DAY, then CHECKPOINT). Keep the versions in columns as well, so SELECT model, template_version, count(*) … GROUP BY 1, 2 tells you what share of the cache is stale.

Retrieved context is the subtle one. A RAG prompt is a template plus chunks. Same question, re-indexed knowledge base, different chunks, different resolved prompt, different hash — automatically, if you hash the prompt the model saw and not the question the user typed. Hash the question and you serve last month's chunks.

The semantic tier: brute force first, HNSW only in memory

Measured at 1,536 dimensions, float32, 8 threads:

• Brute-force array_cosine_similarity top-1 over 50,000 rows: ~70ms from an in-memory table, ~170ms from the disk-backed file. At 200,000 rows, roughly 180ms and 430ms.
• HNSW (cosine) top-1 on an in-memory table: ~7ms p50, 10ms p99, about 4ms of which is parsing the 1,536-number literal. Build time at the defaults (ef_construction 128, M 16): 53s for 50,000 vectors, 239s for 200,000.
• The gotcha. Binding the query vector as a ? parameter from a Python list or numpy array cost ~57ms per call before any search happened. Pass it inline as a literal or via a registered one-row Arrow table. It dwarfed the search in every measurement until I found it, and it's why the single-row inserts above were slow.

The caveats, from DuckDB's current docs: the VSS extension is experimental. The HNSW index is in-memory-only unless you SET hnsw_enable_experimental_persistence = true, and because WAL recovery isn't implemented for it, a crash with uncommitted changes "can end up with data loss or corruption of the index" — the docs "still recommend that you do not use this feature in production environments". The index isn't buffer-managed, must fit in RAM, and doesn't count towards memory_limit. Deletes are only marked, so it needs PRAGMA hnsw_compact_index or a rebuild, and every checkpoint serialises the whole index again.

What works for a cache: the persistent table holds the embeddings; at startup the process copies them into an in-memory table and builds the index. A cache is rebuildable by definition, so the caveats cost a startup delay, not data — budget for it. Honestly, though: under about 100,000 entries, skip the index and accept 100–200ms on the semantic path. It only runs on an exact-match miss, and the model call it saves takes a second or more.

I don't have a threshold that transfers between embedding models: sample hits, review them, and set it from the false-positive rate you can live with. And a semantic hit must still match on model, template version and context hash — a similar prompt over a different knowledge-base snapshot is a wrong answer, not a near miss. VSS basics are in search your documents without a vector database.

The analytics argument, which nobody makes

Because the cache is columnar and speaks SQL, the question every conversation about LLM spend ends in — what did this save us, and where is the money going? — is a query. Prices live in a small model_prices table because price cards change.

-- Hit rate and spend avoided, last 30 days, per model and template version.
WITH hits AS (
    SELECT cache_key, count(*) AS n
    FROM cache_hits
    WHERE hit_at >= now() - INTERVAL 30 DAY
    GROUP BY 1
)
SELECT c.model,
       c.template_version,
       count(*)                                  AS misses,        -- one row per miss
       coalesce(sum(h.n), 0)                     AS hits,
       round(hits / (hits + misses), 3)          AS hit_rate,
       round(sum(coalesce(h.n, 0) * (c.prompt_tokens     * p.in_usd_per_m
                                   + c.completion_tokens * p.out_usd_per_m)) / 1e6, 2)
                                                 AS usd_saved
FROM llm_cache c
LEFT JOIN hits h USING (cache_key)
JOIN model_prices p USING (model)
WHERE c.created_at >= now() - INTERVAL 30 DAY
GROUP BY 1, 2
ORDER BY usd_saved DESC;

-- Which prompts are the spend? By template and retrieved context, not by exact key.
SELECT template_version, context_hash,
       count(*)                               AS calls,
       sum(prompt_tokens + completion_tokens) AS tokens,
       round(100.0 * tokens / sum(tokens) OVER (), 1) AS pct_of_tokens
FROM llm_cache
WHERE created_at >= now() - INTERVAL 30 DAY
GROUP BY 1, 2
ORDER BY tokens DESC
LIMIT 20;

A Redis cache can't answer either. It can tell you its hit ratio, in aggregate, right now. It can't tell you that one template version is 80% of your token spend, or that the semantic tier's hits cluster on a context hash you re-indexed last Tuesday. Point the same file at a notebook and the spend review needs no export.

When not to use it

• Many processes writing with immediate visibility — the common case for a fleet of stateless API workers. Redis or Postgres, and it should be said plainly.
• Sub-millisecond p99 across many processes or machines. One process, yes; a fleet, no — there is no server to share.
• A cache shared across machines. It's a file.
• Rows updated in place constantly — counters, TTL bumps. Append and expire instead, or use a store built for updates.
• A team that needs the cache to be boring. Redis is boring in the good sense; if nobody on call has read a DuckDB concurrency doc, the questions this design raises cost more than the analytics it returns.

So, should you?

One process or one pipeline, a cache per service, and you want to know what it's doing: yes. The exact tier does most of the work, the key does most of the correctness, and the SQL over it is the thing you can't get elsewhere.

A fleet of stateless workers: Redis for the hot path — and still DuckDB for the log of what was cached, written by one process from the stream of cache events. The analytics argument survives even when the cache itself doesn't.

The long version of the local-first pattern, with runnable code, is my book Local-First Analytics. Related: what DuckDB is actually good at in production, running DuckDB on your own infrastructure, DuckDB as a SaaS warehouse, and should there be an LLM in your pipeline.

If you're trying to get an LLM workflow past the demo stage without an open-ended API bill, the cache is usually the first control I put in. The scoping call below is free.