PostgreSQL на Linux: базовая настройка и тюнинг для продакшена

Коротко: PostgreSQL из пакета дистрибутива настроен под машину с 1 ГБ памяти: shared_buffers 128 МБ, work_mem 4 МБ, чекпоинты каждые 5 минут или 1 ГБ WAL. Для продакшена достаточно поправить десяток параметров памяти, WAL, планировщика и autovacuum, подготовить ядро Linux (huge pages, swappiness, THP) и включить pg_stat_statements. Остальное настраивается по метрикам, а не наугад.

Типичная история: проект запустили на виртуалке с 16 ГБ памяти, поставили apt install postgresql и забыли. Через полгода запросы, которые раньше выполнялись за 20 мс, идут по секунде, диск постоянно занят, а в логах раз в несколько минут пишется checkpoints are occurring too frequently. База при этом использует меньше гигабайта памяти из шестнадцати — потому что ей никто не разрешил больше.

Дефолтный postgresql.conf на боевом сервере — ещё и заметный красный флаг для любого внешнего ревьюера. Когда продукт переходит к новой команде или инвесторы перед сделкой заказывают технический due diligence, первым делом смотрят именно сюда: конфигурация базы по умолчанию, отсутствие бэкапов и мониторинга, autovacuum, который не успевает за нагрузкой. Ниже — порядок, в котором стоит приводить PostgreSQL в рабочее состояние, с конкретными значениями и командами проверки.

Где лежит конфигурация PostgreSQL и как применять изменения?

Расположение файлов зависит от дистрибутива:

• Debian и Ubuntu: /etc/postgresql/<версия>/main/postgresql.conf, рядом pg_hba.conf и каталог conf.d/.
• RHEL, Rocky, Alma (пакеты PGDG): /var/lib/pgsql/<версия>/data/postgresql.conf.
• Любая система: psql -c 'SHOW config_file;' покажет точный путь.

Удобнее не править основной файл, а положить свои параметры в отдельный файл, например conf.d/10-tuning.conf: при обновлении пакета он не затрётся, а diff с дефолтом виден сразу. Команда ALTER SYSTEM SET ... тоже работает, но пишет в postgresql.auto.conf, и этот файл перекрывает всё остальное — держите настройки в одном месте, иначе через год никто не поймёт, откуда взялось значение.

Часть параметров применяется перечитыванием конфигурации, часть — только рестартом. Узнать, какие изменения ждут перезапуска:

sudo systemctl reload postgresql          # или SELECT pg_reload_conf();
psql -c "SELECT name, setting, context FROM pg_settings WHERE pending_restart;"

Параметры с context = postmaster (shared_buffers, max_connections, huge_pages, shared_preload_libraries) требуют systemctl restart, а значит — окна обслуживания. Соберите их в одну правку, чтобы не перезапускать базу трижды. Как устроены юниты и перезапуск сервисов, разобрано в статье про systemd без боли.

Какие параметры памяти менять в первую очередь?

Четыре параметра дают основной эффект (полное описание — в разделе Resource Consumption документации). Значения ниже — стартовая точка для выделенного сервера под базу, а не догма.

shared_buffers — собственный кэш страниц PostgreSQL. Больше 40 % RAM ставить обычно бессмысленно: база всё равно читает через страничный кэш Linux, и данные начинают кэшироваться дважды. effective_cache_size память не выделяет вовсе — это подсказка планировщику, сколько данных, скорее всего, уже лежит в кэше ОС. Если оставить 4 ГБ на сервере с 64 ГБ, планировщик будет недооценивать индексы.

work_mem — самый опасный параметр. Он выделяется не на соединение, а на каждую операцию сортировки или хеширования в плане запроса, а для хеш-операций ещё умножается на hash_mem_multiplier (2.0 по умолчанию с PostgreSQL 15). Сложный отчёт со своими пятью сортировками при 100 соединениях и work_mem = 256MB легко съест всю память и разбудит OOM killer. Безопаснее держать глобальное значение скромным и поднимать его точечно для тяжёлых запросов: SET work_mem = '256MB'; в сессии или ALTER ROLE reports SET work_mem = '256MB'; для роли отчётов.

Понять, что work_mem не хватает, помогает параметр log_temp_files = 0: каждый временный файл на диске попадёт в лог. В EXPLAIN (ANALYZE, BUFFERS) признак тот же — строка Sort Method: external merge Disk: ... вместо quicksort Memory.

Сколько соединений нужно и зачем PgBouncer?

Каждое соединение с PostgreSQL — отдельный процесс ОС с собственной памятью. max_connections = 1000 «на всякий случай» не ускоряет работу, а замедляет её: растут накладные расходы на снапшоты и блокировки, а 1000 процессов × work_mem превращаются в лотерею с OOM. Рабочий ориентир — количество активных запросов в пике в пределах 2–4 × число ядер CPU.

Если приложению нужны сотни соединений (PHP-FPM, много воркеров, serverless), ставьте перед базой пулер PgBouncer в режиме pool_mode = transaction. Приложение держит 500 «лёгких» соединений с пулером, а база — 40 реальных. Учтите ограничение transaction-режима: сессионные вещи (SET без LOCAL, advisory-локи на сессию, LISTEN) там работают не так, как ожидается.

Как настроить WAL и чекпоинты, чтобы диск не захлёбывался?

При чекпоинте PostgreSQL сбрасывает на диск все изменённые страницы. Если чекпоинты идут слишком часто, диск работает на запись постоянно, а после каждого чекпоинта резко растёт объём WAL из-за полных копий страниц (full page writes). Механика подробно описана в главе WAL Configuration. Стартовые значения:

# conf.d/10-tuning.conf — WAL и чекпоинты
wal_buffers = -1                    # авто: 1/32 от shared_buffers, до 16 МБ
max_wal_size = 4GB                  # по умолчанию 1GB
min_wal_size = 1GB
checkpoint_timeout = 15min          # по умолчанию 5min
checkpoint_completion_target = 0.9  # размазать запись по интервалу
wal_compression = on                # меньше WAL ценой немного CPU

Цель — чтобы чекпоинты запускались по таймеру, а не по переполнению max_wal_size. Проверить можно в логе (с PostgreSQL 15 log_checkpoints включён по умолчанию) или в статистике: в PostgreSQL 17+ это pg_stat_checkpointer, где колонка num_requested должна быть заметно меньше num_timed; в более старых версиях — checkpoints_req и checkpoints_timed в pg_stat_bgwriter.

Помните о цене: чем реже чекпоинты, тем дольше восстановление после аварийного падения — база проигрывает весь WAL с последнего чекпоинта. 15 минут — разумный компромисс для большинства проектов. И отдельно: тюнинг WAL не заменяет резервные копии и архивирование WAL — как организовать их по правилу 3-2-1, описано в статье о стратегиях бэкапа Linux-сервера.

Что поменять в планировщике для SSD?

Дефолты планировщика написаны для вращающихся дисков. random_page_cost = 4.0 говорит ему, что случайное чтение в четыре раза дороже последовательного, — поэтому на SSD он часто выбирает полный перебор таблицы там, где индекс был бы быстрее.

• random_page_cost = 1.1 — для SSD и NVMe, а также если рабочий набор данных целиком помещается в память.
• effective_io_concurrency = 200 — сколько параллельных запросов на чтение может обслуживать хранилище; для SSD значение по умолчанию сильно занижено.
• default_statistics_target оставьте 100, а для колонок с неравномерным распределением поднимайте точечно: ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000; и затем ANALYZE orders;.

Если план запроса внезапно стал плохим после роста таблицы, сначала проверьте, когда по ней последний раз выполнялся ANALYZE (last_autoanalyze в pg_stat_user_tables). Устаревшая статистика встречается гораздо чаще, чем неправильные параметры стоимости.

Почему autovacuum нельзя выключать и как его ускорить?

PostgreSQL не удаляет строки сразу: UPDATE и DELETE оставляют «мёртвые» версии, которые потом убирает autovacuum. Если он не успевает, таблицы и индексы раздуваются (bloat), запросы читают лишние страницы, а в худшем случае база приближается к переполнению счётчика транзакций и уходит в защитный режим. Отключать autovacuum, «потому что он грузит диск», — классическая ошибка, которая через пару месяцев оборачивается многочасовым VACUUM FULL с блокировкой таблицы (см. Routine Vacuuming).

Дефолтные пороги срабатывают, когда изменено 20 % строк таблицы. На таблице в 100 млн строк это 20 млн мёртвых версий до первой очистки. Для крупных таблиц пороги снижают индивидуально, а самого autovacuum делают энергичнее:

# глобально
autovacuum_max_workers = 4
autovacuum_vacuum_cost_limit = 1000     # по умолчанию 200 (через vacuum_cost_limit)
autovacuum_naptime = 30s
log_autovacuum_min_duration = 1s

-- точечно для большой и часто меняющейся таблицы
ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.02,
                        autovacuum_analyze_scale_factor = 0.01);

Найти таблицы, где мёртвых строк больше всего:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

Если last_autovacuum пустой или давний при большом n_dead_tup, ищите долгие транзакции: открытая и забытая сессия idle in transaction не даёт очистке удалить ничего новее себя. Защититься помогает idle_in_transaction_session_timeout = 10min.

Как подготовить Linux под PostgreSQL?

Часть производительности теряется на уровне ядра, и никакой postgresql.conf это не исправит.

1. Huge pages. С большими shared_buffers таблицы страниц занимают сотни мегабайт на каждый процесс. Узнайте, сколько страниц нужно (PostgreSQL 15+, при остановленном сервере): sudo -u postgres /usr/lib/postgresql/17/bin/postgres -D /var/lib/postgresql/17/main -C shared_memory_size_in_huge_pages — путь к бинарнику и данным подставьте свои. Запишите число в vm.nr_hugepages и поставьте huge_pages = on, чтобы база отказалась стартовать, если страниц не хватило, а не тихо работала без них.
2. Transparent Huge Pages — выключить. Фоновая дефрагментация THP даёт непредсказуемые задержки. Проверка: cat /sys/kernel/mm/transparent_hugepage/enabled; нужное значение — [never] или [madvise], задаётся параметром ядра transparent_hugepage=never.
3. Swappiness. vm.swappiness = 1 — ядро не будет выталкивать в swap память базы ради страничного кэша. Полностью убирать swap не стоит: небольшой своп даёт время заметить утечку памяти до прихода OOM killer.
4. Overcommit. Документация PostgreSQL рекомендует vm.overcommit_memory = 2, чтобы OOM killer не убил главный процесс базы. Включайте только вместе с подобранным vm.overcommit_ratio и только на выделенном сервере, иначе другие сервисы начнут получать отказы в выделении памяти.
5. Файловая система. ext4 или XFS с опцией noatime, данные и WAL — на SSD. Вынести WAL на отдельный диск имеет смысл на нагруженной записи.

Параметры ядра кладите в /etc/sysctl.d/30-postgresql.conf и применяйте sysctl --system. Подробно эти механизмы разобраны в разделе Managing Kernel Resources официальной документации.

Как проверить, что тюнинг сработал?

Тюнинг без измерений — гадание. Минимальный набор инструментов:

• pg_stat_statements. Добавьте shared_preload_libraries = 'pg_stat_statements', перезапустите сервер и выполните CREATE EXTENSION pg_stat_statements;. Дальше запрос SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; покажет, какие запросы реально съедают время. Обычно 2–3 запроса дают половину нагрузки, и индекс под них полезнее любого параметра.
• Cache hit ratio. SELECT round(100.0 * sum(blks_hit) / nullif(sum(blks_hit) + sum(blks_read), 0), 2) FROM pg_stat_database; — для OLTP-нагрузки ожидайте 99 % и выше. Заметно меньше — данные не помещаются в память или есть полные сканы больших таблиц.
• Медленные запросы в логе. log_min_duration_statement = 500ms пишет в лог всё, что дольше полсекунды, — без лишнего шума.
• Метрики во времени. postgres_exporter + Prometheus + Grafana покажут, как меняются соединения, чекпоинты, bloat и задержки после каждой правки. Как поднять такой стек, описано в статье о мониторинге Linux-сервера.

Меняйте по одному блоку параметров за раз и сравнивайте метрики за сопоставимые периоды — иначе не поймёте, что помогло, а что навредило. Для первой прикидки значений под конкретное железо подойдут калькуляторы вроде PGTune, но их результат — тоже только стартовая точка.

Не забыть про безопасность

Производительность бессмысленна, если база открыта всему интернету. Проверьте три вещи: listen_addresses — только localhost или внутренний адрес, а не '*' с публичным IP; в pg_hba.conf — метод scram-sha-256 и конкретные подсети вместо 0.0.0.0/0 и trust; порт 5432 закрыт файрволом снаружи. Базовая защита самого сервера разобрана в материале о защите Linux-сервера.

Чеклист настройки PostgreSQL для продакшена

1. Свои параметры лежат в отдельном файле в conf.d/, а postgresql.auto.conf пуст или осознанно используется.
2. shared_buffers ≈ 25 % RAM, effective_cache_size ≈ 50–75 % RAM.
3. work_mem скромный глобально, повышен точечно для тяжёлых ролей и запросов.
4. max_connections соответствует реальной нагрузке, при сотнях клиентов есть PgBouncer.
5. Чекпоинты идут по таймеру, а не по max_wal_size.
6. random_page_cost и effective_io_concurrency настроены под SSD.
7. Autovacuum включён, усилен, для крупных таблиц снижены пороги; есть таймаут для idle in transaction.
8. Huge pages включены, THP выключены, vm.swappiness низкий.
9. Работает pg_stat_statements, медленные запросы и временные файлы пишутся в лог, метрики собираются.
10. База не слушает публичный интерфейс, в pg_hba.conf нет trust, есть проверенные бэкапы.

Частые вопросы

Можно ли поставить shared_buffers на половину памяти?

Можно, но обычно бесполезно. PostgreSQL читает данные через страничный кэш Linux, и при 50 % RAM под shared_buffers одни и те же страницы кэшируются дважды, а на кэш ОС и work_mem остаётся меньше. Начинайте с 25 % и меняйте только по метрикам cache hit ratio.

Какой work_mem ставить?

Глобально — 16–64 МБ в зависимости от памяти и числа соединений. Помните, что work_mem выделяется на каждую операцию сортировки или хеширования, а не на запрос. Для тяжёлых отчётов поднимайте значение через SET в сессии или ALTER ROLE для отдельной роли.

Нужно ли перезапускать PostgreSQL после изменения настроек?

Зависит от параметра. shared_buffers, max_connections, huge_pages и shared_preload_libraries требуют рестарта, большинство остальных применяется через reload. Запрос к pg_settings с условием pending_restart покажет, что ещё ждёт перезапуска.

Помогают ли калькуляторы вроде PGTune?

Да, как стартовая точка: они учитывают объём памяти, число ядер, тип дисков и нагрузки. Но они не знают ваших запросов, поэтому после применения значений всё равно смотрите pg_stat_statements, логи временных файлов и метрики чекпоинтов.

Можно ли выключить autovacuum на нагруженной базе?

Нет. Без autovacuum таблицы и индексы раздуваются, статистика устаревает, а при приближении к переполнению счётчика транзакций база перестанет принимать запись. Если autovacuum мешает, его не выключают, а настраивают: больше воркеров, выше cost_limit, ниже пороги для крупных таблиц.

Нужен ли PgBouncer, если соединений меньше ста?

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

Какие настройки ядра Linux важны для PostgreSQL?

Главные — huge pages под shared_buffers, выключенные Transparent Huge Pages, низкий vm.swappiness и, на выделенном сервере, vm.overcommit_memory = 2 с подобранным overcommit_ratio. Плюс файловая система ext4 или XFS с noatime на SSD.

Вернуться в блог