Привет!

Если вы администрируете PostgreSQL или хотя бы раз пытались понять, почему ваш отчёт выполняется полчаса, то наверняка натыкались на интересную настройку work_mem. Одна строчка в конфиге, а может превратить черепашку в гепарда. Или наоборот, положить сервер.

Суть проблемы

work_mem — это сколько оперативки PostgreSQL может выделить на одну операцию внутри запроса. Какую операцию? Сортировку, джойн, агрегацию — всё, где нужно что‑то посчитать или упорядочить перед выдачей результата.

Не хватило памяти? Postgres начнёт писать промежуточные данные на диск. А это в разы медленнее. Вы наверняка видели, как простой ORDER BY вдруг тормозит секунд 30 — вот это оно и есть.

Но если дать слишком много памяти, то сервер начнёт умирать.

Как это работает

По дефолту work_mem = 4MB. Звучит смешно, но Postgres перестраховывается: он не знает, сколько у вас оперативки и сколько будет одновременных запросов.

И вообще 4MB — это на одну операцию, а не на весь запрос. Простой пример:

SELECT * 
FROM orders o
JOIN customers c ON o.customer_id = c.id 
WHERE c.city = 'Москва' 
ORDER BY o.created_at;

Что тут происходит:

• Hash join для соединения таблиц съест до work_mem

• Сортировка результата ещё до work_mem

Итого один запрос может схавать в два раза больше work_mem. А если джойнов три и сортировок две? Умножайте на пять.

Теперь представьте, что вы поставили work_mem = 50MB, и у вас 20 одновременных пользователей делают сложные отчёты. В худшем случае это 20 × 5 × 50мб = 5 гигабайт.

Что происходит, когда памяти мало

Postgres не падает с ошибкой, он просто уходит на диск. Запускаете EXPLAIN ANALYZE, видите:

Sort Method: external merge  Disk: 2048kB

Это значит: «я попытался отсортировать в памяти, не влезло, записал на диск 2 мегабайта». Казалось бы, немного, но запрос вместо миллисекунд работал секунды.

То же самое с хеш‑джойнами. Если увидели, что PostgreSQL разбивает хеш на батчи, память кончилась.

Как понять, что у вас проблема

Cмотрите планы запросов:

EXPLAIN (ANALYZE, BUFFERS) 
SELECT ...

Ищите external merge или Hash Buckets с большим числом батчей.

Проверьте статистику temp файлов:

SELECT datname, temp_files, temp_bytes 
FROM pg_stat_database;

Растёт temp_bytes каждый день на гигабайты? У вас точно есть запросы, которые много пишут на диск.

pg_stat_statements

Если подключён этот модуль (а он должен быть подключён), там есть колонка temp_bytes для каждого запроса. Сортируете по ней, и сразу видите проблемные места.

Сколько вешать в граммах?

Окей, а сколько ставить?

Всё зависит от вашей нагрузки.

Обычно советуют начать с 32MB и смотреть. Большие отчёты всё ещё пишут на диск? Попробуйте 64MB или 128MB.

Но тут важна конкуренция за ресурсы. Формула для прикидки:

work_mem × max_connections × 5

Где 5 — примерное число узлов плана, требующих памяти в сложном запросе. У вас может быть 3 или 10, но для оценки сойдёт.

Пример: max_connections = 100, work_mem = 50MB
100 × 50 × 5 = 25 гигов потенциально

Если у вас сервер на 32GB, терпимо (с учётом shared_buffers и ОС). Но если connections = 500, это уже 125GB, явный перебор.

Адекватный подход

1. OLTP‑системы (много коротких транзакций, высокая конкуренция) держим work_mem на 16-32MB глобально. Соединений много, запросы простые, важнее не отдать всю память.

2. Аналитика (тяжёлые отчёты, мало соединений), можно 64-128MB и выше. Тут каждый запрос имеет право на роскошь.

3. Узкая настройка:

SET LOCAL work_mem = '256MB';
SELECT ... тяжёлый отчёт ...;

Это выделит 256MB только этому запросу. После выполнения настройка вернётся к глобальной.

По пользователям:

ALTER ROLE analyst SET work_mem = '128MB';

Аналитикам больше, обычным пользователям меньше.

Правильно выставленный work_mem может ускорить тяжёлый запрос. Лучше держать глобально на среднем уровне, а для тяжёлых операций поднимать чисто через SET.

Вообще есть pgtune для стартовых настроек, но в самом деле это только отправная точка.

Если work_mem заставил вас снова открыть EXPLAIN ANALYZE и задуматься, «куда утекает память», то полезно собрать картину шире. На курсе «PostgreSQL для администраторов баз данных и разработчиков» разбирают настройку производительности кластера, индексы/джойны/статистику, блокировки и deadlock, бэкапы и большие объёмы данных.

Немного практики в тему — пройдите вступительный тест по PostgreSQL и узнаете, есть ли пробелы в знаниях.

Чтобы узнать больше о формате обучения и познакомиться с преподавателями, приходите на бесплатные демо-уроки:

• 14 января 18:30. «Улица разбитых кластеров: про бэкапы и реплики в PostgreSQL». Записаться

• 15 января 20:00. «Топ-5 SQL инструментов, которые используют аналитики каждый день». Записаться

• 21 января 20:00. «Мониторинг: как понять, что твой сервис болен». Записаться