11 · Materialized Views — Precomputed Query Results for Speed¶
Performance & Distribution · Topic 11 of 13
What is a Materialized View?¶
A materialized view is a precomputed snapshot of a query result, stored as a physical table. Unlike a regular view (which re-runs the query), a materialized view reads from stored data.
Regular View vs Materialized View¶
| Regular View | Materialized View | |
|---|---|---|
| Storage | None (virtual) | Physical table |
| Query speed | Same as base query | Fast (pre-aggregated) |
| Freshness | Always current | Stale until refreshed |
| Best for | Simplifying queries | Fast aggregations/reports |
Refresh Strategies¶
- Manual:
REFRESH MATERIALIZED VIEW view_name; - Scheduled: cron job or event-based trigger
- Incremental (fast refresh): only process changed rows (not all DBs support this)
- On-commit: refresh on every base table write (very expensive)
Cloud Implementations¶
CREATE MATERIALIZED VIEW daily_sales AS
SELECT date_trunc('day', created_at) AS day, SUM(amount) AS total
FROM orders
GROUP BY 1;
-- Refresh without locking reads:
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;
CONCURRENTLY refresh.
- Native materialized views with automatic refresh
- BigQuery rewrites queries transparently to use MVs when beneficial
- Incremental refresh for supported query patterns
- Do not assume PostgreSQL-style
REFRESH MATERIALIZED VIEWsupport - A portable pattern is a summary table maintained transactionally by application code or asynchronously via change streams; do not assume generic SQL triggers
- No native MVs
- Pattern: duplicate data in a separate table tailored to the read pattern (single-table design)
- Materialized Views (Cassandra MV feature)
- Automatically maintained by Cassandra on write path
- ⚠️ Performance overhead; use with caution on high-write tables
Freshness Is Part of the Interface¶
Expose a refresh watermark alongside a report. A fast total that silently excludes the last hour may be worse than a slower correct query. PostgreSQL's CONCURRENTLY refresh requires an already populated view and a qualifying unique index covering all rows; create that index before running the refresh shown above.
For incremental aggregates, define how updates, deletes and late events retract old contributions. A SUM can be adjusted with signed deltas; a distinct count or median needs more state. Keep a full rebuild path to reconcile drift. A regular view sees data according to the caller's transaction snapshot, not an absolute "latest" wall-clock state.