0008. Хранение временных рядов: PostgreSQL 16 + native range-partitioning, без TimescaleDB

Копия ADR из репозитория: backend/docs/adr/0008-time-series-storage.md. Канонический текст — в репозитории; портал обновляется командой npm run sync:adr.

Статус: accepted
Дата: 2026-06-09
Связанные задачи: OP-40 (Spike: стратегия хранения временных рядов и истории), OP-86 (правила объединения данных РНП), OP-4 (F4 · Платформа данных и хранилище)

Контекст и проблема

ТЗ §5.6/9.3: единая аналитика по товару требует копить ежечасно/ежедневно продажи, рекламные метрики, конверсии, остатки и т.п. ТЗ прямо отмечает: «Структура хранения данных: уточняется». Возможные источники одной и той же метрики — несколько эндпоинтов WB (orders/sales/statistics), приходящие с разной задержкой и точностью; late-arriving данные за «вчера/прошлую неделю» — норма.

Ограничения и требования:

Phase-1 — 10–100 селлеров. При оценочных ~10 тыс. SKU на селлера и ежечасной гранулярности — 10⁷–10⁸ строк/день суммарно по доменам.
PostgreSQL — обязательно один. Внешняя аналитическая БД (ClickHouse и аналоги) на phase-1 не вводится: лишний рантайм, лишняя репликация, потерян ACID-стейтмент с справочниками. Pg уже в стеке (ADR-0001).
Coolify managed PG. Базовый образ — postgres:16-alpine без extension'ов. Любая зависимость от внешних extensions требует подмены образа и теряет встроенные апдейты Coolify.
Idempotent ingestion. WB-эндпоинты могут вернуть один и тот же отрезок повторно (in-flight ingestion retry, рестарт воркера). Запись повторного payload не должна задвоить факт.
Late-arriving data. Витрина (OP-86) должна уметь пересчитать дневной агрегат «вчера» при поступлении нового сырого payload без полного rebuild.
Партиционирование как операция, а не как ручная миграция. Создание/дроп партиций — задача планировщика (OP-42), а не миграции.

Из обсуждения с заказчиком (2026-06-09):

SLA на запрос карточки не зафиксировано — берём ориентир p95 < 2 с.
Юридических требований к сроку хранения сырых данных нет.
TimescaleDB на phase-1 не вводим.
Менеджер партиций — собственный, поверх ScheduleRegistryService (ADR-0009).

Рассмотренные варианты

Где живут TS-данные

Vanilla PostgreSQL 16 + native declarative partitioning.
- Нет новых extension'ов; работает в базовом образе Coolify.
- Полная поддержка PARTITION BY RANGE, partition pruning по period_start, BRIN-индексы для time-range scan'ов.
- Простая миграционная траектория на Timescale при росте объёмов: имена столбцов и индексов выбираются так, чтобы create_hypertable(...) накатывался без переделки схемы. − Нет нативной компрессии — диск будет линейно расти. − Continuous aggregates руками: переписываем дневные/недельные роллапы своим upsert-воркером.
TimescaleDB extension (timescale/timescaledb:latest-pg16).
- Hypertables, continuous aggregates, columnar compression «из коробки».
- Готовая идиоматика для TS — меньше своего кода. − Compression с 2024 — Timescale Community License (не Apache 2). Платформа становится vendor-locked. − Подмена образа PG в Coolify ломает managed-апдейты. − Для 10–100 селлеров на phase-1 ценность compression не оправдывает добавленный operational footprint.
Отдельная аналитическая БД (ClickHouse / DuckDB / Citus).
- Производительность на больших объёмах кратно выше. − Отдельная инфра, отдельная репликация, отдельные ADR-доменные сервисы, отдельный operational track. Overkill для phase-1.

Стратегия партиционирования

RANGE по period_start, месячные партиции.
- Партиций мало (12/год × 3 года = 36) — планнер быстрый.
- Создание/дроп — раз в месяц, ложится на scheduler-джоб.
- При росте объёмов — переход на недельные партиции = одна миграция, совместима по запросам.
RANGE по period_start, недельные партиции.
- Меньше latency на DROP старых партиций, меньше «остаточного» места. − В 5× больше партиций; на phase-1 оправдания нет.
HASH по account_id.
- Изоляция «горячих» tenant'ов. − Теряем естественный partition-pruning по времени, которым пользуется любой TS-запрос. Кандидат на phase-2 при селлер-counte >1000.

Менеджер партиций

pg_partman extension.
- Зрелое решение, retention/pre-create встроены. − Extension; не гарантировано в Coolify managed PG; добавляет ещё одну операционную зависимость.
Свой менеджер поверх ScheduleRegistryService (ADR-0009).
- Используем уже работающий планировщик; cron 0 3 * * 1 — pre-create партиций на 3 месяца вперёд + DROP за пределами ретенции.
- 100–150 строк TypeScript + миграция, всё в репозитории. − Логика партиционирования живёт в коде, а не в БД — потенциальный риск рассинхрона при ручных изменениях схемы. Mitigation: миграции — единственный путь изменения партиционируемых таблиц.

Роллапы (день/неделя/месяц)

Materialized View + REFRESH MATERIALIZED VIEW CONCURRENTLY.
- Одна сущность — гарантированная консистентность с источником. − Каждый refresh — полная перезапись слайса; для late-arriving данных приходится рефрешить «вчера» и «позавчера» целиком. − CONCURRENTLY требует UNIQUE индекс и держит долгий exclusive lock на swap; на больших объёмах это деградирует.
Таблицы-агрегаты + idempotent upsert из worker'а.
- Точечный апдейт затронутых (account_id, nm_id, day) — масштабируется линейно по объёму изменений, а не по объёму витрины.
- Late-arriving raw → отдельная BullMQ-джоба «перепрогон агрегата по набору ключей» (re-aggregate). Просто рассуждать, просто тестировать.
- Версионирование роллап-схемы развязано от raw. − Больше своего кода: маппер raw → daily, политики merge (OP-86).

Идемпотентный ingestion

PK (account_id, nm_id, source, period_start) + ON CONFLICT … DO UPDATE … WHERE excluded.source_updated_at > existing.source_updated_at.
- Повторный payload идентичен или новее — записываем; устаревший — игнорим.
- raw_payload_hash фиксируется для отладки, но не участвует в PK (хеш меняется при микро-различиях формата без смены смысла).
Append-only с дедупом на чтении.
- Чистая семантика истории «что мы видели и когда». − В разы дороже по диску и по чтению; для phase-1 неоправданно.

Решение

Хранилище: vanilla PostgreSQL 16, без TimescaleDB

Все факты и агрегаты живут в основной БД. Образ — postgres:16-alpine (как сейчас). Никаких extension'ов, кроме встроенных. Дизайн схемы и индексов выбран так, чтобы переход на Timescale (если понадобится) превращался в create_hypertable(...) + перенастройку BRIN-индексов, без переделки запросов или ingestion-логики.

Партиционирование: RANGE по времени, месячные партиции

Каждая fact-таблица:

CREATE TABLE sales_facts (
  account_id        uuid        NOT NULL,
  nm_id             bigint      NOT NULL,
  source            text        NOT NULL,          -- 'orders' | 'sales' | 'statistics' | ...
  period_start      timestamptz NOT NULL,          -- начало часового окна (UTC)

  -- Доменные метрики:
  orders_count      int,
  sold_count        int,
  gross_revenue     numeric(12,2),
  net_revenue       numeric(12,2),
  returns_count     int,

  -- Provenance:
  source_updated_at timestamptz NOT NULL,          -- момент по данным WB
  raw_payload_hash  bytea       NOT NULL,          -- для аудита, не для PK
  ingested_at       timestamptz NOT NULL DEFAULT now(),

  PRIMARY KEY (account_id, nm_id, source, period_start)
) PARTITION BY RANGE (period_start);

CREATE INDEX brin_sales_period   ON sales_facts USING BRIN (period_start);
CREATE INDEX sales_card_lookup   ON sales_facts (account_id, nm_id, period_start DESC);
CREATE INDEX sales_account_range ON sales_facts (account_id, period_start, nm_id);

-- Месячные партиции; создаются менеджером партиций, не миграциями:
CREATE TABLE sales_facts_2026_06 PARTITION OF sales_facts
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

Аналогично для ad_facts, conv_facts, stock_facts — каждая таблица домена живёт в своём модуле, со своим набором доменных полей; общие — те, что входят в PK и provenance.

Время — UTC для raw-данных. Конвертация в Europe/Moscow — слой презентации (контроллер/витрина).

Менеджер партиций: собственный, через ADR-0009

Сервис PartitionManagerService регистрирует в ScheduleRegistryService расписание 0 3 * * 1 (понедельник, 3:00 UTC). Каждый запуск:

Для каждой партиционированной таблицы из реестра считает текущий месяц и проверяет наличие партиций на ближайшие 3 месяца. Отсутствующие — создаёт CREATE TABLE … PARTITION OF … FOR VALUES FROM (…) TO (…).
Перечисляет партиции из pg_partitions, для каждой парсит границу FROM и сравнивает с retention для данной таблицы. Партиции, чьи данные полностью за пределами retention, — DETACH + DROP.

Список партиционируемых таблиц и их retention хранится в коде (декларативный реестр модулей-владельцев). Менеджер не лезет в незнакомые таблицы.

DDL-операции идут в той же БД-сессии, anti-overlap наследуется от ADR-0009 (детерминированный jobId по scheduledAt). Логи — структурные (OP-44), успехи/ошибки публикуются как метрики (partitions_created_total, partitions_dropped_total, partition_manager_errors_total).

Роллапы: таблицы-агрегаты + idempotent upsert (НЕ MV)

Для каждой fact-таблицы — своя dau-таблица:

CREATE TABLE sales_daily (
  account_id   uuid    NOT NULL,
  nm_id        bigint  NOT NULL,
  day          date    NOT NULL,
  -- объединённые поля (правила merge — OP-86):
  orders_count_total  int,
  sold_count_total    int,
  gross_revenue_total numeric(12,2),
  ...
  recomputed_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (account_id, nm_id, day)
) PARTITION BY RANGE (day);

Воркер-роллапер слушает событие «raw обновлён в окне (account_id, day)» и делает UPSERT строки sales_daily на основе пересчёта по сырым строкам за этот день. Late-arriving raw → новая re-aggregate джоба → точечный upsert.

Materialized View не используем по двум причинам: late-arriving rebuild слайса дорогой, и CONCURRENTLY упирается в UNIQUE-индекс и swap-lock.

Недельные/месячные агрегаты — отдельные таблицы с тем же подходом, считаются из sales_daily (не из raw).

Идемпотентный upsert

Все fact-таблицы получают данные через единый паттерн:

INSERT INTO sales_facts (...) VALUES (...)
ON CONFLICT (account_id, nm_id, source, period_start)
DO UPDATE SET
  orders_count = excluded.orders_count,
  ...,
  source_updated_at = excluded.source_updated_at,
  raw_payload_hash  = excluded.raw_payload_hash
WHERE excluded.source_updated_at > sales_facts.source_updated_at;

Это даёт безопасный retry на стороне ingest-воркера: повторная доставка того же payload — no-op (по условию WHERE); новый payload — побеждает.

Ретенция

Слой	TTL	Механизм
`*_facts` (час, raw)	90 дней	DROP партиций
`*_daily` (день)	2 года	DROP партиций
`_weekly` / `_monthly`	бессрочно	—

Цифры — стартовая позиция; пересмотр — при первом запросе на «исторические данные глубже 90 дней» или при упоре в стоимость диска.

Триггеры пересмотра ADR (когда возвращаемся к Timescale/ClickHouse)

ADR пересматривается, если выполняется любой из критериев:

p95 запроса карточки товара > 2 с или
размер raw-слоя > 100 GB или
стоимость диска входит в топ-3 строк инфра-бюджета или
селлер-count > 1000.

В этом случае открывается новый spike, и ADR-0008 либо superseded by, либо дополняется. До этого момента — vanilla PG.

Последствия

Положительные:
- Никаких новых extension'ов; managed PG в Coolify продолжает работать с дефолтным образом.
- Дизайн схемы (PK, имена столбцов, BRIN на period_start) совместим с create_hypertable(...) — миграция на Timescale механическая.
- Партиционирование — операция планировщика, не миграции; не блокирует деплой и не требует ручных шагов.
- Идемпотентный ingest безопасен по построению — retry-логика воркеров (ADR-0006/0007) не задвоит факты.
- Роллап-таблицы дают линейную стоимость пересчёта по объёму изменений, а не по объёму витрины.
Отрицательные / компромиссы:
- Нет встроенной компрессии; сырое хранение растёт линейно. Mitigation — ретенция 90 дней + переход на Timescale при пересечении триггера.
- Свой менеджер партиций — наш код, не extension. Любое ручное изменение DDL партиционируемых таблиц вне миграций — риск рассинхрона. Mitigation — миграции единственный путь, code-review.
- Роллап-таблицы требуют merge-логики (OP-86) — это правила объединения, не просто SUM(...). Воркер-роллапер пишется один раз, переиспользуется каждым доменом.
- Партиционирование по времени без HASH по account_id означает: один селлер с аномально большим SKU-портфелем может «нагреть» партицию. Для phase-1 (10–100 селлеров) — приемлемо; пересмотр — при >1000.
Что нужно сделать дальше (последующие тикеты):
- OP-86 — правила объединения данных РНП. Витрина (OP-82) поверх этой схемы. Зависит от текущего ADR.
- OP-82 — реализация витрины РНП (sales_daily/ads_daily/...).
- OP-91 — «Партиционирование fact-таблиц и менеджер партиций»: PartitionManagerService, реестр модулей-владельцев, метрики, документация в Compodoc.
- OP-92 — «Re-aggregate worker для дневных агрегатов»: очередь и воркер пересчёта *_daily при поступлении/обновлении raw.
- Phase-2: spike на Timescale/ClickHouse при срабатывании триггеров пересмотра.

Контекст и проблема​

Рассмотренные варианты​

Где живут TS-данные​

Стратегия партиционирования​

Менеджер партиций​

Роллапы (день/неделя/месяц)​

Идемпотентный ingestion​

Решение​

Хранилище: vanilla PostgreSQL 16, без TimescaleDB​

Партиционирование: RANGE по времени, месячные партиции​

Менеджер партиций: собственный, через ADR-0009​

Роллапы: таблицы-агрегаты + idempotent upsert (НЕ MV)​

Идемпотентный upsert​

Ретенция​

Триггеры пересмотра ADR (когда возвращаемся к Timescale/ClickHouse)​

Последствия​