Память настраивают по механизму, а не по рецепту
«Поставь work_mem побольше» - совет без понимания того, как параметр расходуется, и так роняют сервер. Каждый из четырёх параметров живёт в своей подсистеме.
shared_buffers - общий кеш
Один блок разделяемой памяти на весь кластер - это и есть буферный кеш (buffer-cache). Сюда кладутся страницы таблиц и индексов, чтобы не читать их с диска повторно. Выделяется один раз при старте. Типичная отправная точка - около четверти ОЗУ машины; больше не всегда лучше, потому что под кешем работает ещё и страничный кеш ОС.
work_mem - самый коварный
Лимит памяти на одну операцию: сортировку (ORDER BY),
хеш-соединение (см. join-algorithms), хеш-агрегацию. Ключевое слово - «одну». В одном запросе
таких операций может быть несколько, и каждая берёт до work_mem. Умножь
на число параллельных соединений - и получишь реальный аппетит.
пиковый расход ≈ work_mem × (операций в запросе) × (одновременных запросов)
Поэтому work_mem = 1GB на сервере с сотней коннектов - это заявка на
OOM, а не оптимизация. Параметр поднимают точечно: глобально скромный, а
под конкретный тяжёлый отчёт - SET work_mem в его сессии.
Что происходит при нехватке work_mem
Операция не падает - она «проливается» на диск (temp-файлы). Сортировка
становится внешней, хеш разбивается на пакеты. Запрос отработает, но
медленно. Сигнал виден в плане (Sort Method: external merge Disk: NNNN kB) и в счётчиках:
SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp
FROM pg_stat_database WHERE temp_files > 0;
Растущий temp_files под нагрузкой - прямая подсказка, что work_mem
мал для текущих запросов. Включённый log_temp_files = 0 пишет каждый
пролив в лог.
maintenance_work_mem - для обслуживания
Отдельный лимит для VACUUM, CREATE INDEX, ALTER TABLE. Этих
операций мало и они не параллелятся клиентами, поэтому его ставят
заметно выше work_mem: больше памяти - быстрее vacuum собирает мёртвые
идентификаторы, быстрее строится индекс.
effective_cache_size - подсказка, не аллокация
Этот параметр ничего не выделяет. Он сообщает планировщику, сколько суммарно памяти под кеш доступно (shared_buffers плюс страничный кеш ОС). По нему планировщик оценивает, насколько вероятно, что нужные страницы уже в памяти, и охотнее выбирает index scan вместо seq scan. Заниженное значение отпугивает планировщик от индексов.
Как находить запросы, которым не хватает work_mem - pg-stat-map. Почему запрос вообще выбрал сортировку и индекс - это уже планировщик и его стоимостная модель (см. cost-model).