âš¡IntermediateViews & Indexes
Materialized Views
Cache expensive query results with materialized views and refresh on demand.
Overview
A materialized view stores the result of a query on disk, unlike a regular view which re-executes on every access. This makes queries against the materialized view very fast they just read the cached data. The trade-off is staleness: the data is only as fresh as the last REFRESH MATERIALIZED VIEW.
Materialized views are ideal for expensive aggregations or joins that don't need to be real-time: daily reports, analytics summaries, dashboards. They can be indexed just like regular tables.
Key Points
- •CREATE MATERIALIZED VIEW name AS SELECT ... stores results on disk
- •REFRESH MATERIALIZED VIEW name updates the stored data
- •REFRESH ... CONCURRENTLY refreshes without locking reads (requires unique index)
- •Can be indexed like regular tables for fast queries
- •Data is stale until refreshed not suitable for real-time needs
Examples
Create and refresh a materialized view
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
DATE_TRUNC('month', created_at) AS month,
SUM(total) AS revenue,
COUNT(*) AS order_count
FROM orders
GROUP BY month;
-- Create an index on the materialized view
CREATE UNIQUE INDEX ON monthly_revenue (month);
-- Refresh (concurrent no read lock)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4