Materialized views.
A materialized view saves the result of a query so you can read it later. Refresh it when the underlying data changes and you need a newer result. Refresh is explicit; a stored view does not update itself on every row write.
- Install the view queryDefine the SELECT and compute its initial saved result.
- Read that saved resultUse it until newer source data is needed.
- Request a refreshRe-run the full definition and replace the stored result.
- Verify the refreshed valuesCompare a changed source row with the new summary.
Three endpoints.
POST :name/installDefine the view + compute the initial materialization in one call.POST :name/refreshRe-run the definition and atomically overwrite the materialization.GET :nameRead the saved query result. It reflects the last successful refresh, not later changes to the source rows.
Install.
Prepare a line_items table with customer_id, qty and price, then add a few rows. The reference request below uses an explicit engine hostname, tenant variable T and bearer token; substitute the values for that interface. It creates a saved result named customer_revenue.
curl -X POST "https://acme.db.originchain.ai/v1/tenants/$T/sql/materialized_views/customer_revenue/install" \
-H "Authorization: Bearer $OC_TOKEN" \
-H "Content-Type: application/json" \
-d '{
"sql": "SELECT customer_id, SUM(qty * price) AS revenue FROM line_items GROUP BY customer_id"
}'Refresh.
The refresh atomically overwrites the existing materialization. Readers see either the previous version or the new one — never a half-written state.
curl -X POST "https://acme.db.originchain.ai/v1/tenants/$T/sql/materialized_views/customer_revenue/refresh" \
-H "Authorization: Bearer $OC_TOKEN"Read.
Read the saved customer totals. To check freshness, change one source row, read the view, refresh it and read again: only the refreshed result should include the change.
curl "https://acme.db.originchain.ai/v1/tenants/$T/sql/materialized_views/customer_revenue" \
-H "Authorization: Bearer $OC_TOKEN"Cost considerations.
Refresh re-runs the full definition. Cost scales with the underlying query; pick refresh cadence to match how stale your readers can tolerate. For hot rollups (BI dashboards, leaderboards) refresh on a fixed cron — for cold rollups, refresh on demand. Incremental refresh (delta-only) is on the roadmap; until then, a full refresh is the only path.
Next: SQL queries and transaction boundaries.