Оптимизация запросов к PostgreSQL: 5 неочевидных настроек для продакшена

В реальных проектах 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% от общего объёма оперативной памяти сервера. Это заставит планировщик чаще выбирать индексное сканирование.

Пример для сервера с 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%, чтобы не оставить ресурсы другим процессам.

Пример для 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';

Важные примечания

1. Версии PostgreSQL: Некоторые параметры и их поведение могут отличаться в разных версиях (например, в PostgreSQL 14+ появились новые автонастройки). Обязательно сверяйтесь с официальной документацией под свою версию.

2. Мониторинг: После изменения параметров отслеживайте метрики в pg_stat_database, pg_stat_activity и pg_stat_statements.

3. Документация: Полные описания параметров и их влияние доступны в .

Краткий итог

1. random_page_cost = 1.1 (для SSD) + SELECT pg_reload_conf();

2. effective_cache_size = 12GB (50–75% от RAM).

3. autovacuum_vacuum_scale_factor = 0.05 (для горячих таблиц).

4. work_mem = 64MB (тестируйте под нагрузку, помните про параллелизм).

5. shared_buffers = 6GB (25–40% от RAM) + maintenance_work_mem = 256MB.

Ключевой совет: не меняйте все параметры разом. Внедряйте изменения поэтапно, обязательно мониторите метрики до и после настройки, чтобы убедиться в положительном эффекте.