Materialized views в SQL: гайд с примерами

Обычный VIEW vs materialized VIEW

Обычное представление (VIEW) — это сохранённый SQL-запрос. При обращении к нему база каждый раз выполняет запрос заново. Это удобно для инкапсуляции логики, но не даёт выигрыша в производительности.

Материализованное представление (MATERIALIZED VIEW) — это таблица, в которую один раз записан результат запроса. При обращении к ней база просто читает данные, как из обычной таблицы. Быстро, но данные могут быть устаревшими — их нужно обновлять.

Разница важная для аналитических дашбордов. Обычная VIEW с агрегацией на 100 миллионов строк будет тормозить на каждом открытии дашборда. Материализованная — читается за секунды, но обновляется, скажем, раз в час.

Когда это имеет смысл

Три типичных сценария, где материализованные представления оправданы:

Тяжёлые агрегаты для дашбордов. Daily GMV по 5 миллионам транзакций за 12 месяцев — классический случай. Считать на каждом открытии дашборда — 30 секунд ожидания. Материализовать раз в ночь — 0.5 секунды.

Сложные JOIN-ы с несколькими таблицами. Когорта пользователей с JOIN на события, заказы, платежи. Запрос может выполняться минуты. Материализация сводит это к миллисекундам.

Данные, которые редко меняются. Справочники, нормализованные словари. Не нужно пересчитывать каждую минуту.

А когда не стоит использовать:

Данные, которые должны быть свежими в реальном времени. Материализация всегда имеет lag.

Простые запросы, которые и так быстрые. Overhead материализации не оправдан.

Часто меняющиеся source-таблицы. Если source обновляется каждую минуту, материализация раз в час будет постоянно устаревшей.

Синтаксис в PostgreSQL

Создание материализованного представления:

CREATE MATERIALIZED VIEW mart_daily_gmv AS
SELECT
    DATE_TRUNC('day', created_at)::DATE AS day,
    SUM(amount) AS gmv,
    COUNT(DISTINCT user_id) AS buyers,
    AVG(amount) AS avg_check
FROM orders
WHERE status = 'paid'
GROUP BY 1;

-- Индекс на этом
CREATE INDEX ON mart_daily_gmv (day);

Это обычный SELECT, обёрнутый в CREATE MATERIALIZED VIEW. При выполнении запрос прогоняется, результат сохраняется.

Теперь дашборды могут запрашивать SELECT * FROM mart_daily_gmv WHERE day >= CURRENT_DATE - INTERVAL '30 day' — это быстрее чем агрегация на сырых orders.

Обновление: REFRESH

Материализованное представление само не обновляется. Данные в нём замороженные на момент последнего refresh. Чтобы пересчитать:

REFRESH MATERIALIZED VIEW mart_daily_gmv;

Эта команда заново выполняет SELECT и перезаписывает содержимое. Долго — зависит от размера данных.

Есть важный нюанс: стандартный REFRESH блокирует чтения на время пересчёта. Для production это обычно неприемлемо.

Решение — CONCURRENTLY:

REFRESH MATERIALIZED VIEW CONCURRENTLY mart_daily_gmv;

Эта команда не блокирует чтения. Но требует уникального индекса на представлении. Работает медленнее, зато без downtime для пользователей.

Стратегии refresh

Как часто обновлять — зависит от требований бизнеса.

По расписанию через Airflow / cron. Самый распространённый случай. Раз в час, раз в день, раз в неделю. Airflow DAG вызывает REFRESH MATERIALIZED VIEW после того, как ETL заливает свежие данные в source.

После триггера. Специальный триггер на source-таблицах запускает REFRESH при каждом INSERT. Не рекомендуется — REFRESH долгий, триггер будет блокировать каждый INSERT.

По требованию. Пользователь нажимает «обновить дашборд» — запускается REFRESH. Подходит для отчётов, которые смотрят редко.

Incremental refresh. PostgreSQL не поддерживает инкрементальное обновление «из коробки». Только full refresh. Но есть обходные пути — писать свою логику обновления через процедуры, которые пересчитывают только нужные части. Сложно, но эффективно для огромных представлений.

Разобраться, как правильно проектировать ETL и витрины данных — важная часть middle-роли. В тренажёре Карьерник есть задачи на архитектуру данных и оптимизацию аналитических запросов.

Materialized view vs обычная таблица

Спорный вопрос: чем материализованное представление лучше обычной таблицы, которую вы обновляете SQL-скриптом?

Формально — не особо. Обе хранят предрассчитанные данные. Разница:

Материализованное представление — часть DDL, определение SELECT живёт в схеме БД. Видно через \d+, понятно откуда берутся данные.

Обычная таблица — просто хранилище. Логика заполнения живёт в скрипте Airflow или dbt.

На практике в современных аналитических стеках чаще используют обычные таблицы + dbt-модели. Они лучше версионируются в git, имеют встроенные тесты, легче отлаживаются.

Материализованные представления остаются хороши для простых случаев или в PostgreSQL-ориентированных проектах без dbt.

Пример: ежедневные метрики продукта

Типичная витрина для продуктовой аналитики:

CREATE MATERIALIZED VIEW mart_daily_product_metrics AS
WITH active_users AS (
    SELECT
        event_time::DATE AS day,
        COUNT(DISTINCT user_id) AS dau
    FROM events
    WHERE event_name = 'app_open'
    GROUP BY 1
),
purchases AS (
    SELECT
        created_at::DATE AS day,
        COUNT(*) AS orders_count,
        SUM(amount) AS revenue,
        COUNT(DISTINCT user_id) AS paying_users
    FROM orders
    WHERE status = 'paid'
    GROUP BY 1
),
new_users AS (
    SELECT
        registered_at::DATE AS day,
        COUNT(*) AS new_signups
    FROM users
    GROUP BY 1
)
SELECT
    COALESCE(a.day, p.day, n.day) AS day,
    COALESCE(a.dau, 0) AS dau,
    COALESCE(p.orders_count, 0) AS orders,
    COALESCE(p.revenue, 0) AS revenue,
    COALESCE(p.paying_users, 0) AS paying_users,
    COALESCE(n.new_signups, 0) AS new_signups
FROM active_users a
FULL OUTER JOIN purchases p USING (day)
FULL OUTER JOIN new_users n USING (day);

-- Индекс для быстрого фильтра по дате
CREATE UNIQUE INDEX ON mart_daily_product_metrics (day);

Три CTE с разной логикой, объединённые FULL OUTER JOIN. На сырых данных каждое открытие дашборда считалось бы минуты. В материализованном виде — мгновенно.

Обновление раз в день:

-- Airflow task после ETL
REFRESH MATERIALIZED VIEW CONCURRENTLY mart_daily_product_metrics;

Ограничения

В PostgreSQL материализованные представления не могут:

Запрашивать другие материализованные представления в зависимостях, которые Postgres понимает для каскадного refresh. Нужно вручную организовывать порядок.

Использовать функции с VOLATILE маркировкой — например, NOW() или RANDOM(). Точнее, могут, но после refresh эти значения замораживаются.

Иметь first-class инкрементальный refresh. Только full.

Автоматически обновляться. Нужен внешний триггер (Airflow, cron).

Troubleshooting

Частые проблемы и решения.

REFRESH медленный. Смотрите EXPLAIN ANALYZE самого SELECT. Проблема обычно не в самом REFRESH, а в медленном запросе. Оптимизируйте его — добавьте индексы на source-таблицах, упростите JOIN.

REFRESH блокирует приложение. Используйте CONCURRENTLY вместо обычного REFRESH. Требует уникального индекса на MV.

Данные устарели, но REFRESH не запускается. Проверьте Airflow: DAG упал или отключен. Мониторьте SLA на обновление MV.

Размер MV растёт. Если выросло количество строк в source — ожидаемо. Если MV в разы больше, чем результат её SELECT — возможно, есть баг в определении.

Читайте также

• VIEW в SQL: шпаргалка
• Оптимизация SQL-запросов
• Что такое Data Mart
• Как настроить dbt-проект

FAQ

Materialized view или dbt-модель?

В современных стеках чаще dbt-модель как обычная таблица. Она версионируется в git, имеет тесты, документацию. MV — для старых Postgres-ориентированных проектов.

Можно ли строить MV на MV?

Да, технически. Но каскадный refresh надо делать вручную: сначала refresh базовой MV, потом той, что зависит от неё.

Как узнать, когда последний раз обновлялась MV?

Postgres не хранит эту информацию напрямую. Обычно добавляют колонку refreshed_at в MV (через SELECT NOW() as refreshed_at) или отдельную таблицу-лог refresh-ов.

REFRESH CONCURRENTLY всегда лучше?

Почти всегда, но медленнее обычного REFRESH (создаёт временную таблицу, сравнивает, применяет). Для маленьких MV или если пользователи не смотрят ночью — можно использовать обычный REFRESH.