What is a materialized view and how does it differ from a normal view?
What is a materialized view and how does it differ from a normal view?
A normal view stores only a query and runs it each time. A materialized view stores the result on disk, so reads are fast, but the data can be stale until refreshed.
CREATE MATERIALIZED VIEW daily_sales AS
SELECT date_trunc('day', created_at) AS day, SUM(total) AS revenue, COUNT(*) AS orders
FROM orders
GROUP BY 1;
CREATE UNIQUE INDEX uq_daily_sales_day ON daily_sales (day); -- required for CONCURRENTLY
REFRESH MATERIALIZED VIEW daily_sales; -- locks readers during the rebuild
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales; -- readers keep working
PostgreSQL recomputes the whole query on refresh; it has no built-in incremental refresh. You can index a materialized view like a table.
| Engine | Feature |
|---|---|
| SQL Server | Indexed view: a view with WITH SCHEMABINDING and a unique clustered index, maintained automatically on every write. Many restrictions on the query shape, and it adds write cost. |
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.