Materialized View

A regular SQL view is a saved query — every time you read it, the database re-runs the query. A materialized view persists the query's result as actual rows in a table, so reads are fast (no re-computation). The trade-off: the view must be refreshed when underlying data changes.

Three refresh strategies:

1. On-demand (manual) — refresh when you say so. Used for nightly batch refreshes: at 2 AM, drop and rebuild the view from current data. Reads during the day are fast and consistent with the 2 AM snapshot. Stale, but predictably stale. Common for daily reports.

2. Scheduled (periodic) — refresh every N minutes/hours. Trade staleness for freshness. The view is at most N minutes behind. Used for near-real-time dashboards.

3. Incremental / event-driven — refresh on every write, or on every event. Most fresh, most expensive. Two flavors:

• Synchronous: the write transaction also updates the view. Strongly consistent, but slower writes and harder to recover from failure.
• Asynchronous: the write emits an event; a consumer updates the view. Eventually consistent, but decouples write and view latencies.

The right strategy depends on the query's tolerance for staleness and the write volume. A nightly sales report can tolerate 24-hour staleness; a leaderboard during a live game needs near-real-time.

Materialized views can live in:

• The same database — native materialized views in PostgreSQL, Oracle; refreshed via REFRESH MATERIALIZED VIEW.
• A different store — Elasticsearch index as a view of Postgres data; Redis cache as a view of a row; a denormalized table in a data warehouse as a view of operational data.
• Multiple stores — one source, many views, each optimized for its read pattern (search, analytics, caching).

Choosing a refresh strategy is the core decision. Trade-offs:

Full rebuild — drop and recreate the view from scratch. Simple, correct, but expensive. Use when:

• The view is small enough to rebuild in minutes.
• Underlying data changes so much that incremental is barely cheaper.
• The refresh runs nightly when traffic is low.

Incremental refresh — only update the rows that changed. Much cheaper for large views with small change sets. Use when:

• The view is large (millions of rows) but only a small fraction changes per refresh.
• You can identify which rows changed (via updated_at timestamp, CDC, or event sourcing).
• You can correctly handle deletes (often the hard part).

Event-driven / streaming — every write emits an event; a consumer updates the view. Lowest staleness, highest write amplification. Use when:

• Near-real-time freshness is required (seconds, not minutes).
• The view is a projection of an event-sourced system.
• You're already operating a streaming pipeline (Kafka, Flink).

Synchronous update in the write transaction — the write transaction also updates the view. Strongly consistent reads, slower writes, harder recovery. Use when:

• Reads must be strongly consistent (no staleness acceptable).
• Write volume is low enough that the extra cost is negligible.
• The view fits in the same database (so the update is in the same transaction).

A subtle but important point: materialized views have schemas, and schemas evolve. When the view's definition changes, you may need to rebuild it from scratch — which can take hours for large views. Plan for this: version the view, rebuild in the background, switch over atomically.

When to use materialized views:

• Aggregations — sum, count, average over many rows. Pre-compute once, read many times. Dashboards, reports, leaderboards.
• Denormalized reads — joins that are expensive at read time but cheap when pre-computed. ‘Get user with all their orders’ as one row instead of a join.
• Different query patterns — relational store for writes, search index for full-text, graph for relationships. Each is a view optimized for its query.
• Read-heavy workloads — 100:1 read:write ratio justifies the write amplification.
• CQRS read models — projections of the write model, each shaped for its query.

When NOT to use materialized views:

• Simple CRUD with low read volume — the overhead of refresh isn't worth it.
• Strongly consistent reads required — materialized views are eventually consistent by nature.
• Low write volume AND low read volume — just use the source directly.
• High write volume with low read volume — the refresh cost dominates; don't materialize.
• When the view is rarely queried — pay for refresh, never read. Delete it.

A common anti-pattern: materializing every possible query. Each materialized view costs write amplification, storage, and operational complexity. Materialize the queries that actually matter — the slow ones, the hot ones, the ones on the critical path.

Mental model. Think of Materialized View as a deliberate boundary in a system. The boundary exists because something becomes harder to manage when everything is done in one place: latency, scale, failure isolation, consistency, cost, or team ownership. The useful question is not “what does Materialized View mean?” but “what pressure makes this boundary worth introducing, and what new failure mode does it create?”

Numerical lens: write down traffic, payload size, read/write ratio, peak multiplier and durability target before choosing a component. The numbers should justify the architecture.

Answer this without notes: When would you choose Materialized View, and when would you intentionally avoid it? Mention at least one bottleneck it addresses, one failure mode it introduces, and one alternative. Then quantify the workload you are designing for.

Numerical sanity check

Back-of-the-envelope reasoning beats fake precision. State your traffic, payload, concurrency and growth assumptions explicitly, then calculate enough to know whether the current architecture is orders of magnitude away from the target.