Как оптимизировать хранение и запросы к историческим данным с агрегацией по месяцам, неделям и дням

Что проверяют

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

Как отвечать

Каркас из четырёх решений:

• сырые данные с партиционированием по времени: в PostgreSQL декларативное PARTITION BY RANGE (created_at) помесячно. Запрос за период читает только нужные партиции, а старые данные удаляются через DROP или DETACH партиции вместо долгого DELETE. Для таблиц, куда только дописывают, по времени хорошо работает компактный BRIN-индекс;
• роллапы: отдельная таблица дневных агрегатов, куда фоновая задача пишет count, sum, min, max через INSERT ... ON CONFLICT DO UPDATE. Недели и месяцы считаются уже из дней, а это 365 строк в год вместо миллионов. Среднее храните как сумму и количество, иначе его не свернуть корректно. Уникальные значения не складываются, для них нужен HyperLogLog или отдельный подсчёт;
• готовые механизмы: в TimescaleDB это hypertables, time_bucket() и continuous aggregates, в ClickHouse таблица с PARTITION BY toYYYYMM(ts) и материализованное представление в SummingMergeTree или AggregatingMergeTree;
• хранение по уровням: сырые события держим, например, 90 дней, дневные агрегаты годами.

Сторона Go: вставка пачками через CopyFrom в pgx или батчи клиента ClickHouse, пересчёт роллапов в воркере по time.Ticker с context и таймаутом. Чего точно не делать: вытаскивать миллионы строк в сервис, чтобы сгруппировать их в map.

Где спотыкаются

Игнорируют часовые пояса. День в UTC и день в Москве это разные границы, а date_trunc('day', ...) по timestamptz режет по часовому поясу сессии. Пояс для роллапов надо выбрать заранее.

Пытаются собрать неделю из месяцев. Неделя пересекает границу месяца, собирать её надо из дней. К тому же date_trunc('week', ...) начинает неделю с понедельника, и это стоит согласовать с продуктом.

Не думают об опоздавших событиях. Если событие пришло через сутки, агрегат за вчера нужно пересчитать, поэтому роллап должен уметь перезаписывать бакет, а не только дописывать новые.