Настройка PostgreSQL для небольших серверов: от 1 до 8 ГБ

На сервере баз данных с 1-8 ГБ памяти почти всё решают пять настроек: shared_buffers, work_mem, max_connections, maintenance_work_mem и пара для контрольных точек. Остальной postgresql.conf либо нормален по умолчанию, либо ничтожен рядом с отсутствующим индексом. Если после этой статьи вы больше ничего не измените, задайте shared_buffers около четверти памяти, держите work_mem небольшим, держите max_connections низким и позвольте autovacuum работать активнее, чем он работает из коробки.

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

Одна оговорка перед цифрами: как применить изменение, зависит от вашего хоста. Сервер, которым вы управляете сами, даёт вам postgresql.conf и шелл. Управляемая база данных может дать учётную запись суперпользователя и ALTER SYSTEM, либо панель, либо фиксированную конфигурацию, которую вообще нельзя менять. Выясните, что из этого у вас есть, прежде чем строить план изменения, и помните об опциях уровня сессии, потому что они работают везде.

Что предполагают значения по умолчанию#

PostgreSQL поставляется с конфигурацией, которая запустится почти на любой машине, в том числе с 256 МБ памяти. Это сознательный выбор проекта, и он означает, что значения по умолчанию не являются рекомендацией.

random_page_cost = 4.0 - самый наглядный пример. Он закладывает предположение, что случайное чтение стоит в четыре раза дороже последовательного, что было верно для вращающихся дисков 2005 года и дико неверно для NVMe. Если оставить как есть, планировщик склоняется к последовательному сканированию там, где индекс был бы быстрее. Аппаратная сторона этого разрыва разобрана в статье что на самом деле меняет NVMe.

Память: три настройки, которые решают всё#

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

`shared_buffers` выделяется один раз и разделяется между всеми бэкендами. Четверть памяти машины - давнее правило, и оно хорошо держится примерно до 8 ГБ. Больше на небольшом сервере редко помогает, потому что операционная система и так кэширует те же файлы, а дублирование тратит память дважды.

`work_mem` - та, что кусается. Она относится не к соединению, а к каждому узлу сортировки, хэш-соединения или хэш-агрегации, и в одном сложном запросе их может быть несколько, каждый вправе занять всю сумму. Десять соединений, выполняющих по запросу с тремя сортировками при work_mem = 64MB, могут запросить почти 2 ГБ. Держите глобальное значение небольшим и повышайте его для того единственного отчёта, которому нужно:

BEGIN;SET LOCAL work_mem = '64MB';SELECT ... ;  -- the monthly aggregateCOMMIT;

`maintenance_work_mem` используют VACUUM, CREATE INDEX и ALTER TABLE, причём одновременно лишь несколько процессов (каждый рабочий процесс autovacuum берёт до autovacuum_work_mem, который по умолчанию равен этому значению). Это самая дешёвая для повышения настройка, и именно она делает так, что построение индексов и vacuum заканчиваются за минуты, а не часы.

`effective_cache_size` не выделяет вообще ничего. Она сообщает планировщику, какая доля ваших данных, скорее всего, лежит где-то в кэше, и реалистичное значение делает индексное сканирование таким дешёвым, каким оно и является. Половина или две трети общей памяти - разумная оценка для выделенного сервера баз данных.

Вот отправная точка, а не истина. Проверяйте на собственной нагрузке.

Задайте effective_cache_size рядом с ними примерно на 60% памяти сервера: 512MB, 1GB, 2500MB, 5GB и 9GB для строк выше.

На RE:NODE достижение лимита памяти останавливает контейнер и запускает его заново с чистого листа, а не даёт уйти в своп: это милосерднее к остальной машине и беспощадно к чрезмерно оптимистичному work_mem. Консоль панели рисует графики памяти, CPU и диска относительно лимитов тарифа, так что число, под которое нужно подгонять, видно до любого изменения.

Соединения - это настройка памяти#

Каждое соединение PostgreSQL - это процесс операционной системы со своей памятью. Простаивающие дёшевы, но не бесплатны, а занятые вправе занять work_mem несколько раз. Поэтому max_connections - решение о памяти, а не о ёмкости, и ответ на небольшом сервере - на удивление небольшое число.

База данных с двумя ядрами CPU не может с пользой выполнять больше горстки запросов одновременно. Соединения сверх этого встают в очередь внутри PostgreSQL, а очередь там дороже, чем в пуле, потому что каждый ожидающий бэкенд всё равно держит память и место в планировщике.

• Сначала задайте размер пула приложения: 10-20 на процесс покрывает большинство веб-приложений.
• Умножьте на число процессов и добавьте фоновые воркеры. Эта сумма - ваш реальный спрос.
• Задайте max_connections чуть выше неё, а superuser_reserved_connections (по умолчанию 3) сохранит место для вас.
• Если сумма становится большой, поставьте перед базой пулер, а не поднимайте лимит.

FATAL: sorry, too many clients already - почти всегда утечка или неограниченный пул, а не настоящая нагрузка. В статье пулы соединений и лимиты коротко объяснено, почему больше соединений замедляет работу, а сами параметры подключения описаны в удалённых подключениях к PostgreSQL.

Как сообщить планировщику, что у вас за хранилище#

Это ничего не стоит и меняет выбор плана планировщиком. На NVMe:

random_page_cost = 1.1effective_io_concurrency = 200default_statistics_target = 100

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

effective_io_concurrency позволяет планировщику считать, что одновременно могут выполняться несколько чтений, а это помогает bitmap heap scan. default_statistics_target повышает подробность, с которой ANALYZE собирает данные по столбцу; глобально оставьте 100, а для столбца с перекошенным распределением, который планировщик всё время оценивает неверно, повышайте на уровне столбца через ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 500.

Ещё две, обе про реалии маленького сервера. jit включён по умолчанию с PostgreSQL 12 и помогает долгим аналитическим запросам, но добавляет время компиляции и память коротким; на небольшой OLTP-базе замерьте его, а отключение - разумное значение по умолчанию. Если ваш тариф даёт меньше полного ядра CPU, задайте max_parallel_workers_per_gather = 0: параллельные воркеры при жёстком ограничении CPU лишь делят тот же кусок на большее число частей и добавляют накладные расходы на координацию. Когда планы выглядят неправильно, нужный инструмент - EXPLAIN ANALYZE, а не новые догадки.

Контрольные точки, WAL и запись#

Каждое изменение сначала записывается в журнал предзаписи, а периодически контрольная точка сбрасывает изменённые страницы в файлы данных. Слишком частые контрольные точки превращают небольшую нагрузку записи в поток записей целых страниц, потому что первое изменение страницы после контрольной точки пишет в WAL всю страницу.

checkpoint_timeout = 15minmax_wal_size = 2GBmin_wal_size = 512MBcheckpoint_completion_target = 0.9wal_compression = on

Если разнести контрольные точки дальше друг от друга, усиление записи снижается ценой более долгого восстановления после сбоя. Пятнадцать минут - разумная середина для небольшого сервера. max_wal_size - потолок того, сколько WAL может накопиться между контрольными точками, и 2GB на диске в 20 ГБ - это комфортно; это не резерв диска, а лишь верхняя граница, после которой контрольная точка запускается принудительно.

checkpoint_completion_target со значением 0.9 по умолчанию с PostgreSQL 14, а это то значение, которое вам и нужно: оно растягивает сброс на 90% интервала, а не выливает его разом. wal_compression = on меняет немного CPU на заметно меньший объём WAL, что важно, когда диск 20 ГБ, а не 2 ТБ.

Настройка, которая соблазняет людей, - synchronous_commit. Её отключение заставляет коммиты возвращаться до того, как WAL сброшен на диск: это реальное ускорение для нагрузок с большим количеством записи и означает, что жёсткий сбой может потерять последнюю долю секунды подтверждённых транзакций. Базу данных это не портит, и это различие - весь смысл: для аналитики и приёма событий это допустимо, для всего, что связано с деньгами, - нет. fsync = off и full_page_writes = off - другого рода: они могут оставить невосстановимую базу данных, и нет нагрузки на продакшен-сервере, где они того стоят.

Autovacuum на небольшом сервере#

Пороги autovacuum по умолчанию ждут, пока 20% таблицы станет мёртвыми строками, прежде чем её чистить. На небольшом сервере это неверный компромисс: один большой vacuum на загруженной таблице куда разрушительнее частых небольших, а мёртвые строки занимают память и кэш, которых у вас нет.

autovacuum_vacuum_scale_factor = 0.05autovacuum_analyze_scale_factor = 0.02autovacuum_vacuum_cost_limit = 1000autovacuum_naptime = 30slog_autovacuum_min_duration = 1s

Эта комбинация чистит при 5% мёртвых строк вместо 20%, чаще пересчитывает статистику, чтобы статистика планировщика оставалась честной, и поднимает лимит стоимости, чтобы воркер не замедлялся до ползания. Одной таблице, в которую пишут больше всего, обычно стоит дать собственные настройки, а не менять глобальные:

ALTER TABLE app.sessions SET (  autovacuum_vacuum_scale_factor = 0.01,  autovacuum_vacuum_cost_delay = 0);

Никогда не отключайте autovacuum. Полный рассказ о том, что он делает, почему отстаёт и во что обходится раздувание таблиц, есть в статье vacuum и раздувание в Postgres.

Таймауты и лимиты, которые стоит задать#

Здесь по умолчанию всё «без ограничений», а значит, один плохой запрос может удерживать блокировку, заполнить диск временными файлами или держать транзакцию открытой достаточно долго, чтобы autovacuum ничего не мог очистить. Эти четыре строки предотвращают больше инцидентов на маленьких серверах, чем любая настройка памяти:

statement_timeout = 30sidle_in_transaction_session_timeout = 60slock_timeout = 5stemp_file_limit = 2GB

statement_timeout лучше задавать по ролям, а не глобально, чтобы миграцию или ночной отчёт не убивало на полпути: ALTER ROLE app SET statement_timeout = '30s'. idle_in_transaction_session_timeout убивает сессии, которые открыли транзакцию и ушли, а это самая частая причина, по которой vacuum ничего не может освободить. lock_timeout не даёт DDL-оператору встать в очередь за долгим чтением и по цепочке заблокировать всех пишущих за ним. temp_file_limit ограничивает то, что одна сессия может сбросить на диск, когда work_mem не хватает, а на томе в 20 ГБ это разница между медленным запросом и полным диском. В PostgreSQL 17 добавили и transaction_timeout, если у вас версия, которая его имеет.

Где живут настройки и какие требуют перезапуска#

У настройки есть четыре возможных источника, в порядке возрастания приоритета: postgresql.conf (и любой включённый в него файл), postgresql.auto.conf (его пишет ALTER SYSTEM), настройки на уровне базы данных и роли, и сама сессия.

-- See the value, its unit, and whether changing it needs a restartSELECT name, setting, unit, context, source, pending_restartFROM pg_settingsWHERE name IN ('shared_buffers','work_mem','max_connections','random_page_cost');-- Change one durably, if your host gives you the superuser accountALTER SYSTEM SET random_page_cost = 1.1;SELECT pg_reload_conf();-- Narrower scopes, which need no superuserALTER DATABASE shop SET work_mem = '8MB';ALTER ROLE reporting SET work_mem = '64MB';SET LOCAL work_mem = '64MB';   -- this transaction only

Читать нужно столбец context. postmaster означает перезапуск: shared_buffers, max_connections, max_worker_processes и всё в shared_preload_libraries. sighup означает, что достаточно перезагрузки конфигурации: work_mem, настройки autovacuum, стоимости планировщика, таймауты. user означает, что любая сессия может изменить это для себя.

Получите ли вы сам postgresql.conf, ALTER SYSTEM или ничего из этого - зависит от хоста, так что выясните, что можно менять, прежде чем строить план вокруг правки файла. Две привычки делают это безопасным, где бы вы ни оказались: меняйте по одной настройке за раз и делайте бэкап перед перезапуском, потому что сервер, который не стартует из-за опечатки в файле конфигурации, - неудачный момент обнаружить, что бэкапа нет. На RE:NODE восстановление - это кнопка, а слоты бэкапов есть на каждом тарифе; статья проверка восстановления до того, как оно понадобится убеждает нажать её один раз нарочно.

Измерения до и после#

Настройка без измерений - это украшательство. Почти всё покрывают три источника.

-- The queries actually costing you time (needs the extension installed)CREATE EXTENSION IF NOT EXISTS pg_stat_statements;SELECT calls, round(mean_exec_time::numeric, 1) AS avg_ms,       round(total_exec_time::numeric) AS total_ms, queryFROM pg_stat_statementsORDER BY total_exec_time DESCLIMIT 10;

pg_stat_statements должен быть в shared_preload_libraries, а значит, для включения нужен перезапуск. Он того стоит: это единственное представление, которое говорит, куда уходит время на самом деле, а не куда вы предполагаете.

log_min_duration_statement = 500mslog_checkpoints = onlog_temp_files = 0log_lock_waits = on

log_temp_files = 0 логирует каждый временный файл, и так вы узнаёте, что work_mem мал для конкретного запроса, а не для всего сразу. log_checkpoints (включён по умолчанию с PostgreSQL 15) показывает, вызываются ли контрольные точки принудительно из-за max_wal_size, а не по таймауту, и это сигнал его повысить. log_lock_waits называет оператор, который блокирует остальных.

Наконец, pg_stat_activity во время инцидента, pg_stat_database для соотношения попаданий в кэш во времени и pg_stat_io, если у вас PostgreSQL 16 или новее. Сравнивайте один и тот же запрос до и после каждого изменения через EXPLAIN (ANALYZE, BUFFERS) и держите в поле зрения форму графика нагрузки: чтение графика нагрузки сервера объясняет, почему среднее скрывает то, что причинило боль. Если цифры говорят, что рабочий набор просто не помещается, настройке некуда расти, и честный следующий шаг - когда переходить на другой тариф.

FAQ#

Каким должен быть shared_buffers на сервере с 2 ГБ?

Около 384 МБ, то есть немного меньше четверти. Большие значения оставляют меньше кэшу операционной системы и памяти запросов, а на небольшом сервере двойное кэширование стоит дороже, чем выигрыш от лучшего процента попаданий.

Почему мой сервер убили за нехватку памяти, если Postgres был настроен правильно?

Почти всегда причина - work_mem, умноженная на конкурентность. Настройка относится к узлу сортировки или хэша, а не к соединению, поэтому горстка сложных запросов может занять её по несколько раз. Понизьте глобальное значение и повышайте его на уровне сессии там, где нужно.

Нужен ли пулер соединений небольшой базе данных?

Если ваше приложение уже пулирует соединения внутри процесса, обычно нет. Он нужен, когда у нескольких процессов приложения по своему пулу и сумма подтолкнёт max_connections к сотням, либо когда serverless-подобные воркеры открывают соединение на каждый запрос.

Безопасно ли отключать synchronous_commit?

Безопасно в том смысле, что база данных от этого не повредится, и небезопасно в том, что сбой может потерять последние подтверждённые транзакции. Разумно для приёма событий и аналитики, не годится для заказов и платежей. Никогда не отключайте fsync или full_page_writes там, где данные вам дороги.

Какие настройки требуют перезапуска, а не перезагрузки конфигурации?

shared_buffers, max_connections, max_worker_processes, wal_level, listen_addresses, port и shared_preload_libraries. Смотрите столбец context в pg_settings: postmaster означает перезапуск, sighup - перезагрузку.

Что настраивать в первую очередь, если база данных медленная?

Ничего из этой статьи. Найдите медленный запрос через pg_stat_statements, прочитайте его план и проверьте, нет ли индекса. Конфигурация возвращает проценты; отсутствующий индекс на большой таблице стоит порядков.