Тюнинг производительности PostgreSQL (shared_buffers, work_mem, effective_cache_size)

Тюнинг производительности PostgreSQL (shared_buffers, work_mem, effective_cache_size)

Дефолтная конфигурация PostgreSQL — shared_buffers = 128MB, work_mem = 4MB — написана для запуска на любом железе 2005 года. На современном сервере с 32–128 ГБ ОЗУ она оставляет 95% памяти неиспользованной. Правильный тюнинг — это не просто «поставить цифры побольше», а понимание, как PostgreSQL использует память и взаимодействует с ОС.

Как PostgreSQL использует память

shared_buffers — общий кэш страниц базы данных для всех процессов. Страницы, которые PostgreSQL прочитал с диска, попадают сюда. Если страница в shared_buffers — следующий запрос получает её из памяти.

work_mem — память для каждой операции сортировки или hash join в рамках одного запроса. Если запрос имеет 3 sort node, он может занять 3 × work_mem. При 100 параллельных соединениях с тяжёлыми запросами потребление может быть 100 × 3 × work_mem.

effective_cache_size — не реально выделяемая память, а подсказка планировщику о том, сколько памяти доступно для кэширования OS + shared_buffers. Влияет на выбор между index scan и seq scan.

maintenance_work_mem — для VACUUM, CREATE INDEX, ALTER TABLE. Не влияет на обычные запросы.

Рекомендуемые значения

Для выделенного сервера PostgreSQL с 32 ГБ ОЗУ:

# postgresql.conf

# 25% RAM для выделенного сервера
shared_buffers = 8GB

# Остаток RAM за вычетом shared_buffers и системы
effective_cache_size = 24GB  # shared_buffers + OS page cache

# Осторожно: work_mem умножается на число параллельных операций
# Для OLTP с короткими запросами: 4-16 MB
# Для аналитики с GROUP BY / ORDER BY: 64-256 MB
work_mem = 16MB

# Для VACUUM и CREATE INDEX — повышать только во время обслуживания
maintenance_work_mem = 2GB

# WAL буфер — 64 МБ достаточно для большинства нагрузок
wal_buffers = 64MB

Инструмент pgtune (https://pgtune.leopard.in.ua) генерирует стартовые значения по типу нагрузки (OLTP, DW, Web).

Настройка планировщика

# Стоимостная модель для SSD (дефолт рассчитан на HDD)
random_page_cost = 1.1    # SSD: 1.1, HDD: 4.0 (дефолт)
seq_page_cost    = 1.0    # обычно не трогают

# Включить параллельные запросы (PostgreSQL 9.6+)
max_parallel_workers_per_gather = 4   # до числа CPU cores
max_parallel_workers = 8
parallel_tuple_cost  = 0.1
parallel_setup_cost  = 1000.0

# Статистика: больше = лучше планировщик выбирает индексы
default_statistics_target = 100  # дефолт 100, для сложных колонок — до 500

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

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

Checkpoint и WAL

# Checkpoint происходит реже — меньше I/O спайков
checkpoint_completion_target = 0.9  # растянуть checkpoint на 90% интервала
checkpoint_timeout = 15min          # максимальный интервал (дефолт 5min)
max_wal_size = 4GB                  # при интенсивной записи — увеличить

# fsync отключать НЕЛЬЗЯ в продакшне (потеря данных при сбое питания)
fsync = on

# synchronous_commit = off — разрешить потерю до 60ms транзакций
# Уместно для некритичных данных, не для финансовых
synchronous_commit = on

Тюнинг для конкретного случая: сортировка

Запрос медленно выполняет ORDER BY по большой таблице — сортировка идёт через временный файл на диске:

-- Проверяем: есть ли temp files в запросе
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM events
WHERE user_id = 1
ORDER BY created_at DESC
LIMIT 100;

-- Если в выводе "Sort Method: external merge  Disk: 45678kB" — нужен индекс или больше work_mem

-- Создаём покрывающий индекс
CREATE INDEX CONCURRENTLY idx_events_user_date
  ON events(user_id, created_at DESC)
  INCLUDE (id, event_type, payload);
-- INCLUDE добавляет данные в индекс, запрос обходится без обращения к таблице

Мониторинг hit rate

-- Эффективность shared_buffers: должно быть > 99% для OLTP
SELECT
    sum(heap_blks_hit)  AS heap_hit,
    sum(heap_blks_read) AS heap_read,
    round(
        sum(heap_blks_hit)::numeric /
        nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100,
        2
    ) AS cache_hit_ratio
FROM pg_statio_user_tables;

-- Аналогично для индексов
SELECT
    round(
        sum(idx_blks_hit)::numeric /
        nullif(sum(idx_blks_hit) + sum(idx_blks_read), 0) * 100,
        2
    ) AS index_hit_ratio
FROM pg_statio_user_indexes;

Если cache_hit_ratio < 99% — либо shared_buffers мал, либо рабочий набор данных не помещается в память вообще.

Расширение pg_buffercache

CREATE EXTENSION pg_buffercache;

-- Что сейчас находится в shared_buffers?
SELECT c.relname,
       count(*) AS buffers,
       round(count(*) * 8192.0 / 1024 / 1024, 1) AS size_mb,
       round(count(*) * 100.0 / (SELECT count(*) FROM pg_buffercache), 1) AS pct
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = c.relfilenode
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 20;

Параметры соединений

max_connections = 200   # для прямых соединений; с PgBouncer можно меньше

# Размер стека для каждого backend-процесса
# Не уменьшать ниже 100KB
# Уменьшение max_stack_depth не рекомендуется

При использовании PgBouncer в transaction mode: max_connections = 50-100 на PostgreSQL (реальные бэкенды), тысячи клиентов подключаются к PgBouncer.

Очерёдность применения изменений

-- Применить без перезапуска
SELECT pg_reload_conf();

-- Проверить, что изменение вступило в силу
SHOW work_mem;

Профиль нагрузки влияет на тюнинг

Web OLTP (много коротких транзакций, INSERT/SELECT по PK): маленький work_mem (4–16 МБ), большой shared_buffers, max_connections через PgBouncer.

Аналитические запросы (GROUP BY, window functions, большие JOIN): большой work_mem (256 МБ – 1 ГБ), max_parallel_workers_per_gather = 4+, можно уменьшить max_connections.

Смешанная нагрузка: PgBouncer для OLTP-соединений + отдельная реплика для аналитики с другим work_mem.