Почему 25% от ОЗУ — это не просто правило

В документации PostgreSQL нет жёсткой формулы «выделяй столько-то памяти». Есть рекомендации, но они часто воспринимаются как догма. На практике всё сложнее: shared_buffers — это буферный кэш данных, который хранит страницы таблиц и индексов в оперативной памяти. Когда запросу нужна страница, она берётся из этой области, а не с диска. Разница между чтением из RAM и с SSD измеряется миллисекундами, но при высокой нагрузке эти миллисекунды складываются в минуты простоя.

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

Размер shared_buffers задаётся в мегабайтах, но на самом деле это количество блоков по 8 килобайт (или больше, если таблица использует расширенные блоки). Параметр работает только для серверной части PostgreSQL — клиентские библиотеки используют кэш на стороне приложения. Если вы запускаете веб-сервер с PHP или Python и подключаете его к базе, они не делят эту область памяти с процессом СУБД.

Типичная ошибка: поставить shared_buffers = 24 ГБ на сервере с 32 ГБ ОЗУ. Останется 8 ГБ для ядра и других сервисов. В пиковую нагрузку другие процессы начнут дергаться, читать с диска, а PostgreSQL почувствует это в виде роста времени отклика. Напротив, если поставить shared_buffers = 4 ГБ на сервере с 16 ГБ ОЗУ — базы данных хватит на 25%, но остальные 75% пойдут в ядро и файловую систему. Это работает стабильнее.

Есть ещё один нюанс: shared_buffers нельзя увеличить во время работы сервера без перезапуска. Изменения применяются только при старте или через SIGHUP, но некоторые параметры относятся к категории «только при запуске». Если вы поменяли размер буфера после того, как база уже работала — старая область памяти останется, а новое значение применится только к новым соединениям и операциям. Это создаёт асимметрию: процесс может думать, что у него 8 ГБ, но фактически использовать меньше.

Как work_mem влияет на выполнение запросов

work_mem — это память, которую каждый запрос использует для сортировки, агрегации и построения хеш-таблиц внутри планировщика. Значение задаётся в мегабайтах и применяется ко всем операциям параллельно: если у вас 40 запросов выполняются одновременно, и каждый занимает по 128 МБ work_mem — серверу понадобится минимум 5 ГБ только под эти структуры.

По умолчанию work_mem = 4 МБ. Это кажется скромным, но на практике его часто поднимают до 64–128 МБ. Проблема в том, что если вы поставите слишком большое значение — запросы начнут вытеснять данные из shared_buffers и ядра, создавая давление на дисковую подсистему. Особенно это заметно при параллельных операциях: JOIN на больших таблицах, GROUP BY с агрегацией по многим колонкам, сортировка результатов перед возвратом клиенту.

Важно понимать: work_mem не делится между запросами. У каждого подключения своя область памяти. Если у вас 16 ядер и вы запускаете параллельные сканирования — каждый процесс будет требовать отдельный буфер сортировки. Это создаёт линейный рост потребления ОЗУ с числом соединений.

Есть параметр effective_cache_size, который часто путают с shared_buffers. Он не выделяет память физически — это только подсказка для планировщика. Планировщик использует его, чтобы оценить стоимость случайного доступа к диску. Если shared_buffers = 2GB, а effective_cache_size = 4GB — система считает, что в памяти ещё есть место под кэш ядра. Это заставляет её выбирать индексы вместо последовательных сканирований.

Настройка effective_cache_size зависит от того, сколько места реально доступно для кэширования данных. Если у вас 32 ГБ ОЗУ, shared_buffers = 8GB, а в системе работают другие сервисы — стоит оценить, сколько из оставшихся 24 ГБ фактически используется ядром для кэша файловой системы. Часто это около 50–70% от свободного места. Если поставить effective_cache_size слишком большим — планировщик будет ошибочно полагать, что все данные уже в памяти, и выбирать индексы там, где их нет. Это приводит к тому, что запросы начинают ждать подгрузки страниц с диска.

Стоимость случайного доступа и параллелизм

Параметры seq_page_cost и random_page_cost определяют, насколько планировщик считает дорогим чтение страницы с диска. По умолчанию seq_page_cost = 1.0, random_page_cost = 4.0. Это означает, что случайный доступ считается в четыре раза дороже последовательного. Но на современных SSD разница между чтением подряд и разрозненно измеряется десятками микросекунд. Если у вас быстрые NVMe-диски — можно снизить random_page_cost до 1.5–2.0, чтобы планировщик чаще использовал индексы.

Напротив, если вы используете медленные HDD или сетевые файловые системы с высокой задержкой — стоит увеличить random_page_cost до 8–10. Это сделает последовательные сканирования привлекательнее для планировщика, что уменьшит количество случайных обращений к диску. Важно помнить: эти значения относительны. Если вы поднимете seq_page_cost вместе с random_page_cost — их влияние на выбор плана взаимно сократится.

Параллелизм в PostgreSQL управляется отдельными параметрами. min_parallel_table_scan_size по умолчанию = 8 MB, min_parallel_index_scan_size = 512 KB. Если таблица меньше этих значений — параллельное сканирование не начнётся. Это разумно: запуск дополнительных процессов накладывает overhead, который оправдан только при больших объёмах данных.

parallel_tuple_cost определяет стоимость передачи строки от рабочего процесса к главному. По умолчанию = 0.1. Если у вас много параллельных запросов и они возвращают по тысяче строк — этот параметр влияет на итоговую стоимость плана. Уменьшение parallel_tuple_cost делает параллелиз выгоднее, но при этом увеличивается нагрузка на общую память.

Ещё один параметр: enable_parallel_hash. Он включает использование хеш-таблиц в параллельных запросах. Хеш-таблицы требуют много памяти — если work_mem ограничен, они могут не поместиться в буфер сортировки и уйти на диск. Это резко замедляет выполнение. На системах с ограниченной памятью стоит отключить parallel_hash_join или увеличить work_mem заранее.

Мониторинг потребления памяти и анализ нагрузок

После того, как параметры настроены — нужно следить за тем, как они работают в реальности. PostgreSQL предоставляет множество системных таблиц для анализа: pg_stat_database показывает общее потребление памяти каждым подключением, pg_stat_activity даёт информацию о текущих запросах и их состоянии. Если вы видите запросы в состоянии waiting for buffers — значит, буферов не хватает или они заняты другими процессами.

Важно регулярно проверять количество грязных страниц (dirty pages) в shared_buffers. Параметр synchronous_commit влияет на то, как часто данные пишутся на диск. В режиме async_commit PostgreSQL может накапливать много изменений в памяти перед сбросом в файловую систему. Если база использует много данных и dirty pages растут до предела — при отключении питания данные могут потеряться. Это компромисс между скоростью записи и надёжностью.

Для анализа можно использовать pg_stat_user_tables, чтобы увидеть, какие таблицы чаще всего читаются из кэша. Если таблица редко обновляется и почти всегда читается — она должна оставаться в shared_buffers как можно дольше. Напротив, если таблица активно меняется — её данные будут быстро вытесняться при наличии других запросов. Это нормально: база не может держать в памяти всё одновременно.

Ещё один способ диагностики — профилирование выполнения запросов через pg_stat_statements. Оно показывает, сколько времени занимает каждый запрос и какие ресурсы он потребляет. Если есть запросы, которые долго ждут на буферизацию данных — стоит увеличить shared_buffers или пересмотреть нагрузку. Иногда помогает изменить порядок таблиц в JOIN или добавить недостающие индексы.

Итоговые рекомендации по конфигурации памяти

Оптимизация памяти в PostgreSQL — это поиск баланса между скоростью выполнения запросов и стабильностью работы всей системы. shared_buffers лучше ставить на 25–30% от доступной оперативной памяти, но не более того. Остальное место отдайте ядру для кэша файловых систем и другим процессам. work_mem следует подбирать исходя из типов запросов: если у вас много параллельных сортировок и агрегаций — повышайте его до 64–128 МБ, но следите за общим потреблением. effective_cache_size должен быть больше shared_buffers, чтобы планировщик понимал, что есть место под кэш ядра.

Не стоит опираться на фиксированные значения из интернета: у каждого окружения своя нагрузка, свои типы запросов и особенности дисковой подсистемы. Лучше начать с рекомендаций документации, протестировать под нагрузкой, посмотреть статистику и корректировать параметры. Если база работает нестабильно — возможно, стоит выделить больше памяти под буферы или добавить индексов для ускорения сканирований.

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