How Materialized Views Work
What is a Materialized View?
A materialized view (MV) is a database object that stores the result of a query physically on disk — unlike a regular view, which is just a saved query that re-executes every time. Think of it as a cached snapshot of expensive query results.
When to Use Materialized Views
| Use Case | Why MV Helps |
|---|---|
| Dashboards & reports | Precompute expensive aggregations |
| Multi-table JOINs | Store joined result, avoid repeated JOINs |
| Data warehousing | Pre-aggregate star-schema queries |
| Rankings & percentiles | Cache expensive window functions |
When NOT to Use
- Real-time OLTP where data must be current
- Small datasets where regular queries are fast enough
- When write throughput is already a bottleneck (MV adds storage + refresh cost)
Visualization
Switch between the three scenarios to see the full lifecycle:
- Build MV: Watch how source tables are scanned, aggregated, and written to disk
- Query Comparison: See the dramatic difference between live queries and MV reads
- Refresh MV: See what happens when source data changes and the MV needs updating
0 / 10
Source Tables (customers + orders)
-- Source data (customers + orders joined)
-- This is what the MV precomputes
| ID | Customer | Orders | Total | Avg |
|---|---|---|---|---|
| 1 | Emma | 12 | $2,400 | $200 |
| 2 | Liam | 5 | $750 | $150 |
| 3 | Sophia | 28 | $8,400 | $300 |
| 4 | Noah | 3 | $270 | $90 |
| 5 | Olivia | 15 | $3,750 | $250 |
| 6 | James | 8 | $1,600 | $200 |
| 7 | Alice | 42 | $12,600 | $300 |
| 8 | Bob | 1 | $45 | $45 |
Materialized View (precomputed)
CREATE MATERIALIZED VIEW mv_customer_summary AS
SELECT c.id, c.name,
COUNT(o.id), SUM(o.amount), AVG(o.amount)
FROM customers c JOIN orders o ...
MV Definition
↓
Scan Sources
↓
Aggregate
↓
Write to Disk
↓
Ready to Query
MV is stale — data not yet computed
Press Play to start building the materialized view...
Key Concepts
MV vs Regular View
| Aspect | Regular View | Materialized View |
|---|---|---|
| Storage | Virtual (no storage) | Physical disk storage |
| Read speed | Slow (recomputes) | Fast (precomputed) |
| Data freshness | Always current | Stale until refreshed |
| Write overhead | None | Extra storage + refresh cost |
Refresh Strategies
Database Support
| Database | Auto-Refresh | Incremental | Notes |
|---|---|---|---|
| PostgreSQL | Manual | No (full rebuild) | REFRESH MATERIALIZED VIEW |
| Oracle | Yes | Yes (MV Logs) | Most mature implementation |
| SQL Server | Auto (live) | N/A | "Indexed Views" — schema-binding required |
| ClickHouse | Auto | Yes | Core feature for real-time analytics |
Common Pitfalls
| Pitfall | Solution |
|---|---|
| Stale data | Set up scheduled refresh with pg_cron |
| Full rebuild cost | Use REFRESH CONCURRENTLY (PostgreSQL) |
| Missing unique index | Create one — required for concurrent refresh |
| Storage bloat | Monitor MV size, drop unused views |
Learn More
Read the full theory in Database Design and SQL Fundamentals.