A read-heavy query is too expensive to compute on demand. Compare a database materialized view, an application-maintained read model and a stream-processed view, and describe how each handles staleness and rebuild.

The three approaches

Database materialized view — the database maintains a stored result set, refreshed on a schedule or incrementally.

Advantages: no application code; transactional consistency with the source where incremental maintenance is supported; operationally simple, which is worth a great deal. Costs: refresh competes with the transactional workload; the definition lives in the database and is versioned awkwardly; refresh of a large view can be slow and can lock; and scaling is bounded by the database.

Application-maintained read model — the application writes to both the source and the derived table, or updates the derived table in response to its own operations.

Advantages: full control of shape and update logic; the derived store can be a different technology. Costs: the same atomicity problem as any dual write — the write path can update one and not the other, and the divergence is silent. This is the approach that most often produces a read model that is quietly wrong, and it should be implemented through an outbox rather than by direct dual writing.

Stream-processed view — a change stream from the source drives a processor that maintains the view.

Advantages: the source's write path is unchanged and depends on nothing; the log gives ordering; the view can be rebuilt by replay; the view can live in any store, scaled independently. Costs: a pipeline to operate; eventual consistency measured in seconds; and a schema-evolution problem in the stream.

Staleness

Decide the tolerable staleness per view, explicitly, and measure it as an SLO — event time to view-visible time, at p99 rather than average, because complaints come from the tail.

Then handle the actor who can perceive it: the user who just made the change. Read-after-write patching — overlaying that user's recent changes onto the view at query time — solves the perception problem at the layer where it exists, without imposing global freshness requirements that are enormously expensive.

Rebuild, which is the property that decides the design

A view will need to be rebuilt: a bug in the projection logic, a schema change, a new field, or a source correction. How cheaply that can be done is the most important operational property of the design.

• Stream-processed views rebuild by replaying the log into a new view, which is the cleanest available story — provided the log retains enough history, which must be verified rather than assumed.
• Materialized views rebuild by a full refresh, which is simple and may be slow enough to require a maintenance window on a large table.
• Application-maintained views frequently have no rebuild path at all, because the update logic is distributed through the write path and cannot be replayed. That absence is the strongest argument against the approach.

Build into a new view and swap a pointer atomically, rather than rebuilding in place — so readers never see a half-built view and rollback is a pointer flip. Retain the previous version, and validate before promotion with row counts and a canary set of expected values, because the failure being guarded against is a job that completed successfully and produced wrong data, which no infrastructure alarm detects.