Result caching: the key is the query plus everything it reads that is not data

What must a result-cache key contain, and what caps its hit rate no matter how it is designed?

The naive model of a result cache is hash(SQL) -> rows. That model is wrong in a way that quietly returns incorrect answers. Deriving the correct key is most of the mechanism.

What the key must contain

A result depends on the query text, after normalization (making equivalent forms identical: whitespace, letter case, and where literals versus parameters appear). Here the first loss of λ_eff occurs. Literals written directly into the SQL fragment the key space by the number of distinct literal values. A dashboard that writes its date filter into the text has one cache entry per date, so its hit rate follows how often dates repeat, not how often queries repeat.

-- Fragmented: one entry per date literal
SELECT count(*) FROM page_views WHERE day = '2026-10-04';
SELECT count(*) FROM page_views WHERE day = '2026-10-05';

-- Parameterized: one normalized shape, parameter becomes part of the key explicitly
SELECT count(*) FROM page_views WHERE day = $1;

A result also depends on everything the query reads that is not stored data: the clock, the session's time zone, collation (sorting rules), search_path (which schema names resolve to), session variables, and the calling principal (the user or role). Any query containing now(), current_user, random() or a user-defined function of unknown behavior has a result that is not a function of the data alone.

This is why engines keep volatility classifications. Postgres marks functions IMMUTABLE, STABLE or VOLATILE, and this is exactly a cacheability lattice:

IMMUTABLE: same arguments always give the same result.
STABLE: constant within one statement's snapshot. It can be cached within one query and not across queries. This is the caching boundary.
VOLATILE: may change at any time; never cache.

The two standing questions land here

For every mechanism it is worth asking two questions: What does the system assume it cannot check? and Who pays when the assumption breaks? IMMUTABLE is an assertion by the function's author that the engine cannot verify. The system assumes that the function marked immutable really is. When that assumption breaks, the reader pays, silently, with a wrong answer that lasts as long as the entry does. And the mislabeled function is blamed last, because the symptom is data that looks stale, not code that looks wrong.

Security divides the hit rate

The second collapse of λ_eff is security. Under row-level security (RLS: the database filters rows by who is asking) or column masking, the result depends on the principal, so the key must include identity. Hit rate is divided by the number of distinct principals.

The escape is structural, not a tuning knob: cache below the security boundary (the scan, the fragment, the pre-policy aggregate) and apply the policy when reading. Notice what that is: an argument for lowering derivation distance that comes from an entirely non-performance constraint.

Invalidation: a clean solution, and a hard bound

The solution is to fingerprint the input versions into the key: (normalized query, principal, snapshot IDs of every input). A write to an input table then invalidates nothing. It just makes the old key unreachable, because the new query asks for a key containing the new snapshot id. This is the content-addressing of What makes data cacheable applied one level higher, and it converts staleness from a failure mode into an impossibility. The price is that unreachable entries become garbage. So the eviction policy becomes the garbage collector, the "immutable storage plus indirection plus collector" pattern, now inside the result cache.

def result_key(sql, params, principal, input_tables, catalog):
    snaps = tuple(catalog.current_snapshot_id(t) for t in sorted(input_tables))
    return hash((normalize(sql), params, principal, snaps))

The solution has a bound that no key design can escape, because it is not about invalidation. It is about arithmetic. A write to any input makes every cached result over that table unreachable at once. Therefore

RelationshipThe ceiling on a result cache →

h \leq P (recurrence within the inter-write interval)

This is the formula to compute before building anything. It explains the puzzling observation that result caching works beautifully in warehouses with batch loads and is useless in the same warehouse after someone switches one table to continuous ingestion. That was Ifeoma's first symptom.

An oddity: admission is decided after paying the cost

One structural oddity inverts the usual order: a result cache must decide admission after paying P. You do not know a result's size until you have computed it. Everywhere else here, admission comes before the cost. Here the cost is already spent before the decision exists. That forces a size cap and a post-hoc "do not cache results above X rows" rule, and it is why result caches cannot use the admission-filter designs that Making it fit shows to be strictly better.

In one sentence: the key is the query plus its whole environment plus its principal; fingerprint input versions to delete staleness; and h is capped by the chance of an identical repeat inside the inter-write interval.