Как установить и настроить тюнинг PostgreSQL на VPS

MAATRIX

Тюнинг PostgreSQL на VPS — это не магия из десятка чужих конфигов, а понимание нескольких ключевых параметров и настройка их под вашу память, диск и нагрузку. Настройки по умолчанию рассчитаны на самое слабое железо и почти не используют ресурсы сервера. Правильная доводка памяти, планировщика и autovacuum даёт заметный прирост без покупки нового железа. Ниже — рабочий путь: что и почему крутить, как измерять эффект и когда тюнинг упирается в потолок и нужен сервер помощнее.

С чего начинается тюнинг: измерение, а не догадки

Главная ошибка новичка — копировать «идеальный конфиг» из интернета. Такие конфиги писались под другое железо и другую нагрузку, и вслепую они чаще вредят. Правильный тюнинг начинается с измерения: нужно понять, где именно PostgreSQL теряет время. Включите статистику запросов через расширение pg_stat_statements — оно показывает, какие запросы съедают больше всего ресурсов суммарно:

sudo -u postgres psql -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

Добавьте расширение в shared_preload_libraries в конфиге и перезапустите сервер. Теперь вы видите реальную картину нагрузки, а не догадки. Очень часто оказывается, что база тормозит не из-за настроек памяти, а из-за одного-двух тяжёлых запросов без индексов — и тогда правильный ход не крутить параметры, а добавить индекс. Меняйте настройки по одной, замеряйте результат, и только потом двигайтесь дальше.

Настройка памяти: главные параметры

Память — самый весомый рычаг производительности. Четыре параметра определяют почти всё. Ориентиры для сервера с 8 ГБ RAM:

shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 32MB
maintenance_work_mem = 512MB

shared_buffers — основной кэш PostgreSQL, примерно четверть RAM. effective_cache_size — не выделяемая память, а подсказка планировщику, сколько всего памяти доступно под кэш (около 75% RAM); она влияет на выбор планов, но ничего не занимает. work_mem — память на одну операцию сортировки или хеширования, и здесь кроется ловушка: параметр умножается на число одновременных операций, поэтому при сотнях подключений большое значение способно исчерпать всю память. maintenance_work_mem ускоряет создание индексов и работу autovacuum. Начните с этих ориентиров и корректируйте по нагрузке.

Планировщик и настройки под SSD

PostgreSQL по умолчанию считает, что диск медленный, как жёсткий, и его оценки стоимости случайного чтения завышены. На VPS с SSD это приводит к тому, что планировщик избегает индексов там, где они были бы быстрее. Подскажите ему реальную скорость диска:

random_page_cost = 1.1
effective_io_concurrency = 200

random_page_cost = 1.1 говорит планировщику, что случайное чтение на SSD почти так же дёшево, как последовательное, — и он начинает охотнее использовать индексы. effective_io_concurrency разрешает параллельные операции ввода-вывода, что SSD хорошо тянет. Эти два параметра часто дают ощутимый прирост на VPS именно потому, что почти все современные серверы на SSD, а PostgreSQL из коробки настроен консервативно. Проверьте эффект на своих запросах через EXPLAIN ANALYZE — план должен начать выбирать индексы там, где раньше делал полный проход по таблице.

Настройка autovacuum

PostgreSQL из-за своей архитектуры MVCC накапливает «мёртвые» версии строк при обновлениях и удалениях, и их чистит autovacuum. На нагруженной базе с интенсивной записью настройки по умолчанию бывают слишком мягкими: autovacuum не успевает, таблицы «раздуваются», запросы замедляются. Сделайте его агрессивнее:

autovacuum_vacuum_scale_factor = 0.05
autovacuum_vacuum_cost_limit = 2000

autovacuum_vacuum_scale_factor = 0.05 запускает очистку, когда изменилось 5% строк, а не 20% по умолчанию — таблицы чистятся чаще и не успевают раздуться. autovacuum_vacuum_cost_limit разрешает autovacuum работать быстрее, не притормаживая себя. Отключать autovacuum нельзя ни в коем случае — без него база деградирует и в итоге встанет. Следите за раздуванием таблиц и настраивайте агрессивность под свою нагрузку: чем больше обновлений, тем чаще нужна очистка.

WAL и контрольные точки

Журнал предзаписи (WAL) обеспечивает надёжность, но при настройках по умолчанию частые контрольные точки создают всплески нагрузки на диск. Для баз с активной записью стоит увеличить объём WAL между контрольными точками, чтобы сгладить пики:

wal_buffers = 16MB
max_wal_size = 4GB
checkpoint_completion_target = 0.9

max_wal_size определяет, сколько журнала накопится до принудительной контрольной точки — больше значение означает реже, но крупнее сбросы на диск. checkpoint_completion_target = 0.9 растягивает запись контрольной точки во времени, размазывая нагрузку вместо резкого всплеска. Эти настройки снижают «дёрганье» диска на нагруженных базах. Обратная сторона большего max_wal_size — дольше восстановление после аварии и больше места под WAL, так что балансируйте под свои требования к времени восстановления.

Как измерить эффект и когда тюнинг не поможет

После каждого изменения замеряйте результат. Смотрите долю попаданий в кэш — она должна быть высокой:

sudo -u postgres psql -c "SELECT sum(blks_hit)*100/sum(blks_hit+blks_read) AS cache_hit_ratio FROM pg_stat_database;"

Значение выше 99% означает, что почти все обращения идут из памяти, а не с диска — это цель. Если оно низкое, кэшу мало памяти. Отдельно полезно смотреть, как часто PostgreSQL создаёт временные файлы на диске: если в статистике базы растёт temp_files и temp_bytes, значит, запросам не хватает work_mem и сортировки выплёскиваются на диск, что медленно. Это подсказывает, каким именно запросам стоит точечно поднять память. Так тюнинг превращается из гадания в направленную работу: вы видите конкретную метрику, меняете конкретный параметр и проверяете, что метрика улучшилась. Ведите короткий журнал изменений — какой параметр, когда и с каким эффектом поменяли; это бесценно, когда через месяц нужно вспомнить, почему настройка именно такая. Отслеживайте медленные запросы через pg_stat_statements до и после изменений. Но будьте честны с собой: тюнинг выжимает максимум из имеющегося железа, но не создаёт ресурсы из воздуха. Если база стабильно упирается в память, а рабочий набор данных не помещается в RAM, никакие настройки не заменят добавления памяти. Признак потолка простой: вы всё настроили правильно, а база всё равно постоянно читает с диска и упирается в него.

В этом случае честное решение — перейти на VPS с большим объёмом RAM и быстрым диском. В MAATRIX можно арендовать сервер под PostgreSQL с нужными ресурсами в России, США или Великобритании и оплатить картой РФ, по СБП, криптой или токеном MAAT — иностранная карта не нужна.

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