Считаем ресурсы под PostgreSQL

Не так давно на моей текущей работе впервые за весь мой немногочисленный 4-летний опыт бэкендера понадобилось для нового микросервиса рассчитывать ресурсы под PostgreSQL для данного сервиса. Раньше для меня данная тема было чем-то, чем занимаются DevOps/DBA и никогда прежде не задумывался и не исследовал информацию о том, как качественно рассчитать необходимые ресурсы, чтобы бизнесу не пришлось переплачивать за очень дорогие железки лишние деньги, чтобы потом оказалось, что от купленных мощностей в реальности используется 20-40% (опыт на нескольких работах показывает, что такое случается ну очень часто).

Q: Для кого эта статья?
A: Да в целом для любых технических специалистов, которые так или иначе взаимодействуют с технической поддержкой PostgreSQL и которым впервые нужно для новой БД (например, под микросервис) и сформулировать задачу для DevOps команды на поднятие СУБД для вашего сервиса.

Q: "Зачем мне это? Ну прикину я на глаз, что здесь нужно 50ГБ диска, 64ГБ RAM и нормально поедет"
A: Очень часто в условиях микросервисной архитектуры используется парадигма database per service и в таком случае нельзя просто запросить максимально мощную виртуальную машину. Ресурсы стоят много денег, инфраструктура должна масштабироваться, а значит необходимо уметь определять, какой именно мощности ВМ требуется и какие параметры PostgreSQL следует задать на старте.

В статье вы получите пошаговый расчёт диска, RAM, CPU и базовые рекомендации по конфигу PostgreSQL, а также в подарок готовый промпт для ИИ, если захотите делегировать все расчёты нейромозгу.

Важно: статья не претендует на супер-точные расчёты до байта - это просто невозможно. Вы обязательно должны после запуска сервиса мониторить нагрузку и при необходимости корректировать ресурсы и конфигурацию.

Оценка занимаемого места данными

Первое, что можно рассчитать с наибольшей точностью - это объём данных на диске.

Для этого необходимо рассчитать для каждой таблицы в БД занимаемое ею место на диске + служебные механизмы СУБД по следующим шагам.

1. Оценить размер одной строки

Нужно учитывать не только пользовательские данные, но и:

• служебный overhead PostgreSQL (заголовок строки - tuple header);

• индексы (каждый некластерный индекс - отдельная структура данных);

• выравнивание данных (alignment).

Подробное описание хранения строк и страниц в PostgreSQL: Database Page Layout

Пример

CREATE TABLE foo (
	id1 uuid NOT NULL,
	id2 uuid NOT NULL,
	CONSTRAINT pk_foo PRIMARY KEY (id1, id2)
);

CREATE INDEX foo_idx_1 ON foo(id1, id2);
CREATE INDEX foo_idx_2 ON foo(id2, id1);

Тело строки:

• UUID: 16 байт × 2 = 32 байта

• Служебные данные строки (tuple header): 23 байта (фиксированный заголовок - Table Row Layout)

• Итого тело строки: ~56 байт (23 + 32 для двух UUID, с учётом выравнивания)

Индексы (btree):

• Primary Key: ~60-70 байт на строку

• 2 дополнительных индекса: ~60-70 × 2 = 120-140 байт

Итого на одну запись: 56 + ~200 = ~250-270 байт

Для прикидочных расчётов допустимо округлять.

2. Учесть служебные механизмы PostgreSQL

Дополнительно место занимают:

• WAL - журналы предзаписи операций;

• MVCC - старые версии строк;

• системные каталоги PostgreSQL;

• возможные временные файлы (для sort/hash).

Под эти механизмы можно закладывать 30-100% от объёма данных, в зависимости от:

• интенсивности записи;

• частоты VACUUM;

• уровня concurrency операций.

Для read-heavy OLTP чаще ближе к 30-50%, для write-heavy - 70-100%.

3. Оценить объём таблиц

Пример:

• В таблице будет 5 млн строк

• ~257 байт на строку

• 5 000 000 × 257 байт ≈ 1,2 ГБ

4. Итоговый размер диска

Пример расчёта:

• данные: 1,2 ГБ

• WAL + MVCC: ~1-1,5 ГБ

• запас под рост и операции: ~1-2 ГБ

• Итого: ~4-5 ГБ

На практике диск можно и нужно брать с запасом (естественно "брать с запасом" нужно адекватно): рост данных может недооцениваться на старте, и лучше убрать излишки ресурсов с ВМ, чем упереться на проде в то, что на ВМ закончилось место.

Определение характера использования БД

Перед расчётом CPU и RAM необходимо понять тип нагрузки на вашу БД.

OLTP - транзакционный тип использования

Ключевые характеристики:

• короткие транзакции;

• частые INSERT / UPDATE / SELECT;

• небольшие выборки по индексам;

• высокая конкуренция.

Примеры: типичное простое веб-API.

OLAP - аналитический тип использования

Ключевые характеристики:

• тяжёлые запросы;

• JOIN больших таблиц;

• агрегации, GROUP BY;

• низкая конкуренция, но высокая нагрузка на CPU и I/O.

Примеры: аналитика, отчёты.

Mixed

Смешанный тип - самый сложный вариант. Обычно требует:

• компромиссов в конфигурации;

• ограничения аналитических запросов;

• выноса OLAP в отдельную БД.

В целом стоит понимать: если у вас намечается курс на OLAP нагрузку, то возможно, имеет смысл подумать в сторону более специализированных для этого СУБД. PostgreSQL хоть и способен эффективно выполнять аналитические запросы на умеренных объёмах данных, но при большой OLAP нагрузке специализированные СУБД показывают лучшую масштабируемость и предсказуемость.

Расчёт RAM

Общее правило: PostgreSQL очень сильно любит RAM.

Оперативная память используется для:

1. shared_buffers - максимальный объём кэшируемых данных в памяти на стороне Postgres;

2. Page Cache ОС - кэширование данных, прочитанных с диска, в RAM (управляется ОС);

3. work_mem - для сортировок и hash-операций: место в RAM в рамках одного запроса; при нехватке данные временно пишутся на диск;

4. maintenance_work_mem (VACUUM, CREATE INDEX) - память для операций обслуживания, в основном для создания индексов и вакуума таблиц.

Документация по параметрам потребления ресурсов: Resource Consumption

Базовые рекомендации

• Минимум для production: 4-8 ГБ.

• Оптимально: чтобы все активные данные помещались в RAM полностью - тогда обращений к диску будет минимум и данные будут возвращаться с очень высокой скоростью из оперативной памяти.

Типовой подход:

• shared_buffers: ~25% RAM. На больших объёмах RAM увеличение shared_buffers выше 8-16 ГБ редко даёт линейный прирост из-за дублирования с page cache и особенностей управления памятью в PostgreSQL.

• Остальное - под page cache ОС.

Если данных больше, чем RAM:

• БД будет чаще обращаться к диску;

• возрастает latency.

Для OLTP обычно важнее RAM, чем CPU.

Расчёт CPU

CPU влияет на:

• обработку запросов;

• планирование;

• агрегации;

• параллельные операции.

Практические ориентиры:

• OLTP: 2-4 vCPU достаточно для большинства сервисов;

• OLAP: 4-8+ vCPU.

Важно учитывать:

• PostgreSQL масштабируется по CPU хуже, чем по RAM;

• большое количество ядер не компенсирует медленные запросы;

• частые context switch при высоком max_connections требуют больше CPU.

Рекомендация: начинать с умеренного количества CPU и масштабироваться при необходимости.

Количество соединений и connection pool

Каждое соединение PostgreSQL - это логическое и процессное представление одного клиента, подключённого к БД. Одно соединение может выполнять только один запрос.

У PostgreSQL есть настройка максимального количества подключений к БД - max_connections. Чем больше значение max_connections, тем меньше ресурсов PostgreSQL может выделить на каждое соединение.

Проблемы большого max_connections:

• рост потребления RAM;

• переключения контекста;

• меньше ресурсов на каждое соединение;

• деградация latency.

Рекомендации:

• max_connections: 50-200. Крайне не рекомендуется завышать эту настройку без необходимости.

• Использовать connection pool на стороне клиента. Не нужно постоянно закрывать и заново открывать соединения - обычно на стороне клиентских библиотек пул уже реализован. Создание и закрытие соединения на стороне БД - очень дорогая операция.

Формирование конфигурации PostgreSQL

Самый простой и эффективный стартовый вариант - PGTune (калькулятор параметров по объёму RAM, CPU, типу нагрузки и числу соединений). PGTune выдаёт адекватную базовую конфигурацию:

• PGTune (leopard.in.ua)

• PGTune (sainth.de) - альтернативный вариант

• Исходный проект: pgtune на GitHub (Gregory Smith, BSD-3-Clause).

Достаточно указать:

• объём RAM;

• CPU;

• тип нагрузки (OLTP / OLAP);

• размер диска.

Ручная тонкая настройка имеет смысл, когда:

• есть реальные проблемы производительности;

• накоплена статистика мониторинга.

Иначе легко попасть в ловушку преждевременной оптимизации.

Использование ИИ для расчёта ресурсов

Если не хотите проходить все шаги вручную, можно поручить расчёт нейромозгу - достаточно подставить свои данные в промпт ниже (схемы таблиц, тип нагрузки, RPS и т.д.) и ИИ выдаст оценку диска, RAM, CPU и соединений.

Промпт

**Role:**
You are a senior PostgreSQL performance engineer and infrastructure architect.

**Task:**
Estimate the required virtual machine (VM) resources for a new PostgreSQL database based on the provided service requirements and table schemas.

You must:
- Derive approximate row size for each provided table schema.
- Estimate total data volume, including indexes and PostgreSQL internal overhead.
- Produce a **reasonable, cost-efficient initial VM sizing** suitable for production start.

This is **not final tuning**, but an engineering-grade estimation.

---
#### Input Requirements (to be filled by the developer)

**Service Overview**
- Expected workload type — (OLTP / OLAP / Mixed)

**Table Schemas**
Provide table schemas in SQL DDL format.


Example:
CREATE TABLE example (
	id uuid PRIMARY KEY,
	user_id uuid NOT NULL,
	status smallint NOT NULL,
	payload jsonb,
	created_at timestamptz NOT NULL
);

CREATE INDEX idx_example_user_id ON example(user_id);

  
For each table, also provide:
- Expected number of rows (initial and in 6–12 months, if known) — x rows
- Expected write/update frequency — (low / medium / high)

**Traffic & Usage**
- Expected RPS (reads / writes) — x reads; y writes
- Expected concurrency level — x

**Query Characteristics**
- Mostly index-based queries? — (yes/no)
- Heavy JOINs or aggregations? — (yes/no)
- Long-running queries expected? — (yes/no)

**Operational Characteristics**
- Update frequency — (low / medium / high)
- Data growth rate — (per month)
- Retention policy — (if any)

**Environment Assumptions**
- High availability required? — (yes/no)
- Backups & replicas planned? — (yes/no)

---
#### Output Requirements

**1. Assumptions**
List all assumptions made due to missing or ambiguous information.

**2. Per-Table Storage Estimation**

For **each provided table**:
- Estimated base row size (data types + alignment)
- PostgreSQL tuple header overhead
- NULL bitmap impact (if applicable)
- Estimated index size per row
- **Final estimated size per row (bytes)**

Explain calculations briefly but clearly.

**3. Total Disk Size Estimation**
- Estimated total table data size
- Estimated total index size
- WAL, MVCC, and operational overhead (with percentage and justification)
- Growth and safety buffer
- **Final recommended disk size (GB)**

**4. RAM Estimation**
- Recommended total RAM (GB)
- Rationale based on: active dataset size, concurrency, workload type
- Notes on memory pressure risks

**5. CPU Estimation**
- Recommended number of vCPUs
- Justification based on workload type and concurrency
- Notes on PostgreSQL CPU scalability characteristics

**6. Connection Capacity Estimation**
- Recommended maximum number of concurrent database connections
- Rationale
- Explicit recommendation regarding connection pooling

---

#### Calculation & Reasoning Rules

- Use PostgreSQL internal storage rules (alignment, tuple headers, btree index behavior).
- Prefer conservative estimates over optimistic ones.
- Round values where appropriate and explain why.
- If table schemas contain variable-length fields (TEXT, JSONB, ARRAY), make reasonable assumptions and state them explicitly.
- Do not assume compression unless explicitly stated.

---
#### Tone & Style Requirements

- Technical and precise.
- Engineering-focused, not marketing.
- Clearly separate assumptions from derived values.
- Prefer ranges when exact numbers are not defensible.

---
#### Final Reminder

Explicitly state:
> “This estimation provides an initial VM sizing. Real production tuning and scaling must be driven by actual runtime metrics after deployment.”

Финал: ставим задачу девопсам

Пройдя по всем пунктам выше, вы получите примерное представление о том, сколько ресурсов и какая конфигурация БД нужны под ваши нужды. С готовыми требованиями можно идти к девопсам и запрашивать ВМ.

Пример текста задачи для девопсов для сценария из примеров выше:

# DB Version: 18
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 8 GB
# CPUs num: 4
# Connections num: 100
# Data Storage: ssd

max_connections = 100
shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 512MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 8192kB
huge_pages = off
min_wal_size = 512MB
max_wal_size = 2GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2

Финал: кратко вспоминаем шаги расчета

• Диск: считаем размер строки (данные + tuple header + индексы), умножаем на число строк, добавляем 30–100% на WAL/MVCC и запас под рост.

• Тип нагрузки: определяем OLTP / OLAP / Mixed - от этого зависят RAM vs CPU и конфиг.

• RAM: минимум 4-8 ГБ для production; оптимально, чтобы активный датасет помещался в память; shared_buffers ~25% RAM, остальное - под page cache ОС.

• CPU: OLTP обычно 2-4 vCPU, OLAP 4-8+; не раздувать в надежде компенсировать медленные запросы.

• Соединения: max_connections 50-200, использовать connection pool на стороне приложения. Если соединений нужно больше - масштабировать по горизонтали (кластер, реплики, пулеры вроде PgBouncer) или по вертикали (больше RAM/CPU); вертикаль обычно даёт меньший прирост, чем разгрузка через пул и реплики.

• Стартовый конфиг: PGTune по RAM/CPU/типу нагрузки; Ручная оптимизация конфига - только при реальных проблемах и наличии метрик.

• После запуска: мониторить нагрузку и при необходимости корректировать ресурсы и параметры PostgreSQL.

P.S.

Если увидели ошибку/неточность или что автор не все раскрыл, то добро пожаловать в комментарии, постараюсь оперативно все подправить.