Materialized views
A materialized view stores the query result on disk so expensive queries are fast, and you refresh it when needed.
In this page:
Syntax
CREATE MATERIALIZED VIEW view_name AS
SELECT columns FROM table_name;
REFRESH MATERIALIZED VIEW view_name;Materialized views
CREATE MATERIALIZED VIEW name AS SELECT ... runs the query once and keeps the rows. Reads are fast, but the data goes stale until REFRESH MATERIALIZED VIEW.
REFRESH ... CONCURRENTLY refreshes without blocking readers and requires a unique index.
They suit dashboards and reports over large tables.
Note:
REFRESH MATERIALIZED VIEW CONCURRENTLY needs a unique index on the view.
Example: Materialized views
shop=# CREATE MATERIALIZED VIEW daily_sales AS
shop-# SELECT date_trunc('day', created_at) AS day, sum(total) AS revenue FROM orders GROUP BY 1;
SELECT 90
shop=# CREATE UNIQUE INDEX ON daily_sales (day);
shop=# REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;
REFRESH MATERIALIZED VIEW
shop=# SELECT * FROM daily_sales ORDER BY day DESC LIMIT 2;
day | revenue
---------------------+---------
2024-03-15 00:00:00 | 1520.00
2024-03-14 00:00:00 | 1834.50⚠️ Run this in your own terminal or Node.js environment.
Related Topics
Common Mistakes
- Expecting automatic refreshes
- Refreshing without CONCURRENTLY on busy systems
- Using them for real-time data
Chapter Summary
- Stores the result on disk
- Must be refreshed manually or on a schedule
- CONCURRENTLY avoids blocking
- Great for heavy reports
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: