Производительность Postgres для SaaS: когда индексировать, когда кэшировать

Если ошибиться с границей «индекс vs кэш», вы получите либо медленные записи и раздувшуюся БД, либо устаревшие данные и гонки кэша на проде. В SaaS это не теория — это ночные алерты и счёт за облако.

Короткий ответ: индексируйте селективные фильтры и регулярные сортировки, где планер может использовать B‑tree/GIN/BRIN с узким охватом строк. Кэшируйте дорогие агрегации, повторяющиеся выборки с низкой изменчивостью и кросс‑тенантные публичные данные. Ниже — практический алгоритм, архитектуры и грабли, на которые чаще всего наступают.

Как думать об индексации в Postgres

Индексы — ускоряют чтение ценой более тяжёлых вставок/обновлений и дискового объёма. В SaaS это критично: если сущность горячая на запись (счётчики, события), «лишний» индекс быстро станет вашей проблемой.

Ключевые принципы

• Селективность: индекс выигрывает, когда условие фильтра охватывает малую долю строк. Смотрите pg_stats.n_distinct, гистограммы и EXPLAIN ANALYZE.
• Порядок колонок: в B‑tree композитный индекс полезен при фильтрации по префиксу. Если часто фильтруете по tenant_id и created_at, порядок должен отражать реальный шаблон запросов.
• Частичные индексы: уменьшайте размер и улучшайте селективность, индексируя «живую» часть таблицы.
• Покрывающие индексы: INCLUDE даёт планеру Index‑Only Scan, снижая обращения к таблице.
• Тип индекса: B‑tree — дефолт для равенств/сортировок; GIN — для jsonb @>, ARRAY, полнотекста; GiST — гео/диапазоны; BRIN — очень большие, монотонно растущие таблицы по времени.

Пример: типичный мульти‑тенант запрос

Фид последних событий по компании с пагинацией по курсору:

-- Таблица событий
CREATE TABLE events (
  id bigserial PRIMARY KEY,
  tenant_id bigint NOT NULL,
  user_id bigint,
  action text NOT NULL,
  payload jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- Индекс под частые чтения: сначала tenant, затем сортировка по дате
CREATE INDEX CONCURRENTLY idx_events_tenant_created
  ON events (tenant_id, created_at DESC)
  INCLUDE (action, user_id);

-- Частичный индекс, если 90% запросов — за последние 30 дней
CREATE INDEX CONCURRENTLY idx_events_tenant_recent
  ON events (tenant_id, created_at DESC)
  WHERE created_at > now() - interval '30 days';

-- GIN под фильтрацию по полю в payload
CREATE INDEX CONCURRENTLY idx_events_payload_gin
  ON events USING gin ((payload jsonb_path_ops));

Тестируем план:

EXPLAIN (ANALYZE, BUFFERS)
SELECT action, user_id, created_at
FROM events
WHERE tenant_id = 42 AND created_at > now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;

Ищем Index‑Only Scan, малое количество возвращённых строк, умеренные блок‑риды. Если видите Seq Scan по миллионам строк — индекс не бьёт по паттерну запроса, либо селективность низкая.

Когда индекс бессмысленен

• Низкая селективность (status IN ('active','trial')) при равномерном распределении; Seq Scan может быть не хуже.
• Частые массовые апдейты в индексируемой колонке — блоат, VACUUM‑шторм и падение кэша странице.
• «На всякий случай»: индексы на каждую колонку. Цена — замедление записи и рост диска без реальной пользы.

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

Когда кэш лучше индекса

Postgres не имеет встроенного SQL‑кэша результатов. Есть буферный кэш страниц (shared buffers + ОС cache), но это не заменяет прикладной кэш. Кэшируйте там, где:

• Повторяются одни и те же результаты (топ‑N, сводные метрики, публичные справочники).
• Агрегации/джоины дороги, а свежесть допускает задержку 5–300 секунд.
• Результат зависит от немногих параметров, ключ кэша прост и предсказуем.

Виды кэша

• В приложении: in‑process LRU (например, в Node — lru-cache) для горячих малых наборов.
• Redis/Memcached: разделяемый кэш между инстансами, TTL, eviction. Часто — Redis 6+ со строковыми ключами.
• CDN/HTTP: для публичных эндпоинтов (аналитические виджеты, маркетинговые страницы с агрегатами).
• Материализованные представления: REFRESH MATERIALIZED VIEW CONCURRENTLY по расписанию.

Стратегии и инвалидация

• Read‑through: сначала читаем кэш, при промахе — БД, записываем в кэш.
• Write‑through: при записи в БД обновляем/инвалидируем кэш.
• Версионирование ключа: stats:{tenant_id}:{ver} — сменили версию при миграции формата.
• Анти‑штампед: «single flight»/локи, джиттер в TTL, прогретые ключи в фоне.

Пример: агрегат по метрикам с Redis

// Node.js + pg + ioredis
import { Pool } from 'pg';
import Redis from 'ioredis';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const redis = new Redis(process.env.REDIS_URL!);

async function getRevenueByDay(tenantId: number) {
  const key = `rev:v1:${tenantId}:${new Date().toISOString().slice(0,10)}`; // дневной срез
  const cached = await redis.get(key);
  if (cached) return JSON.parse(cached);

  // Анти-штампед: возьмём распределённый лок на 2 сек
  const lockKey = `${key}:lock`;
  const gotLock = await redis.set(lockKey, '1', 'NX', 'EX', 2);
  if (!gotLock) {
    // Подождём и попробуем ещё раз быстро
    await new Promise(r => setTimeout(r, 100));
    const retry = await redis.get(key);
    if (retry) return JSON.parse(retry);
  }

  const sql = `
    SELECT date_trunc('day', created_at) AS d, SUM(amount_cents) AS s
    FROM payments
    WHERE tenant_id = $1 AND created_at >= now() - interval '30 days'
    GROUP BY 1
    ORDER BY 1 DESC
    LIMIT 30;
  `;
  const { rows } = await pool.query(sql, [tenantId]);

  // TTL: 60–120 сек с джиттером
  const ttl = 60 + Math.floor(Math.random() * 60);
  await redis.set(key, JSON.stringify(rows), 'EX', ttl);
  await redis.del(lockKey);
  return rows;
}

Почему кэш, а не индекс? Потому что SUM/GROUP BY всё равно просматривает немалый объём, даже при хороших индексах, и часто результат одинаков в течение минуты.

Архитектура SaaS: быстро читать и не ломать запись

Базовая схема, которую мы используем как отправную точку в продуктах:

• API слой (Node/Go), за ним PgBouncer (transaction pooling).
• Postgres Primary для записи, 1–2 Read Replica для чтения отчётных/публичных запросов.
• Redis как кэш результата и короткоживущих сессий/токенов.
• Фоновый воркер (BullMQ/Sidekiq) для рефреша материализованных представлений и тяжёлых агрегаций.
• CDN (например, Cloudflare) для публичных агрегированных эндпоинтов (ETag/Cache‑Control).
• Партиционирование больших таблиц по tenant_id, created_at или только по времени, если тенантов много и равномерны.

Поток запроса к «дорогой» метрике:

1. Клиент запрашивает /metrics/revenue?tenant=42.
2. API проверяет Redis. Попадание — 1–3 мс, мимо — идём в реплику Postgres.
3. Если результат свежий — кладём его в кэш на 60–120 сек; параллельно пушим задание на фоновый прогрев.
4. Ночные/почасовые задания обновляют материализованное представление, минимизируя нагрузку на праймари.

Для мульти‑тенант структур и Prisma см. наш разбор по моделированию прав и ключей в статье Как построить мульти‑тенант SaaS на Prisma и Postgres.

Партиционирование и BRIN

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

• создайте партиции по месяцу/кварталу;
• для аналитики по времени добавьте BRIN по created_at на каждую партицию;
• для «последних N» по тенанту — локальные B‑tree индексы на партиции.

CREATE TABLE events_y2026m05 PARTITION OF events
FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE INDEX ON events_y2026m05 USING brin (created_at);
CREATE INDEX ON events_y2026m05 (tenant_id, created_at DESC);

Индекс vs кэш: трезвое сравнение

Правило большого пальца: если запрос стабильно возвращает <0.5–1% таблицы и не содержит тяжёлой агрегации — начинайте с индекса. Если доля строк велика или расчёт дорог — начинайте с кэша или материализованного представления.

Дерево решений: как принять решение по шагам

1. Замерьте реальность:
   • Включите pg_stat_statements, снимите топ по total_time, mean_time, calls.
   • Снимите планы EXPLAIN (ANALYZE, BUFFERS), посмотрите на Rows Removed by Filter, Heap Fetches.
2. Быстрая оптимизация запроса:
   • Уберите «скрытые» касты, приведите типы параметров.
   • Явно задайте курсорную пагинацию вместо OFFSET.
3. Спроектируйте индекс:
   • Композитный по префиксу реальных фильтров, INCLUDE для покрывающего чтения.
   • Частичный индекс под «горячий» диапазон времени.
   • Проверьте рост записи на стейдже/генераторе нагрузки.
4. Если план остаётся тяжёлым (Aggregate/HashAggregate по большим наборам):
   • Материализованное представление + фоновые рефреши CONCURRENTLY.
   • Или Redis‑кэш результата с TTL и write‑through инвалидацией при изменении данных.
5. Инвалидация:
   • Выберите стратегию: TTL + «щадящие» апдейты, либо события (триггер → LISTEN/NOTIFY воркеру → DEL/UPDATE кэша).
6. Наблюдаемость и регрессии:
   • Алёрты на рост dead_tuples, время VACUUM, дельту диска индексов.
   • Сэмплинг промахов кэша и распределения ключей (против «горячих» ключей).

Что ломается в продакшене

• Автовакиум не успевает: долгие транзакции держат старые снапшоты → блоат растёт → индексы пухнут. Решение: следите за pg_stat_activity, ограничивайте долгие транзакции, тюньте autovacuum_* для горячих таблиц, fillfactor ниже на часто обновляемых.
• Репликационный лаг: API читает с реплики, кэш инвалидировали, но реплика отстаёт — клиент видит «пропажу» только что созданных данных. Решение: read‑your‑writes через primary для сессии, либо lag-aware фича‑флаг.
• Индексы «не попали»: добавили GIN по jsonb, а запрос использует ->> и LIKE — получаете Seq Scan. Решение: корректный оператор (@>/?), функциональные индексы.
• Кэш‑штампед: TTL закончился на горячем ключе, сто инстансов пошли в БД. Решение: локи/семафоры, раннее продление TTL («refresh ahead»), джиттер.
• Горячие ключи Redis: неравномерная нагрузка → eviction соседних ключей. Решение: шардирование, maxmemory-policy с LRU/LFU, разделение пространств ключей по паттерну трафика.
• Миграции индексов онлайн: забыли CONCURRENTLY — таблица легла в блокировке. Решение: всегда через CREATE INDEX CONCURRENTLY и DROP INDEX CONCURRENTLY, катить отдельным шагом.

Бизнес‑контекст: стоимость и ROI

• Железо vs инженерные часы: иногда перейти с 2 vCPU/8 GB на 4 vCPU/16 GB на управляемом Postgres дешевле, чем неделя оптимизации. Но рост лопаты быстро упирается в I/O и блоат.
• Цена индексов: суммарный размер индексов нередко приближается к размеру таблицы. Каждый дополнительный индекс — дополнительный I/O при вставке/обновлении. Если у сущности >4–5 индексов, проверьте, что все реально используются.
• Цена кэша: Redis с 2–8 GB памяти в облаке — порядка сотен долларов в месяц. Ключи с большими JSON‑пейлоадами умножают память; иногда дешевле сериализовать компактно (MessagePack), либо кэшировать идентификаторы, а не полные объекты.
• Риск устаревших данных: SLA по свежести — это деньги. Если ваш отчёт допустимо отставать на 60 секунд, кэш даст кратный выигрыш. Если нужна консистентность «с точностью до транзакции», инвестируйте в индексы и архитектуру чтения с primary.
• Комбинации: популярная практика — индекс для «последние 7 дней по тенанту», кэш для «исторический срез за 12 месяцев».

Часто окупаемая стратегия: сначала внедряется измеримость (pg_stat_statements, трейсинг), затем минимальный набор индексов, затем — точечный кэш самых дорогих агрегатов. И только потом — партиционирование/реплики.

Распространённые паттерны, которые работают

• Счётчики/таймсерии: запись в «сырую» таблицу без лишних индексов; мельчайшая агрегация в фоновой задаче в отдельную «сводную» таблицу; API читает из свода с индексом по tenant_id, bucket_ts и кэширует на 30–120 сек.
• Каталоги/справочники: маленькие таблицы — in‑process кэш на 5 минут с фоновой репликацией изменений через LISTEN/NOTIFY.
• Поиск по JSONB: GIN на конкретное подмножество путей + нормализация «горячих» атрибутов в колонки; фоллбэк кэширует популярные фильтры.
• Тяжёлые отчёты: материализованные представления с REFRESH ... CONCURRENTLY раз в N минут, Redis на 1–5 минут для самых частых тенантов, CDN для публичных дэшбордов.

И да, не забывайте конфиг: work_mem под сортировки/хеш‑агрегаты (но с учётом конкурентности), effective_cache_size реалистичен под RAM нод, PgBouncer — в режиме transaction pooling, чтобы не душить коннектами primary.

FAQ

Как понять, что индекс действительно используется?

Снимите EXPLAIN (ANALYZE, BUFFERS) по реальному запросу. Ищите Index Scan/Index Only Scan, время и количество прочитанных страниц. Дополнительно проверьте pg_stat_user_indexes.idx_scan — счётчик растёт.

Когда выбирать BRIN вместо B‑tree?

Когда таблица огромная и запросы коррелируют со временем/монотонным ключом (логи, события). BRIN мал по месту и ускоряет «по времени», но бесполезен для точечных фильтров без корреляции.

Как кэш инвалидировать без TTL?

Через события: триггер пишет сообщение (например, в Redis Pub/Sub или LISTEN/NOTIFY), воркер получает и удаляет конкретные ключи (DEL stats:{tenant}). При миграциях — версионирование ключей.

Можно ли кэшировать на уровне реплики?

Реплика — это не кэш результата, а источник чтения. Она помогает разгрузить primary, но сложность в лаге. Часто реплику + Redis используют вместе: читаем с реплики, поверх — кэш результата.

Что быстрее: Memcached или Redis?

Для простых строковых ключ/значение скорости близки. Redis выигрывает универсальностью (Lua, списки, стримы), но и стоит обычно дороже. В SaaS удобнее Redis из‑за богатых примитивов управления штампедом и счётчиками.

Нужна ли нам материализация, если есть Redis?

Часто да: материализованное представление сокращает работу Postgres, Redis сокращает RPS к БД. Вместе они могут дать стабильную латентность и контролируемую свежесть данных.

Ключевые выводы

• Индексы ускоряют селективные чтения и сортировки, но платят записью и диском; добавляйте их под конкретный запрос и план.
• Кэш имеет смысл для дорогих агрегатов и повторяющихся результатов со слабой требовательностью к свежести; стройте надёжную инвалидацию.
• Комбинация: индексы для «онлайн» запросов, материализация/Redis для отчётов — типовой выигрыш в SaaS.
• Измеримость (pg_stat_statements, EXPLAIN, метрики штампеда кэша) важнее догадок; начинайте с измерений.
• Прод подкидывает проблемы: блоат, репликационный лаг, штампед — закладывайте защитные механизмы заранее.

Если вы строите SaaS на Postgres и решаете, что индексировать, а что кэшировать, мы в MTBYTE поможем спроектировать и внедрить архитектуру без ночных алертов. Опишите задачу на /contact — разберём ваш профиль запросов и предложим план.