Предрасчёт тяжёлых отчётов через материализованные представления и его пределы

Дашборд с отчётом загружается восемь секунд, и каждое открытие заново перемалывает соединения пяти таблиц и агрегаты по миллионам строк. Очевидное лекарство это посчитать всё заранее и сохранить результат, чтобы запрос отдавал готовое за миллисекунды. В PostgreSQL для этого есть материализованные представления, и они действительно ускоряют чтение в разы. Но за удобство приходится платить, а на определённом масштабе они начинают проигрывать специализированным аналитическим базам. Разберём стратегии обновления, грабли, на которые наступают почти все, и честно покажем границу, за которой стоит смотреть в сторону ClickHouse.

Чем материализованное представление отличается от обычного

Обычное представление это просто сохранённый запрос: каждое обращение к нему заново выполняет лежащий под ним код. Материализованное представление это снимок результата запроса, физически сохранённый на диск. Когда вы обращаетесь к нему, вы читаете уже посчитанные данные, а не пересчитываете их на лету. Для тяжёлого отчёта с соединениями и агрегатами разница колоссальная: тяжёлая работа делается один раз, а сотни чтений потом идут по готовому.

Доступ можно ускорить ещё сильнее, навесив на материализованное представление индексы. Как и обычные таблицы, оно их поддерживает. Звучит замечательно, но есть подвох, и он фундаментальный. Материализованное представление не обновляется автоматически вслед за исходными данными. Чтобы привести его в соответствие с источником, его нужно явно обновить командой обновления. И вот тут начинается самое интересное.

Почему наивное обновление блокирует читателей

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

REFRESH MATERIALIZED VIEW report_summary;

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

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

Чтобы не блокировать читателей, PostgreSQL предлагает конкурентное обновление. Оно позволяет читать представление прямо во время пересчёта.

REFRESH MATERIALIZED VIEW CONCURRENTLY report_summary;

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

Есть и менее очевидные сложности. Конкурентное обновление всё равно должно дождаться завершения активных транзакций, прежде чем начать. Если в базе есть долго работающие запросы, обновление застрянет в ожидании. А частые конкурентные обновления со временем порождают раздувание таблицы и индексов, что требует агрессивной настройки автоматической очистки или периодического обслуживания.

Какой приём с подменой представления спасает в тяжёлых случаях

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

-- Шаг 1: создаём новое представление
CREATE MATERIALIZED VIEW report_summary_new AS
SELECT ... FROM source_tables ...;

-- Шаг 2: быстрая подмена через переименование
BEGIN;
ALTER MATERIALIZED VIEW report_summary     RENAME TO report_summary_old;
ALTER MATERIALIZED VIEW report_summary_new RENAME TO report_summary;
COMMIT;

-- Шаг 3: убираем старое
DROP MATERIALIZED VIEW report_summary_old;

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

Как автоматизировать обновление и какой график выбрать

Раз представление не обновляется само, кто-то должен запускать обновление по расписанию. Удобнее всего делать это внутри самой базы через планировщик задач, чтобы не плодить внешний крон и не следить за ним отдельно.

-- Обновлять каждый час
SELECT cron.schedule(
    'refresh-report',
    '0 * * * *',
    $$REFRESH MATERIALIZED VIEW CONCURRENTLY report_summary$$
);

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

Главная боль, на которой обжигаются почти все

Тут кроется ограничение, ради которого стоит читать всю статью. Материализованное представление не умеет частичного обновления. Даже если в исходных таблицах изменилась одна-единственная строка, всё представление пересчитывается целиком. Нет механизма обновить только изменившиеся куски.

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

Что такое инкрементальное обновление и почему оно пока сырое

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

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

Где материализованные представления проигрывают ClickHouse

И вот честная граница. PostgreSQL это транзакционная база, дающая реальные точные результаты по известным шаблонам запросов. ClickHouse это аналитическая база, заточенная под быстрый сбор агрегатов по огромным датасетам. Сравнивать их в лоб почти так же странно, как виноград и грейпфрут, потому что созданы они под разные цели.

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

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

Где же тогда место материализованных представлений PostgreSQL? Там, где данные меняются не непрерывно, а порциями, и где приемлемо обновление по расписанию. Отчёт по результатам тестирования можно посчитать один раз после загрузки всех данных. Сводку, которой достаточно часовой свежести, спокойно держит почасовое обновление. PostgreSQL великолепен в поиске отдельных записей по известному шаблону, тогда как ClickHouse царит на сборе агрегатов из большого датасета.

Какой стратегии придерживаться

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

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