Temporary Computed Columns

Computed Columns, Group Caches, and ORC

Verified against commit c42e19eba on 28 August 2026. These are optimizer-managed physical structures. Applications normally write SQL; MemCP analyzes the plan, creates suitable caches, and maintains their validity automatically.

MemCP reuses expensive expressions and relational stages without copying complete result rows into a generic temporary table. It can attach one computed value to an existing base row, keep a narrow table of grouping keys and aggregates, or maintain an order-dependent column. The surrounding base columns remain where they already are.

This matters for queries that join or filter a million rows, sort by one derived value, and finally return only 100 rows. A conventional wide temporary result may copy every projected column before ORDER BY ... LIMIT. MemCP instead tries to materialize only the value, key domain, record set, or ordering dependency needed by the next physical stage.

The three reusable structures

Structure	Logical meaning	Stored shape	Typical use
Ordinary computed column	One derived value per live row of an existing table	A lazy StorageComputeProxy backed by compressed main values, sparse repaired values, and a validity mask	Repeated scalar expressions, lookup results, JSON paths, correlated scalar probes, computed ordering
Group cache/keytable	One row per distinct grouping/domain key plus computed aggregate columns	A narrow internal ENGINE=cache table with a unique key	Reused GROUP BY, correlated aggregates, prepared membership/group domains
ORC (ordered-reduce column)	One value whose computation depends on partition/order predecessors	An ordered computed proxy with per-row validity and dependency-range repair	Running totals, rank/row-number-like work, reusable window/order calculations

The SQL plan cache is different: it stores compiled plan formulas, not computed rows. A query may reuse a compiled formula without having a warm group cache, or reuse a computed cache after a newly compiled query recognizes the same canonical definition.

From SQL expression to canonical cache

MemCP does not name reusable structures after a query's disposable aliases. During logical planning and physical lowering it analyzes the expression and constructs a canonical identity from the data the structure represents:

• table aliases are rewritten to stable source roles, while two roles in a self-join remain distinct;
• column references include physical schema, relation, and column identity;
• grouping keys, source graph, filters, aggregate recipe, partition/order definition, and relevant bounds become part of the identity;
• SELECT aliases, formatting, and unrelated output columns do not create a second cache;
• stable structural hashes produce names such as internal .grp:..., agg_..., and __orc_... objects.

Consequently, two queries can share a computed aggregate even when their SQL aliases or final projections differ. A second aggregate over the same group domain can add another narrow computed column to the existing keytable instead of cloning the entire intermediate relation. Conversely, a different filter, join role, ordering recipe, or semantic dependency receives a different identity rather than incorrectly sharing values.

Preparation filters are deliberately absent from a computed column's logical identity. They only say which values are likely to be consumed soon and should be warmed eagerly. Every other live row still has a well-defined value that can be computed later.

Automatic analysis of the generated Scheme code

The planner emits a createcolumn call containing the computation lambda and its direct input columns. This is not the end of dependency discovery. The storage engine walks the resulting Scheme AST/procedure to determine what the computation reads and how source changes can reach the cached value.

The analyzer:

1. removes source-location wrappers and accepts both unresolved symbols and already-resolved builtin procedures;
2. descends through lambdas and nested physical expressions;
3. recognizes scan, scan_order, scalar_scan, and scalar_scan_order calls;
4. also examines a scan's table expression, because a dynamic physical source can itself contain dependency-producing scans;
5. resolves literal table handles or canonical table expressions to schema and relation;
6. reads filter and map column lists from both quoted and constructed-list forms;
7. records every source column used by the filter or mapper;
8. inspects conjunctions of equality predicates and maps a source scan parameter back to the outer computed-column input it equals;
9. follows source columns that are themselves computed and expands them transitively to their physical input, map, partition, and sort columns;
10. extracts session-variable dependencies and creates session-specific cache variants where a value must not leak between users or sessions.

For example, a generated lookup conceptually shaped like this:

<syntaxhighlight lang="scheme"> (lambda (customer_id) (scan tx (table "shop" "payments") '("customer_id") (lambda (payment_customer) (equal? payment_customer (outer customer_id))) '("amount") (lambda (amount) amount) + 0)) </syntaxhighlight>

tells the storage analyzer that payments.customer_id is the reverse lookup key for the target row's customer_id, and that payments.amount is relevant to the value. An UPDATE of an unrelated payments.note column therefore need not invalidate the cache.

The analysis is deliberately conservative. If a scan or reverse key relationship cannot be proven from the generated code, MemCP does not guess. It generates complete-column invalidation so the next read recomputes correct values. More analyzable code improves maintenance precision; opaque code must remain correct.

Automatic generation of maintenance triggers

When the computed-column signature is installed or changes, createcolumn calls the storage engine's trigger generator. It registers hidden system triggers on every discovered source table. Registration is idempotent, so preparing the same canonical cache again updates/reuses its metadata instead of accumulating duplicate triggers.

Analyzed shape	Generated reaction to source INSERT/UPDATE/DELETE
Direct row-local inputs	The corresponding row value is marked stale or repaired through the computed proxy.
Lookup with proven equality key	Scan only target cache rows whose key equals the source row's OLD and/or NEW key, then invalidate that exact subset.
Additive aggregate (+, neutral zero, analyzable map without nested scans)	Apply the mapped delta with $increment:<column>; UPDATE subtracts OLD and adds NEW when the group key stays stable.
Additive UPDATE whose group key changes	Invalidate safely because old and new keytable membership may differ.
COUNT-style constant-one helper	Prefer robust invalidation, because the value also controls empty/non-empty group visibility.
ORC with known partition and sort key	Invalidate the affected partition from the OLD/NEW sort position onward.
Opaque or unsupported dependency	Invalidate the complete computed column.

The generated triggers run after INSERT, UPDATE, and DELETE. UPDATE triggers compare only columns proven relevant by the analysis and skip maintenance if none changed. They use the trigger row dictionaries OLD and NEW to find both sides of a key-changing update.

Selective invalidation is itself batched. The internal $invalidate:<column> callback collects matching record IDs during the maintenance scan; invalidation is applied after scan locks are released. Incremental changes are similarly accumulated per proxy and record ID before $increment:<column> updates cached values. This avoids nested lock acquisition in the inner scan loop.

System maintenance triggers are hidden from ordinary SHOW TRIGGERS; they are implementation-owned rather than user DDL. User triggers and the computed-cache trigger graph still share the storage engine's defined timing and locking machinery.

Dependency chains and invalidation waves

A cached column can depend on another computed cache. The storage engine registers AfterInvalidate edges in addition to source DML triggers. Invalidating the lower cache synchronously propagates to its dependants.

One invalidation wave carries a query/goroutine-local visited set keyed by target table and column. This makes repeated edges idempotent and prevents a malformed cycle from recursing forever without introducing a global invalidation lock. Selective invalidation is currently propagated conservatively at column level when a downstream reverse mapping cannot be preserved safely.

Lifecycle cleanup

The generator also owns cleanup:

• dropping an internal target cache removes the triggers it installed on source tables;
• dropping a source table can drop dependent dot-prefixed helper tables so a later table with the same name cannot inherit stale data;
• group-key maintenance reacts to source INSERT, UPDATE, and DELETE;
• dropping the base table or a grouping column removes its dependent keytable;
• trigger target leases pin a cache while maintenance is using it, preventing concurrent eviction from invalidating a live pointer.

These lifecycle triggers must only remove the helper objects they explicitly own. They are not general garbage collection and must never delete unrelated user data.

Race-free initial construction

Installing triggers after filling a cache would leave a lost-update window: a source write between the snapshot and trigger registration would never invalidate the new value. initialize_cache_table closes that race.

It resolves and sorts all source tables into a deterministic lock order, blocks source mutations, registers maintenance, fills the canonical cache from a consistent source view, optionally finalizes/rebuilds it, and only then releases the locks. Concurrent attempts to initialize the same generation wait for the shared result rather than performing duplicate fills.

Internal keytables use ENGINE=cache and can store this initializer as a closed oninit procedure. After restart or eviction, the first idempotent creation guard rebuilds the empty generation before exposing it to consumers. See Persistency and Performance Guarantees for why cache data is reconstructible rather than durable.

Lazy values, eager warming, and repair

An ordinary computed column is a complete logical column even when few physical values exist:

• Compress can eagerly calculate all rows when the plan expects broad consumption;
• CompressFiltered warms only rows selected by a preparation filter;
• reading a missing or invalid ordinary value calculates that row from its current inputs and stores the result;
• valid compressed main values use the normal bulk column-reader fast path;
• repeated createcolumn calls for the same signature preserve valid cached values and repair only missing/dirty state;
• a materially different semantic formula receives a different canonical identity; reissuing the same signature preserves its valid cache. ORC metadata changes explicitly invalidate the affected ordered proxies.

For broad selective invalidations, each proxy compares a short measured repair sample with its last full recomputation cost. If point repair is predicted to cost more, it marks the whole column dirty and lets the next consumer perform one coherent rebuild. This adapts to the actual computor rather than relying on one global row-count threshold.

The historical documentation said computations become parallel above 60,000 rows. That threshold is no longer the public execution contract. Current preparation fans work out over active shards, while planner cost, shard count, input size, and the maintenance/recompute path determine useful parallelism. Large independent shards can compute concurrently; a tiny table or a single ordered dependency cannot gain speed merely from crossing a fixed row number.

GROUP BY through narrow computed relations

A reusable group cache conceptually follows four steps:

1. create a canonical internal table containing a UNIQUE key over all grouping columns;
2. populate the distinct grouping keys;
3. add one computed column for each aggregate or derived ordering value;
4. scan the narrow group relation and join/project base values only where the final result needs them.

<syntaxhighlight lang="sql"> CREATE TABLE t (a TEXT, b INT); INSERT INTO t VALUES ('foo', 1), ('bar', 2), ('foo', 3);

SELECT a, SUM(b) AS total FROM t GROUP BY a; -- bar | 2 -- foo | 4 </syntaxhighlight>

The keytable cleanup trigger inserts a missing key idempotently, removes a key after the last source row leaves the group, and handles OLD and NEW keys on UPDATE. It runs at priority 90, before aggregate invalidation at priority 100, so key membership is current when a value is repaired.

GROUP BY syntax does not force a cache. The physical lowerer compares a one-pass fused aggregation with keytable construction, expected probes/reuse, maintenance cost, memory, and available alternative carriers. Query-local or UNION-shaped groups may instead be filled in one batch, while reusable base-table groups can use storage-managed computed columns.

Different surrounding WHERE ranges can reuse the same row-local computed expression because the preparation predicate does not define the column. A group cache, however, includes the grouping domain and semantic source filter in its canonical identity: two genuinely different group populations must not share one aggregate value.

Order-dependent computed columns (ORC)

An ORC is not pointwise. A running total at row 500 depends on preceding rows in its partition, so changing row 100 may invalidate a suffix rather than just row 100.

The ORC definition records sort columns/directions, the number of leading partition columns, map columns/function, reducer, and neutral value. Generated triggers:

• use equality bounds for the partition prefix;
• invalidate a range beginning at the changed OLD or NEW sort key;
• leave unrelated partitions valid;
• fall back to complete invalidation when a cross-table dependency cannot be mapped selectively;
• compare cumulative selective-invalidation cost with the last suffix/full recomputation and choose complete invalidation when that is cheaper.

On the next read, the ordered proxy repairs the dependency range under the table's ORC recomputation guard. Concurrent readers do not observe the internal “invalid” sentinel used by the reducer. If one ordered scan can compute a window directly, the planner may fuse it instead of creating an ORC at all.

Cost, eviction, and observability

The physical lowerer compares:

• direct scalar/index probes;
• a fused one-pass scan or aggregate;
• a RecSet carrier;
• a canonical computed column;
• an FK-backed/group cache;
• an ORC;
• a narrow temporary relation.

Inputs include build work, expected reuse, source and target cardinality, maintenance/invalidation work, memory, ordering, LIMIT, and available indexes. EXPLAIN PHYSICAL shows the selected physical family for supported decisions; it is better evidence than assuming repeated SQL text always builds a cache.

Temporary computed columns and keytables are accounted by the global memory manager. Access refreshes their lease/last-used state. Because they are reconstructible, the manager can evict them under pressure; a later query recreates or lazily repairs them. Base data remains authoritative throughout.

Correctness boundaries

• A preparation filter changes warming, never the logical domain or NULL value of a computed column.
• A cached value must never outlive a source change without an invalidation/maintenance edge.
• Opaque code requires conservative invalidation; absence of a recognized equality is not evidence of independence.
• Session-dependent formulas require isolated variants and must not reuse one user's value for another session.
• ORC invalidation follows ordering dependencies and cannot be replaced by unrelated point repair.
• Internal trigger generation must be complete before source mutations are unblocked.
• Cache eviction and source-table DDL must remove their owned trigger graph without touching user data.

For exact low-level signatures see Storage. Related architecture is documented in Query Planner and Physical Lowering, Triggers, Columnar Storage, Memory Management and Eviction, RecSets, and Parallel Computing.