postgresql.conf по умолчанию не сломан, это разумно: он рассчитан на работу на минимальной машине, не приводя к сбою установки, а не для обработки вашего производственного трафика. Переход от этих значений совместимости к производственным значениям измеряется, его нельзя угадать. Сам протокол измерения (реалистичная нагрузка, p50/p95/p99, воспроизводимые результаты) см. в нашей методологии внутреннего тестирования . В этой статье подробно описаны сами настройки в том порядке, в котором они наиболее важны.
- Пять проектов по порядку: соединения/пулы, память, автоочистка, индексы/медленные запросы, контрольные точки/WAL.
max_connectionsиshared_buffersтребуют полного перезапуска сервера; большинство других настроек загружаются в горячем режиме.- Никогда не отключайте автоочистку в рабочей среде: реальный риск заключается не в медлительности, а в переносе идентификаторов транзакций.
pg_stat_statementsдолжен быть указан вshared_preload_libraries, прежде чем простойCREATE EXTENSIONсможет что-либо собрать.- Советник, интегрированный в Aurabase Studio, уже автоматически применяет часть этого чек-листа: запросы длительностью более 150 мс, внешние ключи не индексируются, пул соединений с насыщением выше 80%.
Почему значений по умолчанию Postgres никогда не бывает достаточно
postgresql.conf в состоянии по умолчанию предназначен для того, чтобы установка никогда не прерывалась, а не поглощала ваш трафик. Историческая ценность shared_buffers, 128 МБ, позволяет Postgres запускаться на минимальной машине без резервирования каких-либо критических ресурсов. max_connections в количестве 100 умещается на небольшом общем сервере. Ни один из них не был выбран для вашей фактической нагрузки.
Таким образом, проблема не в том, что Postgres по умолчанию плохо настроен: а в том, что он никогда не был установлен для вас. В следующих разделах настройки рассматриваются в том порядке, в котором они наиболее выгодны: от наиболее распространенных узких мест (соединения) до самых медленных (WAL).
Размер max_connections и пул прежде всего
Первый проект – это не память, это связи. Каждое соединение Postgres открывает выделенный серверный процесс, который потребляет ОЗУ и контекстное время ЦП, даже если он простаивает. Увеличение max_connections во избежание ошибок типа «слишком много подключений» меняет проблему: за пределами определенного количества одновременных активных подключений конкуренция за ЦП ухудшает задержку всех запросов, включая самые быстрые.
Правильный подход меняет обычный порядок: масштабируйте max_connections на фактической параллельной работе сервера, затем поглощайте параллелизм на стороне приложения с помощью пулера, такого как PgBouncer, в pool_mode=transaction. Создатель пула мультиплексирует сотни клиентских соединений на несколько действительно активных серверных соединений. В нашей специальной статье подробно описан механизм режима транзакций и его ограничения (подготовленные операторы, LISTEN/NOTIFY), а также сравнение PgBouncer, Supavisor и PgCat для выбора реализации.
max_connections — это параметр контекста «postmaster»: его изменение требует полного перезапуска сервера, а не простой перезагрузки. В выделенных экземплярах Aurabase Postgres (планы Pro и Enterprise, один кластер CNPG на проект) этот параметр настраивается для каждого уровня плана, а не остается со значением по умолчанию. Перезапуск — это не тривиальная операция, которую нужно повторить в рабочей среде, что оправдывает этот выбор поэтапно, а не фиксированным значением. Формула выбора собственного значения с его пределами — тема отдельной статьи: size max_connections.
Четыре настройки памяти весят больше, чем все остальные вместе взятые: shared_buffers, effective_cache_size, work_mem и maintenance_work_mem. Первые три определяют, сколько данных Postgres сохраняет в памяти перед возвратом на диск; четвертый определяет скорость создания ВАКУУМА или индекса.
shared_buffers устанавливает внутренний кеш, общий для всех соединений. Тестовый показатель, обычно документируемый проектом PostgreSQL, составляет примерно 25 % оперативной памяти, доступной на выделенном сервере базы данных. Помимо этого, выигрыш снижается, и управление берет на себя дисковый кеш операционной системы. effective_cache_size ничего не выделяет: это оценка, данная планировщику запросов, общего объема памяти, доступной для кэша (Postgres и ОС вместе взятых). Уменьшение его размера подталкивает планировщик к последовательному сканированию, тогда как индекс в основном будет кэшироваться; текущий тест составляет от 50 до 75% оперативной памяти.
work_mem — самая распространенная ловушка. Это не глобальный предел. Каждая операция сортировки или хеширования в запросе может использовать свою долю, а запрос с несколькими соединениями может резервировать свою долю несколько раз. Слишком большое значение в сочетании с высоким значением max_connections может истощить оперативную память сервера при одновременной нагрузке, даже если каждый запрос, рассматриваемый изолированно, кажется разумным. maintenance_work_mem, наоборот, может оставаться значительно более щедрым: он применяется только к операциям обслуживания (VACUUM, CREATE INDEX), которые редко совпадают друг с другом.
Только shared_buffers требует перезагрузки. Остальные три перезагружаются в горячем режиме, в том числе для изолированного сеанса: SET work_mem = '64MB'; на время одного жадного запроса, не затрагивая глобальные настройки.
Автоочистка: отрегулируйте пороговые значения, никогда не отключайте ее.
Никогда не отключайте автоочистку в рабочей среде, даже временно, чтобы «освободить ресурсы» во время пиковой нагрузки. Postgres использует MVCC: каждое UPDATE и каждое DELETE оставляет недействительную строку, которую может восстановить только вакуум. Без этого таблицы раздуваются, индексы ухудшаются, а планы выполнения постепенно ухудшаются, и ошибок не видно, пока не становится поздно.
Самая серьезная опасность отключенного или недостаточного размера автоочистки — это не производительность, а циклический перенос идентификаторов транзакций. После достижения порогового значения Postgres переключает всю базу данных в режим «только для чтения», чтобы избежать повреждения данных, пока не будет выполнено ВАКУУМ вручную. Это производственный инцидент, которого можно полностью избежать путем правильной настройки.
Значение по умолчаниюautovacuum_vacuum_scale_factor (20 % неактивных строк до запуска) подходит для небольшой таблицы, а не для многомиллионной таблицы с интенсивной записью. В таблице из 10 миллионов строк эти 20% представляют собой 2 миллиона неактивных строк, накопленных до первого прохода. Уменьшайте этот порог по таблице, а не меняйте общее значение всей базы данных.
-- Понизьте порог для таблицы с большим объемом записи, а не в целом.
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_vacuum_cost_limit = 2000
);
Советник, интегрированный в Aurabase Studio, проверяет эту конфигурацию при каждом анализе проекта, так же, как таблицы без первичных ключей или неиндексированные внешние ключи. Это явный сигнал, а не молчаливая деградация, обнаруженная слишком поздно.
Индекс перед добавлением ОЗУ
Большинство проблем с задержкой в производстве происходят не из-за процессора и не из-за оперативной памяти: они происходят из-за отсутствия или неправильно выбранного индекса. Прежде чем коснуться одного параметра postgresql.conf, EXPLAIN (ANALYZE, BUFFERS) в рассматриваемом запросе остается наиболее надежным диагнозом. Это значительно снижает задержку запроса Postgres перед добавлением ресурсов.
-- Прежде чем приступать к настройке, диагностируйте медленный запрос.
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = '…'
ORDER BY o.created_at DESC
LIMIT 20;
Seq Scan в таблице с несколькими миллионами строк, где ожидался Index Scan, почти всегда сигнализирует о проблеме с индексом. Чаще всего возникают три причины: отсутствующий индекс, тип столбца, несовместимый с существующим индексом, или устаревшая статистика после массового импорта без ANALYZE. Добавление оперативной памяти или увеличение work_mem иногда скрывает этот симптом при небольшом объеме данных; проблема появляется снова, как только таблица растет.
Чтобы найти эти запросы без поиска их один за другим, pg_stat_statements объединяет статистику выполнения всех запросов сервера. Распространенная ошибка: расширение сначала должно быть указано в shared_preload_libraries, параметре контекста «postmaster», который требует перезагрузки. Без этого шага CREATE EXTENSION pg_stat_statements; молча выполнит операцию, но ничего не соберет.
# Требуется полный перезапуск (параметр «postmaster»)
shared_preload_libraries = 'pg_stat_statements'
-- После перезапуска сервера в каждой базе данных для аудита:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
Это именно та ошибка, которую возвращает серверная часть Aurabase, когда это расширение отсутствует: явное сообщение, а не молчаливый пустой список, который можно спутать с «нет медленного запроса». Studio Advisor идет еще дальше: он автоматически классифицирует любой запрос со средним временем более 150 мс как предупреждение, а свыше 500 мс — как критический. Эти пороговые значения основаны на той же статистике pg_stat_statements.
Контрольные точки и WAL: сглаживайте нагрузку, а не терпите ее
Контрольная точка заставляет Postgres записать на диск все страницы, измененные в памяти с момента предыдущей. По умолчанию эта статья может быть сосредоточена на слишком коротком временном окне. Результатом является заметный скачок задержки диска на стороне приложения, своего рода периодическое замедление, которое трудно связать с конкретным запросом.
checkpoint_completion_target управляет распространением этой записи в интервале между двумя контрольными точками. Деталь, отсутствующая в старых контрольных списках: PostgreSQL 14, выпущенный в 2021 году, изменил значение по умолчанию с 0,5 на 0,9. Таким образом, в экземпляре PostgreSQL 18, таком как выделенные клиентские кластеры Aurabase, этот параметр уже по умолчанию правильный; настройка вручную имеет смысл только в версии до 14. См. наше сравнение PostgreSQL 16 vs 17 vs 18, чтобы узнать о других изменениях версий, влияющих на настройку.
max_wal_size действует в том же направлении: слишком низкое значение вызывает более частые контрольные точки, чем ожидалось, даже если checkpoint_timeout еще не достигнуто. Увеличение этого значения снижает частоту контрольных точек за счет увеличения времени восстановления после сбоя, поскольку требуется воспроизведение большего количества WAL. Компромисс, который должен быть решен в соответствии с вашей терпимостью к недоступности, а не с универсальной ценностью.
Мониторинг – это не шаг, это цикл, замыкающий чек-лист
Этот контрольный список не представляет собой разовую проверку, которую необходимо проверить один раз перед запуском в производство. База, объем которой удваивается, или трафик, утроенный, делает тесты, выбранные при запуске, устаревшими, часто без явной ошибки, а просто постепенное ухудшение задержки p95.
Три сигнала заслуживают постоянного мониторинга. pg_stat_statements идентифицирует запросы, качество которых со временем ухудшается. pg_stat_activity сообщает о заблокированных или аномально длительных запросах, а соотношение активных подключений к max_connections предвидит насыщение, прежде чем оно приведет к ошибкам на стороне приложения.
Вкладка Observability в Studio охватывает часть этой основы для любого проекта Aurabase без необходимости установки сторонних инструментов. Он перечисляет медленные запросы, позволяет отменить или прекратить активный запрос по PID и отображает индикатор насыщения пула, который выдает предупреждение при использовании более 80%. На локальном экземпляре тот же самый мониторинг создается вручную с активированным pg_stat_statements и подключенным к нему внешним инструментом мониторинга.
Шпаргалка: полный контрольный список
Восемь настроек в том порядке, в котором они приносят наибольшую пользу, и все, что вам нужно знать, прежде чем прикасаться к ним.
| max_connections | Перезагрузить | Размер соответствует реальной конкуренции, а не круглому числу; остальное поглотить через пулер в режиме транзакции. |
|---|---|---|
| общие_буферы | Перезагрузить | ≈ 25% оперативной памяти выделено для Postgres. |
| эффективный_cache_size | Горячий | ≈ от 50 до 75% оперативной памяти (Postgres + кеш ОС вместе взятые). |
| рабочая_мемь | Горячая / сессия | Осторожен по умолчанию; тестируйте вверх по каждому запросу с помощью SET. |
| Maintenance_work_mem | Горячий | Более щедрый, чем work_mem; ускоряет ВАКУУМ и СОЗДАНИЕ ИНДЕКСА. |
| autovacuum_vacuum_scale_factor | Горячее, за стол | Меньше в больших таблицах с большим объемом записи, но не глобально. |
| checkpoint_completion_target | Горячий | 0.9 по умолчанию, начиная с PostgreSQL 14; особенно проверить более раннюю версию. |
| Shared_preload_libraries | Перезагрузить | Необходимо включать pg_stat_statements перед анализом медленных запросов. |
Приведенные здесь пороговые значения, такие как 150 мс и 80% насыщение пула, которые отслеживает Aurabase Studio Advisor, являются проверенной кодом отправной точкой, а не универсальной истиной. Ваше фактическое обвинение остается единственным окончательным судьей. Чтобы точно определить размер max_connections, а не следовать общему правилу, в специальной статье подробно описана формула и ее ограничения.
Часто задаваемые вопросы
Три вопроса, которые систематически возникают после первого применения контрольного списка.
Должен ли я перезапустить Postgres после изменения значения postgresql.conf?+
max_connections и shared_buffers находятся в контексте «почтмейстера»: требуется полный перезапуск. work_mem, effective_cache_size, maintenance_work_mem и большинство порогов автоочистки можно заряжать в горячем режиме с помощью SELECT pg_reload_conf(); или pg_ctl reload без прерывания обслуживания.