Все, что нужно PostgreSQL: быстрые диски, дорогой процессор и терабайты RAM

В облачном мире PostgreSQL возникает много вопросов. Какую конфигурацию выбрать для старта кластера? Сколько оперативной памяти и ядер CPU нужно под мою базу данных? Нужны ли под такой профиль нагрузки высокочастотные процессоры? Какое должно быть соотношение RAM к Storage в кластере? Хватает ли ресурсов и на системные службы, и на кэширование запросов? Не переплачиваю ли я?

Всем привет! Меня зовут Гришин Александр, я руководитель по развитию продуктов хранения данных в Selectel, отвечаю за объектное S3-хранилище ^[1] и облачные базы данных ^[2]. В этой статье я поделюсь своими практическими рекомендациями и ориентирами по планированию использования ресурсов кластера в PostgreSQL — в зависимости от типа и профиля нагрузки, размера данных и характера доступа к ним. Погнали!

Эта информация основывается на моем опыте, не претендует на единственно верный и всеобъемлющий подход. Рассчитываю, что она пригодится инженерам, разработчикам и СТО для оптимального выбора стартовой конфигурации облачного кластера PostgreSQL и, как следствие, ускорения работы приложения.

Используйте навигацию, если не хотите читать текст целиком:
→ Особенности работы PostgreSQL с ресурсами кластера ^[3]
→ Базовая формула ^[4]
→ Как узнать размер индексов ^[5]
→ Типовые профили нагрузки и рекомендации для них ^[6]
→ Слоты логической репликации ^[7]
→ Мониторинг и метрики ^[8]
→ Заключение ^[9]

Особенности работы PostgreSQL с ресурсами кластера

Оперативная память

Давайте поверхностно разберем, как PostgreSQL использует доступную ему память:

• shared_buffers — основной кэш приложения PostgreSQL,
• page cache — основной кэш операционной системы,
• work_mem,temp_buffers — память на сортировки и join-операции (умножается на каждую операцию и соединение),
• maintenance_work_mem — для VACUUM, CREATE INDEX и прочего,
• резервное копирование и мониторинг в услугах DBaaS так же использует ресурсы вашего кластера,
• прочие системные и фоновые задачи.

shared_buffers — это часть оперативной памяти, управляемая самой PostgreSQL. В ней хранятся страницы таблиц и индексов, которые PostgreSQL активно использует. PostgreSQL сначала читает данные в shared_buffers, прежде чем возвращать их в ответ на запрос. При записи изменения сначала идут в shared_buffers, и только потом синхронизируются на диск (через WAL и чекпоинты). Не стоит отдавать shared_buffers 100% от общей RAM кластера. Помним, что память потребуется под системный кеш, рабочую память (work_mem и прочее) и прочие нужды.

page cache — файловый кэш операционной системы, в который попадают данные автоматически, если они читаются или пишутся через обычные файловые вызовы (например, read(), write()). PostgreSQL не знает напрямую, что хранится в page cache, но использует его косвенно, потому что все операции чтения/записи идут через файловую систему. Page Cache может хранить как данные PostgreSQL, так и всех остальных системных служб в ОС.

Оба эти уровня используют одну и ту же оперативную память. Если задать слишком большой shared_buffers, не останется памяти под Page Cache. Это может ухудшить производительность, особенно при больших последовательных чтениях.

Следует следить за количеством подключений. 100 соединений с work_mem = 64МБ — это +6 ГБ памяти только под join-операции! Неожиданно? Особенно если вам на старте казалось, что хватит всего 4 ГБ RAM на весь кластер.

RAM распределяется между множеством компонентов услуги DBaaS, от операционной системы и дополнительных сервисов услуги типа мониторинга, до резервного копирования, high availability и т.д. — важно учитывать это при планировании.

CPU

Есть у PostgreSQL и особенности работы с CPU, которые важны при стартовом выборе конфигурации кластера. PostgreSQL однопоточная на уровне выполнения запроса, то есть один SQL-запрос = один процесс = одно ядро.

Это значит, что масштабирование происходит по количеству параллельных запросов, а не внутри одного запроса (за исключением параллельных запросов). Отсюдо вытекает, что высокочастотные ядра (CPU линейки HighFreq) могут быть особенно полезны в нагрузке, где важна минимальная латенси одиночных запросов.

В PostgreSQL поддерживаются параллельные запросы для SELECT с JOIN, AGGREGATE, INDEX SCAN, но только если это разрешено планом выполнения и параметрами (parallel_setup_cost, parallel_tuple_cost, max_parallel_workers_per_gather).

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

PostgreSQL выигрывает от CPU с высокой тактовой частотой и от многопоточности при большом числе параллельных клиентов. Но каждый отдельный запрос в большинстве случаев выполняется в одном потоке.

Быстрые диски

Возможно, самый важный элемент системы почти при любом профиле нагрузки для PostgreSQL — дисковая подсистема. Обычно в PostgreSQL не вся база помещается в память и именно диск становится узким местом.

Особенно сильно медленные диски влияют на работу аналитики и BI. Такой профиль нагрузки включает в себя тяжелые вложенные JOIN, GROUP BY, сортировки, математические функции и временные таблицы. Все это неизбежно обращается к диску. Кроме того:

• PostgreSQL активно использует временные файлы при нехватке work_mem — тут тоже параметры диска будут напрямую влиять на отклик;
• autovacuum, checkpoints, WAL-логирование также создают дисковую нагрузку, и медленные накопители могут стать бутылочным горлышком.

Использование сетевых дисков (InfiniBand, Fibre Channel, Ethernet и т. д.) гарантированно увеличивает latency. Несмотря на то что «скорость света высока», сигнал проходит не мгновенно. Физику не обманешь. По моему опыту, локальные SSD NVMe-диски дают кратный прирост практически во всех сценариях.

Производительность диска: IOPS, bandwidth, latency становятся самыми важными параметрами практически при любом профиле нагрузки в PostgreSQL. Медленные диски гарантированно станут бутылочным горлышком вашей информационной системы.

Базовая формула

Оперативная память

Тонкая настройка любой системы, в том числе СУБД, в первую очередь зависит от профиля нагрузки. Если вы не знаете его, не беда. Основываясь на своем опыте работы в Selectel, я подготовил общие рекомендации по RAM:

• оцените общий размер данных в кластере,
• оцените долю активных данных от этого объема,
• объем RAM в ГБ должен превышать 25–50% от активного объема данных (включая индексы, как измерить индексы расскажу далее),
• заложите дополнительное количество RAM на сервисные службы кластера.

Рекомендуемое соотношение количества RAM к объему активных данных.

Например, если вы храните 1 ТБ данных, но активно работаете с 300-500 ГБ (например, заказы за последние 12 месяцев), вам потребуется как минимум 75-250 ГБ RAM, чтобы держать все горячее в памяти.

CPU

Общую рекомендацию по CPU я бы свел к тому, что чем больше ядер и выше их частота, тем быстрее PostgreSQL выполняет вычисления. Но учитываем, что не вся нагрузка масштабируется линейно по потокам.
Особенно параметры CPU критичны для:

• сложных запросов с множественными JOIN, GROUP BY, с сортировками,
• OLAP и HTAP профилей нагрузок,
• интенсивных параллельных операций,
• математических и статистических вычислений,
• шифрования данных средствами СУБД.

Диски

Если вы не знаете профиль нагрузки, просто используйте самые быстрые локальные диски, которые вам доступны. Это подойдет для любого кейса.

Нужно интенсивное чтение больших объемов данных в OLAP-профиле? Берите быстрые локальные диски. Нагрузка гибридна и распределена по многим файлам и индексам? Берите быстрые локальные диски. Важна низкая латенси доступа к данным и быстрая обработка каждого конкретного запроса? Однозначно берите быстрые локальные диски. Главная задача при их выборе — они не должны стать узким местом в работе вашей системы.

Как узнать размер индексов

Чтобы определить, сколько места занимают индексы в вашей базе данных, PostgreSQL предоставляет несколько полезных запросов. Ниже представлю пару примеров с пояснениями.

Общий размер всех индексов в базе данных

SELECT
  pg_size_pretty(SUM(pg_relation_size(i.indexrelid))) AS total_index_size
FROM
  pg_index i
  JOIN pg_class c ON c.oid = i.indrelid
WHERE
  c.relkind = 'r'; 

Этот запрос суммирует размеры всех индексов (pg_relation_size(i.indexrelid)) для всех обычных таблиц (relkind = 'r'). Он покажет общий объем, который индексы занимают на диске. Это полезно, если вы хотите прикинуть, сколько RAM потребуется, чтобы кэшировать их в памяти.

Пример вывода запроса из BI Superset с общим размером индексов. Это дополнительные 20ГБ, которые ноде кластера СУБД нужно будет как-то обработать при запросах.

Размер индексов по каждой таблице

SELECT
  relname AS table,
  pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables;

Этот запрос показывает, сколько места занимают индексы по каждой отдельной пользовательской таблице (pg_statio_user_tables). Это особенно полезно, если вы хотите определить «тяжелые» таблицы, где основную нагрузку создают именно индексы.

Пример вывода:

Таким образом, если вы понимаете, что ваше приложение будет активно работать именно с таблицей orders, вам стоит заложить дополнительные 3 ГБ RAM на ноду только под ее индексы в shared_buffers.

Рекомендую периодически запускать эти запросы и отслеживать рост индексов. Это поможет вовремя пересмотреть конфигурацию RAM и настроить автоматическое обслуживание — например, REINDEX, если размер индекса раздувается из-за частых обновлений.

Типовые профили нагрузки и рекомендации для них

OLTP (классические транзакционные системы)

Примеры: интернет-магазины, CRM, SaaS, микросервисы и т. д.

Характеристики:

• много коротких операций SELECT, INSERT, UPDATE, DELETE,
• нагрузка точечная, преимущественно по индексу,
• критичны отклик и масштабируемость.

Рекомендации по объему RAM в таблице основаны на принципе, что RAM должна покрывать как минимум активные индексы и часто используемые данные, чтобы избежать чтения с диска. В OLTP-нагрузке важнее обеспечить:

• быстрый доступ к индексам (особенно на больших таблицах),
• удержание горячих строк (например, последние месяцы заказов, пользователи, корзины и т. п.),
• минимизацию блокировок и задержек при высоком количестве подключений.

Рекомендации по CPU

Для такого профиля нагрузки обычно не нужно много ядер, поэтому:

• выбирайте CPU с высокой однопоточной производительностью. Линейки процессоров High Frequency (например, Intel Xeon Gold, AMD EPYC с высокими частотами ГГц) будут лучшим для вас выбором;
• для OLTP обычно важна не ширина (много ядер), а производительность одного потока;
• 1-4 CPU на каждую активно используемую базу — хорошая отправная точка.
• Для быстрого старта рекомендую рассмотреть к использованию линейку готовых Облачных баз данных на Enterprise-оборудовании с высокочастотными процессорами ^[10].

В OLTP RAM особенно важна для кэширования индексов, чтобы ускорить поиск и избежать чтения с диска. Размер индексов может составлять от 20% до 40% от объёма таблиц, в зависимости от структуры базы и количества вторичных индексов. Не нужно много ядер CPU, но может быть важна их частота. Диски потребуются локальные SSD NVMe.

OLAP (аналитика, BI, бизнес-отчетность)

Примеры: КХД, отчеты по продажам, витрины данных, агрегации, дашборды.

Характеристики:

• долгие SELECT-запросы с GROUP BY, JOIN, ORDER BY,
• большие объемы данных,
• временные таблицы и сортировки,
• параллельные запросы.

Рекомендации по RAM

• желательно ≥ 25–50% всего объема данных.
• work_mem — от 16 до 128 МБ на соединение (зависит от числа одновременных запросов).

Рекомендации по CPU

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

• Частота CPU становится менее важной характеристикой. Вместо нее выбираем большее количество ядер: 16, 32, 64 или больше.
• Многопоточные CPU нужны для распараллеливания аналитических задач.
• Поддержка parallel query в PostgreSQL позволяет эффективно использовать такие процессоры.

В OLAP-кластерах особенно важна пропускная способность диска (IOPS) и SSD. Размеры аналитических данных сейчас доходит до десятков ТБ (и больше!). Будет технически сложно покрыть нужным объемом RAM кластер такого размера.

Преимущество локальных дисков пропадает с увеличением размера кластера до десятков терабайт. Сетевой latency для такого кластера становится не критичным и может быть выгодно выбирать быстрые сетевые диски.

HTAP (транзакции + аналитика)

Примеры: e-commerce с real-time аналитикой, ERP, логистические платформы.

Характеристики:

• одновременные короткие транзакции и длинные аналитические запросы,
• часто выполняются агрегации по реплике или срезам данных.

Рекомендации по RAM

• Используйте RAM ≥ 50–75% от объема горячих данных.
• Разделите нагрузку (вертикальная изоляция). Основной OLTP-трафик будет обслуживаться мастер нодой, а аналитику направляйте только через реплики, чтобы не мешать основной нагрузке.
• Но при разнесении нагрузки по разным нодам кластера, мастер-нода может распухнуть. Такое действительно может случится когда долгий SELECT мешает применять WAL-журнал, и Postgres вынужден прервать запрос. Возможно вам придется разносить разную нагрузку по разным кластерам.
• Используйте materialized views (материализованное представление) для подготовке BI-отчетов.

Материализованное представление — это сохраненный результат запроса, обновляемый вручную или по расписанию. Оно помогает ускорить повторные тяжелые запросы без постоянной нагрузки на живые таблицы. Например, если у вас есть тяжелый отчет по продажам, его можно сохранить в materialized view и обновлять раз в час — это снизит нагрузку на основную таблицу заказов.

Рекомендации по CPU

Тут сложнее всего дать хорошие рекомендации. Кластер, подходящий под разные профили нагрузки, скорее всего, не подходит ни под одну из них. Однако если уж так стоит задача, вам потребуется сразу и большое количество ядер для использования многопоточности в OLAP, и высокая частота для OLTP профиля нагрузки. Дорого? Да.

Еще раз подумайте о том, чтобы разнести нагрузку на разные кластеры.

Может появиться желание тонко затюнить каждую ноду. Мастер выбрать с небольшим количеством высокочастотных ядер CPU и локальными дисками, а реплику с большим количеством обычных ядер CPU и на сетевых дисках. Но я строго не рекомендую так делать!

По моему опыту ноды кластера должны быть гомогенны по характеристикам, т. к. на эти параметры завязано множество других ситуаций, обрабатываемых кластером СУБД. Например, switchover: роль ноды кластера может переключиться в любой момент жизни системы, и это штатная ситуация. В таком случае у вас получатся не оптимальные характеристики нод, обслуживающих приключившуюся нагрузку. Реплика начнет отставать от мастера. Это рано или поздно приведет к разбалансировке всей системы и аварийной ситуации. Узкое место на одной ноде — это всегда узкое место для всего кластера.

Важным для HTAP профиля нагрузки будет вертикальное разделение нагрузки по ролям в кластере, однако это может привести к распуханию мастера. Такое может случится когда долгий SELECT мешает применять WAL-журнал, и Postgres вынужден прервать запрос. Так же, не нужно тюнить характеристики каждой отдельной ноды. Характеристики нод кластера обязательно должны быть гомогенны. Еще раз подумайте о том чтобы разнести разные нагрузки на разные кластера.

Архив / холодное хранилище

Примеры: исторические логи, старые транзакции, бэкапы.

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

На старте это самый нетребовательный к ресурсам профиль нагрузки.

• RAM можно держать на минимуме, ведь ожидается редкий доступ к данным.
• Частота и количество ядер не критичны, главное — надежность и доступность. На старте должно хватить минимальной конфигурации в 2-4 ядра CPU.
• Можно использовать сетевые диски. Они обеспечивают хорошую скорость и отказоустойчивость ваших данных.

Слоты логической репликации

Логическая репликация — мощный механизм PostgreSQL, позволяющий передавать изменения из одной базы в другую (например, для интеграции с Kafka, CDC, миграций, BI-систем и т.д.). Но важно помнить, что она потребляет дополнительные CPU-ресурсы — как на отправляющей стороне, так и на принимающей.

Примерные ориентиры по нагрузке на CPU во время логической репликации:

Что влияет на нагрузку:

• объем DML-операций (INSERT, UPDATE, DELETE),
• тип и размер данных (особенно jsonb, text, bytea),
• используемый output plugin (pgoutput, wal2json, и т. д.),
• наличие сложных фильтров и преобразований на стороне подписчика.

Закладывайте примерно по одному ядру CPU на каждый активный логический слот, особенно если поток изменений интенсивный.

Однако логическая репликация — это не только нагрузка на процессор, но и на диск, особенно если подписчик временно не может получать данные (например, из-за сетевого разрыва, перегрузки или проблем на принимающей стороне). Рассмотрим упрощенный механизм такой ситуации:

1. PostgreSQL сохраняет WAL — журналы изменений, которые необходимы подписчику для воспроизведения операций;
2. если слот не потребляет данные вовремя, WAL-файлы не очищаются (не могут быть удалены), и начинают накапливаться на диске;
3. это может привести к переполнению диска и даже остановке кластера (если место под pg_wal закончится).

Рекомендации по диску

• Контролируйте свободное место в pg_wal. Обязательный минимум — 3-5 ГБ запаса на каждый активный логический слот, лучше больше.
• Если сеть нестабильна или подписчик работает с задержками, увеличьте wal_keep_size (или wal_keep_segments в старых версиях) до безопасного уровня.
• Для интенсивной репликации закладывайте SSD-диски с максимально высоким IOPS: WAL-файлы пишутся часто и последовательно. Отставания реплик нам не нужны.
• Мониторьте pg_replication_slots, pg_stat_replication, pg_wal_lsn_diff.

Если вы понимаете, что будете использовать слоты логической репликации, то обязательно стоит заложить одно ядро CPU на каждый слот репликации. И еще 20–25% к емкости диска под pg_wal, если понимаете, что соединение будет нестабильным. Обязательно настройте мониторинг состояния слотов и алерты на заполнение диска и заранее продумайте действия и реакцию на такие инциденты.

Мониторинг и метрики

Измеряешь, значит управляешь. Поэтому я считаю, что мало правильно выбрать количество и соотношение ресурсов на старте. Более того — этот выбор не имеет никакого смысла, если вы не настроили мониторинг и не измеряете состояние системы в реальном времени.

В первую очередь, могу рекомендовать следующие метрики (но не ограничиваясь ими):

В нашей услуге метрики можно отслеживать как в панели управления ^[11], так и в собственной системе мониторинга, настроив трансляцию метрик в формате prometheus. Подробнее ознакомиться с этой частью функциональности можно в нашей технической документации ^[12].

cache hit ratio = 1 — (blks_read / blks_hit) — метрика, которую я рекомендую отслеживать в первую очередь. Она способна дать понимание, правильно ли вы выбрали соотношение RAM/Storage.

Заключение

Небольшое резюме:

• объем RAM должен превышать объем 25–50% активных данных;
• индексы могут занимать до 50% и более от объема — это тоже память;
• сервисные процессы и службы также нуждаются в памяти;
• HTAP требует грамотных архитектурных решений: разделения ролей, репликации, materialized views и т. д.;
• для OLAP важно количество ядер CPU, достаточный объем памяти и производительность диска;
• для OLTP важна как частота CPU, так и быстрые диски;
• мониторинг — это единственный способ понять, куда уходит ресурс и что улучшать.

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

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

Все вышесказанное — мой личный опыт и мое личное мнение, которые, надеюсь, помогут вам оптимизировать выбор и соотношение ресурсов кластера PostgreSQL. Однако если уж начистоту, я считаю, что нет лучшего рецепта, чем найм DBA-инженера себе в штат. Очень рекомендую!

А для получения максимально возможной по производительности конфигурации в виде готовой облачной услуги рекомендую рассмотреть облачные базы данных на выделенном сервере ^[13]. Подробнее о том, как и почему мы первые в РФ сделали подобную услугу, я уже рассказывал в статье на Хабре ^[14].