Materialized views.

When a view beats a plain query.

A view is a cache with a manual invalidation button. It pays off when the read/write ratio is lopsided and a little staleness is acceptable.

Before you start.

Every example below runs against the shop.orders table from the quickstart. Register it first if you haven't - views are installed over schemas that already exist, and nothing in this TOML is view-specific.

namespace   = "shop"
table       = "orders"
primary_key = ["id"]

[[columns]]
name = "id"
ty   = "str"
required = true

[[columns]]
name = "customer"
ty   = "str"

[[columns]]
name = "amount_cents"
ty   = "i64"        # money in minor units - never f64

[[columns]]
name = "status"
ty   = "str"

[[columns]]
name = "notes"
ty   = "str"

[[columns]]
name = "placed_ms"
ty   = "u64"        # epoch milliseconds

# Makes the WHERE status = 'paid' inside the view cheap to re-run.
[[indexes]]
name    = "by_status"
columns = ["status"]

The query we will materialize is the "revenue per customer" aggregate - the kind of thing a dashboard asks for on every page load:

SELECT customer,
       COUNT(*)          AS orders,
       SUM(amount_cents) AS total_cents
  FROM shop.orders
 WHERE status = 'paid'
 GROUP BY customer

1. Install a view.

Install does three things in one call: it translates the SQL, runs the query once to produce the initial snapshot, and commits that snapshot in a single write-ahead-log frame. When the call returns 200, the view is already populated - there is no separate backfill step and no window where the view exists but is empty.

The body is { name, query, refresh_mode?, source_schema? }. Omitting refresh_mode gives you on_demand; source_schema is a hint the engine otherwise derives from the plan's first scan target.

curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql/materialized-views" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name":  "mv_paid_by_customer",
    "query": "SELECT customer, COUNT(*) AS orders, SUM(amount_cents) AS total_cents FROM shop.orders WHERE status = '\''paid'\'' GROUP BY customer",
    "refresh_mode": "on_demand"
  }'

// No SDK wrapper for materialized views yet - plain fetch.
const res = await fetch(
  `https://${process.env.OC_HOST}/v1/tenants/${process.env.OC_TENANT}/sql/materialized-views`,
  {
    method: "POST",
    headers: {
      "Authorization": `Bearer ${process.env.OC_TOKEN}`,
      "Content-Type":  "application/json",
    },
    body: JSON.stringify({
      name: "mv_paid_by_customer",
      query:
        "SELECT customer, COUNT(*) AS orders, SUM(amount_cents) AS total_cents " +
        "FROM shop.orders WHERE status = 'paid' GROUP BY customer",
      refresh_mode: "on_demand",
    }),
  },
);
const out = await res.json();
console.log(out.rows_materialized, "groups materialized");

# The SDK helper (db.sql.install_materialized_view) still sends the
# pre-release refresh_mode names, so call the endpoint directly.
import os, requests

BASE = f"https://{os.environ['OC_HOST']}/v1/tenants/{os.environ['OC_TENANT']}"
H    = {"Authorization": f"Bearer {os.environ['OC_TOKEN']}"}

r = requests.post(
    f"{BASE}/sql/materialized-views",
    headers=H,
    json={
        "name": "mv_paid_by_customer",
        "query": (
            "SELECT customer, COUNT(*) AS orders, SUM(amount_cents) AS total_cents "
            "FROM shop.orders WHERE status = 'paid' GROUP BY customer"
        ),
        "refresh_mode": "on_demand",
    },
)
r.raise_for_status()
print(r.json()["rows_materialized"], "groups materialized")

// No SDK wrapper for materialized views yet - net/http.
body, _ := json.Marshal(map[string]any{
    "name": "mv_paid_by_customer",
    "query": "SELECT customer, COUNT(*) AS orders, SUM(amount_cents) AS total_cents " +
        "FROM shop.orders WHERE status = 'paid' GROUP BY customer",
    "refresh_mode": "on_demand",
})
req, _ := http.NewRequestWithContext(ctx, "POST",
    "https://"+os.Getenv("OC_HOST")+"/v1/tenants/"+os.Getenv("OC_TENANT")+
        "/sql/materialized-views",
    bytes.NewReader(body))
req.Header.Set("Authorization", "Bearer "+os.Getenv("OC_TOKEN"))
req.Header.Set("Content-Type", "application/json")

resp, err := http.DefaultClient.Do(req)
if err != nil { /* handle */ }
defer resp.Body.Close()

var out struct {
    Name             string `json:"name"`
    RowsMaterialized int    `json:"rows_materialized"`
    BytesWritten     int    `json:"bytes_written"`
    RefreshTS        uint64 `json:"refresh_ts"`
}
json.NewDecoder(resp.Body).Decode(&out)
fmt.Println(out.RowsMaterialized, "groups materialized")

{
  "name":              "mv_paid_by_customer",
  "rows_materialized": 3,
  "bytes_written":     412,
  "refresh_ts":        1714478100000
}

rows_materialized is the number of result rows stored - here, one per customer group, not the 12,480 base rows that were scanned to produce them. refresh_ts is the millisecond timestamp of the snapshot; it is the only "how stale is this" signal the engine gives you, so log it.

2. Read the view.

A view is read by name through its own route - it is not addressable from SQL. You cannot write SELECT * FROM mv_paid_by_customer; the view is not a table and the planner does not know its name. Read it, then use the rows in your application.

curl "https://$OC_HOST/v1/tenants/$OC_TENANT/sql/materialized-views/mv_paid_by_customer" \
  -H "Authorization: Bearer $OC_TOKEN"

const res = await fetch(
  `https://${process.env.OC_HOST}/v1/tenants/${process.env.OC_TENANT}/sql/materialized-views/mv_paid_by_customer`,
  { headers: { "Authorization": `Bearer ${process.env.OC_TOKEN}` } },
);
const view = await res.json();
for (const row of view.rows) {
  console.log(row.customer, row.orders, row.total_cents);
}

view = db.sql.read_materialized_view("mv_paid_by_customer")
for row in view.rows:
    print(row["customer"], row["orders"], row["total_cents"])

req, _ := http.NewRequestWithContext(ctx, "GET",
    "https://"+os.Getenv("OC_HOST")+"/v1/tenants/"+os.Getenv("OC_TENANT")+
        "/sql/materialized-views/mv_paid_by_customer",
    nil)
req.Header.Set("Authorization", "Bearer "+os.Getenv("OC_TOKEN"))

resp, err := http.DefaultClient.Do(req)
if err != nil { /* handle */ }
defer resp.Body.Close()

var view struct {
    Name string           `json:"name"`
    Rows []map[string]any `json:"rows"`
}
json.NewDecoder(resp.Body).Decode(&view)
for _, row := range view.Rows {
    fmt.Println(row["customer"], row["orders"], row["total_cents"])
}

{
  "name": "mv_paid_by_customer",
  "rows": [
    { "customer": "01JTRX1H4Q9P0N2WMX0F5JZ001", "orders": 4, "total_cents": 51800 },
    { "customer": "01JTRX1H4Q9P0N2WMX0F5JZ002", "orders": 2, "total_cents": 18400 },
    { "customer": "01JTRX1H4Q9P0N2WMX0F5JZ003", "orders": 1, "total_cents":  9900 }
  ]
}

The row shape is exactly what the original SELECT projected, aliases included - orders and total_cents here come straight from the AS clauses. The whole snapshot comes back in one response; there is no pagination on this route, which is the practical ceiling on how many groups a view should have.

3. Refresh the view.

Under on_demand - the default and the mode to build against - the snapshot never changes on its own. Writes to shop.orders do not touch the view. It goes stale the instant the first write lands, and it stays exactly as stale as it was until you call refresh.

Refresh reloads the stored definition, re-translates the SQL against the current catalog, re-executes it, and atomically overwrites the snapshot. It is a full recompute, not a delta: cost scales with the base table, not with how much changed.

curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql/materialized-views/mv_paid_by_customer/refresh" \
  -H "Authorization: Bearer $OC_TOKEN"

const res = await fetch(
  `https://${process.env.OC_HOST}/v1/tenants/${process.env.OC_TENANT}/sql/materialized-views/mv_paid_by_customer/refresh`,
  {
    method:  "POST",
    headers: { "Authorization": `Bearer ${process.env.OC_TOKEN}` },
  },
);
const out = await res.json();
console.log(out.rows_materialized, "groups rebuilt at", out.refresh_ts);

out = db.sql.refresh_materialized_view("mv_paid_by_customer")
print(out.rows_materialized, "groups rebuilt at", out.refresh_ts)

req, _ := http.NewRequestWithContext(ctx, "POST",
    "https://"+os.Getenv("OC_HOST")+"/v1/tenants/"+os.Getenv("OC_TENANT")+
        "/sql/materialized-views/mv_paid_by_customer/refresh",
    nil)
req.Header.Set("Authorization", "Bearer "+os.Getenv("OC_TOKEN"))

resp, err := http.DefaultClient.Do(req)
if err != nil { /* handle */ }
defer resp.Body.Close()

var out struct {
    Name             string `json:"name"`
    RowsMaterialized int    `json:"rows_materialized"`
    RefreshTS        uint64 `json:"refresh_ts"`
}
json.NewDecoder(resp.Body).Decode(&out)
fmt.Println(out.RowsMaterialized, "groups rebuilt at", out.RefreshTS)

{
  "name":              "mv_paid_by_customer",
  "rows_materialized": 4,
  "bytes_written":     540,
  "refresh_ts":        1714481700000
}

Refresh modes, precisely.

refresh_mode takes exactly two values. Anything else is a hard error, not a fallback - a typo does not quietly become the default.

What "apply-time" actually means

When an incremental view is live, maintenance is not a background job and not a lagging follower. The engine updates the view's stored cells while holding the same store write lock the base commit takes, so the base row and the view move together: once a write returns, a read of the view already reflects it. There is no replication delay to reason about and no window where the two disagree.

Install closes the obvious race too. The initial fill and the switch that turns on live maintenance happen under one lock hold, so a write that lands in between cannot be counted twice or dropped - which would otherwise be a permanent miscount that only a full refresh could repair.

The trade is where the cost sits. on_demand makes writes free and pays the whole scan on refresh. incremental puts a small amount of work on the commit path of every write to the base table, and reads are always current. A write-heavy table with a rarely-read view is the wrong place for it.

Why an incremental install is usually refused

Incremental maintenance ships behind two independent preview gates - one for filter/projection views, one for aggregate views - and an install is only accepted if its own kind's gate is on. The filter/projection gate is on by default; the aggregate gate is off by default. So the revenue-per-customer view above is refused rather than silently maintained wrong:

curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql/materialized-views" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name":  "mv_paid_live",
    "query": "SELECT customer, SUM(amount_cents) AS total_cents FROM shop.orders WHERE status = '\''paid'\'' GROUP BY customer",
    "refresh_mode": "incremental"
  }'

HTTP/1.1 422 Unprocessable Entity

{
  "error": "incremental materialized views over a COUNT/SUM/AVG aggregate are a preview feature that is DISABLED on this server (OC_MV_AGG_PREVIEW); use refresh_mode=on_demand, or ask your operator to enable the preview"
}

HTTP/1.1 400 Bad Request

{
  "error": "incremental materialized views (preview) support only a pure filter/projection over a single table - no MIN/MAX, DISTINCT, join, set-op, subquery, or limit; use refresh_mode=on_demand for this query"
}

The distinction is worth reading closely. A 422 means "this shape is supported but switched off here" and names the gate. A 400 means "this shape is not supported by incremental at all". Both tell you to use on_demand, and both are honest refusals - the engine will not accept an incremental view it cannot keep correct.

One safety property worth knowing: if an incremental view ever stops being maintained - the gate is turned off, or a rebuild fails after a restart - reads do not serve its stale cells. The engine recomputes from live base rows for that read instead, so a read is never wrong, only more expensive than you expected. A successful refresh, or a restart with the gate back on, restores fast serving.

Limits and gotchas.

• A view cannot be dropped. There is no DELETE route. Installing over an existing name returns 409, so a name is claimed permanently and a view's query can never be edited. Version the name if you expect the definition to change - mv_paid_by_customer_v2 - rather than hoping to replace it.
• A view cannot be listed. No index route exists, and reads are by exact name. Keep view names in source control; a forgotten name is unreachable and unremovable.
• Views are invisible to SQL. They cannot appear in a FROM clause, cannot be joined, and cannot be nested inside another view. CREATE MATERIALIZED VIEW is rejected by the translator by design - the HTTP route is the only way in.
• The snapshot is returned whole, and it is capped at 16 MiB. There is no LIMIT, offset, or filter on the read route, and an on_demand snapshot that encodes larger than 16 MiB is rejected with a 400 at install and at refresh - so a view can outgrow its cap months after it was created. Group by something with bounded cardinality; customer is fine, a raw millisecond timestamp is not. Nothing caps group count directly, so an unbounded GROUP BY is your problem to avoid.
• A view cannot read another view. Installing a view whose query references an existing view's name is rejected. Views do not compose.
• Single-shard only. On a sharded instance, a view whose base tables do not live on the same shard as the view itself returns 501 at install and at refresh. Cross-shard views are not in this release.
• Dropping the base table does not clean up an on_demand view. The snapshot survives and keeps serving its last contents, the name stays claimed, and with no drop route there is no way to remove either. Drop the view's data source only when you have accepted that.
• Incremental SUM and AVG can drift in the low bits. Floating-point addition is not associative, so a value folded write-by-write can differ slightly from the same value computed by one scan. COUNT is exact. A refresh rebuilds from scratch and reconciles the difference - schedule one periodically if you display these figures to the decimal.
• Refresh is a full recompute. Changing one row costs the same refresh as changing a million. Refreshing an on_demand view in a per-write hook is the anti-pattern this design invites - batch it.
• Names are unique per view, not per query. Nothing stops two views from materializing the same SELECT under different names, and nothing deduplicates the work. Both will need refreshing.
• The Python SDK helper is behind the engine. db.sql.install_materialized_view() still sends the pre-release mode names manual / on_write, which the engine no longer recognises. Call the install route directly, as the Python tab above does. read_materialized_view() and refresh_materialized_view() take no mode and work as documented.
• TypeScript and Go have no view helpers. Both tabs above use the raw HTTP client. The routes are stable; the wrappers are not shipped.

Status codes you will actually hit