12. Materialized views and caches
og-core keeps two kinds of computed rows. They are independent and can both serve one request.
| Materialized view cache | Result cache | |
|---|---|---|
| What | The rows of a materialized view, per scope | The rows of a repeated read request |
| Where | Collections in the store (_cache.v<key>s<0|1>), listed by _cache | Memory of ogd |
| Lifetime | Until what the view reads changes, or the view is redefined | Until a read collection changes, or one minute without a reader |
| Size | No cap of its own: it is data, stored like any collection | OGD_MEMORY_LIMIT profile: 128 MiB by default, at most 1/16 of the managed memory; one entry at most a quarter of it |
| Survives a restart | The rows stay on disk; they are computed again on the first read after it | No |
| Switch | materialized in the definition | OGD_RESULT_CACHE=off |
12.1 Materialized views
define view sales.by_month | materialized | exec
| on view sales.paid | group | by month, region | sum total | end
| end
| end
The first read in a scope computes the rows into a cache collection; later reads in that scope read it, as fast as reading a small collection. A scope is the reading Place and App instance, plus the access filters of the reader when its Place access filters rows (such a reader gets rows of its own, computed under its filters).
The rows are computed again when, since they were computed:
- a collection the view reads changed for that Place: a write of the Place, or a write og-core cannot attribute
to one Place (an unscoped write, a
load, a direct storage write), which counts for every Place. A write in another Place leaves this Place’s cache alone; - a view it reads (at any depth) was redefined, restored or undefined: the cache records the name, version and Place of each view it reads, and the collections it watches are those of the views as they are defined now;
- the view itself got a new version: its cache key holds its version;
ogdrestarted (what it knew of the writes is not kept).
They are computed into the scope’s other cache collection (slots 0 and 1), so a reader still reading the previous rows is not cut short.
Automatic refresh. When a view is redefined (define view, a restore, undefine view),
ogd hands it to a background worker that computes again, in every scope a cache already exists for, the
materialized views reading it (and the view itself). The next reader finds the rows ready. A scope filtered by a
Place access is left to its next read.
Clean-up. The rows of an earlier version of a view in the same scope are dropped once the new ones are in; the cache of an undefined view is removed by the refresh.
Rules
- A materialized view takes no parameter: its body binds what it needs with
let. Filters written afteron viewapply to the cached rows (they reach the cache like any source). - A view defined over a materialized view lists the collections it reads among its own
reads. - Only og-core writes the cache: a query writing
_cache…is refused.
Listing caches
on _cache | select view, version, place, appInstance, slot, collection, computedAt, reads, changed
| Field | Meaning |
|---|---|
key | The cache entry: view name, version, view Place, reading Place, App instance and filters |
scope | The same without the version: the entries of one view in one scope across versions |
view, version, viewPlace | The view read |
place, appInstance, filtered | The reading scope |
slot, collection | Which of the two collections holds the rows |
reads | The collections it watches |
changed | What they were when computed: the change tokens of the collections, and the views read with their versions |
computedAt | When |
When to materialize
| Materialize | Keep inline |
|---|---|
A costly aggregation read by many reports (group over millions of rows) | A cheap filter or projection |
| A reference table classified once (account → category) and joined often | A view with parameters ($from, $to) |
| A view read by other views each time a report opens | A view read rarely, over data that changes at every read |
A common shape: a materialized base (the heavy join and classification, no parameters), and inline views over it that take the parameters (period, scenario) and filter the cached rows.
define view ledger.cube.all | materialized | exec | … heavy classification … | end | end
define view ledger.cube | exec | on view ledger.cube.all | where date >= $from and date <= $to | end | end
12.2 The result cache
ogd keeps the rows of read requests that a Place repeats: clients ask the same views again each time a report
opens, and every open client asks again after an import.
- The key is built from the prepared query text (views expanded, parameters bound), the scope, and the change tokens of the collections it reads: a write of the Place makes a new key, so kept rows are never stale.
- A read cheaper than 20 ms runs again rather than being kept.
- Rows nobody asks again for a minute leave.
- A read arriving while the same one runs waits for its rows instead of running twice.
- The budget is the
resultsmemory class of the profile (128 MiB by default, at most 1/16 of the managed memory); an entry larger than a quarter of it is not kept. OGD_RESULT_CACHE=offdisables it.
A read of a materialized view uses both: the view’s rows come from its cache collection, and the response to a repeated request comes from memory.
12.3 Choosing what to cache
- Nothing to do for repeated identical reads: the result cache handles them.
- Materialize what is expensive to compute and read by many different queries (other views, different filters, different reports): the result cache cannot share across different queries, the view cache can.
- Keep the cache small: materialize the aggregated or classified shape, not a copy of the raw collection.