What is a materialized view in PostgreSQL?
Answer
A materialized view stores the result of a query physically on disk, unlike a regular view which runs the query on every access. This makes reads very fast for expensive queries. Create: CREATE MATERIALIZED VIEW monthly_sales AS SELECT DATE_TRUNC('month', order_date) AS month, SUM(total) FROM orders GROUP BY 1;. Query it like a table: SELECT * FROM monthly_sales;. Refresh the data: REFRESH MATERIALIZED VIEW monthly_sales; — blocks reads during refresh. Non-blocking refresh: REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales; — requires a unique index on the view. Materialized views are ideal for complex analytical queries, dashboards, and pre-aggregated reports that can tolerate slightly stale data. Schedule refreshes with pg_cron or application-level jobs.