Materialized views в PostgreSQL: как работают и когда использовать

← назад к разделу

Иногда один запрос к базе выполняется секунду, другой — пять, а третий — двадцать. Это почти всегда аналитика: COUNT, SUM, GROUP BY, несколько JOIN. Такой запрос не оптимизировать индексом — он по природе тяжёлый.

Materialized view — способ сохранить результат такого запроса на диске и отдавать его быстро, не пересчитывая каждый раз.

Что такое materialized view

Обычный VIEW — это просто сохранённый SQL. Каждый раз, когда вы к нему обращаетесь, PostgreSQL выполняет запрос заново.

MATERIALIZED VIEW работает иначе: PostgreSQL выполняет запрос один раз, сохраняет результат как таблицу на диске, и дальше читает из неё. Данные «устаревают» — но зато читаются мгновенно.

CREATE MATERIALIZED VIEW order_stats_mv AS
SELECT
    customer_id,
    count(*)          AS orders_count,
    sum(total_amount) AS total_spent,
    max(created_at)   AS last_order_at
FROM order_doc
WHERE status != 'CANCELLED'
GROUP BY customer_id
WITH DATA;

После этого запрос выглядит как обычный SELECT из таблицы:

SELECT * FROM order_stats_mv WHERE customer_id = 42;

WITH NO DATA создаёт пустой materialized view без немедленного вычисления — удобно при миграциях, когда хочется сначала создать структуру, а наполнить позже.

Когда materialized view помогает

Materialized view хорошо подходит в нескольких ситуациях:

• Тяжёлые агрегации, которые читаются часто, а небольшая задержка обновления допустима: отчёты, дашборды.
• Сложные JOIN по нескольким таблицам, результат которых меняется редко.
• Денормализация для чтения: вместо того чтобы каждый раз пересчитывать статистику, держите уже готовую.
• Предвычисленные поисковые индексы — например, когда нужно хранить заранее обработанные tsvector-векторы для полнотекстового поиска.

Materialized view не подходит, когда:

• данные меняются постоянно и нужна актуальность в реальном времени;
• запрос простой — достаточно обычного индекса;
• стоимость обновления materialized view выше, чем выигрыш от кэширования.

Индексы на materialized view

Materialized view — это таблица, и на неё можно создавать индексы так же, как на обычную:

CREATE INDEX ix_order_stats_customer ON order_stats_mv (customer_id);
CREATE UNIQUE INDEX uk_order_stats_customer ON order_stats_mv (customer_id);

Обычный индекс ускоряет поиск. Уникальный индекс нужен ещё по одной причине — он обязателен для REFRESH CONCURRENTLY, о котором дальше.

Как обновлять materialized view

Данные в materialized view не обновляются сами. Нужно явно вызвать REFRESH.

Простой REFRESH — только в окно обслуживания

REFRESH MATERIALIZED VIEW order_stats_mv;

Этот вариант перевычисляет всё заново и во время выполнения блокирует любые SELECT из этого view. На большой materialized view блокировка может длиться минуты. Подходит только для небольших view или в период, когда активных пользователей нет.

REFRESH CONCURRENTLY — стандартный вариант для продакшена

REFRESH MATERIALIZED VIEW CONCURRENTLY order_stats_mv;

Этот вариант не блокирует чтение. Пока идёт обновление, запросы к materialized view продолжают работать — они видят старые данные, но не ждут.

Как это работает: PostgreSQL вычисляет новый результат во временной структуре, затем сравнивает с текущими данными и применяет только разницу. Поэтому:

• нужен уникальный индекс — без него PostgreSQL не знает, как сопоставить строки;
• работает немного медленнее обычного REFRESH из-за этого сравнения.

Для продакшена — всегда CONCURRENTLY.

Инкрементальное обновление

PostgreSQL не умеет обновлять materialized view частично «из коробки». Либо всё, либо ничего.

Если нужно инкрементальное обновление, есть несколько вариантов:

• pg_ivm — расширение для PostgreSQL, которое добавляет инкрементальное обновление materialized view;
• триггеры на исходных таблицах с ручным обновлением нужных строк;
• TimescaleDB continuous aggregates — если TimescaleDB уже в стеке.

Как часто обновлять

Периодически по расписанию — для аналитики

Самый простой и надёжный вариант: обновлять раз в несколько минут независимо от изменений в данных. Подходит для дашбордов и отчётов, где небольшая задержка не критична.

@Component
class OrderStatsRefreshJob {

    private final JdbcTemplate jdbc;

    OrderStatsRefreshJob(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    @Scheduled(fixedDelay = 300_000)
    public void refreshOrderStats() {
        jdbc.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY order_stats_mv");
    }
}

Триггер на каждое изменение — слишком дорого

Можно поставить триггер на исходную таблицу, который запускает REFRESH после каждого INSERT, UPDATE или DELETE. Проблема: при активной записи триггер будет запускать тяжёлый REFRESH на каждую строку. Это убивает производительность.

Этот вариант оправдан только если изменения в исходной таблице очень редкие, а materialized view небольшая.

Debouncing через флаг dirty — золотая середина

Лучший вариант для ситуаций, когда данные меняются регулярно, но не постоянно: обновляйте materialized view только когда данные изменились, и не чаще одного раза в минуту.

Идея простая: при изменении данных ставим флаг «нужно обновить». Периодическая задача проверяет флаг и запускает REFRESH только если флаг выставлен.

@Component
class OrderStatsDebouncedRefresh {

    private final StringRedisTemplate redis;
    private final JdbcTemplate jdbc;

    OrderStatsDebouncedRefresh(StringRedisTemplate redis, JdbcTemplate jdbc) {
        this.redis = redis;
        this.jdbc = jdbc;
    }

    @EventListener
    public void onOrderChanged(OrderChangedEvent e) {
        redis.opsForValue().set("mv:order_stats:dirty", "1");
    }

    @Scheduled(fixedDelay = 60_000)
    public void refreshIfDirty() {
        Boolean deleted = redis.delete("mv:order_stats:dirty");
        if (Boolean.TRUE.equals(deleted)) {
            jdbc.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY order_stats_mv");
        }
    }
}

Флаг можно хранить в Redis, в памяти приложения или в любом другом быстром хранилище. Главное — не запускать REFRESH без повода и не чаще, чем нужно.

Materialized view или отдельная таблица-проекция

Иногда materialized view сравнивают с подходом read model из CQRS — отдельной таблицей, которая обновляется обработчиком событий.

Правило выбора простое: если нужен сложный SELECT ... GROUP BY с несколькими JOIN, данные читаются часто, и задержка в минуту допустима — materialized view. Если нужна минимальная задержка и обновление по строкам — отдельная таблица с обработчиком событий.

Частые ошибки

Использовать простой REFRESH на продакшене. Он блокирует чтение. Всегда используйте CONCURRENTLY.

Нет уникального индекса — нет CONCURRENTLY. REFRESH CONCURRENTLY упадёт с ошибкой, если уникального индекса нет. Создавайте его сразу при создании materialized view.

Триггер AFTER на каждый INSERT. При высокой нагрузке на запись это уничтожает производительность. Используйте debouncing или периодическое расписание.

Materialized view для данных реального времени. Если данные обновляются каждую секунду и нужна актуальность — это не задача для materialized view.

REFRESH из миграции базы данных. Миграция — не место для REFRESH. Обновляйте через scheduled job или вручную после деплоя.

Коротко

• Materialized view хранит результат тяжёлого запроса на диске — чтение мгновенное, но данные могут быть немного устаревшими.
• Для продакшена — всегда REFRESH MATERIALIZED VIEW CONCURRENTLY. Он не блокирует чтение.
• CONCURRENTLY требует уникальный индекс — создавайте его сразу.
• Простой REFRESH блокирует SELECT на всё время выполнения — только в окно обслуживания.
• Периодическое расписание (раз в 5 минут) — самый простой вариант для аналитики.
• Debouncing через флаг dirty — лучший вариант, когда нужно обновлять только при реальных изменениях.
• Триггер на каждое изменение — только для редких изменений и маленьких view.
• Materialized view не подходит для данных реального времени и для простых запросов без агрегаций.

Что почитать дальше

• Индексы в PostgreSQL — как индексировать materialized view.
• Полнотекстовый поиск в PostgreSQL — materialized view для поисковых индексов.
• Партиционирование — альтернатива для архивных данных.