Horizon Materialization

QueryStorage PolicyMaterialized Views

Opt-in Implemented · off by default — enable with enable_avmm = true · set enable_avmm in the install config (renders --enable-avmm) to run the policy tier-migration daemon; predictive materialisation works without it

What it is

Most caches evict by recency: whichever page was read least recently gets dropped first. That heuristic ignores the structure of your business. A 35-day-old log entry is rarely useful even if it was queried two minutes ago; today’s open orders matter even if no one has touched them yet.

Horizon policies let you express that logic directly. You write CREATE HORIZON POLICY rules — “events older than 30 days move to NVMe,” “the open-orders table stays pinned in RAM” — and a background evaluator promotes, demotes, or pins data on idle cycles as the rules dictate. Separately, the engine notices which aggregations you ask for repeatedly and materialises them ahead of time so the next query is instant.

Why it matters

Horizon policies replace dozens of bespoke retention scripts and eviction tunables with a single declarative source of truth. The predictive layer turns your workload itself into the tuning signal — you don’t have to predict which aggregates matter, you just run them and the system catches on.

• Policy-driven tiering — RAM, NVMe, and archive selection is one SQL statement.
• Background eviction — demotions happen on idle CPU, never on the query path.
• Predictive materialisation — repeating query shapes get pre-built without DBA intervention.

flowchart LR
  classDef hot  fill:#1f2640,stroke:#4ade80,color:#4ade80
  classDef warm fill:#1f2640,stroke:#00d4ff,color:#e8eaf0
  classDef cold fill:#1f2640,stroke:#7c5cff,color:#e8eaf0
  classDef pol  fill:#1f2640,stroke:#ffaa55,color:#ffaa55

  POL[Horizon policy<br/>declarative DDL]:::pol
  POL --> EVAL[Background evaluator]:::pol
  EVAL --> H[RAM · Luminous]:::hot
  EVAL --> W[NVMe · Shadow]:::warm
  EVAL --> C[Archive · Object Store]:::cold

How you use it

Declare a horizon policy with familiar SQL. A policy attaches to one or more tables and can combine temporal, conditional, and cardinality rules.

-- Demote events older than 30 days to NVMe; older than a year to archive.
CREATE HORIZON POLICY log_retention
  ON events
  (
    WHEN timestamp < NOW() - INTERVAL '30 DAYS'
      THEN SHADOW L3,
    WHEN timestamp < NOW() - INTERVAL '365 DAYS'
      THEN ARCHIVE
  );

-- Force a small hot table to live entirely in RAM.
CREATE HORIZON POLICY orders_pinned
  ON open_orders
  (ALWAYS THEN LUMINOUS);

-- Watch the engine work:
SHOW HORIZON STATUS;

The predictive materialisation layer needs no syntax at all: keep running your daily reports and the engine starts pre-computing them. You can also CREATE MATERIALIZED VIEW explicitly when you want a particular shape guaranteed.

Incremental (reactive) materialized views

Beta Works · surface still evolving · On by default; supported shape is a single-table GROUP BY over SUM/COUNT/MIN/MAX/AVG

A plain CREATE MATERIALIZED VIEW is a snapshot: it holds the query result until you run REFRESH, and a refresh recomputes the whole thing. For a rollup you read constantly and want always-current, add the INCREMENTAL keyword:

CREATE INCREMENTAL MATERIALIZED VIEW sales_by_status AS
  SELECT status, COUNT(*) AS orders, SUM(amount) AS total
  FROM   transactions
  GROUP  BY status;

-- Always live — no REFRESH needed:
SELECT * FROM sales_by_status;

An incremental view is auto-maintained: every write to a base table in its definition updates only the affected group of the view, in place — never a full recompute. Insert one PENDING order and only the PENDING row changes; the other groups are untouched. Reads are served straight from the per-group aggregate (a hidden covering mirror — see Advanced Indexing), so SELECT … FROM the_view is a lookup, not a scan.

• The keyword is the contract. INCREMENTAL means the view is bound to commit of every participating base table — there is no ON COMMIT clause and no refresh schedule to manage.
• REFRESH is a no-op on an incremental view (it’s already current), and DROP tears down the hidden mirror with it.
• Fails loudly, never silently. If the defining query is a shape the engine can’t maintain incrementally, CREATE is rejected with the reason — it will not quietly fall back to a stale snapshot. Use a plain MATERIALIZED VIEW (on-demand) for those.

Supported shape (Phase 1): a single base table, a GROUP BY over plain columns, and the distributive/algebraic aggregates SUM, COUNT, MIN, MAX, AVG. Joins, HAVING, DISTINCT, COUNT(DISTINCT), percentiles, sub-queries, and a WHERE in the definition are rejected at CREATE (they need a plain materialized view instead).

Key concepts

Term	What it means
Luminous (RAM)	The fastest tier — data is resident and served at memory bandwidth.
Shadow (NVMe)	Local fast-disk storage, paged in on demand.
Archive	Object-store backing for rarely-touched data; cheapest tier, highest latency.
Predictive materialisation	Aggregates the engine builds in the background for query shapes it expects again.

Notes & caveats

Tip

Combine a horizon policy with cross-tier execution and your hot/cold split becomes transparent to applications: occasionally-touched cold data is paged in automatically on demand, with no code change on the client side.

Heads up

Promotion from archive back to a warmer tier costs real time — it’s bound by object-store bandwidth. If you expect a cold table to become hot suddenly (a quarter-end report, say), schedule an explicit RESTORE ahead of time rather than relying on first-access promotion.

Horizon Policy Syntax →

The exact CREATE HORIZON POLICY grammar — conditions, actions, and tiers.

Native Multidimensional Pivot →

Hot rotations the materialisation layer caches automatically.

Advanced Indexing (PTI) →

Exact pre-computed aggregates that answer covered GROUP BY queries without scanning the table.

Storage Overview →

The tiers data moves between and how they're laid out.

Configuration & Availability →

Feature maturity and defaults.