Оптимизация PostgreSQL: тюнинг postgresql.conf под VPS

PostgreSQL из коробки настроен консервативно — он должен запуститься даже на слабом железе. На VPS с NVMe и достаточным объёмом RAM параметры по умолчанию оставляют производительность на столе. Разберём, какие строки postgresql.conf реально влияют на скорость и как подобрать их под свой сервер.

С чего начать: где лежит конфиг

Прежде чем крутить параметры, найдите активный файл конфигурации. На разных дистрибутивах он лежит по-разному, поэтому спросим сам сервер:

sudo -u postgres psql -c 'SHOW config_file;'
# типовой путь: /etc/postgresql/16/main/postgresql.conf

Заодно проверим версию и объём оперативной памяти — от него пляшем при расчёте буферов.

psql --version
free -h
nproc   # число ядер

Все правки делайте в отдельном файле conf.d, чтобы не потерять изменения при обновлении пакета. PostgreSQL подхватывает такие файлы автоматически, если в основном конфиге есть директива include_dir.

Память: shared_buffers, work_mem, cache

Три ключевых параметра памяти определяют, насколько активно СУБД использует RAM вместо диска. Базовые ориентиры для выделенного под БД сервера:

• shared_buffers — 25% от RAM. Это собственный кэш страниц PostgreSQL.
• effective_cache_size — 50–75% от RAM. Планировщику это подсказка, сколько данных реально в кэше ОС и БД.
• work_mem — память на одну операцию сортировки/хэша. Начните с 16–64 МБ, помня, что параметр умножается на число параллельных операций.

Для VPS на 8 ГБ RAM разумная отправная точка выглядит так:

# /etc/postgresql/16/main/conf.d/tuning.conf
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 32MB
maintenance_work_mem = 512MB

Диск и WAL: настройки под NVMe

На SSD/NVMe стоимость случайного чтения почти равна последовательному, поэтому планировщик нужно об этом предупредить, снизив random_page_cost. Иначе он будет избегать индексов там, где они выгодны.

random_page_cost = 1.1
effective_io_concurrency = 200   # для NVMe

Журнал WAL отвечает за надёжность и влияет на скорость записи. Увеличенные пределы уменьшают число контрольных точек и сглаживают нагрузку на диск:

wal_buffers = 16MB
min_wal_size = 1GB
max_wal_size = 4GB
checkpoint_completion_target = 0.9

Быстрый NVMe — здесь не формальность: на тарифах MAATRIX диски NVMe на AMD EPYC, и именно на операциях WAL и контрольных точек разница с обычным SSD видна в latency записи.

Соединения и autovacuum

PostgreSQL создаёт процесс на каждое соединение, поэтому огромный max_connections вредит — лучше поставить пул (PgBouncer) и держать лимит умеренным.

max_connections = 100
# для параллельных запросов на многоядерном VPS
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

Autovacuum убирает мёртвые строки и обновляет статистику. На нагруженной БД его стоит сделать активнее, чтобы таблицы не пухли:

autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
autovacuum_max_workers = 4

Применение и проверка

Часть параметров требует полного перезапуска, часть подхватывается перезагрузкой конфигурации. Проверить, что именно нужно, поможет сама СУБД.

sudo systemctl restart postgresql
# или мягко, без разрыва соединений, если правка это позволяет:
sudo -u postgres psql -c 'SELECT pg_reload_conf();'

Убедитесь, что значения применились, и включите статистику по медленным запросам через расширение pg_stat_statements — без данных тюнинг превращается в гадание.

sudo -u postgres psql -c 'SHOW shared_buffers;'
sudo -u postgres psql -c 'CREATE EXTENSION IF NOT EXISTS pg_stat_statements;'

• Задрали work_mem на весь сервер — при десятках соединений память кончится, придёт OOM-killer.
• shared_buffers = 80% RAM — двойное кэширование с ОС, толку меньше, чем кажется.
• Не тронули random_page_cost на NVMe — планировщик игнорирует индексы.

Где держать базу

PostgreSQL любит быстрый диск и предсказуемое ядро CPU. AMD EPYC + NVMe у MAATRIX дают низкую latency на WAL и быстрый VACUUM, а локации UK, США и РФ подходят, когда база обслуживает зарубежный сервис. Оплата картой РФ, СБП, криптой или токеном MAAT снимает проблему с зарубежными провайдерами, ежедневные бэкапы страхуют от потери данных, а root-доступ позволяет править любые строки postgresql.conf без ограничений.

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