SQL Notes
Learn what materialized views are, how they differ from regular views by storing precomputed results, and when to use them for performance optimization.
Regular views are virtual — every time you query them, the database re-executes the underlying query from scratch. For simple queries, that is fine. But what about a view that joins five tables with millions of rows and calculates complex aggregations? Re-running that every time someone queries the dashboard is wasteful and slow.
Materialized views solve this by physically storing the query results. The data is computed once and saved to disk, so subsequent queries read from the stored result instead of re-executing the complex query. Think of it as caching at the database level.
The trade-off is straightforward: you gain speed but lose real-time accuracy. A materialized view might be 5 minutes or even an hour behind the live data, depending on how often you refresh it. For many use cases — daily sales reports, weekly analytics dashboards, monthly summaries — this staleness is perfectly acceptable. The key skill is understanding when materialized views are appropriate and how to manage their refresh cycles to balance freshness against performance.
When to Use Materialized Views
Materialized views are ideal when:
- The underlying query is expensive — complex joins, aggregations over millions of rows
- Data does not change frequently — daily sales reports, weekly summaries
- Query speed is critical — dashboard queries that must respond in milliseconds
- Slight staleness is acceptable — the report from 5 minutes ago is good enough
They are NOT ideal when:
- Data must always be real-time
- The underlying data changes constantly
- Storage space is limited
- The base query is already fast
The decision to use a materialized view comes down to a cost-benefit analysis: if the query takes 30 seconds to run and is called 100 times per hour, you are spending 3000 seconds of computation per hour on the same calculation. A materialized view that refreshes every 10 minutes spends only 30 seconds per refresh cycle — a hundredfold improvement. This kind of analysis guides database architects in deciding where materialized views add the most value.
Creating a Materialized View
PostgreSQL
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
DATE_TRUNC('month', order_date) AS month,
COUNT(*) AS total_orders,
SUM(amount) AS total_revenue,
AVG(amount) AS avg_order_value,
COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE status != 'cancelled'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;Oracle
CREATE MATERIALIZED VIEW monthly_sales
BUILD IMMEDIATE
REFRESH ON DEMAND
AS
SELECT
TRUNC(order_date, 'MONTH') AS month,
COUNT(*) AS total_orders,
SUM(amount) AS total_revenue,
AVG(amount) AS avg_order_value,
COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE status != 'cancelled'
GROUP BY TRUNC(order_date, 'MONTH');SQL Server (Indexed Views)
SQL Server does not use the term "materialized view" but achieves the same result with indexed views:
CREATE VIEW dbo.MonthlySales WITH SCHEMABINDING AS
SELECT
YEAR(order_date) AS OrderYear,
MONTH(order_date) AS OrderMonth,
COUNT_BIG(*) AS TotalOrders,
SUM(amount) AS TotalRevenue
FROM dbo.orders
GROUP BY YEAR(order_date), MONTH(order_date);
-- Create a unique clustered index to materialize it
CREATE UNIQUE CLUSTERED INDEX idx_monthly_sales
ON dbo.MonthlySales (OrderYear, OrderMonth);Querying a Materialized View
Once created, you query it exactly like a regular table:
-- This reads from the stored (precomputed) data — instant!
SELECT * FROM monthly_sales WHERE month >= '2026-01-01';
-- Aggregate on already-aggregated data — extremely fast
SELECT SUM(total_revenue) AS yearly_revenue
FROM monthly_sales
WHERE month BETWEEN '2026-01-01' AND '2026-12-31';The difference in speed can be dramatic. A query that took 30 seconds against the base tables might complete in 5 milliseconds against the materialized view.
Refreshing Materialized Views
Since materialized views store a snapshot of data, they become stale as the underlying tables change. You need to refresh them periodically:
Manual Refresh (PostgreSQL)
-- Complete refresh: Rebuilds the entire materialized view
REFRESH MATERIALIZED VIEW monthly_sales;
-- Concurrent refresh: Allows reads during refresh (requires UNIQUE index)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;Oracle Refresh Options
-- Manual refresh
EXEC DBMS_MVIEW.REFRESH('monthly_sales');
-- Schedule automatic refresh every hour
CREATE MATERIALIZED VIEW monthly_sales
REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + 1/24 -- Every hour
AS SELECT ...;Common Refresh Strategies
| Strategy | How It Works | Best For |
|---|---|---|
| On demand | Manually triggered | Ad-hoc reporting |
| Scheduled | Timer-based (hourly, daily) | Dashboards |
| On commit | Auto-refresh after transactions | Small tables, critical freshness |
| Incremental | Only update changed rows | Large datasets |
Real-World Example: E-Commerce Analytics Dashboard
CREATE MATERIALIZED VIEW product_performance AS
SELECT
p.product_id,
p.product_name,
p.category,
COUNT(oi.order_id) AS times_ordered,
SUM(oi.quantity) AS total_units_sold,
SUM(oi.quantity * oi.unit_price) AS total_revenue,
AVG(r.rating) AS avg_rating,
COUNT(r.review_id) AS review_count
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.order_id AND o.status = 'delivered'
LEFT JOIN reviews r ON p.product_id = r.product_id
GROUP BY p.product_id, p.product_name, p.category;This query joins four tables and aggregates across potentially millions of rows. Without materialization, the dashboard would take 20+ seconds to load. With it, responses are instant.
-- Dashboard queries are now instant
SELECT * FROM product_performance WHERE category = 'Electronics' ORDER BY total_revenue DESC LIMIT 10;
SELECT * FROM product_performance WHERE avg_rating >= 4.5 ORDER BY times_ordered DESC;Refresh it every hour during business hours:
-- Run this as a scheduled job
REFRESH MATERIALIZED VIEW CONCURRENTLY product_performance;Real-World Example: Student Performance Summary
CREATE MATERIALIZED VIEW student_performance AS
SELECT
s.student_id,
s.name,
s.department,
COUNT(e.course_id) AS courses_taken,
AVG(CASE e.grade
WHEN 'A+' THEN 10 WHEN 'A' THEN 9 WHEN 'A-' THEN 8.5
WHEN 'B+' THEN 8 WHEN 'B' THEN 7 WHEN 'B-' THEN 6.5
WHEN 'C' THEN 6 WHEN 'D' THEN 5 ELSE 0
END) AS cgpa,
SUM(c.credits) AS total_credits
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
LEFT JOIN courses c ON e.course_id = c.course_id
WHERE e.grade IS NOT NULL
GROUP BY s.student_id, s.name, s.department;Adding Indexes to Materialized Views
Since materialized views store physical data, you can add indexes for even faster queries:
-- PostgreSQL: Add indexes to the materialized view
CREATE INDEX idx_mv_category ON product_performance (category);
CREATE INDEX idx_mv_revenue ON product_performance (total_revenue DESC);
-- Required for CONCURRENTLY refresh
CREATE UNIQUE INDEX idx_mv_product_id ON product_performance (product_id);Dropping a Materialized View
-- PostgreSQL
DROP MATERIALIZED VIEW IF EXISTS monthly_sales;
-- Oracle
DROP MATERIALIZED VIEW monthly_sales;MySQL Alternative
MySQL does not natively support materialized views, but you can simulate them using tables and scheduled events:
-- Create a regular table to store results
CREATE TABLE mv_monthly_sales AS
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
COUNT(*) AS total_orders,
SUM(amount) AS total_revenue
FROM orders GROUP BY DATE_FORMAT(order_date, '%Y-%m');
-- Create a scheduled event to refresh it
CREATE EVENT refresh_monthly_sales
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
TRUNCATE TABLE mv_monthly_sales;
INSERT INTO mv_monthly_sales
SELECT DATE_FORMAT(order_date, '%Y-%m'),
COUNT(*), SUM(amount)
FROM orders GROUP BY DATE_FORMAT(order_date, '%Y-%m');
END;Common Mistakes to Avoid
Mistake 1: Never refreshing the materialized view. Stale data leads to incorrect decisions. Set up a refresh schedule.
Mistake 2: Refreshing too frequently on large views. A full refresh of a 100-million-row view every minute is wasteful. Use incremental refresh or longer intervals.
Mistake 3: Using materialized views for real-time data. If data must be current to the second, use regular views or direct queries.
Mistake 4: Forgetting to add indexes. Without indexes, even materialized views can be slow for filtered queries.
Best Practices
- Use for expensive, frequently-run queries like dashboards and reports
- Set appropriate refresh schedules based on how often data changes
- Add indexes on columns you frequently filter or sort by
- Use CONCURRENTLY in PostgreSQL to avoid blocking reads during refresh
- Monitor view freshness and alert if refresh fails
- Document the staleness tolerance so users know the data might be slightly behind
Materialized views are a powerful performance optimization tool. They bridge the gap between real-time accuracy and query speed, giving you precomputed results for complex analytics while keeping your application responsive.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Materialized Views in SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, views, materialized, view
Related SQL Complete Guide Topics