Тюнинг PostgreSQL: shared_buffers, work_mem, effective_cache_size

Дефолтная конфигурация PostgreSQL рассчитана на скромное железо и неэффективна на современных серверах. shared_buffers = 128MB, work_mem = 4MB — эти параметры оставляют 95% памяти неиспользованной. Например, сервер с 32 ГБ RAM и дефолтными настройками использует лишь 128 МБ для кэша — база простаивает, а запросы тормозят. Правильный тюнинг даёт прирост производительности минимум 30% и снижает нагрузку на дисковую подсистему. Мы выполняем настройку под ваш профиль: OLTP, аналитика или смешанная нагрузка. Опыт команды — 50+ успешных проектов, гарантия результата.

Тюнинг PostgreSQL — это не просто «поставить цифры побольше», а понимание, как планировщик использует память, как устроен кэш и как избежать проблем с вводом-выводом. Настройка shared_buffers, work_mem и effective_cache_size — базовая, но критически важная задача. Неправильная конфигурация ведёт к свопингу или недогрузке ОЗУ. Наши инженеры анализируют ваш сервер и нагрузку, чтобы подобрать оптимальные значения.

Проблемы, которые решаем

Нехватка work_mem для аналитики

Медленные отчёты из-за сброса сортировок на диск. Типичная ситуация: запрос с ORDER BY по большой таблице выполняется минуты, хотя план показывает сортировку на диске. Это решается увеличением work_mem для конкретных запросов или созданием покрывающих индексов.

Неверный effective_cache_size

Планировщик выбирает последовательные сканирования вместо индексных, потому что думает, что кэш мал. Установка effective_cache_size в 75% RAM сразу увеличивает количество index scan.

Конфликт shared_buffers с кэшем ОС

Слишком большой shared_buffers (более 25% RAM) приводит к конкуренции с кэшем операционной системы и снижает cache hit ratio. Проверка через pg_buffercache помогает найти оптимум.

Как мы это делаем

Используем проверенную методику: аудит текущей конфигурации, анализ планов запросов, подбор параметров под нагрузку. Пример: интернет-магазин с нагрузкой 10 000 запросов в минуту. После настройки время выполнения отчётов сократилось с 5 минут до 20 секунд, cache hit ratio вырос с 97% до 99,8%.

Стек инструментов

• PostgreSQL 14–17
• pgtune для начальной оценки
• pg_buffercache для мониторинга буферов
• EXPLAIN ANALYZE для анализа запросов
• pg_stat_statements для выявления тяжёлых запросов

Процесс настройки

1. Аудит текущей конфигурации и профиля нагрузки
2. Сбор метрик: cache hit ratio, использование буферов, план запросов
3. Настройка shared_buffers — 25% RAM для выделенного сервера
4. Настройка work_mem — от 4 МБ до 64 МБ для OLTP, 256 МБ – 1 ГБ для аналитики
5. Установка effective_cache_size — 75% RAM
6. Оптимизация планировщика: random_page_cost для SSD, параметры параллельных запросов
7. Настройка checkpoint и WAL под диск (SSD/HDD)
8. Мониторинг hit rate и pg_buffercache после изменений
9. Документация внесённых изменений
10. Гарантия 30 дней: если производительность не улучшилась — пересмотрим настройки бесплатно

Тюнинг параметров памяти

Как настроить shared_buffers для OLTP?

Общий кэш страниц базы для всех процессов. Для выделенного сервера — 25% RAM. На сервере с 32 ГБ это 8 ГБ. Больше 25% может привести к конфликту с кэшем ОС. Проверить, хватает ли shared_buffers, можно по hit rate: если cache_hit_ratio < 99% — либо shared_buffers мал, либо рабочий набор не помещается в память. Используйте pg_buffercache, чтобы увидеть, какие таблицы и индексы занимают буфер. Увеличивайте shared_buffers до 25% RAM, но не более 8 ГБ на Linux из-за архитектурных ограничений.

Что делать при низком cache hit ratio?

Если cache_hit_ratio ниже 99%, это сигнал к тюнингу. Проверьте shared_buffers — возможно, нужно увеличить. Также может помочь добавление индексов. Для аналитических запросов рассмотрите увеличение work_mem. Используйте запрос из code-блока для проверки hit rate.

Почему маленькое work_mem часто лучше большого

work_mem — память для каждой сортировки / hash join в одном запросе. Если запрос имеет 3 sort node, он может занять 3 × work_mem. При 100 параллельных соединениях с тяжёлыми запросами потребление может быть 100 × 3 × work_mem. Слишком большое значение вызовет swap. Типичная ошибка — установить 64 МБ глобально, тогда как 100 соединений с 4 сортировками = 100 × 4 × 64 МБ = 25,6 ГБ. Начинайте с 16 МБ, увеличивайте для конкретных запросов через SET LOCAL. Для OLTP-нагрузки высокое work_mem ведёт к перерасходу памяти и падению производительности из-за свопинга. Наша методика: анализируем планы запросов, выявляем сортировки на диске, увеличиваем work_mem только для проблемных запросов.

effective_cache_size: простая подсказка планировщику

Подсказка планировщику о доступном кэше ОС + shared_buffers. Для сервера с 32 ГБ: 24 ГБ. Влияет на выбор между index scan и seq scan. Не резервирует память, но критически важен для правильного выбора плана. Более детально — в официальной документации PostgreSQL. Рекомендуемая установка — 75% от RAM.

maintenance_work_mem: для операций обслуживания

Для VACUUM, CREATE INDEX, ALTER TABLE. Повышайте только во время обслуживания. Значение 2 ГБ подходит для большинства задач. Не держите высоким постоянно — это спасёт память.

Как настроить планировщик и мониторинг производительности

Стоимостные параметры и параллельные запросы

# Cost model for SSD random_page_cost = 1.1 # SSD: 1.1, HDD: 4.0 (дефолт) seq_page_cost = 1.0 # Parallel queries (PostgreSQL 9.6+) max_parallel_workers_per_gather = 4 max_parallel_workers = 8 parallel_tuple_cost = 0.1 parallel_setup_cost = 1000.0 

Мониторинг hit rate и буферного кэша

-- Cache hit ratio 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; -- Buffer usage details CREATE EXTENSION IF NOT EXISTS pg_buffercache; SELECT c.relname, count(*) AS buffers, round(count(*) * 8192.0 / 1024 / 1024, 1) AS size_mb FROM pg_buffercache b JOIN pg_class c ON b.relfilenode = c.relfilenode GROUP BY c.relname ORDER BY buffers DESC LIMIT 20; 

Если cache_hit_ratio < 99% — требуется тюнинг shared_buffers или добавление индекса.

Тюнинг под нагрузку: OLTP, аналитика, смешанная

Checkpoint и WAL

checkpoint_completion_target = 0.9 checkpoint_timeout = 15min max_wal_size = 4GB fsync = on synchronous_commit = on 

Профили нагрузки: сравнение настроек

Практический пример: оптимизация сортировки

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

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); 

Применение изменений

После изменения параметров выполните SELECT pg_reload_conf(); для применения.

Типичные ошибки при настройке PostgreSQL

Заключение

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