Для первоначальной настройки PostgresSQL мы берем из официальной документации

В этой статье я напишу мои рекомендации по оптимизации

Настройки PostgreSQL

Настройки будут производиться через ALTER SYSTEM

Параллельные операции

ALTER SYSTEM SET parallel_setup_cost = '500';  -- Стоимость запуска параллельных процессов
ALTER SYSTEM SET cpu_operator_cost = '0.0025'; -- Стоимость CPU для операций (+, -, *)
ALTER SYSTEM SET cpu_tuple_cost = '0.01';      -- Стоимость обработки строки CPU

Эффект: Уменьшение parallel_setup_cost делает параллельные запросы более привлекательными для планировщика. Остальные параметры оставлены по умолчанию.

Autovacuum Оптимизация

ALTER SYSTEM SET autovacuum_work_mem = '1GB';         -- Память на процесс autovacuum
ALTER SYSTEM SET autovacuum_naptime = '30s';          -- Пауза между проверками
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = '4000'; -- Лимит работы за цикл
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms'; -- Задержка при лимите
  • Для чего:
    • Ускорение autovacuum за счет увеличения памяти (work_mem) и агрессивности (cost_limit в 20 раз выше дефолта)
    • Частые проверки (naptime=30s вместо 60s)
    • Минимизация простоев (cost_delay=2ms вместо 20ms)

Пороги срабатывания Autovacuum

ALTER SYSTEM SET autovacuum_vacuum_scale_factor = '0.05';  -- 5% изменённых строк
ALTER SYSTEM SET autovacuum_analyze_scale_factor = '0.05'; -- 5% для анализа
ALTER SYSTEM SET autovacuum_vacuum_threshold = '10000';    -- Мин. число мёртвых строк
ALTER SYSTEM SET autovacuum_analyze_threshold = '500';     -- Мин. изменений для ANALYZE
  • Логика:
    • VACUUM запустится при: >10к мертвых строк ИЛИ >5% мертвых строк
    • ANALYZE запустится при: >500 изменений ИЛИ >5% изменений
    • Важно: Для больших таблиц уменьшение scale_factor критично (5% от 1 млрд = 50 млн — много!)

Надёжность и WAL

ALTER SYSTEM SET synchronous_commit = 'on';    -- Гарантия записи WAL на диск
ALTER SYSTEM SET wal_compression = 'zstd';       -- Сжатие WAL (lz4/zstd лучше)
ALTER SYSTEM SET full_page_writes = 'on';      -- Защита от порчи данных при сбое
ALTER SYSTEM SET wal_keep_size = '1GB';        -- Минимальный размер WAL для реплик
  • Зачем:
    • Безопасность данных (synchronous_commit, full_page_writes)
    • Экономия места на диске (wal_compression)
    • Обеспечение работы реплик/бэкапов (wal_keep_size)

Мониторинг

ALTER SYSTEM SET log_autovacuum_min_duration = '2s'; -- Логирование медленных autovacuum

Помогает выявлять проблемы с длительными операциями обслуживания.

Проверка параметров

SELECT name, setting FROM pg_settings WHERE name LIKE '%autovac%';

Вывод: Все параметры autovacuum с их текущими значениями (удобно для аудита)

Оптимизация ОС: Ядро Linux

Конфигурация (/etc/sysctl.conf)

Раздел VM (Virtual Memory)

vm.dirty_background_ratio = 0

Отключает процентный расчет фоновых «грязных» страниц. Используется вместе с dirty_background_bytes.

vm.dirty_ratio = 0

Отключает процентный расчет для основных «грязных» страниц. Используется с dirty_bytes.

vm.dirty_background_bytes = 104857600

Максимальный объем «грязных» страниц (незаписанных данных), при котором фоновые процессы начинают асинхронную запись на диск.
Зачем: Предотвращает лавинообразную запись при заполнении буфера.

vm.dirty_bytes = 1073741824

Максимальный объем «грязных» страниц, при котором приложения блокируются для синхронной записи на диск.
Для PostgreSQL: Защищает от длительных блокировок при внезапной нагрузке.

vm.dirty_expire_centisecs = 300

Время, после которого «грязные» данные обязаны быть записаны на диск.
Эффект: Гарантирует, что данные не задерживаются в памяти слишком долго.

vm.dirty_writeback_centisecs = 100

Частота активации процессов записи «грязных» страниц на диск.
Оптимизация: Более частые, но мелкие операции записи вместо больших пиков.

vm.overcommit_memory=2
  • 0: Разрешать overcommit (с риском OOM-Killer)
  • 2: Запрещать выделение памяти, если нет гарантий ее доступности
    Для БД: Защита от внезапной смерти процесса PostgreSQL при нехватке памяти.
vm.overcommit_ratio=100

Процент RAM + SWAP, разрешенный для выделения при overcommit_memory=2.
100% = RAM + весь SWAP.

vm.swappiness = 1

Агрессивное избегание использования swap:

  • 0: полное отключение свопа (не рекомендуется)
  • 1: минимально возможное использование
    Для PostgreSQL: Предотвращает деградацию производительности при своппинге.

Раздел Kernel

kernel.numa_balancing = 0

Отключает автоматический баланс памяти между узлами NUMA.
Зачем: Для СУБД ручное управление через numactl часто эффективнее.

kernel.sched_autogroup_enabled = 0

Отключает автоматическую группировку задач по сессиям.
Для PostgreSQL: Предотвращает несправедливое распределение CPU между фоновыми процессами и основными запросами.

Далле идут три параметра которые требуют тонкой настройки под конкретную нагрузку

kernel.sched_migration_cost_ns = 50000000

Время, после которого задача считается «кешированной»

kernel.sched_nr_migrate = 2

Макс. задач для миграции между CPU

kernel.sched_min_granularity_ns = 100000000

Минимальное время выполнения задачи

Раздел Network

Отключение IPv6:

net.ipv6.conf.all.disable_ipv6 = 1
net.ipv6.conf.default.disable_ipv6 = 1
net.ipv6.conf.lo.disable_ipv6 = 1

Упрощение сетевого стека, если используется только IPv4.

Буферы TCP:

net.core.rmem_max = 1073741824

Макс. размер буфера приёма (1 GB)

net.core.wmem_max = 1073741824

Макс. размер буфера отправки (1 GB)

net.ipv4.tcp_rmem = 1048576 16777216 1073741824

Min, default, max для приёма

net.ipv4.tcp_wmem = 1048576 16777216 1073741824

Min, default, max для отправки

Для PostgreSQL: Улучшает производительность при:

  • Множестве параллельных подключений
  • Передаче больших результатов запросов
  • Репликации

Таймауты TCP

net.ipv4.tcp_fin_timeout = 30

Время ожидания FIN-пакета (сек)

net.ipv4.tcp_keepalive_intvl = 30

Интервал между keepalive-пакетами (сек)

Быстрее освобождает соединения в состоянии TIME_WAIT.

net.ipv4.tcp_reordering = 20

Увеличивает порог переупорядочивания пакетов.
Зачем: Снижает ложные ретрасмиты в сетях с высокой вариативностью задержек.

net.ipv4.tcp_mem = 1048576 16770216 1073741824

Управление памятью для TCP:

  • Min (4096 страниц × 4KB = 16MB)
  • Pressure (≈65MB)
  • Max (1GB)

Для применения настроек необходимо выполнить 

sudo sysctl -p

Huge Pages: Ускорение работы с памятью

Зачем использовать Huge Pages?

  1. Уменьшение накладных расходов TLB
    • TLB (Translation Lookaside Buffer) — кэш процессора для трансляции виртуальных адресов в физические.
    • Стандартные страницы (4KB): TLB покрывает малый объём памяти → частые промахи → замедление работы.
    • Huge Pages (1GB/2MB): Одна запись TLB покрывает гигабайт памяти → меньше промахов → ускорение операций с памятью.
  2. Снижение нагрузки на ядро ОС
    • Меньше структур данных для управления памятью (1 запись для 1GB вместо 262,144 записей для 4KB-страниц).
    • Уменьшение contention на спин-блокировках ядра.
  3. Ускорение инициализации shared_buffers
    • PostgreSQL при запуске выделяет shared_buffers. С Huge Pages это происходит в 100-1000 раз быстрее.

Эффект для PostgreSQL:

  • До +30% к скорости OLTP-нагрузки при больших shared_buffers (особенно > 64GB).
  • Стабильность производительности при интенсивной работе с памятью.

Как включить Huge Pages (1GB) в Debian

Шаг 1: Проверить поддержку CPU

grep pdpe1gb /proc/cpuinfo | uniq

Если есть вывод — процессор поддерживает 1GB-страницы.

Шаг 2: Добавить параметры ядра

Отредактировать /etc/default/grub:

GRUB_CMDLINE_LINUX="... hugepagesz=1G hugepages=16 default_hugepagesz=1G"
  • hugepagesz=1G — размер страницы.
  • hugepages=16 — количество страниц (16 × 1GB = 16GB памяти).
  • default_hugepagesz=1G — размер по умолчанию.

Обновить конфигурацию GRUB:

update-grub

Перезагрузить сервер:

reboot

Шаг 3: Проверить выделение памяти

grep HugePages_ /proc/meminfo

Ожидаемый вывод:

HugePages_Total:      16
HugePages_Free:       16
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:    1048576 kB  # 1GB

Шаг 4: Настроить PostgreSQL

 ALTER SYSTEM SET huge_pages = "on";
 ALTER SYSTEM SET huge_page_size = "1GB";

Важно! Установить vm.nr_overcommit_hugepages (если PostgreSQL не может выделить память):

sysctl -w vm.nr_overcommit_hugepages=32  # Резерв страниц

Когда НЕ использовать Huge Pages?

  1. Маленькие БД (shared_buffers < 8GB).
  2. Системы с нехваткой RAM (память резервируется заранее).
  3. Контейнеризованные среды (cgroups могут блокировать доступ).