Когда создание промежуточной агрегированной (материализованной) таблицы для дневных метрик оправдано, а когда достаточно обычного запроса "на лету"?

Короткий ответ

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

Как это работает подробнее

материализованное представление (MATERIALIZED VIEW в PostgreSQL, таблица, обновляемая по расписанию, или материализованная вью в BigQuery) физически сохраняет результат агрегации, избавляя от повторного пересчёта по сырым данным при каждом обращении; обновление такой таблицы требует отдельного процесса (REFRESH MATERIALIZED VIEW, scheduled query и т.п.), который должен быть надёжным и мониториться отдельно.

Пример кода

question-68.sqlКопировать

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT DATE_TRUNC('day', order_date) AS day, SUM(amount) AS revenue
FROM orders
GROUP BY 1;
-- Обновление по расписанию:
REFRESH MATERIALIZED VIEW daily_revenue;

Что использовать на практике

• Практика. выбор — это компромисс между скоростью чтения и свежестью данных плюс дополнительной инфраструктурной сложностью (ещё один процесс, который может сломаться, ещё один источник расхождений между «сырыми» и «агрегированными» цифрами, если они временно не синхронизированы). Для не слишком больших объёмов и редких запросов материализация часто избыточна.
• Проверка. Проверка: после REFRESH сравнить несколько строк daily_revenue с прямым агрегатом по orders за те же дни — они должны совпадать на момент обновления.

Подводные камни

• Частая ошибка. материализуют агрегаты без чёткого процесса инвалидации/обновления, из-за чего дашборд незаметно показывает устаревшие цифры; либо, наоборот, пересчитывают на лету очень тяжёлый запрос при каждом открытии дашборда, создавая ненужную нагрузку и задержку для пользователей.