Materialized view в PostgreSQL: ускорение отчётов без переписывания запросов

Прямой ответ

Materialized view (MV) — это сохранённый результат запроса, к которому можно обращаться как к таблице. Обычное представление (CREATE VIEW) лишь хранит текст запроса и вычисляется при каждом обращении; материализованное — физически хранит строки и обновляется явно командой REFRESH.

Когда MV решает задачу:

• отчётный запрос агрегирует миллионы строк, но меняется результат редко (раз в час, раз в сутки);
• один и тот же тяжёлый расчёт нужен нескольким потребителям — дашборду, выгрузке, API;
• нужно снять аналитическую нагрузку с OLTP-таблиц и не уносить данные в отдельное хранилище.

Базовая механика:

CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT date_trunc('day', created_at) AS day,
       region,
       sum(total) AS revenue,
       count(*)   AS orders_cnt
FROM orders
GROUP BY 1, 2
WITH DATA;

-- индекс обязателен для CONCURRENTLY и для быстрых выборок
CREATE UNIQUE INDEX mv_daily_sales_uq ON mv_daily_sales (day, region);
CREATE INDEX mv_daily_sales_day_idx  ON mv_daily_sales (day);

-- обновление без блокировки чтения
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;

Ключевые ограничения, о которых забывают:

1. Нет автоматического обновления. PostgreSQL не пересчитывает MV при изменении исходных таблиц. Нужен планировщик (cron, pg_cron, Airflow) или триггер.
2. CONCURRENTLY требует уникальный индекс. Без него остаётся только обычный REFRESH, который держит эксклюзивную блокировку и блокирует чтения на всё время пересчёта.
3. CONCURRENTLY медленнее обычного refresh. Он вычисляет разницу между старым и новым состоянием, поэтому на больших MV и при малом числе изменений может быть выгоднее обычный refresh в окне обслуживания.
4. MV не наследует индексы исходных таблиц. Все индексы для быстрого доступа к MV создаются отдельно.

Практика: выбор стратегии обновления

Вариант 1: полный refresh по расписанию. Подходит, если MV небольшая (миллионы строк), а допустимая задержка данных — часы. Просто, предсказуемо, легко мониторить.

Вариант 2: REFRESH ... CONCURRENTLY. Нужен, когда читатели не должны видеть блокировку. Условие — уникальный индекс, покрывающий всё содержимое MV.

Вариант 3: инкрементальное обновление. PostgreSQL не поддерживает его нативно, поэтому используют одну из схем:

• отдельная таблица-агрегат, которую обновляют триггеры на исходной таблице;
• пересчёт только «свежих» партиций с последующим INSERT ... ON CONFLICT;
• специализированные расширения (pg_ivm) для инкрементальных MV.

-- инкрементальный вариант: пересчёт только последнего дня
DELETE FROM agg_daily_sales WHERE day = CURRENT_DATE;
INSERT INTO agg_daily_sales
SELECT date_trunc('day', created_at), region, sum(total), count(*)
FROM orders
WHERE created_at >= CURRENT_DATE
GROUP BY 1, 2
ON CONFLICT (day, region) DO UPDATE
  SET revenue = EXCLUDED.revenue, orders_cnt = EXCLUDED.orders_cnt;

Практические рекомендации:

1. Измеряйте стоимость refresh. Записывайте длительность и объём прочитанных строк; если refresh занимает десятки минут, MV спроектирована неправильно — нужен инкрементальный подход или партиционирование.
2. Следите за блокировками. Обычный refresh берёт ACCESS EXCLUSIVE; если рядом работают длинные транзакции, возникает очередь блокировок. Диагностика — в статье про дедлоки и блокировки.
3. Индексы под запросы, а не «на всякий случай». Каждый индекс замедляет refresh. Правила выбора — в статье про индексы PostgreSQL.
4. Не храните в MV то, что должно быть актуально «сейчас». Для балансов и статусов подходит только прямой запрос к таблицам.

Практический сценарий: отчёт тормозит, а refresh блокирует API

Контекст: MV mv_daily_sales на 40 млн строк, обновляется обычным REFRESH раз в 15 минут. Во время обновления дашборд и публичный API получают таймауты.

1. Симптом. В pg_stat_activity виден REFRESH MATERIALIZED VIEW в состоянии active несколько минут; запросы к MV ждут AccessExclusiveLock.
2. Первая мера. Создаём уникальный индекс и переходим на REFRESH ... CONCURRENTLY — чтения перестают блокироваться.
3. Новая проблема. CONCURRENTLY занимает втрое больше времени обычного refresh, потому что вычисляет дельту по всей таблице.
4. Решение. Переходим на инкрементальную схему: MV уменьшаем до «последние 90 дней», историю уводим в отдельную таблицу-агрегат, которую обновляем раз в сутки. Refresh сокращается до 20 секунд.
5. Профилактика. Добавляем метрику длительности refresh и алерт на рост, а также проверку, что обновление не пересекается с пиковой нагрузкой. Планирование таких задач — в статье про Airflow.

Результат: блокировки исчезли, отчёт отвечает за 150 мс. Вывод: MV ускоряет чтение ценой усложнения обновления — стратегию refresh нужно выбирать осознанно и измерять, а не ставить «по умолчанию».

Как понять, что MV пора заменить другим подходом. Признаки: refresh стабильно занимает больше 10 % интервала обновления; данные нужны свежее, чем раз в несколько минут; число MV выросло настолько, что их обновление конкурирует за ресурсы с основной нагрузкой. В первом случае помогает инкрементальный пересчёт, во втором — отказ от MV в пользу прямого запроса с покрывающим индексом, в третьем — вынос аналитики в отдельный слой или колоночное хранилище.

FAQ

Чем материализованное представление отличается от обычной таблицы-агрегата? Функционально почти ничем: MV — это таблица с привязанным определением запроса и командой refresh. Преимущество MV — декларативность и единый способ пересчёта; преимущество обычной таблицы — полный контроль над инкрементальным обновлением и отсутствие ограничений CONCURRENTLY.

Можно ли построить MV поверх другой MV? Да, PostgreSQL это позволяет. Но refresh нужно выполнять в правильном порядке — от базовых к производным, иначе производная MV построится на устаревших данных. Порядок удобно задавать в DAG оркестратора.

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

Когда вместо MV лучше взять колоночное хранилище? Когда объёмы измеряются сотнями миллионов строк и выше, а запросы сканируют широкие диапазоны. В этом сценарии строковое хранение PostgreSQL упирается в объём чтения, и выигрыш даёт колоночная модель — см. OLAP-хранилища.

Источники: PostgreSQL docs (materialized views, CREATE/REFRESH MATERIALIZED VIEW) — ссылки в шапке.