Redshift materialized views are precomputed query results stored physically in Amazon Redshift, which makes repeated analytics queries much faster than running heavy joins and aggregations every time. They are ideal for dashboards, BI tools, and reporting workloads where performance matters and data can be refreshed periodically.
What Is a Materialized View in Redshift?
A materialized view (MV) in Amazon Redshift stores the output of a SQL query on disk. Unlike a normal view (which stores only SQL logic), an MV stores actual results that can be queried directly.
Normal View vs Materialized View
| Feature | Normal View | Materialized View |
|---|---|---|
| Stores SQL definition | Yes | Yes |
| Stores query results physically | No | Yes |
| Query speed for heavy workloads | Slower | Faster |
| Needs refresh | No | Yes |
Why Materialized Views Improve Performance
- Repeated full-table scans
- Heavy join computation
- Repeated aggregations (
SUM,COUNT,AVG,GROUP BY) - Latency for dashboard users
Redshift Materialized View Architecture
The diagram shows a common pattern: source fact and dimension tables feed a materialized view, and BI tools query the MV for low-latency responses.
Step-by-Step: Create and Use a Materialized View in Redshift
1) Create the MV
CREATE MATERIALIZED VIEW mv_daily_product_sales AS
SELECT
date_trunc('day', fs.order_time) AS order_day,
fs.product_id,
dp.product_name,
SUM(fs.amount) AS total_sales,
COUNT(*) AS total_orders
FROM fact_sales fs
JOIN dim_product dp
ON fs.product_id = dp.product_id
GROUP BY 1,2,3;2) Query the MV
SELECT order_day, product_name, total_sales, total_orders
FROM mv_daily_product_sales
WHERE order_day >= current_date - 7
ORDER BY order_day DESC, total_sales DESC;3) Refresh the MV
REFRESH MATERIALIZED VIEW mv_daily_product_sales;Refresh Strategy: How Often Should You Refresh?
- Near real-time dashboards: every 5–15 minutes
- Operational reporting: hourly
- Executive summary reports: daily
If your ETL runs nightly, refresh the MV right after ETL completion.
Best Practices for Redshift Materialized Views
- Use MVs for repeated expensive queries.
- Keep MV logic stable.
- Refresh after data loads.
- Start with high-impact dashboards.
- Monitor query performance before/after.
Common Mistakes to Avoid
- Creating too many MVs without usage monitoring
- Forgetting refresh schedules (stale data risk)
- Treating MV as real-time when refresh is periodic
- Materializing low-value queries with little reuse
FAQ
1) What is a materialized view in Amazon Redshift?
A materialized view in Redshift stores precomputed query results physically, improving performance for repeated complex queries.
2) How is a materialized view different from a normal view?
A normal view stores only SQL logic; a materialized view stores query output and must be refreshed to stay current.
3) How do you refresh a Redshift materialized view?
REFRESH MATERIALIZED VIEW your_mv_name;4) When should I use Redshift materialized views?
Use them for frequent dashboard/report queries with heavy joins and aggregations where fast response time is critical.
5) Are materialized views always better?
Not always. They improve speed but add storage and refresh overhead, so they are best where query reuse is high.
