Когда нужна предварительная агрегация
Выбирайте между агрегацией в реальном времени, материализованными представлениями и последующей обработкой в OLAP с учётом актуальности данных и стоимости
«Когда нужна предварительная агрегация» — бесплатный урок SQL Academy на CoddyKit. Это урок 4 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.
Три стратегии для агрегатных запросов
- В реальном времени — пересчитывать каждый раз
- Материализованная — сохранять результат и периодически обновлять
- С триггерами или кэшированием — инкрементально обновлять при каждом изменении
Агрегация в реальном времени
Простой и всегда актуальный вариант:
SELECT user_id, COUNT(*) FROM orders WHERE status = 'paid' GROUP BY user_id;Когда подходит агрегация в реальном времени
Запрос достаточно быстр (хорошие индексы, небольшой результат, редкие вызовы). По умолчанию выбирайте вычисление в реальном времени — оптимизируйте только после измерения проблемы.
Материализованная агрегация
Для дорогих отчётов, которым достаточно «разумной актуальности»:
CREATE MATERIALIZED VIEW user_revenue_30d AS
SELECT user_id, SUM(total) AS revenue
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY user_id;
-- Refresh nightly:
REFRESH MATERIALIZED VIEW CONCURRENTLY user_revenue_30d;Агрегация с триггерами и инкрементальным обновлением
Для панелей мониторинга в реальном времени поддерживайте сводную таблицу с помощью триггеров:
CREATE TABLE user_summary (
user_id BIGINT PRIMARY KEY,
order_count INT NOT NULL DEFAULT 0,
revenue NUMERIC(12,2) NOT NULL DEFAULT 0
);
CREATE FUNCTION incr_summary() RETURNS TRIGGER AS $$
BEGIN
INSERT INTO user_summary (user_id, order_count, revenue)
VALUES (NEW.user_id, 1, NEW.total)
ON CONFLICT (user_id) DO UPDATE
SET order_count = user_summary.order_count + 1,
revenue = user_summary.revenue + EXCLUDED.revenue;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_summary AFTER INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION incr_summary();Компромиссы
| Стратегия | Актуальность | Стоимость записи | Стоимость чтения |
|---|---|---|---|
| В реальном времени | Мгновенная | Отсутствует | Высокая |
| Материализованная | Устаревшая | Пакетное обновление | Низкая |
| С триггерами | Мгновенная | Для каждой записи | Низкая |
Выбор по соотношению чтения и записи
- Много записей, редкое чтение → вычисление в реальном времени (или пакетное материализованное представление)
- Много чтений, умеренное количество записей → материализованное представление
- Много чтений И записей, актуальность критична → сводная таблица с триггерами
Внешняя предварительная агрегация
Для аналитики масштаба хранилища данных перенесите агрегацию в:
- OLAP-базы данных (ClickHouse, Druid)
- модели dbt в отдельном хранилище
- непрерывные агрегаты TimescaleDB (расширение Postgres)
Сводные таблицы и материализованные представления
Пользовательские сводные таблицы позволяют выполнять инкрементальные обновления, а материализованные представления требуют полного REFRESH. Сопоставьте затраты на разработку с простотой эксплуатации.
Избегайте триггеров в часто изменяемых таблицах
Сводные данные на основе триггеров увеличивают задержку записи при каждой операции. Для таблиц с высокой частотой изменений (события, метрики) предпочитайте пакетное обновление материализованного представления.
Остерегайтесь недействительности кэша
«Есть только две действительно сложные вещи в CS». Сводные данные, обновляемые триггерами, являются кэшем. Ошибки в них проявляются в неправильных показателях панели мониторинга. Добавьте ежедневное задание сверки, которое пересчитывает данные из источника.
Материализация многоэтапных конвейеров
Связывайте материализованные представления в цепочку: этап 1 агрегирует события, этап 2 агрегирует результаты этапа 1. Выполняйте REFRESH по порядку.
Итоги
Выполняйте предварительную агрегацию, когда чтение становится основным источником затрат.
- В реальном времени → проще всего, данные всегда актуальны
- Материализованное представление → дорогой запрос, небольшое устаревание допустимо
- Сводная таблица с триггерами → данные всегда актуальны, но запись становится дороже
- Выбирайте стратегию с учётом соотношения чтения и записи
Быстрая проверка
У Вас есть панель мониторинга в реальном времени, которая должна показывать доход от пользователей с точностью до секунды. Какая стратегия подходит лучше всего?
Часто задаваемые вопросы
Урок «Когда нужна предварительная агрегация» бесплатный?
Да — полный текст урока «Когда нужна предварительная агрегация» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.
Чему я научусь в уроке «Когда нужна предварительная агрегация»?
Выбирайте между агрегацией в реальном времени, материализованными представлениями и последующей обработкой в OLAP с учётом актуальности данных и стоимости Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.
Нужен ли мне опыт, чтобы начать SQL Academy?
Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 4 из 4.
Сколько времени занимает урок «Когда нужна предварительная агрегация»?
Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.
Можно ли писать и запускать код в этом уроке SQL Academy?
Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.
Все уроки этого курса
- Обычные представления: логическое повторное использование
- Обновляемые представления и триггеры INSTEAD OF
- Материализованные представления и стратегии REFRESH
- Когда нужна предварительная агрегация