Значения PostgreSQL по умолчанию рассчитаны на машину 2005 года, которая делит свой диск со всем остальным. На VPS-4 с 8 GB RAM и NVMe десять параметров превращают его из осторожного в быстрый, и ни один из них не экзотичен. Ниже — значения, которые мы используем в инсталляциях PostgreSQL, которые ведёт наша команда, и для каждого указан симптом, который он устраняет, чтобы вы могли определить, какие из них вам действительно нужны.
Память: четыре параметра
shared_buffers = 2GB
Кэш, которым PostgreSQL управляет сам. Четверть RAM — давно известное правило, и на Linux оно остаётся справедливым: сверх этого ту же задачу решает страничный кэш ядра, причём с меньшей двойной буферизацией. Симптом значения по умолчанию (128 MB): высокий read IOPS на таблицах, которые должны помещаться в память.
effective_cache_size = 6GB
Это не выделение памяти, а подсказка: сколько памяти планировщик вправе считать доступной для кэширования, включая страничный кэш ядра. Задайте около 75% от RAM. Симптом: планировщик выбирает последовательное сканирование вместо индексного на таблицах среднего размера, потому что считает чтение дорогим.
work_mem = 32MB
Выделяется на каждую операцию сортировки или хеширования в каждом запросе, поэтому сложный запрос может занять в несколько раз больше. При 50 соединениях 32 MB безопасно на 8 GB; для отчётных запросов повышайте значение в рамках сессии командой SET work_mem = '256MB'. Симптом значения по умолчанию (4 MB): EXPLAIN ANALYZE показывает «external merge Disk» на сортировках.
maintenance_work_mem = 512MB
Используется командами VACUUM, CREATE INDEX и ALTER TABLE. Чем больше значение, тем заметно быстрее строятся индексы и работает vacuum, а больше одного процесса одновременно его используют редко. Симптом: создание индекса на большой таблице занимает гораздо больше времени, чем можно ожидать, глядя на число строк.
Чекпоинты: как убрать всплески записи
checkpoint_completion_target = 0.9 и max_wal_size = 4GB
PostgreSQL сначала записывает изменения в WAL, а грязные страницы сбрасывает в файлы данных во время чекпоинтов. При настройках по умолчанию под нагрузкой на запись чекпоинты наступают примерно раз в минуту и сбрасывают данные одним махом, после чего следуют скачки задержки. Если растянуть чекпоинт на 90% интервала и разрешить 4 GB WAL между чекпоинтами, скачки превращаются в ровную неспешную запись. Симптом: периодические скачки задержки каждые несколько минут, совпадающие с записью checkpoint starting в логе; checkpoints_req в pg_stat_bgwriter растёт быстрее, чем checkpoints_timed.
wal_compression = on
Сжимает полностраничные образы в WAL. Немного нагружает CPU, зато заметно экономит пропускную способность записи, а на VPS с лимитом IOPS такой обмен всегда оправдан.
Расскажите планировщику про NVMe
random_page_cost = 1.1 и effective_io_concurrency = 200
Значение random_page_cost по умолчанию, равное 4, сообщает планировщику, что случайное чтение стоит в четыре раза дороже последовательного. Для вращающихся дисков это было верно, для NVMe — нет. При значении 1.1 планировщик выбирает индексное сканирование там, где оно действительно выигрывает. effective_io_concurrency позволяет bitmap-сканированиям читать с упреждением; для NVMe правильный порядок величины — 200. Симптом: запросы с селективными условиями WHERE по-прежнему выбирают последовательное сканирование.
Autovacuum: пусть работает агрессивно
autovacuum_vacuum_scale_factor = 0.05 и autovacuum_vacuum_cost_limit = 1000
По умолчанию vacuum запускается, только когда мёртвыми становятся 20% таблицы; на таблице в 50 миллионов строк это 10 миллионов мёртвых строк и раздутые индексы. При 5% таблицы остаются компактными; увеличенный cost limit позволяет vacuum действительно успевать на NVMe, а не тормозить сам себя до скорости дисков 2005 года. Для очень «горячих» таблиц задавайте коэффициент отдельно для каждой: ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.01). Симптом: размер таблицы на диске растёт, а число строк — нет; n_dead_tup в pg_stat_user_tables измеряется миллионами.
Соединения: используйте пул
max_connections = 100, затем pgbouncer
Каждое соединение PostgreSQL — это отдельный процесс со своей памятью. Веб-приложение, которое открывает 200 соединений из 20 воркеров, тратит RAM на простаивающие процессы, а CPU — на переключения контекста. Оставьте max_connections равным 100 и, как только число соединений превысит примерно 50, поставьте перед базой pgbouncer в режиме transaction pooling: приложение сохранит свои 200 соединений с пулером, а база увидит 20. Симптом: FATAL: sorry, too many clients already либо нехватка памяти при преобладании простаивающих соединений в pg_stat_activity.
Конфиг целиком
# /etc/postgresql/16/main/conf.d/tuned.conf (8 GB VPS, NVMe)
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 32MB
maintenance_work_mem = 512MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB
wal_compression = on
random_page_cost = 1.1
effective_io_concurrency = 200
autovacuum_vacuum_scale_factor = 0.05
autovacuum_vacuum_cost_limit = 1000
max_connections = 100После изменения shared_buffers или max_connections перезапустите PostgreSQL; остальное применяется через SELECT pg_reload_conf(). Для VPS-8 с 16 GB удвойте первые две строки, для тарифа с 4 GB — уменьшите их вдвое. Остальные от объёма RAM не зависят.
Замеряйте до и после
Прежде чем что-либо менять, включите pg_stat_statements и посмотрите на самые тяжёлые запросы по суммарному времени: настройка без такого обзора — это гадание. После изменений следите за pg_stat_bgwriter.checkpoints_req (должен быть около нуля), скоростью чтения с диска (должна падать по мере прогрева кэша) и p99 обращений вашего приложения к базе данных. На нашем парке один только этот файл обычно вдвое снижает p99 веб-приложения с преобладанием чтения и полностью убирает периодические всплески записи.