memory-and-spill

Память, work_mem и spill на диск

Предпосылки: EXPLAIN (как смотреть actual и BUFFERS), планировщик запросов (hash join, sort, aggregate), буферный кеш (страницы и I/O).

← EXPLAIN | Prepared statements →

Одна и та же форма запроса может быть «быстрой» на одних данных и «внезапно медленной» на других, хотя индексы те же. Частая причина — переход из режима «всё в памяти» в режим «часть работы ушла на диск»: сортировки, хеш‑соединения и агрегации начинают писать во временные файлы, и latency перестаёт быть предсказуемой.

Что именно «не помещается»

Два типа операций особенно чувствительны к объёму данных:

Сортировка (Sort). Нужна для ORDER BY, DISTINCT, GROUP BY (в вариантах GroupAggregate), merge join и некоторых оконных функций. Если вход большой, сортировка требует много памяти.

Хеш‑структуры (Hash Join, HashAggregate). Hash join строит хеш‑таблицу на одной стороне соединения. HashAggregate строит хеш‑таблицу групп. Если групп много (например, GROUP BY по почти уникальному полю) или строки широкие, объём быстро растёт.

Пока структура помещается в память, работа в основном CPU‑характера. Когда не помещается, появляется spill: часть данных пишется в temp, а потом читается обратно. Это добавляет I/O и часто делает запрос в разы медленнее.

work_mem: откуда берётся лимит

PostgreSQL ограничивает память, которую одна операция сортировки или хеширования может использовать, параметром work_mem.

Важно понимать два следствия:

1. work_mem — это лимит на операцию, а не «на запрос целиком». Один запрос может содержать несколько сортировок и хешей.
2. Операции могут выполняться параллельно (несколько узлов плана, несколько workers), поэтому суммарная потребляемая память легко оказывается выше, чем «кажется по одному числу».

Поэтому spill может появляться даже при, казалось бы, умеренных значениях work_mem: суммарная память запроса зависит от числа операций сортировки/хеширования и от параллелизма.

Как spill выглядит в EXPLAIN ANALYZE

Sort: in-memory vs external

У сортировки обычно есть строка Sort Method, и по ней видно, влезло ли в память:

Sort  (actual time=... rows=... loops=...)
  Sort Key: created_at
  Sort Method: quicksort  Memory: 512kB

Это вариант «в памяти».

Если данных много, появляется external sort и использование диска:

Sort  (actual time=... rows=... loops=...)
  Sort Key: created_at
  Sort Method: external merge  Disk: 120MB

Когда вы видите external и большой Disk, это почти всегда объясняет скачок latency: в запрос добавился temp I/O.

Hash Join / HashAggregate: batches

У хеш‑узлов обычно печатается статистика по хеш‑таблице. Ключевой сигнал spill — batches (разбиение на пакеты) и рост Disk Usage:

Hash Join  (actual time=... rows=... loops=...)
  Hash Cond: (o.user_id = u.id)
  ->  Seq Scan on orders o  (...)
  ->  Hash  (...)
        Buckets: 262144  Batches: 8  Memory Usage: 4096kB  Disk Usage: 300MB

Идеальная ситуация — Batches: 1 и только Memory Usage. Как только Batches становится больше 1, PostgreSQL начинает писать часть данных во временные файлы и обрабатывать соединение в несколько проходов.

Почему это проявляется «вдруг»

Spill часто появляется не из‑за изменения кода, а из‑за изменения входа:

• стало больше строк после фильтра (селективность упала),
• выросла ширина строк (добавили колонки, тащите SELECT *),
• выросло число групп (GROUP BY по «почти уникальному» полю),
• планировщик выбрал другой алгоритм (например, hash join вместо nested loop), потому что ошибся в оценке rows.

Поэтому диагностика обычно начинается не с «подкрутить память», а с вопроса: почему в узел попало так много строк и можно ли сократить ввод раньше.

Что можно сделать на уровне запроса

Одна и та же операция может либо не понадобиться вовсе, либо стать дешёвой, если уменьшить вход.

Если сортировка появляется из‑за ORDER BY, первое, что стоит проверить: может ли индекс отдать строки в нужном порядке. Для типового случая это B-tree по ключу сортировки или составной индекс, который покрывает фильтр и порядок.

Если hash aggregate раздувается из‑за большого числа групп, часто помогает изменить уровень агрегации: сначала сузить данные (по времени, по статусу), а уже потом группировать, или вынести часть логики в отдельный этап (например, материализованный промежуточный набор при наличии строгой причины).

Если hash join уходит в batches, полезно спросить, действительно ли join должен обрабатывать миллионы строк. Иногда причина — отсутствие селективного условия, иногда — «не тот» порядок соединения, иногда — банально нет индекса на ключе соединения, и раннее ограничение результата невозможно.

Sources

• PostgreSQL Documentation (пример: v16): Resource Consumption (work_mem). https://www.postgresql.org/docs/16/runtime-config-resource.html
• PostgreSQL Documentation (пример: v16): Using EXPLAIN (Sort/Hash details). https://www.postgresql.org/docs/16/using-explain.html

---

← EXPLAIN | Prepared statements →