A Materialized View is a special type of database object that stores the result of a SQL query physically on disk, unlike a regular view which is computed dynamically every time it’s queried.
Stored on disk: The result of the query is saved, not just the query definition.
Faster performance: Since the data is precomputed, queries against it are typically much faster.
Needs refreshing: Because the underlying data can change, a materialized view must be explicitly or automatically refreshed to stay up to date.
| Feature | View | Materialized View |
|---|---|---|
| Storage | Only the query, no data stored | Query and data are stored |
| Performance | Slower for complex queries | Faster, as results are precomputed |
| Freshness | Always up to date | Can become stale |
| Needs refresh | No | Yes (manually or automatically) |
-- Creating a materialized view in PostgreSQL
CREATE MATERIALIZED VIEW top_customers AS
SELECT customer_id, SUM(order_total) AS total_spent
FROM orders
GROUP BY customer_id;To refresh the data:
REFRESH MATERIALIZED VIEW top_customers;For complex aggregations that are queried frequently
When performance is more important than real-time accuracy
In data warehouses or reporting systems
Webflow
