Cost-based routing and result caching

When does routing to another engine pay, and why is hit rate the wrong objective for a cost cache?

Cost-based workload routing

Engines differ in the shape of their cost curve, not only its level. Some have a high fixed cost per query and a low marginal cost per byte. Others are the reverse. Query costs span orders of magnitude, so any single engine is a compromise at one end or the other. The break-even is where the two curves cross, and routing means sending small queries to the engine with low fixed cost and large queries to the scalable one.

The prerequisite is the same estimate that the governor needs. So routing is available exactly where pre-execution estimation is, and it inherits the same information constraint.

The price is rarely money. It is almost always semantics. Two engines mean two SQL dialects, two sets of rules for converting types, two caches, and the possibility of two different answers to the same question. That is not a performance regression. It is a correctness incident, with a political dimension. This is why the single compromise engine often wins in practice. This topic is contested: the savings are real at large scale, the operational cost is real at every scale, and which wins depends on team size and query-mix concentration, which vary more than the mechanics do.

Result caching as cost avoidance

The value of a cache hit depends on the meter, and the difference is total, not marginal.

Under scan pricing, a hit avoids the whole billable quantity: a 100% saving on that query.
Under occupancy pricing on a cluster you already pay for, a hit saves nothing unless it shortens the cluster's lifetime or delays a scale-up.

Same cache, same hit rate, a saving of everything or nothing, decided by a billing clause.

Hit rate is the wrong objective when costs are heavy-tailed

In ordinary caching, items cost about the same to recompute, so maximizing the hit rate is right. Here item costs span about five orders of magnitude, so hit rate and dollars saved separate completely.

Worked example (illustrative)

Out of 10,000 queries in a day, 500 are expensive dashboard queries at $10 each (total $5,000) and 9,500 are cheap lookups at $0.0002 each (total about $1.90).

Cache	Hits	Hit rate	Dollars saved
X: keeps cheap lookups	6,000 (cheap)	60%	6,000 × 0.0002 = $1.20
Y: keeps repeated dashboards	250 (expensive)	2.5%	250 × 10 = $2,500, about half of all spend

A cache with a 2.5% hit rate can eliminate half the spend. A cache with a 60% hit rate can save nothing measurable.

A cost-aware policy keeps the items with the highest (recompute cost × probability of reuse) ÷ size. (Cost-aware eviction policies were designed for exactly this situation.)

The unpriced term: invalidation

A result cache is correct only if you know whether its inputs changed. That requires change detection and lineage (a record of which data each result was built from). Without them, the fallback is a TTL (time to live: discard after a fixed time). Then the cache is trading correctness for money on a dial that nobody has labeled.