Капстоун: аналитическая система запросов от начала до конца

Форма системы

К концу этого урока у тебя будет собран полный конвейер — схема, запрос, пагинация, конкурентность и фикс индекса по EXPLAIN — и ты будешь точно знать, какое решение из какого раздела предотвращает каждый класс ошибок.

Эндпоинт — GET /tenants/:id/metrics?from=…&to=…&cursor=…. Он возвращает per-tenant временной ряд — daily active users (DAU), выручку и топ типов событий — постранично, быстро, под тысячами конкурентных тенантов. За ним стоят сырой журнал событий и дневной rollup, который поддерживает фоновый воркер. Архитектура — это конвейер: сырые события текут в предагрегированный rollup; API читает rollup для временного ряда и сырую таблицу только для самого свежего дня; воркер держит rollup актуальным, не блокируя читателей.

Схема и типы — фундамент (раздел 06)

Подбери колонки правильно — и половина проблем производительности просто не возникнет. Эти решения не косметика:

CREATE TABLE events (
  id         bigint GENERATED ALWAYS AS IDENTITY,
  tenant_id  bigint      NOT NULL,
  user_id    bigint      NOT NULL,
  type       text        NOT NULL,
  ts         timestamptz NOT NULL,            -- всегда tz-aware; никогда naive timestamp
  props      jsonb       NOT NULL DEFAULT '{}',
  PRIMARY KEY (tenant_id, id)                 -- tenant впереди, чтобы каждое чтение было tenant-local
);

-- Предагрегированный rollup, который API читает для исторических дней:
CREATE TABLE metrics_daily (
  tenant_id   bigint      NOT NULL,
  day         date        NOT NULL,
  dau         integer     NOT NULL,
  revenue     numeric(14,2) NOT NULL,
  PRIMARY KEY (tenant_id, day)
);

В этом DDL спрятаны три сеньорских решения. ts — это timestamptz, а не timestamp: naive timestamp молча теряет смещение, поэтому пользователь в Токио и пользователь в Сан-Паулу попадают в один и тот же «день», и твой DAU неверен by design. props — это jsonb, а не json: jsonb разбирается один раз в бинарную форму, так что props->>'amount' и GIN-индексы работают; json перепарсится при каждом чтении. А id — это ключ GENERATED ALWAYS AS IDENTITY, стандартная замена serial, с составным первичным ключом (tenant_id, id), так что таблица физически кластеризована по тенанту — каждый per-tenant скан остаётся в непрерывном куске индекса.

Запрос метрик — условная агрегация (раздел 03)

DAU, выручка и разбивка по типам — это один проход по данным, а не три запроса. Клауза FILTER превращает один GROUP BY во множество условных метрик — приём из раздела 03 в реальной работе:

SELECT
  date_trunc('day', ts)                                   AS day,
  count(DISTINCT user_id)                                 AS dau,
  count(*) FILTER (WHERE type = 'purchase')               AS purchases,
  coalesce(sum((props->>'amount')::numeric)
           FILTER (WHERE type = 'purchase'), 0)           AS revenue,
  count(*) FILTER (WHERE type = 'signup')                 AS signups
FROM events
WHERE tenant_id = $1
  AND ts >= $2 AND ts < $3
GROUP BY 1
ORDER BY 1;

Один скан среза одного тенанта выдаёт все метрики. Альтернатива — отдельный запрос на метрику — умножает round trips и перечитывает те же heap-страницы каждый раз. FILTER лучше CASE WHEN ... END здесь, потому что читается как намерение, а планировщик трактует их одинаково. Заметь: count(DISTINCT user_id) — дорогая часть: он форсирует сортировку или hash всех user_id в диапазоне, и именно поэтому исторические дни идут в предвычисленный rollup.

Top-N на тенанта и нарастающие итоги — оконные функции (раздел 04)

«Топ-5 типов событий за период» и «накопленная выручка» — это работа оконных функций. Ранжирование партиционирует по тенанту; нарастающий итог накапливается внутри упорядоченного фрейма:

-- Топ-5 типов событий для одного тенанта, с долей каждого типа:
SELECT type, n,
       round(100.0 * n / sum(n) OVER (), 1) AS pct
FROM (
  SELECT type, count(*) AS n
  FROM events
  WHERE tenant_id = $1 AND ts >= $2 AND ts < $3
  GROUP BY type
) t
ORDER BY n DESC
LIMIT 5;

-- Накопленная выручка по дневному ряду (после per-day rollup):
SELECT day, revenue,
       sum(revenue) OVER (ORDER BY day
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS revenue_to_date
FROM metrics_daily
WHERE tenant_id = $1 AND day >= $2 AND day < $3
ORDER BY day;

sum(n) OVER () с пустым окном считает общий итог не схлопывая строки, так что каждый тип сохраняет свою долю — GROUP BY так в одном проходе не смог бы. Для настоящего per-tenant top-N сразу по многим тенантам ты обернул бы row_number() OVER (PARTITION BY tenant_id ORDER BY n DESC) и отфильтровал <= 5 — ровно паттерн из урока ранжирования раздела 04.

CTE-конвейер для производного измерения (раздел 05)

Типы событий сворачиваются в категории (purchase, refund → commerce; signup, login → lifecycle). Дерево категорий естественно рекурсивно; рекурсивный CTE обходит его, а второй CTE соединяет события с разрешённой категорией. Это паттерн раздела 05 — читаемый, поэтапный конвейер вместо чащи вложенных подзапросов:

WITH RECURSIVE category_tree AS (
  SELECT id, parent_id, name, name AS root
  FROM event_categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.root
  FROM event_categories c JOIN category_tree t ON c.parent_id = t.id
),
typed AS (
  SELECT et.type, ct.root AS category
  FROM event_types et JOIN category_tree ct ON et.category_id = ct.id
)
SELECT typed.category, count(*) AS n
FROM events e
JOIN typed ON typed.type = e.type
WHERE e.tenant_id = $1 AND e.ts >= $2 AND e.ts < $3
GROUP BY typed.category
ORDER BY n DESC;

Keyset-пагинация — не OFFSET (разделы 01 и 05)

Когда добавляешь пагинацию в аналитический эндпоинт, выбор механизма определяет, будет ли страница 500 такой же быстрой, как страница 1 — или в пятьдесят раз медленнее.

OFFSET 100000 LIMIT 50 заставляет Postgres прочитать и отбросить 100 000 строк, чтобы вернуть 50 — латентность страницы растёт линейно с её номером, так что самый глубокий скролл — самый медленный. Keyset- (она же cursor-) пагинация несёт ключ сортировки последней строки и сикает прямо к ней:

-- Первая страница: курсора нет.
SELECT day, dau, revenue
FROM metrics_daily
WHERE tenant_id = $1
ORDER BY day DESC
LIMIT 50;

-- Следующая страница: курсор — последний увиденный день; сикаем за него. O(log n), не O(offset).
SELECT day, dau, revenue
FROM metrics_daily
WHERE tenant_id = $1
  AND day < $2                       -- $2 = последний day с предыдущей страницы
ORDER BY day DESC
LIMIT 50;

С первичным ключом (tenant_id, day) предикат курсора — это index range scan: страница 1 и страница 10 000 стоят одинаково. Когда ключ сортировки не уникален, курсор должен быть кортежем — (day, id) < ($cursor_day, $cursor_id) — через сравнение строк (row comparison), чтобы никогда не пропустить и не задублировать строки на стыке равных значений.

JOIN без fan-out (раздел 02)

Соединять события с per-event измерением (одна категория на тип) безопасно. Ловушка — соединение с one-to-many таблицей: скажем, таблица tenant_features с многими строками на тенанта молча размножает строки событий до агрегата, и DAU с выручкой возвращаются раздутыми. Фикс — агрегировать измерение до одной строки на ключ до JOIN, либо держать lookup измерения one-to-one:

-- НЕВЕРНО: у tenant_features N строк на тенанта → каждое событие посчитано N раз.
-- SELECT count(*) FROM events e JOIN tenant_features f USING (tenant_id) ...

-- ВЕРНО: сначала схлопни измерение, потом соединяй one-to-one.
SELECT e.type, count(*) AS n
FROM events e
JOIN (SELECT tenant_id, max(plan) AS plan
      FROM tenant_features GROUP BY tenant_id) f USING (tenant_id)
WHERE e.tenant_id = $1
GROUP BY e.type;

Раздутые агрегаты из fan-out JOIN (когда JOIN умножает строки из-за связи один-ко-многим) — это аналитический баг, который доезжает до прода: запрос отрабатывает, возвращает числа, и числа тихо завышены в 3 раза.

Конкурентность: rollup-воркер (раздел 07)

Множество инстансов воркера должны разгребать бэклог так, чтобы двое не обрабатывали одну пачку. SELECT ... FOR UPDATE SKIP LOCKED — это очередной примитив очереди: каждый воркер забирает строки, которые никто не держит, и пропускает залоченные вместо блокировки. Выбор изоляции тоже важен — воркер гоняет каждую пачку в READ COMMITTED (дефолт), потому что ему не нужен замороженный снапшот между стейтментами; отчётный экспорт, который должен быть внутренне согласованным, взял бы REPEATABLE READ.

BEGIN;  -- READ COMMITTED достаточно для идемпотентного upsert-воркера
WITH batch AS (
  SELECT id, tenant_id, ts, type, props
  FROM events
  WHERE rolled_up = false
  ORDER BY id
  LIMIT 1000
  FOR UPDATE SKIP LOCKED          -- забрать 1000 строк, которые не держит другой воркер
)
INSERT INTO metrics_daily (tenant_id, day, dau, revenue)
SELECT tenant_id, date_trunc('day', ts)::date,
       count(DISTINCT user_id),
       coalesce(sum((props->>'amount')::numeric) FILTER (WHERE type='purchase'),0)
FROM batch GROUP BY 1, 2
ON CONFLICT (tenant_id, day)
  DO UPDATE SET dau = excluded.dau, revenue = excluded.revenue;
-- (здесь пометить пачку rolled_up = true)
COMMIT;

Без SKIP LOCKED десять воркеров сериализуются на одних и тех же строках в голове очереди, и ты получаешь пропускную способность одного воркера ценой десяти. С ним пропускная способность масштабируется почти линейно, пока не насытится I/O.

Тюнинг: пусть EXPLAIN скажет правду (раздел 08 + трек databases)

Запрос метрик медленный в первый день, потому что нет полезного индекса — Postgres seq-сканит весь журнал событий на каждый запрос. Сеньорский workflow — измерь, диагностируй, почини одну вещь, перемерь:

EXPLAIN (ANALYZE, BUFFERS)
SELECT date_trunc('day', ts) AS day, count(DISTINCT user_id) AS dau
FROM events
WHERE tenant_id = 7 AND ts >= '2026-05-01' AND ts < '2026-06-01'
GROUP BY 1;
-- Seq Scan on events  (rows=480000)  Buffers: shared read=61000
-- Планировщик оценил 12 строк; фактически 480000 → промах в 40 000×.

Два фикса, по порядку. Когда видишь такой промах оценки, не торопись сразу добавлять индекс — сначала почини статистику планировщика, иначе он может индекс и не выбрать. Сначала промах оценки: tenant_id и ts коррелированы (события тенанта кластеризованы во времени), поэтому планировщик перемножает их селективности как независимые и гадает слишком мало строк. CREATE STATISTICS с dependencies учит его этой корреляции. Затем путь доступа: составной индекс с колонкой равенства первой и колонкой диапазона второй превращает seq scan в плотный index range scan.

-- Научи планировщик, что tenant_id и ts коррелированы (extended statistics):
CREATE STATISTICS events_tenant_ts (dependencies)
  ON tenant_id, ts FROM events;
ANALYZE events;

-- Индекс, который решает: равенство (tenant_id) первым, диапазон (ts) вторым.
CREATE INDEX events_tenant_ts_idx ON events (tenant_id, ts);

Порядок колонок — это всё (правило ведущей колонки из трека databases): (tenant_id, ts) позволяет Postgres сикнуть к одному тенанту и затем range-сканить временное окно внутри него; (ts, tenant_id) форсировал бы скан всех тенантов в окне. После индекса тот же запрос — это Index Scan, читающий shared read=420 вместо 61000 — вот тот скачок с 9 секунд до 40 миллисекунд. Наконец, следи за bloat: таблица rollup UPDATE-нагружена через upsert, так что мёртвые кортежи накапливаются; пусть autovacuum держит её поджарой (глава MVCC трека databases объясняет, почему мёртвые строки замедляют сканы даже после того, как живой набор сжался).