Result-set caching

When is caching a whole result worth it, and why does it depend on a cheap version token?

The economics are the simplest here. A hit costs a lookup. A miss costs the query plus a store. Cache if hit rate × query cost is greater than store cost plus invalidation cost. Storage is never the hard part.

The two hard parts are matching and invalidation.

Matching is the same problem as materialized-view rewriting. Exact-text matching is easy and gives a poor hit rate: whitespace, parameter differences and session settings all cause misses. Anything better is semantic caching. A cached result computed under a broader predicate can answer a narrower query by applying a residual filter. This is query containment, which is undecidable in general, so practical systems restrict it conservatively, and it fails silently. The point to keep: result caching and materialized-view rewriting are one mechanism, differing only in who decided to store the result. A human did it deliberately for a materialized view; the system did it automatically for a result cache. They share the same subsumption test, the same incompleteness and the same silent misses.

Invalidation is a topic of its own, but one aspect decides whether result caching is viable at all. Correctness requires knowing whether any input changed since the result was computed. Where a table carries a monotonic version (a snapshot ID or a table version counter), this is an O(1) comparison. Where it does not, you must track partitions or rows, and the tracking can cost more than the query. That is why aggressive result caching is easy in warehouses over versioned, immutable storage and awkward in databases that change rows in place.

This is a dividend worth counting. The indirection layer that immutability forces (the manifest, the snapshot pointer) also supplies a free change-detection token. Immutability was adopted for concurrency and reproducibility, and it paid out in cacheability. The same shape appears in commit protocols: the indirection forced by immutability is also what makes a single atomic pointer swap possible. The indirection keeps producing capabilities that nobody bought it for.

In one sentence: result caching is materialized-view rewriting with the system as author; its matching problem is undecidable containment, and its viability depends on whether the storage layer supplies a cheap version token.