Skip to content

Materialized View Data Freshness Design Decisions

Jump to bottom
Michael Avrukin edited this page Jun 21, 2026 · 2 revisions

Materialized View Data Freshness — Design Decisions

Status: Accepted design, pre-implementation · Date: 2026-06-20 Applies to: activerecord-materialized bootstrap, read path, and incremental view maintenance (IVM)

This document records the agreed design for how materialized views (MVs) are provisioned, kept fresh, and read — with the explicit goal of being safe to launch against a large production database. It supersedes the original "lazy full bootstrap on first read" behavior.

1. Problem statement

The original implementation builds an MV lazily on the first read of a missing cache table: it runs the source query twice (once to infer the table schema, once for data), materializes every row into Ruby memory, and writes them back with one large insert_all. On a big database this is dangerous:

  • the expensive analytical query runs synchronously inside a web request;
  • the entire result set is buffered in process memory — a likely OOM;
  • a single multi-million-row INSERT is a long write transaction (and can exceed MySQL max_allowed_packet);
  • concurrent first-readers raise while a build is in progress.

We want the opposite posture: a full scan of the base tables never happens by accident. Provisioning is a migration; freshness is maintained incrementally; reads are always correct; and the developer only declares the view and its dependencies.

2. Goals & non-goals

Goals

  • Never trigger a full MV rebuild implicitly (migration, deploy, read, or scheduled refresh). Full materialization is an explicit, intentional act.
  • Reads are always correct, even against a cold or partially-warm view.
  • Writes to base tables drive incremental maintenance using the most efficient algorithm that is provably correct for the view's aggregates.
  • The MV table is provisioned and schema-migrated through normal Rails migrations.
  • Developer surface stays minimal: declare materialized_from + depends_on; everything else is automatic.

Non-goals

  • Maintaining arbitrary SQL with zero base re-scan. That is provably impossible for some aggregates (see §4); we degrade gracefully, never to a full rebuild.
  • Strong (synchronous) read-your-write consistency on the MV. The model is eventual consistency with a correct read-through fallback.

3. The fundamental tension

The request is always correct + maximally efficient + fully general, with no full rebuild. For arbitrary SQL the first three cannot hold simultaneously — a known result, not a shortcoming:

Aggregate class Insert Delete Maintenance
Distributive — COUNT, SUM, COUNT(*) self-maintainable self-maintainable apply signed deltas
Algebraic — AVG, variance self-maintainable (keep SUM+COUNT) self-maintainable derive from maintained components
Non-distributive — MIN, MAX self-maintainable not self-maintainable scoped recompute of affected group (or auxiliary top-k)
Holistic — COUNT(DISTINCT), median needs multiplicity state needs multiplicity state multiplicity table or scoped recompute

Resolution principle: classify each view's projection, use true delta maintenance where it is provably correct, and degrade to scoped partition recompute (re-run the query for only the affected group keys) where it is not. "No base re-scan" is not always achievable; "no full rebuild" always is. That distinction is the backbone of this design.

4. Core mechanism: per-partition freshness

Every downstream behavior reduces to one piece of state we do not have today: which slices of the view are authoritative right now. We introduce a partition-state map, keyed by the view's GROUP BY key, with three states:

  • absent — never materialized (e.g. immediately after the migration creates an empty table);
  • fresh — the MV row is authoritative;
  • dirty — backing data changed; the MV row is stale pending maintenance.

Every lens becomes a transition over this map:

                 read (absent|dirty)        write to partition
   ┌─────────┐  ─────────────────────▶  ┌─────────┐  ───────────▶  ┌─────────┐
   │ absent  │   read-through to base   │  dirty  │   enqueue IVM   │  fresh  │
   └─────────┘   + enqueue maintenance  └─────────┘  ◀───────────  └─────────┘
        ▲                                                 maintain
        └──────────────────  never: full rebuild  ──────────────────┘

Storage

A side table records partition freshness:

ar_materialized_view_partitions
  view_name      string   (indexed with partition_key, unique)
  partition_key  string   (serialized GROUP BY tuple)
  state          string   (fresh | dirty)   -- absent = no row
  updated_at     datetime

absent is represented by the absence of a row, so cardinality is bounded by the number of partitions that have been touched, not the whole key space.

Coarse mode to cap metadata

A view carries a coarse mode so the side table never has to enumerate every possible group:

  • cold — few/no partitions authoritative; track the small fresh set as it warms.
  • warm — all partitions authoritative; track only the small dirty set.

An explicit rebuild or a warm-up run promotes cold → warm and clears the set. This is demand-driven partial materialization, related to the warehouse "summary-delta" model (Mumick, Quass & Mumick, 1997) and to differential dataflow / DBSP (Budiu et al., 2023), adapted to the application layer.

5. Design by lens

Lens 1 — Migration-provisioned schema

The MV table is created by a generated Rails migration, not inferred at runtime. The view generator introspects the relation (relation.limit(0) for column names, Arel/result metadata for types) and emits a create_table migration plus the ar_materialized_view_partitions and metadata tables. At deploy, db:migrate provisions an empty MV table.

Definition changes produce a new ALTER migration (Rails-idiomatic). A startup schema-verify compares the relation's projection against the table and raises with a "regenerate the migration" message on drift — it never auto-alters and never rebuilds.

Decision 4: auto-generate the create_table migration from the relation in the view generator (most Rails-idiomatic), with drift verification at boot.

Lens 2 — Read-through fallback that triggers IVM

ensure_materialized! is replaced by a partition-aware read:

  1. Resolve the partitions the query touches.
  2. For fresh partitions, serve from the MV (fast).
  3. For absent/dirty partitions, read through to the base relation for that slice, return the correct merged answer, and enqueue scoped maintenance.

Read-through to base for a cold slice runs the slow query, so it is a correctness safety net, not the hot path — which is exactly why the warm-up list (Lens 4) exists. Cold-slice policy is configurable:

  • :read_through — silent base read (default, Decision 3);
  • :serve_stale — return whatever is cached (possibly empty) and warm in background;
  • :raise — fail loudly (useful in tests / strict environments).

Whole-view queries that don't filter on the group key can only be served from the MV when the view is warm; otherwise they read through. This is inherent.

Lens 3 — Efficient, always-correct IVM (no full rebuild)

Two layers:

  1. Summary-delta (true delta IVM) for self-maintainable aggregates. Maintain per-group SUM, COUNT, and COUNT(*); derive AVG = SUM/COUNT. Apply signed deltas for insert/update/delete using bag (multiset) semantics so counts stay correct under deletes (Blakeley-Larson-Tompa 1986; Gupta-Mumick-Subrahmanian counting algorithm 1993; Griffin-Libkin duplicates 1995). No base re-scan.
  2. Scoped partition recompute as the correctness floor for non-self- maintainable cases — MIN/MAX under delete, COUNT(DISTINCT), fan-out joins, HAVING. Re-run the query for only the affected group keys (Quass 1996; Palpanas et al. non-distributive aggregates 2002).

The maintainer classifies each projected aggregate and routes per column: distributive/algebraic → delta; non-distributive/holistic → scoped recompute. A view mixing both uses delta where possible and recompute only for the columns that need it. Neither path ever rebuilds the whole table.

Decision 2: implement the full set now — summary-delta for the self- maintainable aggregates and correct scoped-recompute handling for MIN/MAX/DISTINCT/joins. We do not punt on the hard cases; we route them to the always-correct path.

Lens 4 — Never auto-rebuild; intentional rebuild + warm-up

  • No implicit full scan. Migrations, refresh_if_stale!, and read paths are guarded to refuse a full base scan. A view that needs materialization stays cold and reads through until warmed.
  • (a) Intentional rebuild. A deliberate, hard-to-fire API (Klass.rebuild!(confirm: true) / rake task) performs the full materialization, with chunked INSERT … SELECT in the database (no Ruby row round-trip), for recovery or first warm-up.
  • (b) Warm-up list. A configurable set of representative queries (warm_up { [Klass.where(...), ...] }) is run at boot/deploy to populate the hot partitions on purpose. This is what keeps Lens 2's read-through from firing in practice, and doubles as a smoke-verify of the view.

Lens 5 — Writes drive efficient updates; seamless DX

The write backbone already exists: after_*_commit → MaintenanceDeltaBuilder → accumulated partition keys in MaintenanceStore → scheduled maintenance. Layered on the freshness map, the target "win" falls out for free:

The server boots with an empty MV. A background write to partition P maintains/populates P and marks it fresh. The user then reads P from the MV — fast and correct — while the rest of the view is still absent and harmlessly reads through.

The developer writes only materialized_from + depends_on; freshness, read- through, maintenance routing, and warm-up are automatic.

6. Developer-facing surface (target)

class SalesSummary < ActiveRecord::Materialized::View
  self.table_name = "mv_sales_summary"

  materialized_from { ... }       # source ActiveRecord::Relation
  depends_on LineItem, Order      # write tracking

  cold_read :read_through         # :read_through (default) | :serve_stale | :raise
  warm_up { [where("revenue > 0")] }   # representative queries run at boot
end

# Provisioning (idiomatic Rails):
#   bin/rails generate activerecord_materialized:view SalesSummary
#   bin/rails db:migrate            # creates empty MV + partition/metadata tables

# Intentional, never-implicit full materialization:
#   SalesSummary.rebuild!(confirm: true)
#   bin/rails materialized:rebuild VIEW=SalesSummary

7. Phased implementation plan

Each phase is an independent issue / PR.

  • P1 — Freshness core. ar_materialized_view_partitions table; partition state machine; read-through path with :read_through default; the "never full rebuild" guard; explicit rebuild!(confirm:).
  • P2 — Migration-provisioned schema. Generator emits the create_table migration from the relation; boot-time drift verification.
  • P3 — Summary-delta IVM. Self-maintainable SUM/COUNT/AVG with signed deltas and bag semantics; per-column classification; scoped-recompute fallback for MIN/MAX/DISTINCT/joins.
  • P4 — Warm-up. warm_up DSL + boot hook; chunked in-database INSERT … SELECT for rebuild/warm.

8. Decisions (locked)

  1. Per-partition freshness storage: dedicated ar_materialized_view_partitions side table with cold/warm promotion to cap cardinality.
  2. Delta-IVM scope: implement everything — true delta for self-maintainable aggregates and correct scoped-recompute for the rest; no punting.
  3. Cold-slice read default: :read_through (silent base read).
  4. Migration UX: auto-generate the create_table migration from the relation in the view generator, with boot-time drift verification.

9. References

  • Blakeley, Larson, Tompa. Efficiently Updating Materialized Views. SIGMOD 1986.
  • Gupta, Mumick, Subrahmanian. Maintaining Views Incrementally. SIGMOD 1993. (counting algorithm; DRed for aggregates/recursion)
  • Griffin, Libkin. Incremental Maintenance of Views with Duplicates. SIGMOD 1995.
  • Colby, Griffin, Libkin, Mumick, Trickey. Algorithms for Deferred View Maintenance. SIGMOD 1996.
  • Quass, Gupta, Mumick, Widom. Making Views Self-Maintainable for Data Warehousing. PDIS 1996.
  • Quass. Maintenance Expressions for Views with Aggregation. 1996.
  • Mumick, Quass, Mumick. Maintenance of Data Cubes and Summary Tables in a Warehouse. SIGMOD 1997. (summary-delta)
  • Palpanas, Sidle, Cochrane, Pirahesh. Incremental Maintenance for Non-Distributive Aggregate Functions. VLDB 2002. (MIN/MAX)
  • Ahmad, Kennedy, Koch, Nikolic. DBToaster: Higher-order Delta Processing for Dynamic, Frequently Fresh Views. VLDB 2012.
  • Budiu, McSherry, Ryzhyk, Tannen. DBSP: Automatic Incremental View Maintenance for Rich Query Languages. VLDB 2023.

10. Open questions / future work

  • Partition-key serialization for composite/typed group keys (collation, null handling).
  • Eviction/TTL for fresh partitions in cold mode to bound the side table on very high-cardinality views.
  • Maintenance under base-table schema changes (column rename/drop on a dependency).
  • Multi-process coordination of warm-up and rebuild (advisory locks) so only one worker materializes.

Clone this wiki locally