Настройка памяти PostgreSQL

Postgres 14 на сервере 128 ГБ RAM. shared_buffers = 128 МБ (дефолт). hit_ratio = 67%. Каждый второй запрос идёт на диск. После изменения shared_buffers до 32 ГБ, work_mem до 64 МБ, effective_cache_size до 96 ГБ - hit_ratio = 99.2%, 95-й перцентиль latency упал с 450 мс до 12 мс. Нулевые изменения кода. Только конфиг.

• **Amazon RDS** - автоматически настраивает shared_buffers в 25% от RAM инстанса при создании. effective_cache_size = 75% RAM. work_mem = 1 МБ (консервативно для multi-tenant)
• **Gitlab.com** - work_mem для reporting-запросов: увеличен с 4 МБ до 64 МБ для specific query classes, время выполнения monthly reports упало с 45 сек до 8 сек
• **Percona** benchmark: huge_pages = on на PostgreSQL 14 с shared_buffers 8 ГБ - прирост TPS 15-18% на OLTP нагрузке по сравнению с обычными страницами

shared_buffers: главный кеш

**shared_buffers - буферный пул PostgreSQL.** Все таблицы и индексы читаются с диска в shared_buffers и кешируются там. Чем больше shared_buffers, тем меньше обращений к диску. Дефолт - 128 МБ. Для production - 25% от общей RAM. На сервере 32 ГБ: shared_buffers = 8 ГБ.

**shared_buffers > 40% RAM - контрпродуктивно.** PostgreSQL использует двойное кеширование: shared_buffers + OS page cache. При shared_buffers = 50% RAM остаётся мало для OS кеша, что замедляет операции WAL и внешние файлы. Оптимум: 25-33% RAM.

work_mem: память для сортировки

**work_mem** - память для операций сортировки, хеш-таблиц join и bitmap операций в рамках одного узла плана запроса. Если данных больше чем work_mem - PostgreSQL использует временные файлы на диске (в разы медленнее). Дефолт - 4 МБ. Риск: каждый узел плана каждого запроса каждого backend процесса может использовать work_mem.

**pgBadger и auto_explain** помогают найти запросы с `external merge Disk` - это кандидаты для увеличения work_mem. Gitlab увеличил work_mem с 4 МБ до 64 МБ для reporting-запросов - время выполнения упало с 45 сек до 8 сек.

maintenance_work_mem: VACUUM и индексы

**maintenance_work_mem** - память для операций обслуживания: VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY, CLUSTER. В отличие от work_mem, одновременно используется немного autovacuum workers и ручных операций - можно ставить значительно больше. Дефолт - 64 МБ. Рекомендация - 1-5% RAM, но не более 2 ГБ.

**Для CREATE INDEX CONCURRENTLY на больших таблицах** рекомендуется временно увеличить maintenance_work_mem в сессии. Это не требует рестарта и не влияет на параллельные соединения.

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

**effective_cache_size** - не выделяет память, а подсказывает планировщику сколько данных реально можно прочитать из кеша (shared_buffers + OS page cache). Влияет на выбор между index scan и sequential scan. Чем выше effective_cache_size - тем чаще планировщик предпочитает индексы.

**Занижение effective_cache_size** - частая причина плохих планов на свежеустановленных серверах. Дефолт 4 ГБ на сервере с 64 ГБ RAM заставляет планировщик считать seq scan дешевле index scan, хотя данные реально в кеше ОС. После исправления некоторые запросы ускоряются в 10-100 раз без изменения кода.

Huge Pages: снижение overhead TLB

**Huge pages** (2 МБ вместо 4 КБ) снижают количество записей в TLB (Translation Lookaside Buffer). При shared_buffers = 8 ГБ с обычными страницами нужно 2 млн TLB-записей. С huge pages - 4000. Меньше TLB-промахов = меньше latency при доступе к данным. Прирост производительности: 5-20% на OLTP нагрузках.

**transparent_hugepages (THP)** в Linux - автоматическое объединение обычных страниц в huge. Для PostgreSQL THP обычно вреден из-за latency spikes при defragmentation. Рекомендуется: THP отключить, huge_pages = try/on включить.

Итоги

• **shared_buffers = 25% RAM** - главный рычаг. Мониторинг: hit_ratio в pg_stat_database. < 90% = мало
• **work_mem** - на сортировку/join. Multiply-danger: max_connections × avg_nodes × work_mem = пиковая RAM. Дефолт 4 МБ слишком мал для аналитики
• **maintenance_work_mem** - для VACUUM и CREATE INDEX. Каждый autovacuum worker использует свой пул. 1-5% RAM = разумный диапазон
• **effective_cache_size = 75% RAM** - подсказка планировщику, не выделение памяти. Частая причина плохих планов при дефолте 4 ГБ
• **huge_pages** снижают TLB-overhead на 5-20% при shared_buffers > 4 ГБ. THP в Linux отключить

Связанные темы

Память связана с другими параметрами производительности:

• Настройка WAL — wal_buffers и checkpoint_completion_target взаимодействуют с shared_buffers через dirty pages flush
• Настройка autovacuum — autovacuum_work_mem (наследует maintenance_work_mem) определяет эффективность очистки dead tuples
• Архитектура PostgreSQL — shared_buffers - это shared memory из архитектурного урока. Понимание shared memory объясняет почему нельзя давать более 40% RAM

Вопросы для размышления

• Сервер 64 ГБ RAM, shared_buffers = 16 ГБ, max_connections = 300, work_mem = 32 МБ. Запрос с 4 Sort узлами плана. Рассчитать пиковое потребление памяти при полной нагрузке. Что происходит с сервером?
• hit_ratio = 72% на БД с таблицей 200 ГБ, shared_buffers = 8 ГБ. Увеличение shared_buffers до 16 ГБ улучшит hit_ratio? Какой инструмент покажет какие таблицы занимают больше всего кеша?
• Запрос с ORDER BY выбирает 10M строк. EXPLAIN показывает 'external merge Disk: 2.5 GB'. work_mem = 8 МБ. Рассчитать необходимый work_mem. Почему нельзя просто поставить work_mem = 3 ГБ глобально?

Связанные уроки

• db-11-query-optimization