Тюнинг PostgreSQL на сервере: частые ошибки и решения

MAATRIX

Тюнинг PostgreSQL на сервере способен как ускорить базу, так и уронить её, если крутить параметры вслепую. Ниже — разбор частых ошибок тюнинга: сервер падает с out of memory из-за завышенного work_mem, база не стартует после правки конфига, autovacuum то тормозит запись, то не справляется, планировщик игнорирует индексы, а «идеальный конфиг из интернета» делает только хуже. Для каждой проблемы сначала решение, потом причина — чтобы настройка приносила прирост, а не новые инциденты.

Out of memory после увеличения work_mem

Классическая ошибка: подняли work_mem до крупного значения, и под нагрузкой сервер начал падать с out of memory или ловить OOM-killer. Причина в коварстве этого параметра: work_mem — это память не на всю базу, а на одну операцию сортировки или хеширования в каждом запросе. Один сложный запрос может использовать несколько таких операций, а при сотне одновременных подключений итог умножается в сотни раз.

Посчитайте потолок: work_mem × число подключений × операций на запрос не должно превышать доступную RAM с запасом. Если у вас 100 подключений и work_mem = 256MB, теоретический пик — десятки гигабайт, которых нет. Решение — вернуть work_mem к умеренным 16–64 МБ для основной массы подключений, а тяжёлым аналитическим запросам поднимать его локально на уровне сессии командой SET work_mem = '512MB'; перед конкретным запросом. Так вы даёте память тому, кому она нужна, не рискуя всем сервером.

База не стартует после правки конфига

Отредактировали postgresql.conf, перезапустили — и сервер не поднимается. Первым делом смотрите журнал, там всегда есть причина:

sudo journalctl -u postgresql -n 30
sudo tail -n 30 /var/log/postgresql/postgresql-17-main.log

Частые причины. Опечатка в параметре или единице измерения — например, 2G где ожидается 2GB, или лишний символ. shared_buffers выставлен больше, чем позволяет система: тогда в логе будет ошибка про разделяемую память. Слишком большой shared_buffers относительно RAM тоже роняет старт. Ещё вариант — включили расширение в shared_preload_libraries, но не установили сам пакет расширения.

Решение простое: откатите последнее изменение и запуститесь, затем вносите правки по одной. Именно поэтому золотое правило тюнинга — менять по одному параметру за раз: когда что-то ломается, вы точно знаете, что именно. Держите под рукой рабочую копию конфига, чтобы быстро вернуться к ней.

Autovacuum тормозит запись или не справляется

Две противоположные беды с autovacuum. Первая: во время его работы запись в базу заметно тормозит, особенно на слабом диске. Вторая, куда опаснее: autovacuum не успевает за нагрузкой, таблицы «раздуваются», а запросы деградируют. Проверьте состояние — раздутость и последний проход очистки:

sudo -u postgres psql -c "SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;"

Если n_dead_tup велик, а last_autovacuum давно или пуст — очистка отстаёт. Сделайте autovacuum агрессивнее: уменьшите autovacuum_vacuum_scale_factor до 0.05 и поднимите autovacuum_vacuum_cost_limit. Если же autovacuum, наоборот, слишком нагружает диск и мешает работе, распределите нагрузку через autovacuum_vacuum_cost_delay. Главное, что делать нельзя, — отключать autovacuum. Это кажется способом убрать тормоза, но ведёт к катастрофе: база пухнет и в итоге встаёт, а для приведения в порядок понадобится тяжёлый ручной VACUUM FULL с блокировкой таблиц.

Планировщик игнорирует индексы

Вы создали индекс, а запрос всё равно медленный и делает полный проход по таблице. Посмотрите план выполнения:

sudo -u postgres psql -c "EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;"

Если видите Seq Scan вместо Index Scan, планировщик решил, что полный проход дешевле. Причин несколько. Первая — устаревшая статистика: после массовой загрузки данных выполните ANALYZE вручную, чтобы планировщик увидел реальное распределение. Вторая — неверная оценка стоимости диска: на SSD параметр random_page_cost по умолчанию завышен, из-за чего индексы кажутся дорогими; снизьте его до 1.1. Третья — индекс просто не подходит под запрос: например, условие по функции от столбца не использует обычный индекс на столбце.

Иногда Seq Scan действительно быстрее — на маленькой таблице или когда запрос возвращает большую часть строк. Планировщик не всегда неправ. Но если после ANALYZE и настройки random_page_cost он всё ещё игнорирует нужный индекс на большой выборке, разбирайтесь с конкретным запросом и структурой индекса. Частая тонкость — несовпадение типов: если столбец имеет тип bigint, а в запросе сравнивается с текстовой константой или значением другого типа, индекс может не примениться из-за неявного приведения. Приводите типы явно и следите, чтобы условие точно соответствовало тому, на что построен индекс. Ещё одна ловушка — составной индекс по нескольким столбцам работает только когда запрос фильтрует по первым столбцам этого индекса слева направо; фильтр лишь по второму столбцу такой индекс не задействует.

Идеальный конфиг из интернета сделал хуже

Скопировали чужой «оптимальный» конфиг, а база стала работать нестабильнее. Это самая частая стратегическая ошибка тюнинга. Чужие конфиги писались под другой объём RAM, другой диск, другой характер нагрузки. Значения памяти, рассчитанные на сервер с 64 ГБ, на вашем VPS с 4 ГБ приведут к out of memory. Агрессивные настройки WAL, полезные для аналитики, навредят базе с частыми мелкими транзакциями.

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

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

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