Как ускорить обновление большой materialized view

— Вась, ты чего такой хмурый? — спросил Макс, заметив коллегу у кофе-поинта.

— Да вот, дали задачу, — вздохнул Вася. — Есть матвью, которая рефрешится почти полтора часа. А бизнес требует обновлять её каждые 10 минут, потому что, — он противным голосом передразнил Евгения Вадимовича, — «нам нужны максимально оперативные данные». А там данные по проводкам за 5 лет, оптимизировать уже некуда.

— Классика, — кивнул Макс. — Сделай им отдельную матвью и отдельный отчет на оперативном периоде.

— Если бы так просто. Требуют, чтобы данные были все в одном месте.

— Ну так и сделай им «одно место», но из двух частей, — не сдавался Макс. — Раздели одну большую матвью на историческую, где данные почти не меняются, и оперативную за последнюю неделю. Историю обновляй редко, оперативную — часто. Это не настоящий incremental refresh: PostgreSQL всё равно полностью пересчитает каждую выбранную materialized view, зато пятилетняя часть перестанет обновляться каждые десять минут.

— Это мысль! — оживился Вася. — А основную матвью заменить на VIEW, которая их через UNION ALL объединяет! Но как избежать дублей и дыр в данных на стыке?

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

-- 1. Исторические данные
-- Обновляется редко (раз в сутки)
CREATE MATERIALIZED VIEW mv_historical AS
SELECT * FROM source_transactions
WHERE operation_date < (CURRENT_DATE - INTERVAL '7 days');

-- 2. Оперативные данные (с буфером в 1 день)
-- Обновляется часто
CREATE MATERIALIZED VIEW mv_current AS
SELECT * FROM source_transactions
WHERE operation_date >= (CURRENT_DATE - INTERVAL '8 days');

— Отлично, с матвью разобрались. А как теперь их правильно “склеить” во VIEW, чтобы не было дублей из-за буферного дня? — спросил Вася. — UNION без ALL не подойдет, т.к. объем данных огромный, производительность просядет сразу.

— А вот тут у нас есть два пути, — Макс нарисовал на доске две стрелки. — Выбирай любой.

— Первый способ — самый очевидный, — начал Макс. — Мы просто жёстко задаём границу во VIEW с помощью CURRENT_DATE.

CREATE OR REPLACE VIEW v_full AS
-- Берем все из оперативной части
SELECT * FROM mv_current
WHERE operation_date >= CURRENT_DATE - INTERVAL '8 days'; -- исключаем буферные данные
UNION ALL
-- И добавляем историю, отсекая по той же границе
SELECT * FROM mv_historical
WHERE operation_date < (CURRENT_DATE - INTERVAL '8 days');

— Выглядит просто, — кивнул Вася. — И должно быть быстро, планировщик легко поймет такое условие. А почему исключаем буферные данные из обеих матвью? тут нет ошибки?

— Тут есть нюанс, — улыбнулся Макс. — Однодневное пересечение страхует только от ограниченной задержки refresh. Если историческая часть не обновлялась дольше буфера или две части отражают разные срезы источника, появятся дыры либо несогласованные значения. Граница должна храниться и мониториться как состояние процесса, а не молча зависеть от настенных часов.

— Есть еще и второй путь, — продолжил Макс. — Границу отсечения брать из самих данных.

CREATE OR REPLACE VIEW v_full_safe AS
-- Берем все из оперативной части
SELECT * FROM mv_current
UNION ALL
-- А историю отсекаем по МИНИМАЛЬНОЙ дате из оперативных данных
SELECT * FROM mv_historical h
WHERE h.operation_date < (SELECT min(t.operation_date) FROM mv_current t);

— Понял! — воскликнул Вася. — Граница определяется данными из оперативной матвью. Но… этот подзапрос в WHERE… Он же может свести с ума планировщик на сложных отчётах, верно?

— В точку, — одобрительно кивнул Макс. — Этот подзапрос может усложнить оценку объёма данных. Кроме того, при пустой mv_current выражение вернёт NULL и скроет всю историю. Нужен как минимум явно определённый fallback, а лучше — отдельная проверенная граница refresh.

Вася задумчиво посмотрел на доску.

— Так что же выбрать? Какой способ лучше?

— А это тебе и предстоит решить, — улыбнулся Макс. — Проверь не только EXPLAIN ANALYZE, но и сценарии запоздавших данных, исправлений в истории, пустого оперативного окна и сбоя между двумя refresh. Нужны метрики возраста обеих частей и тесты на дубли и пропуски. Нет идеальных рецептов, есть только явно зафиксированные границы и проверенные компромиссы.