Конфигурация PostgreSQL — postgresql.conf

ОБЯЗАТЕЛЬНО

Инженеру

Раздел 8.11, шаг 3 из 12. Дальше — JSONB и партиционирование.

---

Где лежат настройки

Файл / механизм	Содержание
postgresql.conf	Основные GUC-параметры
pg_hba.conf	Кто и как подключается (trust, scram, cert)
pg_ident.conf	Маппинг OS-пользователей
ALTER SYSTEM	Запись в postgresql.auto.conf
Per-role / per-db	ALTER ROLE … SET, ALTER DATABASE … SET

Просмотр текущего значения и контекста:

SHOW shared_buffers;
SELECT name, setting, unit, context, source
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'max_connections');

context показывает, нужен ли restart (postmaster) или достаточно reload (SIGHUP).

---

Память

Параметр	Назначение	Ориентир
shared_buffers	Кэш страниц данных	25% RAM на dedicated сервер (не 40%+ — убывающая отдача)
work_mem	Память на sort/hash на операцию	4–64 MB; осторожно: × параллель × соединения
maintenance_work_mem	VACUUM, CREATE INDEX, CLUSTER	512 MB – 2 GB на больших таблицах
effective_cache_size	Подсказка планировщику «сколько кэша ОС»	50–75% RAM
temp_buffers	Буферы temp-таблиц сессии	обычно default достаточно

shared_buffers = 4GB
work_mem = 32MB
maintenance_work_mem = 1GB
effective_cache_size = 12GB

work_mem и OOM

Сложный запрос с 8 hash join и max_parallel_workers_per_gather = 4 может съесть work_mem × 8 × 4 на одно соединение. При сотнях backend без пулинга риск swap и OOM-killer.

---

Соединения и параллелизм

max_connections = 100

Каждое соединение — отдельный процесс (~5–10 MB RAM минимум). При 500+ клиентах — PgBouncer (шаг 6), а не рост max_connections.

Параллельные запросы (PostgreSQL 9.6+):

max_parallel_workers_per_gather = 2
max_parallel_workers = 8
max_worker_processes = 16

---

I/O и планировщик

Параметр	Смысл
random_page_cost	«Цена» случайного чтения страницы; на SSD снижают до 1.1–1.5 (default 4)
seq_page_cost	Стоимость sequential scan (обычно 1)
effective_io_concurrency	Ожидаемая параллельность prefetch; SSD — 100–200

На NVMe и облачных дисках завышенный random_page_cost толкает планировщик к seq scan там, где index scan выгоднее.

---

WAL и checkpoint

Связь с архитектурой:

wal_level = replica          # минимум для физической репликации; logical — для logical replication
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9
wal_compression = on         # PG 15+, меньше I/O

Параметр	Эффект
max_wal_size	Больше — реже checkpoint, но дольше recovery
checkpoint_completion_target	Растянуть запись dirty pages между checkpoint (0.9 — плавнее)
synchronous_commit	off ускоряет запись, риск потери последних транзакций при crash

---

Autovacuum

Для OLTP с частыми UPDATE/DELETE подстройка autovacuum часто важнее «магических» shared_buffers:

autovacuum = on
autovacuum_max_workers = 4
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_cost_delay = 2ms

Per-table (агрессивнее для «горячих» таблиц):

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.005
);

---

Логирование для диагностики

log_min_duration_statement = 500ms
log_checkpoints = on
log_autovacuum_min_duration = 0
log_lock_waits = on
log_line_prefix = '%t [%p] %u@%d '

Для автоматического EXPLAIN медленных запросов — auto_explain (шаг 11).

---

Контейнеры и managed-сервисы

В Docker/Kubernetes часть параметров задаётся через переменные образа (POSTGRES_SHARED_BUFFERS в официальном образе — через -c). В RDS / Cloud SQL / Managed PostgreSQL часть GUC заблокирована — смотрите документацию провайдера.

Справочник в энциклопедии

Расширенный список параметров и команд администрирования — 3.08/2 Справочник PostgreSQL.

---

Практика

1. На lab-инстансе: pgbench -i -s 50, затем pgbench -c 10 -T 60, меняйте shared_buffers и random_page_cost, фиксируйте TPS.
2. EXPLAIN тяжёлого join до/после SET effective_cache_size и random_page_cost.
3. Сымитируйте bloat — серия UPDATE; подстройте autovacuum и сравните n_dead_tup.

---

Связанные материалы

• Архитектура и WAL
• Профилирование
• Справочник PostgreSQL 3.08/2
• phpPgAdmin — pg_hba и config
• PostgreSQL в Docker — передача -c и volumes для config

---