Bringing HTTP’s caching rules to DuckDB functions with VGI

You ask an AI agent to compare sales across countries. It looks up exchange rates, tries a query, and revises the analysis after a follow-up question. The SQL changes, but the exchange rates it needs may be exactly the ones it fetched a moment ago. Calling the service again adds another wait, and possibly another charge, without giving the agent any new information.

VGI, the Vector Gateway Interface, makes services like this available to DuckDB as SQL functions. Imagine rates.rates() returns exchange rates in US dollars. An agent might inspect those rates before joining them to your sales:

-- Inspect the exchange rates.
SELECT currency, rate FROM rates.rates();

-- Use them to compare sales across countries.
SELECT country, sum(amount * rate) AS sales_usd
FROM sales
JOIN rates.rates() USING (currency)
GROUP BY country;

Both queries ask for the same rates. If the service allows those rates to be reused for five minutes, DuckDB’s VGI client can keep the first answer and use it again. The second query can reuse the rates even though the SQL has changed. DuckDB still reads the sales and calculates the totals; the saving comes from avoiding another trip to the rates service.

But how does DuckDB know that five minutes is acceptable? The code providing the rates—a VGI worker—has to say so. That is the part we borrow from HTTP caching: the provider tells the caller how long an answer is good for. An agent can explore different questions using that answer, while the service controls when it needs to be fetched again.

Where HTTP-style caching fits

SQL engines already have caches. DuckDB’s external file cache, for example, can reduce repeat reads from remote files. What an arbitrary API-backed function still needs is a contract for reusing its result: how long it is valid, who may reuse it, and how to check whether it has changed. VGI borrows that vocabulary from HTTP’s freshness rules in RFC 9111 and conditional requests in RFC 9110.

An exchange-rate feed or weather service may have a useful answer between updates. A worker can give that answer a lifetime that suits the source, allowing repeated queries to share it. This is a choice the worker author makes: connecting an API through VGI does not automatically cache its results or copy its HTTP headers into a caching policy.

A time-based policy is a different contract from a transactional database read. PostgreSQL and MySQL’s InnoDB determine which row versions a query can see through their transaction and snapshot rules. Keeping an arbitrary query result for five minutes cannot, by itself, preserve those guarantees. A service backed by either database could explicitly offer a cached snapshot, but a freshness window is not a substitute for the database’s consistency rules.

The same reasoning applies to scalar functions, which return one value for each input row. Looking up coordinates for an address can involve an API request worth avoiding when that address appears again. Multiplying an integer by two is so cheap that checking a cache may take longer than doing the calculation. Repetition creates an opportunity to save work, but the cost of that work determines whether caching pays.

What repeated queries can reuse

In the sales example, the reusable answer is the rate table. The sales totals can change independently, so storing the result of the entire query would miss the opportunity we care about. VGI keeps results at the function-call level, checking the inputs, request details, and caller before reusing them.

A table function produces rows, so a call that retrieves a rate table can reuse the complete result.

A scalar function returns one value per input row, so repeated addresses can reuse individual geocoding answers even across different queries.

A table-in/out function consumes rows and emits rows; its opportunities depend on whether it receives independent batches, correlated inputs, or the whole input together.

The distinction matters when an agent changes the surrounding query but continues to ask about the same entities. A new batch of orders might contain many familiar addresses, even though that batch as a whole has never appeared before. Per-value reuse can avoid processing those addresses again, while a cache of complete batches alone would miss the repetition.

Function type and call shape	Reusable unit	What a hit avoids
Table function FROM rates()	Complete scan	Producing and transferring the result again
Partitioned table function With partition_scope=True	Individual partition	Producing the cached partition
Scalar SELECT geocode(address)	Distinct input tuple, with per_value=True	Computing cached values; input scanning and output assembly remain
Streaming table-in/out FROM enrich((SELECT …))	Input batch	The worker exchange for that batch
Correlated table-in/out FROM orders, LATERAL enrich(orders.country)	Input chunk, plus distinct tuples when per_value=True	Chunk computation, or computation for cached tuples
Buffered table-in/out	Whole input	Combine and final result production; input ingestion still runs

Input order and duplicate counts can also affect the answer. A function that preserves row positions needs an order-sensitive input key, while an unordered operation may be able to match the same collection of rows in a different order. These rules must follow the function’s behavior rather than assuming that every repeated-looking input is interchangeable.

What the benchmarks show

We wanted to see both sides of that tradeoff: work expensive enough to justify a cache, and work where the cache gets in the way. We measured table, scalar, and table-in/out functions in Haybarn 1.5.5rc1 with VGI 8f9571b, using a local Python subprocess on an Apple M3. These are synthetic workloads, including simulated service waits, rather than measurements of live APIs or agents. The tables show median query time across seven trials, in milliseconds; lower is better.

FasterAbout the sameSlower

Both cached columns are compared with cache off. Amber means within 10% of that baseline, a descriptive band rather than a statistical significance test. Ratios use the unrounded medians.

Table functions

Scalar functions

Table-in/out functions

“Empty cache” excludes worker startup. “Later queries” follows ten untimed queries; every row except the fresh-values case reuses the same input. Cache counters confirmed hits on stable inputs and zero hits on fresh inputs. A cold scalar query can benefit immediately because later batches reuse values cached earlier in that query.

The hash performs 300 PBKDF2-HMAC-SHA256 iterations per distinct value. All runs use one engine thread, memory caching, and the default per-value store cap. Deduplication remains enabled with caching off. The methodology, scripts, and raw samples are available to reproduce the comparisons.

The expensive scalar function becomes about 34× faster when later queries reuse its inputs, because a lookup replaces substantial computation. But cheap arithmetic over 65,536 distinct values is effectively tied, even when those values are already cached. When every query instead introduces fresh values, caching becomes about 3.3× slower: the client pays for lookups and storage without avoiding any computation.

The streaming result is just as instructive. Passthrough sees almost no improvement even with all 32 batch-cache hits. Skipping those exchanges leaves the rest of the query’s execution costs in place. Adding a 5 ms wait per batch makes reuse worthwhile, but the gain is about 1.8×, not the scalar map’s 34×. Function type, repeat rate, and avoided work all matter; a hit rate alone cannot tell you whether caching pays.

For the agent in our opening example, the saving would depend on the time spent fetching rates and how often it asks for them again. The benchmarks show why that is worth measuring: a high hit rate can remove a costly wait, or merely replace cheap work with bookkeeping.

The worker advertises freshness

The client cannot infer a safe lifetime from the function name or its arguments. The worker therefore supplies a time to live (TTL) or an expiry time with its result. Here is the process() method of the rates() table function, returning sample exchange rates with a five-minute lifetime:

import pyarrow as pa
from vgi.cache_control import CacheControl

# Inside the Rates table-function class:
@classmethod
def process(cls, params, state, out):
    rates = pa.record_batch({
        "currency": ["EUR", "GBP", "JPY"],
        "rate": [1.09, 1.27, 0.0067],
    })
    out.emit(rates, cache_control=CacheControl(ttl=300))
    out.finish()

The TTL borrows the freshness-lifetime idea from HTTP’s max-age directive. In VGI, the lifetime starts when the client has received the complete result. During that window, an eligible matching call can use the cached answer.

The policy travels with the table function’s first output batch—the same batch that carries the rates. A Python scalar declares CACHE_CONTROL = CacheControl(ttl=300, per_value=True) on its ScalarFunction class, while a streaming table-in/out function attaches cache_control when emitting an output batch. They share the freshness vocabulary, but cache different units of work. The complete Python worker example includes the class definition and registration.

VGI caching requires an explicit worker advertisement. By default, that includes ttl or expires; no_store overrides either. HTTP also permits heuristic caching in some circumstances, so VGI’s default is deliberately stricter. Memory limits, catalog settings, and eligibility checks can still make the client decline to cache a result.

The worker also chooses a reuse scope. catalog permits reuse across transactions within the calling catalog identity, while transaction restricts reuse to the requesting transaction. These database boundaries are distinct from HTTP’s public and private directives; they are part of deciding which calls may safely share an answer.

When the TTL runs out

Expiry means the freshness promise has ended, but the underlying data may still be unchanged. Refetching an entire rate table in that case repeats work unnecessarily, so a worker that supports revalidation can offer a cheaper check.

The worker can include an ETag, such as etag='"rates-v3"', to identify the result’s version, and set revalidatable=True to offer that check.

On a supported path, the client sends its stored validator back as if_none_match or if_modified_since, borrowing HTTP’s If-None-Match and If-Modified-Since conditions. The worker either returns fresh data or emits a zero-row batch with not_modified=True and a renewed lifetime. The latter is analogous to HTTP’s 304 Not Modified: retain the stored rows and update their expiry.

The worker must implement that check. Setting revalidatable=True does not compare versions automatically, and an ETag must change when the corresponding result changes. A check also costs a round trip, so below its configured payload-size threshold the DuckDB client refetches instead. Split scans currently refetch expired results too; setting a TTL of zero does not guarantee a conditional request on every call.

HTTP’s RFC 5861 inspired the stale_while_revalidate and stale_if_error fields, which describe serving stale data during a refresh or after a failure. The DuckDB implementation covered here parses those fields but does not yet use them to serve stale results.

What makes two calls the same?

A freshness window is useful only if the cached answer belongs to the request being made. A rate table for another date, a result filtered for another customer, or data returned under different credentials is not interchangeable just because it has not expired.

HTTP uses the request method and URI, together with headers named by Vary, to distinguish cached representations. The DuckDB VGI client needs a corresponding set of distinctions for function calls:

A concrete bug showed why this detail matters: a key that omitted the schema allowed two same-named functions in one catalog to share an entry. Including the schema made the key identify the function actually being called. Pushed-down filters need the same care, because an engine that delegates a filter to a worker may trust the returned rows without applying that filter again.

Identity matters just as much as the arguments. Two users asking for “my recent orders” must not receive each other’s results. Workers can receive caller identity through AuthContext; the client separates entries using the calling catalog and authentication fingerprint. Secret-dependent results also need a fingerprint of the secrets resolved at bind time, and become ineligible if that fingerprint is unavailable. Raw secret values do not enter the key. Separate agent processes and different users do not automatically share one cache. Dynamic filters and unseeded samples are excluded because they do not provide a stable request to match at lookup time.

Matching the key still relies on the function author’s promise that the output can be reused for that input and context. A function whose answer depends on an earlier batch, unkeyed external state, or a side effect cannot simply opt into per-value caching. Each tuple’s answer must be independent of the other tuples in its batch, and volatile functions are excluded from deduplication and per-value reuse.

Reusing part of an input

A finer-grained cache can help when only some inputs repeat, but the client must assemble cached and newly computed results into the right output. For an eligible correlated table-in/out call, it first checks whether the whole input chunk is cached. If that misses and per-value caching is enabled, it can gather the values it already has and send only the missing tuples to the worker. Scalar calls use the per-value tier without a whole-chunk cache.

Because workers already return Arrow record batches, the client can store complete batches as Arrow IPC streams. That format was expensive when our early implementation used a separate stream for every small value, so per-value results now share a columnar arena: common column storage that maps each input tuple to its output rows. A hit assembles rows from that storage instead of decoding a separate stream for every value.

Larger captures can spill to disk when disk storage is enabled and replay one batch at a time. A complete-scan entry is committed only after its producer finishes successfully, so an interrupted query cannot leave a partial answer available for reuse. These storage choices belong to the client; the worker supplies the freshness and reuse policy.

Try it with Yahoo Finance history

An agent researching a few companies might ask for average closing prices, then change its mind and look at trading volume. Both questions need the same daily price history. The Yahoo Finance VGI connector makes that history available to SQL, so we can try the same kind of reuse with a real data source.

Click Try it in your browser to run this example in Haybarn WASM. SQL runs in your browser; the hosted VGI worker fetches the data from Yahoo Finance. No API key or local Python process is needed. The first run downloads the engine.

LOAD httpfs;
ATTACH 'yfinance' AS yf (
    TYPE vgi,
    LOCATION 'https://vgi-yfinance.rusty-bb6.workers.dev'
);

CREATE OR REPLACE TEMP TABLE watchlist AS
SELECT * FROM (VALUES ('AAPL'), ('MSFT')) t(symbol);

-- First question: average closing prices over the past month.
SELECT w.symbol, round(avg(h.close), 2) AS average_close
FROM watchlist w,
     LATERAL yf.history(w.symbol, range := '1mo') h
GROUP BY w.symbol
ORDER BY w.symbol;

-- New question, same history: the busiest trading day.
SELECT w.symbol, max(h.volume) AS busiest_day_volume
FROM watchlist w,
     LATERAL yf.history(w.symbol, range := '1mo') h
GROUP BY w.symbol
ORDER BY w.symbol;

SELECT exchange_hits, exchange_misses
FROM vgi_result_cache_stats();

The history worker allows its response to be reused for 60 seconds. That is a short snapshot: today’s candle can still change, and older prices can be corrected. The first query fetches the candles for each symbol; the second can calculate trading volume from those same candles without asking the worker to fetch them again.

Here history() is a correlated table-in/out function: each symbol on the left produces several daily rows on the right. The LATERAL calls use the exchange cache, which is why the final result shows exchange_hits and exchange_misses. A fresh run should show hits from the second question. Changing the symbol or history arguments can require another fetch; after the minute expires, the next call fetches again.

The shell reports each query’s elapsed time. A cache hit confirms reuse, but the saving still depends on how much of the query was spent fetching data. Try another question over the same history, or another symbol, and compare what happens.

An agent revising its analysis should be able to use information it just fetched, as long as the service says that information is still good. VGI makes that agreement part of the function call. The worker guides cover how to add it in Python, TypeScript, Go, and Rust.