В реальных проектах PostgreSQL часто работает не на полную мощность из-за дефолтных настроек, которые не учитывают специфику нагрузки. Разберём 5 неочевидных параметров, которые стоит подкрутить в postgresql.conf для стабильной работы в продакшене.
Важно: перед внесением изменений обязательно протестируйте их в тестовой среде. Изменения глобальных параметров конфигурации могут как ускорить, так и замедлить работу базы данных.
1. random_page_cost — учим оптимизатор работать с SSD
Суть: этот параметр задаёт относительную стоимость случайного чтения с диска по сравнению с последовательным. По умолчанию он равен 4.0, что рассчитано под классические HDD.
Проблема: если у вас SSD (или NVMe), база будет «бояться» использовать индексы, предпочитая полное сканирование таблицы, что сильно замедляет запросы.
Решение: уменьшите значение до 1.1–1.5. Это даст сигнал оптимизатору, что случайное чтение почти не уступает последовательному, и он начнёт активнее строить планы с индексами.
Пример:
sqlALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET random_page_cost = 1.1;
Как применить: после выполнения ALTER SYSTEM обязательно выполните SELECT pg_reload_conf(); или перезапустите сервер, чтобы изменения вступили в силу.
2. effective_cache_size — правильный взгляд на кэш
Суть: планировщик запросов использует этот параметр, чтобы оценить, сколько данных может уместиться в кэше ОС и самой базы. От него напрямую зависит выбор между индексом и полным сканированием.
Ошибка: часто оставляют дефолтные 4 ГБ, даже если у сервера 32+ ГБ ОЗУ.
Решение: установите значение в диапазоне 50–75% от общего объёма оперативной памяти сервера. Это заставит планировщик чаще выбирать индексное сканирование.
Параметр effective_cache_size не выделяет реальную память и не резервирует место в shared_buffers. Это ориентир для планировщика запросов, который оценивает, сколько памяти (включая кеш ОС) доступно для кэширования данных.
Суть: Чем выше значение, тем выше вероятность, что планировщик выберет сканирование по индексу (так как «думает», что данные поместятся в кеш). Это влияет на план запроса, а не на фактическое хранение данных
Пример для сервера с 16 ГБ ОЗУ:
sqlALTER SYSTEM SET effective_cache_size = '12GB';
-- Допустимо также без кавычек: effective_cache_size = 12GB
ALTER SYSTEM SET effective_cache_size = '12GB';
-- Допустимо также без кавычек: effective_cache_size = 12GB
Как проверить эффективность: Выполните запрос, чтобы узнать процент попаданий в кэш:
sqlSELECT sum(blks_hit) / (sum(blks_hit) + sum(blks_read)) * 100 AS cache_hit_ratio
FROM pg_stat_database;
SELECT sum(blks_hit) / (sum(blks_hit) + sum(blks_read)) * 100 AS cache_hit_ratio
FROM pg_stat_database;
Совет: для более детального анализа используйте представление pg_stat_statements — агрегированные данные по всей базе могут быть неинформативны.
3. autovacuum_vacuum_scale_factor — настраиваем частоту «уборки»
Суть: параметр определяет, при каком проценте изменённых строк в таблице запустится VACUUM. По умолчанию — 20% (0.2).
Проблема: для «горячих» таблиц (например, заказов или логов), которые обновляются постоянно, 20% — это слишком много. К моменту запуска вакуума таблица может «распухнуть» от мёртвых строк, что замедлит запросы.
Решение: для активно изменяемых таблиц снижайте порог до 5–10% (0.05–0.1). Можно настроить индивидуально для каждой таблицы.
Пример:
sqlALTER TABLE public.orders SET (autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE public.orders SET (autovacuum_vacuum_scale_factor = 0.05);
Дополнительно: Параметр autovacuum_naptime (интервал между проверками) по умолчанию 60 сек. Для нагруженных систем имеет смысл уменьшить его до 10–30 секунд.
4. work_mem — баланс между памятью и диском
Суть: определяет объём памяти для операций сортировки (ORDER BY), хеширования и группировок (GROUP BY) в рамках одной операции внутри запроса.
Ошибка: дефолтные 4 МБ часто недостаточны. Если памяти не хватает, PostgreSQL сбрасывает данные во временные файлы на диск, что кратно замедляет запрос.
Нюанс: work_mem выделяется на каждую операцию сортировки/хеширования внутри запроса, а не на соединение. Если в одном запросе несколько таких операций, память может выделяться многократно. Также при большом количестве параллельных соединений суммарное потребление памяти может превысить лимит.
Решение: увеличьте значение, но осторожно.
Пример:
sqlALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM SET work_mem = '64MB';
Временное применение: для сложных аналитических запросов можно временно поднять значение через сессионное SET:
sqlSET work_mem = '128MB';
-- Выполнить запрос
RESET work_mem;
SET work_mem = '128MB';
-- Выполнить запрос
RESET work_mem;
5. shared_buffers и maintenance_work_mem — память для кэша и обслуживания
shared_buffers
Суть: буфер в оперативной памяти, где хранятся часто читаемые страницы данных. Это один из самых важных параметров для производительности чтения.
Рекомендация: выделите 25–40% от общего объёма ОЗУ под shared_buffers. Не стоит выделять больше 50%, чтобы не оставить ресурсы другим процессам.
Важно: параметр shared_buffers выделяется при старте сервера и не может быть изменён с помощью SELECT pg_reload_conf(). Для применения новых значений требуется перезапуск PostgreSQL. Это связано с тем, что память резервируется в разделяемом сегменте и не подлежит горячей замене
Пример для 16 ГБ ОЗУ:
sqlALTER SYSTEM SET shared_buffers = '6GB';
ALTER SYSTEM SET shared_buffers = '6GB';
maintenance_work_mem (дополнительный совет)
Суть: объём памяти, выделяемый для операций обслуживания: VACUUM, CREATE INDEX, ALTER TABLE. По умолчанию часто недостаточно для больших таблиц.
Рекомендация: увеличьте для продакшена до 256–512 МБ (или больше, в зависимости от размера таблиц). Это ускорит создание индексов и очистку.
Пример:
sqlALTER SYSTEM SET maintenance_work_mem = '256MB';
ALTER SYSTEM SET maintenance_work_mem = '256MB';
Важные примечания
Версии PostgreSQL: Некоторые параметры и их поведение могут отличаться в разных версиях (например, в PostgreSQL 14+ появились новые автонастройки). Обязательно сверяйтесь с официальной документацией под свою версию.
Мониторинг: После изменения параметров отслеживайте метрики в
pg_stat_database,pg_stat_activityиpg_stat_statements.Документация: Полные описания параметров и их влияние доступны в .
Краткий итог
random_page_cost = 1.1(для SSD) +SELECT pg_reload_conf();effective_cache_size = 12GB(50–75% от RAM).autovacuum_vacuum_scale_factor = 0.05(для горячих таблиц).work_mem = 64MB(тестируйте под нагрузку, помните про параллелизм).shared_buffers = 6GB(25–40% от RAM) +maintenance_work_mem = 256MB.
Ключевой совет: не меняйте все параметры разом. Внедряйте изменения поэтапно, обязательно мониторите метрики до и после настройки, чтобы убедиться в положительном эффекте.