Агрегаты и витрины в PostgreSQL: когда считать заранее

Если отчёт каждый раз группирует миллионы заказов, ускорение запроса не отменит сам расчёт: при каждом открытии сервер заново читает и складывает строки. В PostgreSQL 16 можно перенести эту работу на расписание и читать уже готовые итоги — но за выигрыш во времени придётся заплатить задержкой обновления и более сложной поддержкой данных.

Что именно предрассчитывать

Возьмём отчёт с количеством оплаченных заказов и суммой продаж по дням. Исходная таблица orders содержит order_id, created_at типа timestamptz, status и amount. Обычный запрос каждый раз отбирает оплаченные заказы, группирует их по дате и суммирует суммы. Чем длиннее период и больше строк, тем дороже повторение этой работы.

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

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

Три варианта — и цена каждого

Предрасчёт ускоряет чтение не бесплатно: вы меняете время ожидания запроса на задержку данных и стоимость их обновления.

Быстрый старт: материализованное представление

Для PostgreSQL 16 это самый простой способ проверить, поможет ли предрасчёт: определить итоговый запрос, создать материализованное представление и обновлять его по расписанию. В примере дата считается в UTC. Если бизнес отчётности живёт в другом часовом поясе, замените UTC на его правила до группировки: иначе заказ около полуночи может попасть не в тот день.

CREATE MATERIALIZED VIEW daily_paid_sales AS
SELECT
    (created_at AT TIME ZONE 'UTC')::date AS sales_day,
    count(*) AS order_count,
    sum(amount) AS gross_amount
FROM orders
WHERE status = 'paid'
GROUP BY (created_at AT TIME ZONE 'UTC')::date;

CREATE UNIQUE INDEX daily_paid_sales_day_idx
    ON daily_paid_sales (sales_day);

Читатель отчёта обращается уже к компактному результату:

SELECT sales_day, order_count, gross_amount
FROM daily_paid_sales
WHERE sales_day >= CURRENT_DATE - 29
ORDER BY sales_day;

Обновить данные можно так:

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_paid_sales;

Для CONCURRENTLY представление должно быть уже заполнено, а на нём должен существовать уникальный индекс, который однозначно идентифицирует строки. В примере одна строка соответствует одному дню, поэтому уникальность задаёт sales_day. Конкурентное обновление позволяет читать представление во время пересчёта, но не превращает обновление в инкрементальное: PostgreSQL по-прежнему пересчитывает весь результат. Одновременно обновлять одно и то же представление несколько раз нельзя.

Когда выбирать каждый подход

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

Для materialized view расписание задаёт внешняя система или планировщик, который запускает SQL-команду. Частоту выбирайте от допустимой задержки: если отчёт должен отставать не более чем на 15 минут, обновление раз в час уже не подходит. Это ограничение нужно проверять по фактическому времени пересчёта, а не только по интервалу запуска.

Что ломается первым

Старые данные принимают за текущие

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

Исправление заказа не попадает в итог

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

Число строк раздувается из-за неверной детализации

Витрина по дням и категориям содержит больше строк, чем витрина только по дням. Если бездумно добавить клиента, товар и регион, число комбинаций может вырасти настолько, что выигрыш от предрасчёта уменьшится. Храните только те измерения, по которым отчёт действительно должен фильтровать или группировать.

Обновление мешает соседним задачам

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

Что проверить дальше

Зафиксируйте три требования: допустимую задержку данных, детализацию отчёта и время, за которое витрина должна обновиться. Затем сравните исходный запрос и чтение из представления на реальном объёме данных. Для PostgreSQL 16 отдельно проверьте план запроса и длительность REFRESH MATERIALIZED VIEW; при переходе на другую версию сверьтесь с документацией именно для неё, особенно если меняются настройки обновления или используемый способ доставки данных.

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