Rollups and memory residence

Rollups are maintained serving objects for repeated aggregate answers. Memory residence keeps selected serving state hot while durable storage remains authoritative.

Rollup syntax

CREATE ROLLUP orders_by_status_hourly
ON app.orders
DIMENSIONS (tenant, status, bucket_hour_created_at)
MEASURES (
  count(*) AS order_count,
  sum(total_cents) AS total_cents
)
WHERE status IN ('open', 'paid', 'cancelled')
WITH residency = memory_first
 AND memory_residency_ttl = '10 minutes'
 AND protect_from_scans = true;

WAIT FOR ROLLUP orders_by_status_hourly QUERYABLE TIMEOUT '60 seconds';

SELECT tenant, status, bucket_start, order_count, total_cents
FROM ROLLUP orders_by_status_hourly
WHERE tenant = 't1'
  AND bucket_start >= 1700000000;

Table-level memory options

CREATE TABLE app.sessions (
  tenant text,
  session_id text,
  user_id text,
  expires_at bigint,
  state text,
  PRIMARY KEY (tenant, session_id)
) WITH (
  residency = memory_first,
  memory_residency_ttl = '15 minutes'
);

Option	Meaning
`residency = memory_first`	Prefer hot serving state where admitted.
`residency = standard`	Normal non-memory-first posture.
`memory_residency_ttl`	Resident state lifetime, not logical row TTL.
`max_memory`	Memory budget hint for an admitted policy.
`protect_from_scans`	Guardrail to prevent rollup/memory state from becoming scan permission.
`pinned`	Not accepted by current preview table-option gates.

Residence policies

CREATE RESIDENCE POLICY hot_session_rows
ON TABLE app.sessions FOR ROW KEY (tenant = 't1', session_id = 's1')
WITH residency = memory_first
 AND memory_residency_ttl = '15 minutes';

CREATE RESIDENCE POLICY hot_tenant_sessions
ON TABLE app.sessions FOR PARTITION (tenant = 't1')
WITH residency = memory_first
 AND max_memory = '128 MB';

CREATE RESIDENCE POLICY recent_tenant_sessions
ON TABLE app.sessions FOR RANGE (tenant = 't1' WHERE expires_at >= 1700000000 AND expires_at < 1700003600)
WITH residency = memory_first
 AND memory_residency_ttl = '5 minutes';

CREATE RESIDENCE POLICY hot_open_orders
ON QUERY SHAPE AS
SELECT order_id, created_at, total_cents
FROM app.orders
WHERE tenant = 't1'
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 50
WITH residency = memory_first
 AND memory_residency_ttl = '5 minutes';

CREATE RESIDENCE POLICY hot_order_rollup
ON ROLLUP orders_by_status_hourly
WITH residency = memory_first
 AND memory_residency_ttl = '10 minutes';

ALTER RESIDENCE POLICY hot_order_rollup
SET residency = memory_first, memory_residency_ttl = '20 minutes';

DROP RESIDENCE POLICY hot_order_rollup;

Memory residence must not be used to make unsafe queries safe. Model the access path, rollup, or bounded query first; then use memory residence for hot serving state.