Что такое Materialized View
Materialized view — database object, хранящий результат query как physical table, в отличие от regular view (virtual). Фаst при SELECT (просто read table), но требует explicit refresh при data changes. Используется для: expensive aggregations (SUM, AVG по millions rows), JOIN multiple large tables, analytics dashboards. Native support: Postgres, Oracle. MySQL — через triggers или periodic table swap.
Ниже: подробности, пример, смежные термины, FAQ.
Подробности
- REFRESH MATERIALIZED VIEW mv_name — full rebuild
- Incremental refresh: Postgres 14+ с pg_ivm extension
- Index можно создать — faster queries
- Stale data: read-time может быть N минут behind source
- Alternative: DB triggers + summary table (hand-managed incremental)
Пример
-- 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 для fast queries
CREATE INDEX ON orders_daily_summary (day);
-- Refresh periodically (cron)
REFRESH MATERIALIZED VIEW CONCURRENTLY orders_daily_summary;Смежные термины
Больше по теме
Часто задаваемые вопросы
Materialized view vs regular view?
Regular — virtual, re-computed при каждом query. Materialized — stored, fast read, stale data.
Refresh frequency?
Balance freshness vs load. For dashboards: 5-15 min. For reports: hourly/daily. Incremental better для high-rate tables.
Postgres CONCURRENTLY flag?
<code>REFRESH MATERIALIZED VIEW CONCURRENTLY</code> — no lock on читателей. Требует UNIQUE index. Recommended для prod.