Group Caches
Group Caches: Reuse Calculations Across Queries
A group cache remembers calculations over groups of database records. Once MemCP has worked out a group's total, a later query can reuse that work instead of reading and adding up the same records again.
For example, a dashboard might repeatedly ask for an order count, revenue over a selected interval, or the total quantity held by a set of accounts. These questions often share most of their work, even when the user changes a filter or moves an interval boundary. Group caches let MemCP reuse the parts that still apply.
The goal is to touch as few source records as possible to produce the answer. Sometimes the answer is already available. Sometimes it can be assembled from stored subtotals. Sometimes only a small set of contributions needs to be recalculated.
What does the cache remember?
Think of a group cache as an automatically managed table of reusable calculations. Its keys describe which records and parameter values a calculation belongs to; its values hold totals or the information needed to update those totals.
The cache survives the SQL request that created it. Compatible calculations in later requests can use it too. It remembers intermediate results, so reuse does not require the entire SQL query to be identical. A query-plan cache, by comparison, remembers how to execute a query; a group cache remembers work already done.
The planner chooses whether to use a group cache. Applications continue to issue SQL and do not have to create or refresh these tables themselves.
Three ways to reuse a calculation
The three dimension types describe how a calculation depends on its inputs. A dimension can be a customer ID, a price, a position, a revision number, or a timestamp. The mechanism is not specific to time.
| Dimension | Question | How the cache helps |
|---|---|---|
| Equality / point | What is the total for this customer? | Look up that customer's stored result. |
| Additive range | What is the total inside this interval? | Combine stored subtotals for disjoint parts of the interval. |
| Snapshot | What is the total when evaluated at this parameter value? | Reuse a stored result and correct the contributions that may differ. |
Equality: calculate a group once
SELECT SUM(amount) FROM orders WHERE customer_id = :customer;
After calculating the total for customer 42, MemCP can retain it under that key. Another request for customer 42 can read the result without adding up all of that customer's orders again.
The same idea applies to counts and other supported aggregates, to several keys together, and to aggregates used inside larger queries. A total over the whole table is simply a calculation with no varying group key.
This is useful when many outer rows or repeated requests ask for the same grouped calculation. The group and its filters must describe the same computation; unrelated totals are not interchangeable.
Additive ranges: assemble a total from smaller totals
SELECT SUM(revenue) FROM sales WHERE position >= :start AND position < :end;
Suppose the cache already knows the subtotals for positions [0, 100) and [100, 200). It can answer [0, 200) by adding those two numbers. It does not need to visit every sale again.
For a different interval, MemCP can reuse matching parts and calculate the missing parts. It splits coverage where necessary so overlapping requests do not cause records to be counted twice.
This works when a record's contribution is fixed and the interval only decides whether to include it. Examples include revenue over reporting windows, quantities within a position range, and cumulative counts. Each supported aggregate needs a valid way to combine its partial results.
Snapshots: update the answer when contributions change
Some questions cannot be answered by adding up all records in an interval. Consider an account's history:
| Revision | Recorded quantity |
|---|---|
| 10 | 3 |
| 20 | 5 |
At revision 10, the account contributes 3. At revision 20, it contributes 5. Its contribution to the total has increased by 2. Adding both history records would give the wrong answer.
A typical SQL calculation selects one state for each account:
SELECT SUM((
SELECT h.quantity
FROM account_history h
WHERE h.account_id = a.id AND h.revision <= :revision
ORDER BY h.revision DESC
LIMIT 1
))
FROM accounts a;
Here, each account has at most one history entry per revision.
A snapshot stores the total at a particular parameter value, together with the individual contributions needed to correct it. To answer at another value, MemCP finds the accounts whose contributions might differ and recalculates those accounts:
new total = stored total
- previous contributions of affected accounts
+ new contributions of affected accounts
If 100 out of 100,000 accounts might differ, the other 99,900 contributions can remain untouched. An exact repeat can reuse the stored total directly.
A snapshot belongs to a point. The interval between two points helps locate possible changes. This is why a snapshot is a third dimension type, rather than another name for an additive range.
Snapshot reuse also covers supported conditions on the selected state. An account may start or stop qualifying, or contribute a different amount. A threshold crossing can matter even without a new history entry. MemCP must account for every way the contribution could change before it can safely leave the other accounts untouched.
This makes the pattern useful for versioned quantities, qualifying contracts, configuration states, and series of totals at successive parameter values.
The dimensions can be combined
These are three dimensions of the same group-cache mechanism. A single cache table can combine equality, range, and snapshot components and hold several aggregate calculations.
For example, a report might ask:
- For tenant 7, consider accounts opened within a selected interval, and total their quantities as evaluated at revision 200.
The tenant is an equality key. The opening interval is an additive range if it only selects which accounts participate. The revision is a snapshot point that determines each account's contribution. The cache can reuse results within that tenant, combine disjoint account intervals, and correct contributions when the revision moves.
The planner checks that these roles really are independent enough to combine. A bound that also changes a record's contribution cannot simply be treated as an additive filter.
How does the cache become useful?
A cache has a construction cost. MemCP compares caching with direct execution; it does not need to build an expensive cache for every eligible query.
For candidates subject to deferred admission, it initially executes the calculation directly and records the work spent. Once enough work has accumulated to justify materialization, the shared cache path can take over. An already calculated scalar result can be retained without calculating it again. A snapshot needs additional contribution information, so preparing it involves more work than saving a single number.
After preparation:
- A repeated key or snapshot point can read retained results.
- An overlapping range can reuse existing partial results.
- A new snapshot point can reuse an anchor and correct affected contributions. The current implementation compares neighboring anchors on either side using the number of potentially affected records, rather than numerical distance alone.
- A large correction can trigger a fresh snapshot instead. Future requests can then reuse that new starting point.
Changing a query parameter is different from changing the underlying data. Inserts, updates, deletes, and schema changes must be reflected in dependency checks or invalidation. Reuse must also respect transaction visibility. Cached state can be discarded and rebuilt; it is not a durable replacement for the source data. Memory pressure can evict it as well.
How much faster can it be?
The gain comes from avoiding source-record processing. There is no fixed speedup for a dimension type.
| Example | Work avoided after preparation |
|---|---|
| Repeated total for a large customer group | Replace another aggregation over thousands of orders with a stored-result lookup. |
| Interval covered by 100 cached subtotals | Combine 100 results instead of reading, for example, a million sales. |
| 100 affected accounts out of 100,000 | Re-evaluate roughly 1,000 times fewer account contributions, plus the work needed to find them. |
| Series of nearby snapshot points | Replace repeated full evaluations with a build followed by corrections where possible. |
These examples describe reductions in work, not guaranteed latency factors. Finding affected records, preparing plans, executing other query parts, and returning results still take time. An exact repeat can be very cheap, while a moving parameter usually requires some additional work. Small inputs or constantly changing data may offer little benefit.
A useful performance comparison includes one cold request and a fixed number of follow-up requests. This includes construction costs and shows whether reuse pays off. Testing moving parameters is essential: repeating an identical request alone does not measure snapshot correction.
Which queries are covered today?
Equality grouping and supported additive range aggregates cover conventional grouped totals and repeated interval calculations. Snapshot recognition adds supported sums over uniquely identified records whose contribution depends on an ordered parameter, including ordered state selection and certain combinations of filters, conditional values, and nested lookups.
The snapshot correction path currently focuses on exact integer-valued sums within its numerical safety limits and a supported record identity. It is not a general incremental engine for every SQL aggregate or join. Arbitrary floating-point sums, distinct counts, and arbitrary combinations of moving dimensions are not automatically eligible. If safe reuse cannot be established, the query retains an ordinary execution plan.
Technical Details
Candidate selection. Before materializing a deferred cache, MemCP records the measured cost of direct execution in system_statistic.group_cache_candidates. Each entry has a canonical_name and an accumulated_ns counter. Compatible queries contribute to the same counter. The planner/runtime compares that accumulated work with the estimated additional cost of caching, allowing useful candidates to become caches without building every possible cache on first use.
Canonical names. Cache tables use names such as .grp:source:hash; aggregate columns use agg_hash. The table identity describes normalized sources, grouping dimensions, and filters, while the column identity describes the aggregate formula. SQL aliases and output labels do not define these identities. The unified cell layout additionally distinguishes the dimension types; individual keys, range bounds, and snapshot coordinates are stored as cell values rather than requiring a new table for each request.
Consequently, similarly structured queries can share a cache table, and matching aggregate formulas can share its calculated values—even when the queries use different aliases or request different parameter values. Several aggregates can also occupy separate columns of the same table. Reuse requires matching normalized semantics and valid source dependencies; merely looking similar is not sufficient.
For executable examples using generated data, see snapshot workloads and combined dimension tests. The unified implementation is described in PR #999.