Финальный урок про планирование роста. До этого мы говорили про конкретные запросы — индексы, refactoring, bloat. Сейчас — про систему в целом: как понять, что через 6 месяцев база начнёт задыхаться, и что делать раньше, чем это случится.

Capacity planning — это смесь мониторинга (чтобы знать тренд), настройки (чтобы вытащить из текущего hardware максимум), и архитектурных решений (когда железа уже мало).

Трекинг роста

Базовый набор метрик, который должен собираться ежедневно (или ежечасно):

-- Размер по каждой таблице
SELECT
  relname,
  pg_relation_size(relid) AS heap_bytes,
  pg_indexes_size(relid) AS index_bytes,
  pg_total_relation_size(relid) AS total_bytes
FROM pg_stat_user_tables
ORDER BY total_bytes DESC;

Сохраняй в timeseries-store (Prometheus, отдельная metrics-таблица), строй график. Слежение за дельтой даёт реальный сигнал:

• Линейный рост — нормально для INSERT-heavy таблиц, прогнозируй ёмкость через 3-12 месяцев.
• Экспоненциальный рост — что-то изменилось (новый продукт, юзер-каскад, баг с deduplication). Расследуй.
• Резкий скачок без новых INSERT’ов — bloat. Срочно проверь n_dead_tup и autovacuum.
• Падение размера — VACUUM FULL/pg_repack или DROP/TRUNCATE. Должно быть запланировано.

Ёмкость на год вперёд: возьми текущий размер, умножь на (1 + monthly growth rate)^12. Если получается > 70% диска — заказывай место сейчас, миграция дисков занимает недели.

Connection pool sizing

PostgreSQL отдельный процесс на каждое соединение. На 1000 одновременных connections — 1000 процессов, каждый со своим work_mem-буфером и I/O state. Even idle connection съедает ~5-10 MiB. На 1000 connections это 10 GiB только на пустые подключения, плюс context switching overhead.

Правильный подход — connection pool между приложением и Postgres. Канонический выбор —

Формула для размера pool:

max_db_connections ≈ (CPU_cores * 2) + effective_spindle_count

Для современного сервера с 16 CPU и SSD: 16 * 2 + 1 = 33. Реально берут 50-100, но не больше. Дальше — выгода маленькая, contention растёт.

Размер pool в приложении ≥ max_db_connections суммарно по всем app-инстансам:

total_pool_capacity = app_instances * pool_per_instance ≤ pgbouncer_max_pool_size

Если у тебя 10 app-серверов и в PgBouncer пуле 50 — каждый app может держать максимум 5 соединений. Распределяй sensibly.

Подвох transaction pool’а: некоторые фичи Postgres не работают (session-state не переживает выдачу backend другому клиенту):

• SET LOCAL — переживает (внутри транзакции).
• SET без LOCAL — НЕТ.
• LISTEN/NOTIFY — НЕТ.
• PREPARE — НЕТ (на стороне Postgres; на стороне PgBouncer есть emulation).
• Temp tables — НЕТ.

Большинство ORM работают, но проверять отдельно.

Memory tuning

Три главных параметра памяти Postgres:

shared_buffers

Это page cache Postgres внутри его адресного пространства. Все читаемые heap/index страницы кешируются здесь. Default — 128 MB, на современном сервере это смехотворно мало.

Rule of thumb: 25% от RAM. На сервере с 64 GiB — shared_buffers = 16GB. Больше 40% обычно не имеет смысла: Postgres всё равно полагается на OS page cache как L2.

-- Hit ratio (>95% — нормально, <80% — нужно увеличить shared_buffers или RAM)
SELECT
  ROUND(100.0 * sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) AS hit_pct
FROM pg_statio_user_tables;

work_mem

Это per-операция память для сортов, hash table’ов, hash agg’регаций. Default — 4 MB. Когда операция перерастает work_mem — она «спиливает» на диск (external merge sort), и это медленно.

Rule of thumb: для OLTP work_mem = 16-32 MB. Для OLAP — 64-256 MB. Но умножь на максимум одновременных запросов: 50 коннектов × 64 MB = 3.2 GB. Это должно вместиться в RAM сверх shared_buffers и OS cache.

-- Sort/hash spills to disk
SELECT
  query,
  temp_blks_written
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 10;

temp_blks_written > 0 — операция спилила на диск, work_mem недостаточен. Если конкретный query даёт большой temp — можно поднять work_mem локально через SET LOCAL work_mem в его транзакции.

effective_cache_size

Это подсказка планировщику про размер OS-cache (не реальная аллокация). Postgres использует это для оценки стоимости Index Scan: если effective_cache_size большой, planner предполагает, что страницы будут «горячими» и снижает random_page_cost.

Rule of thumb: 50-75% от RAM. На сервере 64 GiB — effective_cache_size = 48GB. Постгрес не аллоцирует эту память, просто использует число для costing.

Когда масштабировать: вертикаль vs горизонталь

К этому моменту ты вытащил из текущего сервера всё. Запросы оптимизированы, индексы стратегичны, bloat под контролем, память настроена. Тренд графика всё равно показывает: через 6 месяцев — потолок. Что делать?

Сначала — вертикаль

Цена: +CPU, +RAM, +faster SSD/NVMe. Без архитектурных изменений.

Выгода: ускоряет всё одинаково. Не требует переписывания приложения.

Лимит: ~96 CPU и ~2 TB RAM на одну instance в облаке (AWS r6id.32xlarge или эквивалент). Это покрывает 99% workload’ов мира. Если уже на максимуме — иди дальше.

Read replicas

Цена: настройка streaming replication, разделение read/write в app, ~доп. инстанс/реплику.

Выгода: разгружает primary от read-heavy запросов. Особенно — от аналитики, отчётов, дашбордов.

Лимит: all writes идут в primary — если bottleneck в writes, replicas не помогут. И есть replication lag (от ms до секунд), что не всегда приемлемо.

Когда применять: read/write ratio > 80/20, и большая часть read-нагрузки терпит stale-данные на пару секунд.

Partitioning

Цена: ALTER TABLE → PARTITION OF, миграция данных, обновление приложения (опционально — partition key в WHERE).

Выгода: партиция = меньше страниц для scan’а; partition pruning отбрасывает неактуальные. VACUUM/autovacuum работают per-partition (быстрее). DROP старых партиций — мгновенный.

Лимит: требует выбора правильного partition key. Если запросы не используют этот key — partitioning не помогает.

Когда применять: таблица > 100 GiB, есть natural partition key (date, customer_id, region). Не для масштабирования writes — это всё ещё одна instance.

Sharding

Цена: большая. Разделить данные на N независимых инстансов, переписать приложение для shard-aware queries, потерять cross-shard transactions, перестроить ops.

Выгода: линейное масштабирование write-throughput. Каждый shard — отдельный Postgres, со своим primary.

Лимит: сложность. JOIN’ы между shard’ами либо невозможны, либо очень медленны. DDL — отдельная боль. Это не «купи бóльший сервер», это архитектурный пересмотр.

Когда применять: writes > что может один сервер (~50K-100K writes/sec на современном железе), и нет варианта вертикали.

Чек-лист capacity-маневров

Перед тем как ехать в архитектурное масштабирование:

• Размеры таблиц трекаются ежедневно (timeseries-метрики).
• Прогноз ёмкости на год — < 70% диска.
• Connection pool (PgBouncer) настроен; нет тысяч idle backend’ов.
• shared_buffers = 25% RAM; effective_cache_size = 50-75% RAM.
• work_mem × max_connections + shared_buffers + overhead помещается в RAM.
• Buffer cache hit ratio > 95%.
• Нет идущих temp_blks_written > 0 на критичных запросах.
• n_dead_tup стабильный (autovacuum успевает).
• idx_scan = 0 индексов нет (или удалены).

Чек-лист

• Трекай рост (таблицы, индексы) ежедневно. Прогноз — год вперёд.
• Connection pool обязателен для production. PgBouncer transaction-mode, размер по формуле cores * 2 + spindle.
• shared_buffers = 25% RAM — главная настройка памяти.
• work_mem = 16-64 MB OLTP, 64-256 MB OLAP; умножай на max_connections при планировании.
• effective_cache_size = 50-75% RAM — подсказка планировщику, не аллокация.
• Buffer hit ratio > 95% — нормально; меньше — увеличивай память.
• Масштабирование: query/index → memory → vertical → replicas → partitioning → sharding. Идёшь по этой лестнице, не прыгаешь через ступеньки.
• Sharding — последнее средство: дорого, ломает JOIN’ы, требует архитектурного пересмотра.