PostgreSQL и /tmp: temp_files, disk full и настройка памяти для sort/hash join

Почему PostgreSQL внезапно заполняет /tmp

Типичный сценарий: база «жила нормально», затем в логах появляются «No space left on device», запросы начинают падать, а файловая система, где находится /tmp, оказывается забита гигабайтами временных файлов. В PostgreSQL это почти всегда следствие spill — когда операция не помещается в память и «проливается» на диск.

Чаще всего spill возникает на сортировках и хэш-операциях: Sort, Hash Join, HashAggregate. Для их выполнения сервер создаёт временные файлы (в статистике и в разговорах админов это обычно называют temp_files).

Куда именно пишутся временные файлы, зависит от temp_tablespaces и размещения каталога кластера. С точки зрения администратора это нередко выглядит как «растёт /tmp»: потому что он на том же разделе, потому что /tmp маленький, или потому что вся система упирается в один общий диск.

Важно: заполнение /tmp — обычно не «ошибка ОС», а предсказуемое следствие планов выполнения и лимитов памяти. Если не найти конкретные запросы и не поставить предохранители, ситуация будет повторяться на пиках нагрузки, отчётах и неудачных джойнах.

Что такое temp_files и как они появляются

temp_files — это временные файлы, которые PostgreSQL создаёт при выполнении запросов, когда данных для операции слишком много для выделенной памяти. Самые частые источники:

• Sort spill — сортировка не помещается в память и начинает писать промежуточные данные на диск.
• Hash join spill — хэш-таблица для Hash Join не помещается в память и разбивается на батчи, часть данных уходит на диск.
• HashAggregate spill — хэш-агрегация на больших наборах данных также может проливаться.

Физически временные файлы создаются в служебных каталогах кластера (вроде base/pgsql_tmp). Но вам, как администратору, важнее другое: эти файлы потребляют место на том разделе, где лежит соответствующий tablespace, и активно нагружают I/O.

Временные файлы — не «зло» само по себе. Проблема начинается, когда их объём становится непредсказуемым, они создаются массово и выбивают сервер по месту или по дисковой подсистеме.

Если вы держите PostgreSQL на виртуальном хостинге или на VDS, заранее продумайте, где будут жить временные файлы и какие лимиты вы готовы поставить. Это сильно снижает шанс «внезапного» переполнения раздела в пиковые периоды.

Типовой сценарий «disk full tmp»

Сообщения могут отличаться: «disk full tmp», «No space left on device», «could not write to file …: No space left on device». Суть одна: PostgreSQL пытался дописать временный файл, а свободное место закончилось.

Чаще всего это комбинация факторов:

• Есть 1–2 тяжёлых запроса (отчёты, выгрузки, аналитика «прямо на проде»).
• Параллельно выполняется много запросов, и каждый получает лимит work_mem.
• План выполнения выбирает Sort и/или Hash Join на больших объёмах данных.
• Временные файлы пишутся на небольшой раздел (часто это отдельный маленький /tmp).

Коварство в том, что даже «разумный» work_mem способен привести к катастрофе при высокой конкурентности. Он применяется на операцию (sort/hash) и на узел плана. В сложном запросе узлов может быть несколько, плюс параллельные воркеры, плюс одновременные сессии.

Диагностика: подтвердить spill и найти виновные запросы

1) Включаем логирование временных файлов: log_temp_files

Самый практичный шаг — включить логирование временных файлов и поставить порог, чтобы не утонуть в шуме.

ALTER SYSTEM SET log_temp_files = 1048576;
SELECT pg_reload_conf();

Параметр log_temp_files задаётся в байтах. Значение 1048576 означает: логировать только temp-файлы от 1 MiB и больше. В логе будут строки с размером временного файла и (в зависимости от ваших настроек логирования) контекстом, по которому проще найти запрос.

Чтобы поймать вообще всё на коротком окне диагностики, можно поставить 0, но на нагруженных системах это быстро «зашумит» логи.

2) Смотрим агрегированную статистику по базе: pg_stat_database

PostgreSQL ведёт счётчики временных файлов на уровне базы. Это быстрый способ понять, что spill — системная история, а не единичный инцидент.

SELECT
  datname,
  temp_files,
  pg_size_pretty(temp_bytes) AS temp_bytes_pretty
FROM pg_stat_database
ORDER BY temp_bytes DESC;

Ключевые поля:

• temp_files — количество созданных временных файлов.
• temp_bytes — общий объём временных файлов.

Это накопительные счётчики с момента сброса статистики. Для динамики полезно снимать их раз в N минут и смотреть прирост.

3) EXPLAIN (ANALYZE, BUFFERS): где именно spill

Если вы подозреваете конкретный запрос, запускайте EXPLAIN (ANALYZE, BUFFERS) (в идеале — на стенде или на реплике). В выводе ищите признаки:

• для сортировки: «Sort Method: external merge» и размер диска;
• для Hash Join: «Batches: …» и «Disk: …»;
• для агрегаций: HashAgg с признаками использования диска.

Эти строки напрямую отвечают на вопрос «это упёрлось в память или на диск?» и помогают прицелиться: увеличивать work_mem, чинить запрос/индексы или менять подход к отчётам.

Облачный VDS-сервер в России

Аренда виртуальных серверов с моментальным развертыванием инфраструктуры от 195₽ / мес

Подробнее

temp_file_limit: предохранитель, чтобы один запрос не убил диск

temp_file_limit ограничивает объём временных файлов на один процесс (один backend). Это не ускоряет запросы, но защищает от ситуации, когда один неудачный отчёт выедает всё место на разделе и кладёт остальную систему.

Пример лимита в 2 GiB:

ALTER SYSTEM SET temp_file_limit = '2GB';
SELECT pg_reload_conf();

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

temp_file_limit — страховка. Её задача не «сделать красиво», а не дать одной сессии превратить проблему производительности в простои.

work_mem: главный рычаг, но с подвохом

work_mem задаёт, сколько памяти PostgreSQL может использовать для сортировок и хэширования до того, как начнётся spill на диск. Интуитивно хочется просто увеличить work_mem. Но у параметра есть подвох: значение применяется на операцию. В одном запросе может быть несколько сортировок и несколько хэш-узлов; при параллельном выполнении умножайте на число воркеров; при высокой конкурентности — на число одновременных запросов.

Рабочая тактика для продакшена:

• Держать умеренное глобальное значение work_mem для обычного трафика.
• Выдавать повышенный work_mem точечно: на сессию или на роль отчётных пользователей.
• Следить, чтобы вы не загнали систему в swap: это часто хуже, чем аккуратный spill на быстрый диск.

Точечная настройка на время сессии:

SET work_mem = '128MB';

Альтернатива — закрепить повышенный лимит за отдельной ролью, не трогая остальных:

ALTER ROLE report_user SET work_mem = '128MB';

Если проблема упирается не только в настройку, но и в общую конкурентность подключений, часто помогает пулер. См. подробный разбор в статье настройка PgBouncer для PostgreSQL.

Виртуальный хостинг для сайтов

Универсальное решение для создания и размещения сайтов любой сложности в Интернете от 95₽ / мес

Подробнее

Как уменьшить spill на sort/hash join без «разгона памяти в космос»

Оптимизация запросов и индексов

Нередко temp-файлы появляются потому, что сервер вынужден сортировать то, что можно было бы читать уже отсортированным. Проверьте:

• Есть ли подходящий индекс под ORDER BY вместе с фильтрами.
• Не сортируете ли по выражению без функционального индекса.
• Не тащите ли лишние столбцы (широкие строки увеличивают объём сортировки и хэширования).

Кардинальность и статистика

Неожиданный Hash Join на огромном наборе или неадекватный порядок джойнов часто идут от неверной оценки количества строк. Убедитесь, что статистика актуальна: выполните ANALYZE проблемных таблиц, проверьте настройки autovacuum, при необходимости увеличьте статистику на ключевых столбцах через ALTER TABLE ... ALTER COLUMN ... SET STATISTICS.

По теме планирования и обслуживания таблиц полезно держать под рукой материал настройка autovacuum и индексов в PostgreSQL.

Разделяйте отчёты и OLTP-трафик

Если аналитические запросы выполняются рядом с транзакционными, temp-файлы становятся не только вопросом места, но и I/O-конкуренции. Даже без полного заполнения диска вы можете получить лавину задержек из-за активной записи временных файлов.

Практика: выносить отчёты на отдельное окно времени, отдельную роль с отдельными лимитами, либо на реплику (если допустима задержка чтения).

Куда складывать временные файлы: temp_tablespaces и отдельный диск

Если большие сортировки и хэш-операции — регулярная история, имеет смысл управлять тем, куда пишутся temp-файлы. Для этого используют temp_tablespaces и отдельный tablespace на другом разделе (например, на отдельном SSD с запасом места).

Общая схема:

• Создаёте tablespace на нужном диске/разделе.
• Назначаете его в temp_tablespaces (глобально или на роль/базу).

Пример (путь подберите под свою систему):

CREATE TABLESPACE pg_temp_ts LOCATION '/var/lib/postgresql-temp';
ALTER SYSTEM SET temp_tablespaces = 'pg_temp_ts';
SELECT pg_reload_conf();

Так вы снижаете зависимость от маленького /tmp и получаете контроль над ёмкостью и производительностью временного хранилища. Особенно полезно, если /tmp — tmpfs или небольшой системный раздел.

Быстрый чек-лист, если /tmp уже заполнен

Когда «всё горит», действуйте аккуратно: удаление «чужих» файлов из /tmp может ломать сервисы. Временные файлы PostgreSQL обычно удаляются при завершении сессии/процесса, но если место закончилось, новые сессии могут не стартовать корректно, а текущие — падать при записи temp.

1. Остановите генерацию temp-файлов: найдите и прервите самые тяжёлые запросы через pg_stat_activity и pg_terminate_backend. Временно закройте доступ к отчётам/выгрузкам.
2. Освободите место: добавьте место на разделе или перенесите нагрузку. Не рассчитывайте, что «само рассосётся» под пиковой нагрузкой.
3. Включите диагностику: настройте log_temp_files, снимайте pg_stat_database, фиксируйте конкретные запросы.
4. Поставьте предохранители: включите temp_file_limit, приведите work_mem к адекватной стратегии (умеренно глобально, больше — точечно ролям/сессиям).

Рекомендуемая базовая конфигурация для старта

Универсальных значений нет, но как «скелет», который защищает от типовых аварий с disk full tmp, часто используют:

• log_temp_files = 1–10 MiB, чтобы видеть крупные spill и не тонуть в мелочах.
• temp_file_limit = 1–4 GiB на процесс (зависит от профиля запросов и размера диска под temp).
• work_mem — умеренно (например, 16–64 MiB), а всё «большое» выдавать точечно ролям/сессиям.

Дальше параметры калибруются по факту: смотрите прирост temp_bytes в pg_stat_database, ловите конкретные запросы по log_temp_files, проверяйте планы на предмет external merge и батчей в Hash Join.

Итог

История «PostgreSQL заполнил /tmp» почти всегда решается комбинацией трёх вещей: наблюдаемость (через log_temp_files и pg_stat_database), предохранители (через temp_file_limit) и грамотная работа с памятью и планами (через work_mem, индексы и оптимизацию запросов). Когда подход системный, временные файлы перестают быть аварийной неожиданностью и становятся управляемым ресурсом.