A materialized view is a database object that stores the result of a query physically on disk—like a snapshot of data. Unlike regular views (which are virtual and recompute data on access), materialized views cache the results, making read queries much faster.
📌 Key Concepts
| Feature | Materialized View |
|---|---|
| Data storage | ✅ Stored on disk |
| Performance | ✅ Fast reads (no re-computation) |
| Freshness | ❌ May become stale unless refreshed |
| Updates | Manual or scheduled refresh needed |
| Use cases | Reporting, analytics, caching expensive joins/aggregates |
🧠 How It’s Different from a Regular View
| Regular View | Materialized View | |
|---|---|---|
| Definition | Stored SQL query | Stored query result |
| Query speed | Slower (computed live) | Faster (precomputed) |
| Data updates | Always current | Must be refreshed |
🧪 Example in PostgreSQL
1. Create a Materialized View
CREATE MATERIALIZED VIEW recent_orders AS
SELECT customer_id, COUNT(*) AS total_orders
FROM orders
WHERE order_date > CURRENT_DATE - INTERVAL '30 days'
GROUP BY customer_id;
Query it like a table
SELECT * FROM recent_orders;
Refresh the view (to update the data)
REFRESH MATERIALIZED VIEW recent_orders;
Optionally use WITH CONCURRENTLY (PostgreSQL) to avoid locking reads.
🐬 MySQL Support?
MySQL doesn’t support true materialized views, but you can simulate them using:
- A normal table + triggers
- A scheduled job (via
EVENTs or cron +INSERT INTO ... SELECT)
🧰 Use Cases
- Expensive joins or aggregations (e.g. daily reports)
- Caching filtered subsets of large datasets
- Improving read performance when real-time data isn’t needed
⚠️ Trade-offs
| Benefit | Drawback |
|---|---|
| Faster queries | Needs manual/scheduled refresh |
| Reduces CPU load | Uses disk space |
| Simplifies complex logic | Can become stale |