Тюнинг PostgreSQL: shared_buffers, work_mem, effective_cache_size и как не сломать сервер неправильными значениями

PostgreSQL из коробки настроен на запуск в минимальном окружении: 128 МБ shared_buffers и 4 МБ work_mem подходят для разработки, но на продакшене с десятками гигабайт RAM такие значения оставляют ресурсы неиспользованными. При этом завышение параметров памяти — одна из частых причин OOM-kill и деградации производительности. Ниже — механика каждого параметра, формулы расчёта и способы проверить, что настройка не вредит.

shared_buffers: общий пул страниц​

shared_buffers определяет объём разделяемой памяти, которую PostgreSQL выделяет под кэширование страниц данных. Каждая страница (по умолчанию 8 КБ) при первом чтении с диска попадает в этот буфер и остаётся там до вытеснения алгоритмом clock-sweep.

Расчёт для выделенного сервера​

Устоявшаяся рекомендация для выделенного сервера БД — 25% от общего объёма RAM. Для сервера с 64 ГБ это 16 ГБ:

shared_buffers = 16GB

Почему не больше? PostgreSQL работает поверх файловой системы, и ОС тоже кэширует страницы в page cache. Если отдать shared_buffers 50–70% памяти, page cache почти опустеет, и повторные чтения тех же файлов будут идти на диск вместо RAM. На практике при shared_buffers выше 40% RAM выигрыш исчезает, а на некоторых workload'ах производительность падает.

Ограничения и нюансы​

• Параметр требует перезапуска сервера (restart, не reload).
  
• На Windows большие значения shared_buffers могут вызывать проблемы из-за особенностей управления памятью; там часто ограничиваются 512 МБ – 1 ГБ.
  
• На Linux начиная с PostgreSQL 9.3 для выделения shared_buffers используется mmap с флагом MAP_SHARED, а не System V shared memory. Поэтому параметры kernel.shmmax и kernel.shmall на современных версиях не влияют на выделение этого буфера. Они могут быть релевантны только при использовании очень старых версий PostgreSQL или нестандартных сборок.
  
• Если PostgreSQL работает в контейнере с memory limit, считайте 25% от лимита контейнера, а не от RAM хоста.
  
• На Linux при больших значениях shared_buffers (от нескольких гигабайт) имеет смысл включить huge_pages = on в postgresql.conf. Это снижает нагрузку на TLB и уменьшает overhead управления памятью. Предварительно нужно выделить huge pages на уровне ОС через vm.nr_hugepages.
  

Как проверить текущее значение​

SHOW shared_buffers;
-- или
SELECT name, setting, unit FROM pg_settings WHERE name = 'shared_buffers';

work_mem: память на операцию, а не на соединение​

work_mem задаёт объём памяти, доступный для одной операции сортировки или хеширования внутри запроса. Ключевое слово — одной операции. Один запрос может содержать несколько ORDER BY, DISTINCT, JOIN с hash-стратегией, и каждая такая операция получает собственный work_mem.

Почему дефолт 4 МБ — это мало​

При сортировке результата в 100 000 строк с несколькими колонками 4 МБ не хватает, и PostgreSQL переключается на внешнюю сортировку через временные файлы на диске. Это видно в EXPLAIN ANALYZE по строке Sort Method: external merge.

Почему нельзя просто поставить 1 ГБ​

Представьте 200 одновременных соединений, каждое выполняет запрос с двумя hash-join'ами. При work_mem = 1 ГБ пиковое потребление только на операции сортировки/хеширования:

200 соединений × 2 операции × 1 ГБ = 400 ГБ

Это гарантированный OOM. Реальное потребление обычно ниже (не все соединения одновременно выполняют тяжёлые операции), но порядок цифр показывает масштаб риска.

Практичный подход к выбору значения​

1. Начните с консервативного значения: 64–256 МБ для серверов с 16–64 ГБ RAM и умеренной конкурентностью.
   
2. Мониторьте временные файлы: SELECT * FROM pg_stat_database WHERE temp_files > 0; — если temp_files и temp_bytes растут, work_mem можно увеличить.
   
3. Для отдельных тяжёлых запросов или сессий ставьте значение локально:
   

SET work_mem = '512MB';
-- выполнить запрос
RESET work_mem;

• В postgresql.conf можно задать значение по умолчанию, а для конкретных пользователей или баз переопределить через ALTER DATABASE ... SET work_mem = '...' или ALTER ROLE ... SET work_mem = '...'.
  

Формула верхней границы​

Грубая оценка безопасного максимума:

work_mem_max = (RAM - shared_buffers - OS_reserve) / (max_connections × avg_operations_per_query)

Где avg_operations_per_query — среднее число sort/hash-операций в типичном запросе (обычно 2–4). OS_reserve — память, которую нужно оставить системе и другим процессам (1–2 ГБ минимум).

Эта формула даёт верхнюю границу, а не целевое значение. Начинать стоит с половины полученного числа и повышать по результатам мониторинга.

effective_cache_size: подсказка планировщику​

effective_cache_size не выделяет память. Это оценка общего объёма кэша, доступного для чтения данных: shared_buffers + page cache ОС. Планировщик использует это значение при выборе между sequential scan и index scan.

Влияние на планы запросов​

При низком effective_cache_size планировщик считает, что данные скорее всего не в памяти, и предпочитает sequential scan (последовательное чтение эффективнее случайного при холодном кэше). При высоком значении планировщик охотнее выбирает index scan, ожидая, что нужные страницы уже в кэше.

Рекомендуемое значение​

Рекомендация: 50–75% от общего объёма RAM. Для сервера с 64 ГБ:

effective_cache_size = 48GB

Завышение не опасно (память не выделяется), но может привести к неоптимальным планам: планировщик будет выбирать index scan там, где sequential scan быстрее из-за реального состояния кэша.

Параметр не требует перезапуска — достаточно reload:

SELECT pg_reload_conf();

maintenance_work_mem: память для обслуживания​

Отдельный параметр для операций VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY. Эти операции выполняются реже и обычно не конкурируют за ресурсы с пользовательскими запросами, поэтому значение можно ставить выше work_mem.

maintenance_work_mem = 1GB

Для серверов с 32+ ГБ RAM значения 1–2 ГБ ускоряют автовакуум и создание индексов без риска OOM, потому что одновременно выполняется ограниченное число таких операций. Параметр вступает в силу после reload, перезапуск не нужен.

Как проверить, что настройка работает​

Hit ratio shared_buffers​

Расширение pg_buffercache показывает, какие страницы находятся в буфере:

CREATE EXTENSION IF NOT EXISTS pg_buffercache;

SELECT c.relname, count(*) AS buffers
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 10;

Общий hit ratio видно через pg_stat_database:

SELECT datname,
       blks_hit,
       blks_read,
       round(blks_hit::numeric / nullif(blks_hit + blks_read, 0) * 100, 2) AS hit_ratio_pct
FROM pg_stat_database
WHERE datname NOT LIKE 'template%';

Если hit ratio ниже 95% при стабильной нагрузке, shared_buffers может быть мал для рабочего набора данных.

Мониторинг временных файлов​

SELECT datname, temp_files, temp_bytes
FROM pg_stat_database
WHERE temp_files > 0;

Рост temp_files указывает на нехватку work_mem для текущей нагрузки. Обратите внимание: счётчики cumulative и не сбрасываются без pg_stat_reset().

Анализ планов запросов​

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Обращайте внимание на:

• Sort Method: external merge — не хватает work_mem для сортировки в памяти.
  
• Sort Method: quicksort Memory: NkB — сортировка прошла в памяти, значение N показывает реальное потребление.
  
• Buffers: shared hit=X read=Y — если read значительно больше hit, данные не помещаются в кэш.
  

Типичные ошибки​

Ошибка	Последствие	Как избежать
shared_buffers = 70% RAM	Page cache пуст, повторные чтения идут на диск	Не превышать 25–40%
work_mem = 1 ГБ глобально	OOM при пиковой нагрузке	Ставить консервативно, повышать per-session
effective_cache_size = 1 ГБ	Планировщик избегает index scan без причины	Ставить 50–75% RAM
Изменение shared_buffers без перезапуска	Новое значение не применяется	systemctl restart postgresql
Настройка в контейнере по RAM хоста	OOM-kill контейнера	Считать от memory limit контейнера
Игнорирование temp_files	Запросы молча пишут на диск	Мониторить pg_stat_database

Порядок действий при тюнинге​

1. Определите доступную RAM (с учётом лимитов контейнера, если применимо).
   
2. Установите shared_buffers = 25% RAM.
   
3. Установите effective_cache_size = 75% RAM.
   
4. Установите work_mem = 64–256 МБ (в зависимости от max_connections и сложности запросов).
   
5. Установите maintenance_work_mem = 1–2 ГБ.
   
6. Перезапустите PostgreSQL (shared_buffers требует restart).
   
7. Нагрузите сервер типичным трафиком.
   
8. Проверьте pg_stat_database на temp_files и hit ratio.
   
9. Корректируйте work_mem по результатам мониторинга, а не по формулам вслепую.
   

Источники​

• Ubuntu Server documentation
  
• Docker Engine | Docker Docs