What is a Materialized View
Materialized view — a database object storing the result of a query as a physical table, unlike a regular view (virtual). Fast on SELECT (just read table), but requires explicit refresh on data changes. Used for: expensive aggregations (SUM, AVG over millions of rows), JOIN of multiple large tables, analytics dashboards. Native support: Postgres, Oracle. MySQL — via triggers or periodic table swap.
Below: details, example, related terms, FAQ.
Details
- REFRESH MATERIALIZED VIEW mv_name — full rebuild
- Incremental refresh: Postgres 14+ with pg_ivm extension
- Index can be created — faster queries
- Stale data: read-time can be N minutes behind source
- Alternative: DB triggers + summary table (hand-managed incremental)
Example
-- Create materialized view
CREATE MATERIALIZED VIEW orders_daily_summary AS
SELECT
DATE(created_at) AS day,
status,
COUNT(*) AS orders,
SUM(amount) AS revenue
FROM orders
GROUP BY day, status;
-- Index for fast queries
CREATE INDEX ON orders_daily_summary (day);
-- Refresh periodically (cron)
REFRESH MATERIALIZED VIEW CONCURRENTLY orders_daily_summary;Related Terms
Learn more
Frequently Asked Questions
Materialized view vs regular view?
Regular — virtual, recomputed on every query. Materialized — stored, fast read, stale data.
Refresh frequency?
Balance freshness vs load. For dashboards: 5-15 min. For reports: hourly/daily. Incremental better for high-rate tables.
Postgres CONCURRENTLY flag?
<code>REFRESH MATERIALIZED VIEW CONCURRENTLY</code> — no lock on readers. Requires UNIQUE index. Recommended for prod.