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

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

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

снимок лежит отдельно от таблицы и меняется только по REFRESH orders #1 500 #2 400 #3 600 #4 600 REFRESH CONCURRENTLY order_stats_mv id | заказов | сумма 42 | 3 | 1 500 снимок отстал от таблицы временная копия42 | 4 | 2 100 толькоразница42 | 4 | 2 100снимок догнал таблицу SELECT из order_stats_mv 3 заказа, 1 500 4 заказа, 2 100 отвечает мгновенно

Пока идёт REFRESH CONCURRENTLY, PostgreSQL считает новый результат во временной копии и применяет к представлению только разницу. Читатели всё это время получают старый снимок, но не ждут. Обычный REFRESH на том же месте заблокировал бы SELECT до конца пересчёта.

Обязательно

Что такое materialized view

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

живой пример

SELECT customer_id,
       count(*)          AS orders_count,
       sum(total_amount) AS total_spent,
       max(created_at)   AS last_order_at
FROM orders
WHERE status <> 'CANCELLED'
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 5;
Запустить

Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →

На сотне тысяч заказов он каждый раз читает таблицу целиком и считает агрегаты заново. Materialized view выполняет его один раз и кладёт результат на диск:

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 orders
WHERE status <> 'CANCELLED'
GROUP BY customer_id
WITH DATA;

После этого запрос выглядит как обычный SELECT из таблицы — ни count, ни sum, ни GROUP BY в нём больше нет, всё уже посчитано:

SELECT customer_id, orders_count, total_spent
FROM order_stats_mv
ORDER BY total_spent DESC
LIMIT 5;

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

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

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

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

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

Индексы на materialized view

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

CREATE UNIQUE INDEX uk_order_stats_customer ON order_stats_mv (customer_id);
CREATE INDEX ix_order_stats_spent ON order_stats_mv (total_spent DESC);

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

Второй индекс, по сумме, нужен уже под конкретный запрос — витрину «топ клиентов». А вот заводить рядом с уникальным ещё и обычный индекс по тому же customer_id не надо, хотя соблазн велик: искать он будет ровно так же, зато перестраивать его придётся на каждом REFRESH. Уникальный индекс работу обычного делает целиком.

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

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

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

REFRESH MATERIALIZED VIEW order_stats_mv;

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

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

REFRESH MATERIALIZED VIEW CONCURRENTLY order_stats_mv;

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

Для продакшена — CONCURRENTLY. Он дороже обычного REFRESH, но обычный держит представление под блокировкой на всё время пересчёта, и читатели ждут; лишние ресурсы на сравнение копий обходятся дешевле простоя чтения. Обычный REFRESH остаётся для окна обслуживания, когда читателей нет.

Три вещи, которые делают вместе с обновлением

Пересчитать статистику. Материализованное представление для планировщика — обычная таблица, но автоматический анализ ведёт себя с ним иначе, чем с таблицами, и после полного обновления статистика легко оказывается от старого содержимого. Отсюда «представление читается мгновенно… а иногда почему-то нет». Лечится строкой в том же задании: ANALYZE mv_daily_sales; сразу после обновления. Проверьте поведение на своей мажорной версии, но делать явно — надёжнее в любом случае.

Не запускать обновление в трёх экземплярах. @Scheduled на трёх подах — это три параллельных обновления одного представления: каждое берёт свою долю процессора и диска, а результат всё равно один. Выбор из трёх вариантов. Планировщик с внешней блокировкой (ShedLock) — если задание уже живёт в приложении. Транзакционный advisory-замок вокруг обновления — самый короткий путь: не взял замок, значит, кто-то уже обновляет, вышли молча. Или вынести расписание в базу через pg_cron — тогда экземпляр приложения вообще ни при чём.

живой пример

SELECT pg_try_advisory_xact_lock(hashtext('refresh_mv_daily_sales'));
Запустить

Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →

Убирать за собой. REFRESH … CONCURRENTLY применяет разницу как DELETE и INSERT — то есть оставляет мёртвые версии строк ровно как обычная таблица под нагрузкой. Представление, которое обновляется каждые пять минут, раздувается так же, как горячая таблица, и требует настроенной автоматической уборки; при большом проценте изменений ей стоит задать более агрессивные пороги, как описано в статье про VACUUM и bloat.

Сколько это стоит по диску

У CONCURRENTLY есть цена, которая решает дело на больших отчётах: на время обновления существуют две копии данных. Новая версия строится целиком рядом со старой, плюс временная структура для вычисления разницы, плюс индекс по ней. Для представления в тридцать гигабайт это значит, что на диске нужно свободными ещё как минимум столько же, и лучше с запасом.

Обычное обновление (без CONCURRENTLY) дешевле по диску, но берёт тяжёлую блокировку: читатели ждут всё время пересчёта. Выбор между ними — это выбор между «ждут читатели» и «нужно место», и на больших отчётах он часто решается в пользу отдельной таблицы-проекции, о которой ниже.

Как выбрать период обновления

Ориентир берут не из головы, а из двух чисел. Первое — сколько идёт само обновление; его замеряют (\timing в psql или время задания) на боевом объёме, а не на стенде. Второе — насколько устаревшие данные допустимы для того, кто смотрит отчёт; это вопрос к заказчику, и ответ обычно оказывается мягче, чем предполагают.

Правило: интервал между обновлениями должен быть заметно больше времени самого обновления — вдвое-втрое. Обновление на четыре минуты с расписанием «каждые пять» — это система, которая живёт в постоянном пересчёте и падает при первом же замедлении.

Что будет, если не успевает: при внешней блокировке следующий запуск просто пропускается (и это правильное поведение), без неё — запуски накладываются и начинают мешать друг другу. Заметить это можно только метрикой: пишите в журнал время каждого обновления и ставьте оповещение на «дольше половины интервала». Полезно и держать в самом представлении колонку с отметкой времени пересчёта — тогда устаревание видно прямо в отчёте, а не только в мониторинге.

Когда не брать

Два случая, в которых материализованное представление — неправильный выбор, и оба узнаются заранее.

Обновление дольше интервала. Если отчёт пересчитывается пятнадцать минут, а нужен «почти живой», никакое расписание не поможет: нужен либо инкрементальный пересчёт, либо проекция, которую обновляют событиями по мере изменения данных.

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

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

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

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

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

Дополнительно: при первом чтении можно пропустить

Глубже: WITH NO DATA — структура сейчас, наполнение потомрасширенное

У CREATE MATERIALIZED VIEW выше стоит WITH DATA: запрос считается прямо при создании, и на большой таблице это выходит боком.

Миграция создаёт представление на таблице в сто миллионов строк, и накат встаёт на двадцать минут, пока считается запрос. Для этого есть WITH NO DATA: оно создаёт пустой materialized view без вычисления, структура сейчас, наполнение позже, отдельной задачей. Читать такое представление нельзя: до первого REFRESH любой SELECT из него падает с ошибкой.

Глубже: Как устроен REFRESH CONCURRENTLY и инкрементальное обновлениерасширенное

Чтобы понять, откуда у CONCURRENTLY условия и цена, надо посмотреть, как он устроен внутри.

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

  • нужен уникальный индекс — без него PostgreSQL не знает, как сопоставить строки;
  • представление должно быть уже наполнено: вместе с WITH NO DATA указать CONCURRENTLY нельзя;
  • два обновления одного представления одновременно не идут — второе ждёт первое;
  • обычный REFRESH тратит меньше ресурсов и заканчивается быстрее: он просто пишет новый результат поверх старого. CONCURRENTLY вдобавок строит временную копию и сравнивает её со старой, и выигрывает там, где изменившихся строк мало.

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

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

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

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

Чем платят за инкрементальность, стоит сказать прямо. pg_ivm навешивает триггеры на все исходные таблицы и пересчитывает результат при каждом изменении — то есть переносит стоимость с расписания на запись: обычный INSERT в таблицу заказов начинает делать дополнительную работу. Плюс ограничения на вид запроса: поддерживаются не все конструкции, и сложный отчёт с оконными функциями или внешними соединениями может просто не подойти. 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 — золотая середина

Лучший вариант, когда данные меняются регулярно, но не постоянно: при изменении ставим флаг «нужно обновить», а периодическая задача раз в минуту смотрит на флаг и только по нему запускает REFRESH.

Механику видно и без базы. Список orders здесь — исходная таблица, snapshot — то, что лежит в materialized view, а tick() — та самая периодическая задача.

живой пример

import java.util.ArrayList;
import java.util.List;

public class RefreshDemo {
    static final List<Integer> orders = new ArrayList<>(List.of(500, 400, 600));
    static long snapshot = total();
    static boolean dirty = false;

    public static void main(String[] args) {
        for (int minute = 1; minute <= 4; minute++) {
            if (minute == 1 || minute == 3) {
                orders.add(600);
                dirty = true;
            }
            System.out.println("минута " + minute + ": в таблице " + total() + ", в снимке " + snapshot);
            tick();
        }
    }

    static void tick() {
        if (!dirty) {
            System.out.println("  REFRESH пропущен: данные не менялись");
            return;
        }
        snapshot = total();
        dirty = false;
        System.out.println("  REFRESH CONCURRENTLY: снимок стал " + snapshot);
    }

    static long total() {
        return orders.stream().mapToLong(Integer::intValue).sum();
    }
}
Запустить

Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →

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

Глубже: Materialized view или отдельная таблица-проекциярасширенное

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

Materialized viewRead model (отдельная таблица)
Логика обновленияSQL внутри PostgreSQLкод приложения
Гранулярностьвся view целикомпо отдельным строкам
Задержка обновлениясекунды — минутымиллисекунды (через события)
Сложностьнизкая (один SQL)выше (eventual consistency)
Когда выбиратьотчёты, агрегацииCQRS, минимальная задержка

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

Коротко

  • Materialized view хранит результат тяжёлого запроса на диске как таблицу: читается мгновенно, но показывает данные на момент последнего REFRESH. Обычный VIEW — просто сохранённый SQL, он выполняет запрос заново при каждом обращении.
  • Подходит для тяжёлых агрегаций и JOIN по нескольким таблицам, которые читают часто, а задержка в минуты допустима: отчёты, дашборды, заранее посчитанные tsvector. Не подходит, когда нужна актуальность в реальном времени или запрос простой — там хватает индекса.
  • Индексы на materialized view создают как на обычную таблицу. Уникальный индекс по обычным колонкам, без WHERE и без выражений, нужен сразу: он открывает REFRESH CONCURRENTLY. Дублировать его обычным индексом по той же колонке не надо.
  • Простой REFRESH MATERIALIZED VIEW пересчитывает всё заново и блокирует SELECT из представления на всё время пересчёта — только в окно обслуживания.
  • REFRESH MATERIALIZED VIEW CONCURRENTLY чтение не блокирует, но требует уникального индекса, места под вторую копию данных и плодит мёртвые версии (применяет разницу через DELETE и INSERT). Для продакшена — он, с настроенной уборкой.
  • WITH NO DATA создаёт представление без вычисления запроса, чтобы миграция не вставала на двадцать минут; до первого REFRESH любой SELECT из него падает с ошибкой.
  • Частичного обновления «из коробки» нет: либо всё, либо ничего. Инкрементальное — через расширение pg_ivm, триггеры на исходных таблицах или continuous aggregates в TimescaleDB.
  • Расписание раз в несколько минут — простой рабочий вариант, но на нескольких экземплярах его защищают внешней блокировкой (ShedLock, advisory-замок) или выносят в pg_cron; интервал держат вдвое-втрое больше времени самого обновления. REFRESH из миграции не запускают.
  • Минимальная задержка, обновление по строкам, пересчёт дольше интервала и представление поверх представления — не задача materialized view: там нужна отдельная таблица-проекция с обработчиком событий.
  • После обновления делают ANALYZE — статистика представления автоанализом не поддерживается так, как у таблиц.

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