Materialized view
A precomputed query result stored on disk for fast reads, refreshed periodically or incrementally. It trades freshness and storage for read speed. The lightweight cousin of full CQRS.
- Dashboards and aggregations over large tables.
- Read-heavy queries tolerating some staleness.
- Data that must be real-time fresh.
- Queries cheap enough to compute live.
Definition
A materialized view persists the result of a query (e.g., an aggregation or join) so reads hit the precomputed data instead of recomputing. Refresh can be on-demand, scheduled, or incremental (Postgres incremental, ClickHouse).
Why it exists
Expensive aggregations and joins on large tables are too slow to run per request. Precomputing the answer makes reads fast at the cost of some staleness and storage.
How it works
Define the view's query; the DB or a pipeline populates it. Reads query the view like a table. Refresh strategies: full rebuild (cheap to manage, slow), incremental (fast, complex), or trigger-based. Staleness is bounded by refresh frequency.
Scaling limits
Refresh cost grows with data; full refreshes on huge tables are slow. Incremental refresh logic is complex and can drift. Storage doubles for the precomputed data.
Cost shape
Storage for the view + compute to refresh. Blow-ups: many large views and frequent full refreshes.
Alternatives
- Live query + index — Always fresh, but slow on large data.
- CQRS projection — More flexible and event-driven, but more infra.
Failure modes
- Stale data served past expectations.
- Refresh failing silently.
- Incremental drift from bugs.