Group Caches

Group Cache Dimensions: Equality, Additive Ranges, and Snapshots

Implementation reference: 25 September 2026, commit b22387664, PR #999. Examples describe query shapes; actual eligibility and plan selection depend on semantic proofs, estimated costs, and cache state.

A group cache retains reusable aggregate state across SQL requests. It is separate from the query-plan cache and from caching complete HTTP responses. Cache eviction or changes to source dependencies can require reconstruction.

The three dimension modes describe how aggregate results at different parameter values relate to each other. They are not specific to time. Coordinates can represent numeric versions, positions, thresholds, or timestamps.

Dimension mode	Meaning	Reuse strategy	Typical query shape
Equality / point domain	An exact key or key tuple	Look up the aggregate state for that key	Aggregate over an equality group
Additive range domain	An interval of an ordered source dimension	Combine states for disjoint input intervals	Aggregate over selected facts
Snapshot domain	A parameter point at which contributions were evaluated	Read an exact point, or correct an existing state for a new point	Aggregate over parameter-dependent selected states

The new shape is called snapshot aggregation over a parameter domain, or more precisely, a snapshot domain with incremental contribution correction. As-of state aggregation is a useful name for the ordered-state-selection subclass, but the abstraction is not restricted to temporal queries.

1. Equality domains: One aggregate per exact key

SELECT COUNT(*) FROM orders WHERE customer_id = :customer;
SELECT SUM(amount) FROM orders WHERE customer_id = :customer;
SELECT COUNT(*) FROM orders;

The first two queries can reuse aggregate states indexed by customer ID. An aggregate without a correlated parameter, such as the final COUNT(*), is the limiting case with one group and an empty key tuple. Additional fixed filters belong to the computation and its cache identity.

This is particularly useful for correlated aggregates: many outer rows may request the same group. Once its aggregate state is available, the group need not be aggregated again for each outer row. Several equality keys can form a partition, such as tenant and product class.

Work avoided: Repeated processing of M records in a group becomes a lookup of existing aggregate state. Whether to prepare groups together or populate them on demand is a physical planning decision.

Potential speedup: No isolated A/B measurement of this mode is claimed here. If an expensive group aggregate is requested 100 times, its work can conceptually drop from 100 aggregations to one build plus 100 lookups. The speedup of that part approaches at most 100×, while other query work limits the end-to-end gain. Against a cheap index probe or a tiny group, the benefit may be small.

2. Additive range domains: Combine disjoint fact intervals

SELECT SUM(revenue)
FROM sales
WHERE coordinate >= :lo AND coordinate < :hi;

SELECT SUM(revenue)
FROM sales
WHERE tenant_id = :tenant
  AND coordinate >= :lo AND coordinate < :hi;

Each fact has a contribution that does not depend on the requested interval within this computation. The bounds determine which facts participate. The second query combines an equality partition with a range dimension.

For a sum over disjoint intervals:

R([a,c)) = R([a,b)) + R([b,c))

A cache can reuse and combine matching partial states. Missing coverage and boundary fragments still require work. Overlapping cells must not be counted twice. Inclusive/exclusive boundaries and SQL NULL semantics must be preserved.

Suitable query families: Interval sums, interval counts, cumulative fact aggregates, and repeated overlapping reporting windows. SUM and COUNT are straightforward examples. More generally, each aggregate needs a valid state-combination rule. Conceptually, an average requires a sum and a count, rather than an unweighted average of averages. DISTINCT needs its own state semantics.

Work avoided: Instead of reading M facts, a query may process K cached partial states and B uncovered facts.

Potential speedup: No isolated measured factor for this mode is claimed here. If those operations have comparable costs, M/(K+B) approximates the reduction in aggregation work. For example, replacing 1,000,000 facts with 100 cached cells plus 9,900 remaining facts means roughly 100× fewer processing units. This is an illustrative work estimate, not a measured latency speedup. Cold construction, fragmentation, and little overlap between requests can consume much of the benefit.

3. Snapshot domains: Aggregate state at a parameter point

Why an additive range is insufficient

SELECT SUM((
  SELECT h.quantity
  FROM entity_history h
  WHERE h.entity_id = e.id AND h.coordinate <= :x
  ORDER BY h.coordinate DESC
  LIMIT 1
))
FROM entity e;

This example requires an unambiguous ordered selection, for example UNIQUE(entity_id, coordinate).

It does not sum all history entries up to x. Each entity contributes only its selected state. If an entity changes from quantity 3 to quantity 5, the aggregate must increase by 2. Simply adding the new quantity 5 would be incorrect.

A snapshot retains aggregate state at an anchor point x₀, together with logical keys and contribution values needed for correction. The snapshot itself is a point. The interval between x₀ and x helps identify possible changes.

C(k,x) = contribution of entity k at parameter point x
S(x)   = sum of all C(k,x)
A      = a complete superset of potentially changed keys

S(x) = S(x₀) - sum(C(k,x₀), k in A) + sum(C(k,x), k in A)

A key is corrected once even if several dependency paths identify it. For every key outside A, its contribution must be proven unchanged. Original residual predicates are still evaluated for candidates: a candidate may turn out not to change anything.

Query structures recognized by the current implementation

Subject to the corresponding proof conditions, the implementation handles these building blocks:

• Ordered state selection per entity: Select an unambiguous state using a moving bound. Several scalar values may depend on that selection.
• Selected state plus threshold predicates: A contribution also depends on a selected property being less than, greater than, or equal to a coordinate. Boundary crossings without a new history row must be included in the change cover.
• Conditional SUM payloads: CASE/IF chooses a contribution using provable conditions. Change coverage includes selection, predicate, and payload dependencies.
• Fixed first/last properties: A coordinate-independent first or last value can use the same property group cache in both full construction and correction.
• Bounded EXISTS predicates: Relevant child rows crossing a bound can identify affected contributions through projected parent keys.
• Derived outer coordinates: A pure outer expression such as x-5 can define a dimension. The selected states for x and x-5 must remain distinct.
• Supported clamps and combinations: Certain MIN/MAX clamps on selection bounds, including clamps involving selected properties, COALESCE, and Boolean combinations have complete change-cover proofs.
• Repeated aggregate series: Multiple parameter points of the same formula can use retained anchors, which later SQL requests can reuse as well.

MIN/MAX in the clamp example means a scalar bound expression, not general support for retractable MIN/MAX aggregates.

Applications include versioned inventory, quantities of qualifying contracts, valid configurations, and other aggregates of “the selected state at coordinate x”. Eligibility follows the relational formula, not the application's business terminology.

What happens on a subsequent request

1. Same point, matching fixed inputs, unchanged dependencies: Read the retained aggregate state.
2. New point: Choose a valid anchor, identify potential changes, retract the old contributions, and add the new ones.
3. No relevant change between selection bounds: Proven equivalence of ordered selection cuts can permit reuse even when the numeric bound changes. A changed parameter value alone need not imply a fresh cache build.
4. Many changes: Full reconstruction may be cheaper and can produce a new anchor.
5. Source mutation or eviction: Reuse must be re-established. Moving an input parameter is distinct from modifying the underlying data.

For monotone interval covers, the current anchor selection compares affected-key work at the nearest valid anchor on each side. Numeric distance alone is not a work estimate. A farther anchor can require fewer corrections.

Current implementation limits

• The new retraction path currently targets SUM contributions. Runtime admission permits NULL and integral numeric values within a conservative exactness budget; the sum of absolute magnitudes must not exceed 2^52. Other values retain the ordinary SQL reducer. Arbitrary floating-point sums are not silently reordered.
• A count can mathematically be represented as a sum of 0/1 contributions. This does not imply that every COUNT spelling currently selects the snapshot path. Nor does the new path provide general support for COUNT DISTINCT, AVG, MIN/MAX, or arbitrary user-defined aggregates.
• The contribution proof currently requires a suitable single base driver with a single-column primary key. Arbitrarily multiplying joins and composite contribution identities are not generally covered.
• Each contribution proof chooses one moving axis; other inputs partition the cache. This is not yet general incremental correction across freely moving multidimensional surfaces.
• The current snapshot runtime restricts coordinates to suitable integral numeric values. The abstraction is broader than this implementation; support for arbitrary ordered types is not implied.
• Not every algebraically related formula has a proof. For example, a scaled inner selection coordinate is not automatically treated as an identity change domain.
• INSERT/UPDATE/DELETE, changes to classification tables, and table replacement must participate in dependency handling. An invalid snapshot may be discarded and rebuilt. Fully incremental maintenance for every source mutation is not implemented.
• Cost selection and memory budgets may favor direct execution even when a formula is eligible. Small or empty inputs should not pay for unnecessary preparation.

Potential speedup

Let N be the number of contributions in a full build and D the number of potentially changed contributions. A follow-up replaces much of the work on N contributions with change-cover/index work and evaluation of D contributions. With comparable per-contribution costs, N/D is a rough estimate for the reduction in that part of execution, not the complete HTTP latency.

For example, 100,000 entities with 100 change candidates mean roughly 1,000× fewer contributions to re-evaluate. Expensive candidate discovery, remaining scans elsewhere in the query, planning, and response handling reduce the actual speedup.

Across P requested points, a favorable snapshot plan replaces P full evaluations with one build and P-1 corrections. An exact repeat can be cheaper still. Initial construction remains real work.

Performance expectations and reproducible evaluation

There is no universal speedup factor for a dimension mode. The estimates above describe reductions in work under stated assumptions. They are not measured end-to-end latency guarantees.

A reproducible comparison should identify the exact source revisions, hardware, generated fixture, query parameters, and cache preparation. Measure one cold request plus a fixed number of follow-ups, include all requests in the total, and report phase timings separately. Moving-bound tests should cover both empty and nonempty change sets. Repeating an identical query tests exact-point reuse; it does not establish the cost of snapshot correction.

The public test suite provides neutral examples that can be inspected and executed without access to application or customer data:

• tests/performance/domain-snapshot.yaml: generated entities and versioned values; ordered state selection, selected-property boundaries, moving parameters, retained snapshots, anchor selection, and source-change correctness.
• tests/performance/group-range-cache.yaml: generated range-aggregation workloads with performance configuration.
• tests/planner/aggregates/group-range-cache.yaml: correctness and plan regressions for group/range reuse.

Use each test's declared setup and sampling configuration when reproducing it. Small correctness fixtures establish semantics, not production-scale speedups. A comparison intended to isolate one cache mode must control the other planner and runtime changes as well.

This page does not publish latency factors from private application workloads. It makes no independently measured speedup claim for an individual dimension mode.

Combining dimensions and choosing a plan

One report can use all three modes: equality partitions for tenants or categories, additive ranges for fact revenue, and snapshot domains for state-dependent quantities. Different metrics need not use the same mode. For example, the current implementation can keep categories as fixed partitions without computing every category together in one contribution vector.

Logical IR exposes parameter dependencies, unique selection, contribution identity, and a complete superset of potentially changed keys. Physical planning then decides whether direct execution or cache reuse is cheaper. The logical IR does not introduce physical scans or cache tables prematurely.

EXPLAIN IR and EXPLAIN REORDER help inspect the structure. EXPLAIN PHYSICAL exposes decisions such as contribution_domain, with domain_snapshot and direct_aggregate alternatives. EXPLAIN shows the generated Scheme plan. Actual latency and records visited still require execution measurements.

A snapshot cache is distinct from RecMap. RecMap accelerates physical row mappings within a query. Retained snapshots use logical keys and values; query-local physical row identities must not survive arbitrary shard rebuilds.

Implementation and test references

• PR #999: implementation changes.
• INVARIANTS.md: planner and group-cache contracts.
• queryplan-optimize.scm: logical contribution and dependency proofs.
• queryplan-physical-expr.scm: physical selection, snapshot admission, and correction.
• domain-snapshot.yaml: neutral correctness and performance cases.
• group-range-cache.yaml: group/range regression cases.