Problem
Complex analytical queries (joins, aggregations) are too slow for user-facing latency. Running them on demand kills database.
scalingestablished · medium operational burden
Materialized View
Also known as: materialized-view-pattern, precomputed-view, denormalized-view
Intent
Pre-compute and store query results for fast reads, refreshed periodically or on data change.
Forces
- •Query involves multiple tables, billions of rows
- •Read latency must be <100ms
- •Data freshness: minutes to hours acceptable
- •Write path must not be slowed by view maintenance
Solution
✓ When to Use
- •Complex aggregations, joins, window functions
- •Dashboard, reporting, user-facing analytics
- •Data freshness: minutes to hours OK
- •Read pattern is predictable and repeated
✗ When Not to Use
- •Real-time consistency required
- •Ad-hoc queries (unpredictable patterns)
- •Base tables change too frequently (refresh overhead)
- •View size approaches base table size (no win)
Pros
- +Query latency: seconds → milliseconds
- +Offloads analytical load from OLTP primary
- +Can use columnar storage for analytics
- +Incremental refresh minimizes lock time
Cons
- −Stale data: refresh lag = consistency window
- −Storage cost: duplicate data
- −Refresh failures → stale or missing data
- −Schema changes: view must be updated
Cost Profile
Infrastructure
Medium — additional storage, compute for refresh
Operational
Medium — monitor refresh lag, handle failures
Cognitive
Low — standard SQL concept
Failure Modes
Refresh timeout → view stale, concurrent refresh fails
Base schema change breaks view definition
Incremental refresh logic bug → silent data corruption
View size growth → query performance degrades
Real-World Examples
Alternatives
- cqrs-read-model
- precomputation
- caching
- partitioning
Related Patterns
- cqrs
- precomputation
- caching
- event-sourcing
- cdc
Competency Domains
data statescalingeconomics evolution