Зачем вообще трогать postgresql.conf и с чего начать
Значения по умолчанию рассчитаны на то, чтобы СУБД запустилась почти на любом железе. Для PostgreSQL 16 ориентиры такие: shared_buffers обычно 128 МБ (может быть меньше, если ограничения ядра не позволяют выделить больше при initdb), work_mem 4 МБ. Значения maintenance_work_mem, effective_cache_size, max_wal_size и checkpoint_timeout в открытой документации, доступной для этой статьи, не подтверждены, поэтому перед правкой сверяйтесь с ними в pg_settings своего кластера: SHOW maintenance_work_mem; и так далее по списку. На сервере с 16 или 32 ГБ RAM дефолтная конфигурация часто оставляет память неиспользованной, а планировщик недооценивает объём данных, который уже лежит в кэше ОС.
Тюнинг имеет смысл при подтверждённой проблеме. Признаки, по которым его пора начинать: растут temp_bytes в pg_stat_database, число контрольных точек по требованию заметно превышает плановые, в EXPLAIN (ANALYZE, BUFFERS) видны seq scan на селективных выборках, время ответа упирается в дисковое чтение. Если метрики ровные и SLO соблюдаются, конфиг лучше не трогать: любое изменение добавляет риск без гарантированной выгоды.
Параметры памяти и WAL задают коридор, внутри которого значения подбирают под конкретную нагрузку. Цифры из таблицы ниже — отправная точка для тестовой среды. На проде итоговые значения зависят от числа одновременных соединений, доли чтения и записи, типа накопителя и размера горячего набора данных.
Безопасный порядок действий выглядит так:
- Снять базовый срез метрик за окно реальной нагрузки: pg_stat_statements, pg_stat_database, pg_stat_bgwriter.
- Сделать резервную копию. Логическая через pg_dump, физическая через pg_basebackup, и обязательно проверить восстановление на отдельном инстансе.
- Сохранить исходные значения параметров и написать план отката: какие строки вернуть, нужен ли перезапуск.
- Прогнать изменения на тестовой среде с той же нагрузкой, что на проде.
- Менять по одному параметру за прогон, иначе не получится связать эффект с конкретной правкой.
- Выкатить на прод в окне обслуживания, для параметров уровня postmaster — с перезапуском.
Отдельная тема — бэкапы и PITR: настройка pgBackRest с полными и дифференциальными копиями и восстановлением до точки времени закрывает вопрос откатных сценариев полностью.
Какие параметры требуют перезапуска, а какие — reload
Способ применения параметра виден в представлении pg_settings, в столбце context. Запрос ниже показывает контекст для интересующего набора:
SELECT name, setting, unit, context FROM pg_settings WHERE name IN ('shared_buffers','work_mem','maintenance_work_mem','wal_buffers','max_wal_size','checkpoint_timeout','effective_cache_size');
| Параметр | context | Как применить |
|---|---|---|
| shared_buffers | postmaster | только перезапуск |
| wal_buffers | postmaster | только перезапуск |
| max_wal_size | sighup | pg_reload_conf() или pg_ctl reload |
| checkpoint_timeout | sighup | pg_reload_conf() или pg_ctl reload |
| work_mem | user | reload для новых сессий, SET для текущей |
| maintenance_work_mem | user | reload для новых сессий, SET для текущей |
| effective_cache_size | user | reload для новых сессий, SET для текущей |
Для параметров с контекстом user действует нюанс: уже открытые сессии сохраняют старое значение, перечитывание конфига влияет на новые подключения. Если нужно ускорить одну тяжёлую операцию прямо сейчас, примените SET LOCAL work_mem = '256MB' внутри транзакции.
Перезапуск на проде — это плановое окно, разрыв соединений и время на восстановление после остановки. Проверьте, что сервис поднимется автоматически (Restart= в unit-файле systemd), и держите под рукой бэкап. На Ubuntu 24.04 с пакетом postgresql-16 из PGDG перезапуск выполняют через systemctl restart postgresql либо pg_ctlcluster 16 main restart.
Параметры памяти PostgreSQL: что за что отвечает и как они связаны
shared_buffers — разделяемый кэш страниц самой СУБД. Это первое место, где PostgreSQL ищет страницу перед обращением к файловой системе. Ориентир для старта — около 25% RAM: 2 ГБ на 8 ГБ, 4 ГБ на 16 ГБ, 8 ГБ на 32 ГБ. Выше 40% обычно смысла мало: остальное ядро всё равно использует под page cache. Параметр задаётся только при запуске сервера, поэтому его изменение требует перезапуска. Значение по умолчанию обычно 128 МБ, минимально допустимое — 128 КБ (документация PostgreSQL, раздел Resource Consumption).
effective_cache_size память не выделяет. Это подсказка планировщику, сколько данных, по его оценке, доступно в кэше ОС вместе с shared_buffers. Значение влияет на стоимость чтения при оценке плана и на выбор между index scan и seq scan. Типичный ориентир — 50–75% RAM.
work_mem — лимит памяти на одну операцию сортировки или хеширования в узле плана, а не на соединение целиком. Если в запросе два узла с сортировкой, каждый может занять свой work_mem. При сотне активных соединений суммарный расход считается умножением, и именно здесь появляется риск OOM killer. Значение по умолчанию — 4 МБ (документация PostgresPro, Resource Consumption).
maintenance_work_mem обслуживает VACUUM, CREATE INDEX и ALTER TABLE. Значение не умножается на число клиентских соединений, но каждый autovacuum worker может использовать свою порцию, поэтому учитывайте autovacuum_max_workers. Для старта maintenance_work_mem держат заметно выше work_mem.
wal_buffers — буфер под записи WAL до сброса на диск. Значение по умолчанию -1 задаёт авторазмер: 1/32 (около 3%) от shared_buffers, но не меньше 64 КБ и не больше размера одного сегмента WAL (обычно 16 МБ) (документация PostgresPro, Write Ahead Log). Явные 16 МБ покрывают почти все сценарии; реальную потребность показывает wal_buffers_full в pg_stat_wal.
Связки между параметрами простые. shared_buffers плюс кэш ОС примерно равны effective_cache_size: если вы занизили одно, планировщик начнёт недооценивать кэш и уходить в лишние чтения. work_mem и maintenance_work_mem вместе с shared_buffers не должны вытеснять ядро и page cache: суммарное потребление памяти под СУБД оставляйте в пределах 70–80% RAM, остальное нужно системе.
work_mem: почему «побольше» — плохая идея
work_mem выделяется на каждую операцию сортировки или хеша в плане запроса. Один и тот же запрос с двумя сортировками и хеш-соединением способен занять три порции. Умножьте на число одновременных сессий, которые выполняют такие запросы, и получите реальный расход.
Признак нехватки work_mem — временные файлы. Проверяйте их по базам:
SELECT datname, temp_files, temp_bytes FROM pg_stat_database ORDER BY temp_bytes DESC;
Рост temp_bytes при неизменной нагрузке означает, что сортировки уходят на диск. Глобально поднимать work_mem после этого не стоит: сначала найдите конкретные запросы по temp_blks_written в pg_stat_statements и поднимите лимит точечно через SET LOCAL в сессии или через ALTER ROLE ... SET work_mem для сервисной роли.
effective_cache_size и планировщик: почему это не про выделение памяти
Планировщик сравнивает стоимость вариантов плана, и effective_cache_size задаёт его ожидание по кэшу ОС. Заниженное значение толкает к seq scan и лишним чтениям, завышенное — к выбору index scan там, где последовательное чтение дешевле.
Вторая половина картины — стоимость страниц: seq_page_cost по умолчанию 1.0, random_page_cost по умолчанию 4.0 (документация PostgresPro, Query Planning). Соотношение 4:1 отражает механические диски. Для SSD и NVMe в практических материалах встречается ориентир около 1.1, потому что случайное чтение там почти не дороже последовательного; это не норматив из документации, а отправная точка для проверки (практический материал по настройке postgresql.conf). Точное значение подбирают по EXPLAIN (ANALYZE, BUFFERS): сравните план до и после правки на тестовой копии данных и посмотрите, изменился ли выбор метода доступа.
Autovacuum к памяти прямого отношения не имеет, но maintenance_work_mem ускоряет его проходы по индексам, а раздувание таблиц из-за редкого vacuum портит статистику и планы. Настройки autovacuum проверяйте вместе с параметрами памяти.
Стартовые значения для 8, 16 и 32 ГБ RAM
Таблица ниже — точка отсчёта для тестовой среды. Значения рассчитаны на сервер, выделенный под PostgreSQL, с нагрузкой смешанного типа. Универсального рецепта нет: на сервере с 32 ГБ RAM, где горячий набор данных 2 ГБ, shared_buffers в 8 ГБ просто нечем заполнить.
| Параметр | 8 ГБ | 16 ГБ | 32 ГБ | Комментарий |
|---|---|---|---|---|
| shared_buffers | 2GB | 4GB | 8GB | около 25% RAM, нужен перезапуск |
| effective_cache_size | 5GB | 10GB | 20GB | подсказка планировщику, память не резервирует |
| maintenance_work_mem | 256MB | 512MB | 1GB | VACUUM, CREATE INDEX, ALTER TABLE |
| work_mem | 4MB | 8MB | 16MB | на операцию, умножается на соединения |
| wal_buffers | 16MB | 16MB | 16MB | больше 16 МБ редко даёт эффект |
| min_wal_size | 512MB | 1GB | 2GB | чтобы файлы WAL не пересоздавались постоянно |
| max_wal_size | 2GB | 4GB | 8GB | мягкий лимит, требует места на диске |
| checkpoint_timeout | 15min | 15min | 15min | интервал между контрольными точками |
| checkpoint_completion_target | 0.9 | 0.9 | 0.9 | растягивает запись checkpoint во времени |
| random_page_cost | 1.1 | 1.1 | 1.1 | для SSD/NVMe, проверять через EXPLAIN |
Применять изменения удобнее через ALTER SYSTEM, например ALTER SYSTEM SET shared_buffers = '4GB';. Команда пишет значение в postgresql.auto.conf, а он перекрывает postgresql.conf. Это частая причина путаницы: правите postgresql.conf, а действует строка из auto.conf. Столбец source в pg_settings показывает, откуда взято текущее значение.
Дальше для reload-параметров: SELECT pg_reload_conf(); Для postmaster-параметров (shared_buffers, wal_buffers) нужен перезапуск, иначе значение в pg_settings останется старым.
WAL и checkpoint: как не устроить шторм записи
max_wal_size задаёт верхнюю границу WAL, накопленного между контрольными точками. checkpoint_timeout — временной интервал. Срабатывает то, что наступит раньше: если WAL заполнился за три минуты, checkpoint пройдёт по объёму, сколько бы ни было выставлено в checkpoint_timeout. Слишком маленькие значения дают частые checkpoint и пиковую нагрузку на диск.
checkpoint_completion_target растягивает запись грязных страниц на большую часть интервала: при 0.9 checkpointer старается уложиться в 90% времени до следующей контрольной точки, и пики записи сглаживаются.
Рост max_wal_size увеличивает размер pg_wal на диске. Лимит мягкий, при всплесках записи каталог может занять больше заявленного, поэтому держите запас свободного места и мониторьте df. Второй побочный эффект — более долгое восстановление после аварийной остановки: серверу придётся проиграть больше WAL.
full_page_writes оставляйте включённым. Отключение экономит запись, но при сбое посреди записи страницы вы рискуете получить повреждение, которое не восстановить. Настройка по умолчанию on защищает от этого сценария.
Как читать статистику фоновой записи и контрольных точек
В PostgreSQL 16 статистика фоновой записи и контрольных точек собрана в pg_stat_bgwriter. Точный перечень столбцов этого представления в доступной документации не подтверждён, поэтому перед построением запросов сверьтесь со схемой своего кластера:
SELECT * FROM pg_stat_bgwriter;
Ориентируйтесь на смысл счётчиков, а не на угадывание имён. Плановые контрольные точки (запущенные по checkpoint_timeout) и контрольные точки по требованию (из-за объёма WAL или команды CHECKPOINT) в этом представлении считаются отдельно; также там есть счётчики записанных страниц и активности фонового писателя. Если нужного столбца в вашей версии нет, проверьте pg_stat_checkpointer и pg_stat_io — в новых мажорных версиях часть счётчиков перенесена туда. Например, в документации PostgresPro Enterprise 17 отдельное представление pg_stat_checkpointer содержит столбцы num_timed (количество запланированных из-за тайм-аута контрольных точек) и num_requested (количество запрошенных контрольных точек, которые уже были выполнены) (документация PostgresPro Enterprise 17, Monitoring Stats).
Главный сигнал — соотношение плановых и требуемых контрольных точек. Когда требуемых становится столько же или больше, контрольные точки запускаются объёмом WAL, и max_wal_size для этой нагрузки мал. Разумное поведение — steady state, где большинство checkpoint приходит по таймеру.
WAL-часть смотрите в pg_stat_wal: wal_bytes показывает объём сгенерированного WAL, wal_buffers_full — сколько раз запись ждала сброса буфера, wal_write_time и wal_sync_time — затраты времени на запись и fsync. Рост wal_buffers_full при интенсивной записи — аргумент в пользу 16 МБ (значение по умолчанию -1 обычно даёт меньше).
Счётчики накопительные, столбец stats_reset показывает момент последнего сброса. Перед экспериментом статистику удобно обнулить: SELECT pg_stat_reset_shared('bgwriter'); Если позже обновитесь до PostgreSQL 17, учтите перенос: счётчики контрольных точек там живут в pg_stat_checkpointer (num_timed, num_requested), а разбивка ввода-вывода по процессам — в pg_stat_io. Подробный разбор такого расследования есть в материале про pg_stat_io и поиск узких мест по чтению, записи и фоновым процессам.
Проверка результата: pg_stat_statements и метрики
Без измерений тюнинг превращается в гадание. Порядок такой:
- Добавьте shared_preload_libraries = 'pg_stat_statements' в конфиг. Параметр читается только при старте, поэтому нужен перезапуск.
- Создайте расширение: CREATE EXTENSION pg_stat_statements; в каждой базе, где нужны метрики.
- Снимите срез за фиксированное окно нагрузки до изменений.
- Измените один параметр, повторите нагрузку тем же сценарием.
- Сравните срезы.
Запрос по самым дорогим запросам:
SELECT queryid, calls, round(total_exec_time::numeric, 1) AS total_ms, round(mean_exec_time::numeric, 1) AS mean_ms, temp_blks_written, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
temp_blks_written показывает, сколько блоков ушло во временные файлы. Размер блока проверьте через SHOW block_size, по умолчанию 8192 байта. Падение temp_blks_written у тяжёлых сортировок после правки work_mem или maintenance_work_mem — понятный признак, что память дошла до нужного места. Для сравнения «до и после» полезна и доля попаданий в кэш: blks_hit / (blks_hit + blks_read) из pg_stat_database.
Сброс статистики через SELECT pg_stat_statements_reset(); стирает накопленные данные по всем запросам. Делайте это осознанно, лучше сразу после снятия среза, а не посреди эксперимента.
Ожидаемые результаты формулируются как направления, а не как проценты: перестаёт расти temp_bytes при той же нагрузке, доля плановых контрольных точек увеличивается, запись от контрольных точек распределяется ровнее по времени, mean_exec_time у запросов с сортировками снижается. Методику нагрузочного тестирования с pgbench и sysbench, включая интерпретацию результатов, разбираем в отдельном материале про производительность PostgreSQL и MySQL под нагрузкой.
Медленные запросы, не попавшие в топ по суммарному времени, ловятся логированием: log_min_duration_statement = '500ms' даст их в логе. Как настроить уровни и не утонуть в объёме логов, описано в гайде по уровням логирования PostgreSQL, MySQL и MongoDB.
Типичные ошибки и чек-лист перед применением на проде
Ошибки, которые повторяются чаще всего:
- Менять пять параметров сразу и потом не понимать, что дало эффект, а что навредило.
- Поднимать work_mem глобально, не считая произведения на число соединений и узлов плана. Результат — OOM killer и падение сервера под нагрузкой.
- Увеличивать max_wal_size и забывать про место под pg_wal.
- Править postgresql.conf, когда действует postgresql.auto.conf с более поздним значением.
- Применять postmaster-параметры без окна перезапуска и без проверки, что сервис поднимется автоматически.
- Тюнинговать прод без бэкапа и без тестовой среды, повторяющей профиль нагрузки.
Чек-лист перед выкаткой:
- Есть резервная копия и проверенное восстановление (pg_dump, pg_basebackup, для регулярной схемы — pgBackRest с архивом WAL).
- Тестовая среда прогнана реальной нагрузкой, а не только синтетикой.
- Изменения внесены по одному параметру, для каждого зафиксированы метрики до и после.
- Исходные значения сохранены в файл или в задачу, план отката описан по шагам.
- Для postmaster-параметров выделено окно перезапуска и предупреждены потребители.
- После применения в течение хотя бы одного полного цикла нагрузки снимаются метрики и сравниваются с базовыми.
Проверка действующих значений и источника их происхождения:
SELECT name, setting, unit, source FROM pg_settings WHERE name IN ('shared_buffers','effective_cache_size','work_mem','maintenance_work_mem','wal_buffers','max_wal_size','checkpoint_timeout','checkpoint_completion_target','random_page_cost','max_connections');
В столбце source будет configuration file, environment variable, default или override. Если после ALTER SYSTEM и перезапуска shared_buffers всё ещё показывает default, значит правка не попала в файл или применена не к тому кластеру.
Начните с одного сервера и одного параметра: снимите срез метрик, поменяйте shared_buffers или max_wal_size, прогоните ту же нагрузку и сравните цифры. Дальше решение принимают по данным, а не по таблице из статьи.