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

В PostgreSQL память запроса не ограничена одним значением: несколько сортировок и хеш-операций могут одновременно выделить ресурсы в разных процессах, а параллельный план добавит рабочие процессы. Поэтому увеличение work_mem иногда ускоряет один запрос, но под нагрузкой приводит к нехватке памяти на всём сервере. Ниже — разбор для PostgreSQL 16: какие области памяти существуют, что измерять и где настройка перестаёт помогать.

Память PostgreSQL — это несколько разных бюджетов

Сервер использует общую память, доступную процессам PostgreSQL, и частную память отдельных процессов. Это разные расходы, и один параметр не управляет ими всеми.

• Общая память. В ней размещаются, в частности, буферы PostgreSQL и структуры координации. Главный параметр для кэширования страниц базы — shared_buffers. Увеличить эту область без перезапуска нельзя.
• Память процесса. Для каждого подключения работает отдельный серверный процесс. Он расходует память на соединение, выполнение запроса, его операции и служебные структуры. Частная память одного процесса не становится общим кэшем для остальных.
• Кэш операционной системы. PostgreSQL взаимодействует с файловой системой, поэтому часть часто используемых данных может оставаться в кэше ОС. Это не значение shared_buffers, но при оценке общего расхода RAM его тоже нельзя игнорировать.

Термин. Memory context — область, через которую PostgreSQL управляет выделениями памяти для конкретной задачи или времени жизни объектов. Такая организация помогает освобождать группы выделений вместе, но не превращает work_mem в жёсткий лимит всего процесса.

Почему work_mem не равен памяти на запрос

work_mem задаёт ориентир для отдельных операций плана, которым требуется рабочая память. К ним относятся, например, сортировка и хеширование. Один запрос может иметь несколько таких узлов; несколько запросов могут выполняться одновременно. Поэтому реальный риск зависит не только от значения параметра, но и от числа операций и параллельности.

Параллельный план добавляет процессы-исполнители: рабочая операция может выполняться не только в основном процессе запроса. Для хеш-операций есть отдельный коэффициент hash_mem_multiplier; в PostgreSQL 16 он позволяет рассчитывать больший бюджет для хеша относительно work_mem. Эти параметры нельзя механически складывать для каждого подключения как точный прогноз: не все узлы активны одновременно, а фактическое потребление зависит от данных и плана. Но учитывать возможное наложение операций необходимо.

Когда сортировке или хешированию не хватает памяти, PostgreSQL может использовать временные файлы. Это не всегда признак неисправности: иногда диск — разумная плата за ограниченный RAM. Но крупные или частые временные записи увеличивают задержки и нагрузку на хранилище.

Главная ловушка тюнинга: значение, безопасное для одного запроса в тесте, может оказаться слишком дорогим при одновременном запуске множества запросов.

Какие параметры за что отвечают

Особенно важно не считать effective_cache_size резервом RAM. Параметр помогает планировщику оценивать вероятность чтения из кэша; он не заставляет PostgreSQL выделить указанное количество памяти. Напротив, shared_buffers задаёт реальную область общей памяти сервера.

Что измерять до изменения настроек

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

• План запроса. В EXPLAIN (ANALYZE, BUFFERS) смотрите на типы узлов, время, чтение буферов и признаки сортировки с выходом на диск. ANALYZE действительно выполняет запрос, поэтому для изменяющих данные команд заранее учитывайте последствия.
• Временные файлы. Счётчики temp_files и temp_bytes в статистике базы помогают заметить временную активность. Это накопительные значения: сопоставляйте их за интервал, а не принимайте весь счётчик за результат одного запроса.
• Контексты памяти. Представление pg_backend_memory_contexts показывает контексты текущего серверного процесса. Это полезная диагностика для конкретной сессии, но не полный отчёт о расходе всей системы.
• Память на уровне ОС или контейнера. Наблюдайте за фактическим пиком и давлением на память в том же окружении, где работает PostgreSQL. На Linux сумма RSS процессов может повторно учитывать общие страницы; для контейнера ориентируйтесь также на показатели его cgroup.

Если включён pg_stat_statements, его статистика помогает связать временный ввод-вывод с группами запросов. Расширение не заменяет разбор плана: одинаковый запрос может выбрать разные планы для разных параметров или объёмов данных.

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

1. Определите предел памяти. Учитывайте физическую RAM или лимит контейнера, общую память PostgreSQL, запас для ОС и другие процессы. Проверьте не только обычную нагрузку, но и ожидаемый пик.
2. Найдите источник давления. Сопоставьте время проблем с активными запросами, временными файлами, планами и задачами обслуживания. Если растёт общий расход, но нет характерных временных операций, причина может быть не в work_mem.
3. Меняйте один параметр за раз. Запишите исходное значение, примените изменение к ограниченной группе или тестовой среде и повторите измерение при похожей нагрузке. Для shared_buffers запланируйте перезапуск; для других параметров заранее проверьте область действия изменения в документации PostgreSQL 16.
4. Проверьте худший реалистичный сценарий. Сравните не только среднюю задержку одного запроса, но и пик памяти, число временных записей, ошибки и задержку при ожидаемой конкуренции. Успешный одиночный тест не подтверждает безопасность настройки в проде.

Где тюнинг ломается

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

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

Третий — ожидание, что временные файлы исчезнут после повышения лимита. Более крупный бюджет иногда ускоряет конкретный запрос, но если причиной оказались плохая оценка числа строк, неподходящий план, лишняя сортировка или чрезмерная параллельность, одна настройка не устраняет источник работы. После изменения проверьте, что план и измеряемая задержка действительно стали лучше.

Что делать дальше

Сначала выберите один тяжёлый запрос или период, когда сервер упирается в память. Зафиксируйте его план, временные операции, конкурентную нагрузку и пик памяти на уровне ОС или контейнера. Затем измените один параметр в PostgreSQL 16 и повторите тот же сценарий. Если параллельно меняются данные, план или нагрузка, результат нельзя честно приписать настройке.

Инвалидация кэша и настройка конкретных запросов — отдельные темы; здесь речь шла о бюджете памяти и его измерении.