Cache results on the client

How a worker tells the DuckDB client that a result can be reused, so the next query answers without calling you at all. Worth doing when your worker is slower than the query around it — a remote API, a rate-limited service, an expensive model.

Prerequisites

• A working table or scalar function (see Function patterns).

The model

Caching is advertised, not requested. The worker attaches vgi.cache.* metadata to the first data batch it emits, and the client decides what to do with it. Nothing is cached unless you say so.

The vocabulary is deliberately HTTP’s (RFC 9111/9110), because the problem is the same one: a freshness lifetime, a reuse scope, validators for revalidating cheaply, and grace windows for serving stale.

cache/main.go

Copy Copied!

// Copyright 2025, 2026 Query Farm LLC - https://query.farm

// Command cache is the result-caching example for the vgi-go documentation.
//
// Caching is advertised, not requested: the worker attaches vgi.cache.* metadata
// to the FIRST data batch it emits, and the client (the DuckDB extension) decides
// what to do with it. Nothing is cached unless you say so.
//
// This worker exposes rates(), standing in for a slow upstream whose answer is
// worth reusing, and shows the whole vocabulary: a freshness lifetime, a
// validator plus Revalidatable so the client can ask "still good?" instead of
// paying for a recompute, and the 304-equivalent reply to such a request.
//
//	go build -o cacheworker .
//	# then, in a Haybarn shell:
//	ATTACH 'rates' (TYPE vgi, LOCATION './cacheworker');
//	SELECT * FROM rates.rates();   -- repeat calls inside the TTL never land here
package main

import (
	"context"
	"flag"
	"log"
	"os"

	"github.com/Query-farm/vgi-go/vgi"
	"github.com/Query-farm/vgi-rpc-go/vgirpc"
	"github.com/apache/arrow-go/v18/arrow"
	"github.com/apache/arrow-go/v18/arrow/array"
	"github.com/apache/arrow-go/v18/arrow/memory"
)

var ratesSchema = arrow.NewSchema([]arrow.Field{
	{Name: "currency", Type: arrow.BinaryTypes.String},
	{Name: "rate", Type: arrow.PrimitiveTypes.Float64},
}, nil)

// dataVersion stands in for whatever makes the upstream's answer change — a
// last-modified header, a version column, a content hash. It is the ONLY thing
// revalidation compares.
const dataVersion = `"rates-v3"`

var (
	currencies = []string{"EUR", "GBP", "JPY"}
	rates      = []float64{1.09, 1.27, 0.0067}
)

type ratesState struct {
	Done bool
}

// RatesFn emits the rate table once, advertising it as cacheable.
type RatesFn struct{}

var _ vgi.TypedTableFunc[ratesState] = (*RatesFn)(nil)

func (*RatesFn) Name() string { return "rates" }

func (*RatesFn) Metadata() vgi.FunctionMetadata {
	return vgi.FunctionMetadata{
		Description: "Exchange rates from a slow upstream, cacheable for 5 minutes",
		Stability:   vgi.StabilityConsistent,
	}
}

func (*RatesFn) ArgumentSpecs() []vgi.ArgSpec { return nil }

func (*RatesFn) OnBind(_ *vgi.BindParams) (*vgi.BindResponse, error) {
	return vgi.BindSchema(ratesSchema)
}

func (*RatesFn) NewState(_ *vgi.ProcessParams) (*ratesState, error) { return &ratesState{}, nil }

func (*RatesFn) Process(_ context.Context, params *vgi.ProcessParams, state *ratesState, out *vgirpc.OutputCollector) error {
	if state.Done {
		return out.Finish()
	}
	state.Done = true

	// Observable proof that the cache is working: this line appears once per
	// real invocation. Run the same SELECT several times in one session and you
	// should see it once. Logs go to stderr because stdout is the protocol.
	log.SetOutput(os.Stderr)
	log.Println("upstream fetch")

	// Conditional request: the client holds a stale-but-revalidatable copy and
	// is asking whether it may keep it. Both validators are nil on a normal call.
	if params.IfNoneMatch != nil && *params.IfNoneMatch == dataVersion {
		empty := array.NewRecordBatch(ratesSchema, emptyColumns(), 0)
		defer empty.Release()
		if err := vgi.Emit(out, empty, vgi.WithCacheControl(&vgi.CacheControl{
			Ttl:           vgi.Seconds(300),
			ETag:          dataVersion,
			Revalidatable: true,
			NotModified:   true, // 304: keep what you have
		})); err != nil {
			return err
		}
		return out.Finish()
	}

	batch := buildRates()
	defer batch.Release()

	// The metadata rides on the FIRST data batch. Attaching it to a later batch
	// has no effect — by then the client has decided how to treat the stream.
	if err := vgi.Emit(out, batch, vgi.WithCacheControl(&vgi.CacheControl{
		Ttl:                  vgi.Seconds(300), // reusable for 5 minutes without asking
		ETag:                 dataVersion,      // ...and after that, cheap to revalidate
		Revalidatable:        true,             // gates whether the client ever asks
		StaleWhileRevalidate: vgi.Seconds(60),  // serve stale while refreshing behind it
		StaleIfError:         vgi.Seconds(600), // serve stale rather than fail
	})); err != nil {
		return err
	}
	return out.Finish()
}

func emptyColumns() []arrow.Array {
	mem := memory.NewGoAllocator()
	names := array.NewStringBuilder(mem)
	defer names.Release()
	vals := array.NewFloat64Builder(mem)
	defer vals.Release()
	return []arrow.Array{names.NewArray(), vals.NewArray()}
}

func buildRates() arrow.RecordBatch {
	mem := memory.NewGoAllocator()
	names := array.NewStringBuilder(mem)
	defer names.Release()
	vals := array.NewFloat64Builder(mem)
	defer vals.Release()
	names.AppendValues(currencies, nil)
	vals.AppendValues(rates, nil)
	return array.NewRecordBatch(ratesSchema, []arrow.Array{names.NewArray(), vals.NewArray()}, int64(len(currencies)))
}

// NewRates returns the registration-ready function.
func NewRates() vgi.TableFunction { return vgi.AsTableFunction[ratesState](&RatesFn{}) }

func main() {
	httpMode := flag.Bool("http", false, "serve over HTTP instead of stdio")
	logFlags := vgi.RegisterLoggingFlags(flag.CommandLine)
	flag.Parse()
	if err := logFlags.Apply(); err != nil {
		log.Fatalf("logging flags: %v", err)
	}

	w := vgi.NewWorker(
		vgi.WithCatalogName("rates"),
		vgi.WithCatalogComment("Documentation example: an advertised-cacheable result"),
	)
	w.RegisterTable(NewRates())

	if *httpMode {
		if err := w.RunHttp("127.0.0.1:0"); err != nil {
			log.Fatal(err)
		}
		return
	}
	w.RunStdio()
}

Copy Copied!

ATTACH 'rates' (TYPE vgi, LOCATION './cacheworker');
SELECT * FROM rates.rates();   -- repeat calls inside the TTL never reach the worker

Running that SELECT four times in one session calls the worker’s Process once.

vgi.Emit with vgi.WithCacheControl is the whole mechanism:

Copy Copied!

err := vgi.Emit(out, batch, vgi.WithCacheControl(&vgi.CacheControl{
  Ttl: vgi.Seconds(300),
}))

First batch only

The metadata rides on the first batch of the result. Attaching it to a later batch has no effect — by then the client has already decided how to treat the stream.

Why vgi.Seconds? Optional durations are pointers

Ttl, StaleWhileRevalidate and StaleIfError are *int64 so that unset and zero are different things: vgi.Seconds(0) means “always revalidate”, while nil means the field was never advertised. vgi.Seconds is just a pointer helper.

Freshness

Presence of Ttl or Expires is what makes a result cacheable at all. NoStore overrides either.

Field	Meaning
Ttl	Lifetime in whole seconds, measured from full-result receipt. Skew-immune, and wins over Expires.
Expires	Absolute RFC 3339 UTC deadline. Lifetime is expires - now at receipt.
NoStore	Explicit “never cache”. Overrides any freshness key.
Scope	CacheScopeCatalog (the default when empty) reuses across transactions within the calling catalog identity; CacheScopeTransaction reuses only inside the same transaction.

Prefer Ttl unless your upstream genuinely publishes an absolute deadline — it doesn’t depend on the client’s clock agreeing with yours.

Revalidation

A TTL alone means the result is recomputed from scratch once it expires. If you can check freshness more cheaply than you can recompute, advertise a validator and set Revalidatable:

Copy Copied!

vgi.WithCacheControl(&vgi.CacheControl{
  Ttl:           vgi.Seconds(300),
  ETag:          dataVersion,  // strong validator
  Revalidatable: true,         // "ask me instead of recomputing"
})

Revalidatable is what gates whether the client ever sends a conditional request at all. When it does, the validators it holds arrive on the process params:

Copy Copied!

if params.IfNoneMatch != nil && *params.IfNoneMatch == dataVersion {
  // Nothing changed — 304-equivalent. Emit ZERO rows and say so.
  empty := array.NewRecordBatch(ratesSchema, emptyColumns(), 0)
  defer empty.Release()
  if err := vgi.Emit(out, empty, vgi.WithCacheControl(&vgi.CacheControl{
      Ttl:         vgi.Seconds(300),
      ETag:        dataVersion,
      NotModified: true,
  })); err != nil {
      return err
  }
  out.Finish()
  return nil
}

params.IfNoneMatch and params.IfModifiedSince are both nil on a normal call. Answering with NotModified on a zero-row batch tells the client its stored payload is still good; a non-empty batch would be treated as fresh data and replace it.

Use ETag when you have an opaque version token; LastModified (RFC 3339 UTC) is the weaker fallback when you only have a timestamp.

Serving stale

Two grace windows let the client answer immediately instead of blocking on you:

• StaleWhileRevalidate — seconds it may serve the stale result while revalidating in the background.
• StaleIfError — seconds it may serve the stale result if a revalidation RPC fails.

Both are the difference between a slow upstream being a latency problem and being an availability problem.

Caching a slice instead of the whole scan

Two opt-ins cache at a finer grain. Both are additive to the whole-scan cache, not replacements.

• PartitionScope — for a SINGLE_VALUE_PARTITIONS table function, also caches the result split by partition value, so a later =/IN-filtered scan reuses per-partition entries.
• PerValue — for an exchange-mode map (a scalar, or a blended table-in-out under a correlated LATERAL), memoizes each distinct input tuple’s output.

Leave PerValue off unless one call is genuinely expensive

A per-value serve costs a cache probe, a decode and an assembly step per distinct value. That only pays back when it is cheaper than calling you. Turn it on for model inference, geocoding, or a rate-limited remote fetch — not for arithmetic.

Checking that it worked

Caching is advertised, so a mistake is silent: everything still returns the right answer, just without the reuse. The extension exposes the counters directly — ask it rather than guessing.

Copy Copied!

SELECT hits, misses, inserts, entries, total_bytes FROM vgi_result_cache_stats();

Copy Copied!

┌──────┬────────┬─────────┬─────────┬─────────────┐
│ hits │ misses │ inserts │ entries │ total_bytes │
├──────┼────────┼─────────┼─────────┼─────────────┤
│    2 │      2 │       1 │       1 │         448 │
└──────┴────────┴─────────┴─────────┴─────────────┘

Run your query several times and watch hits climb while inserts stays put. If inserts is 0 the result was never considered cacheable — check that the metadata is on the first batch and that ttl or expires is actually set, since neither is optional for a result to be cached at all.

Function	What it gives you
vgi_result_cache_stats()	Counters: hits, misses, inserts, evictions, entries, bytes — plus separate exchange-mode and per-partition tallies.
vgi_result_cache()	One row per cached entry: catalog, function, key hash, scope, versions.
vgi_result_cache_flush()	Drop everything — the quickest way to get a clean measurement.
vgi_result_cache_reap()	Evict what has expired, without waiting for the reaper.

EXPLAIN ANALYZE also annotates the scan with Cache: hit (memory) or Cache: miss, which is often the fastest way to see what one particular query did.

Next steps

• The full field list → Cache control.
• The other way to make a scan cheap → Integrate with the optimizer.