🔤🔤🔤 Временные таблицы в postgresql.

Ни для кого не секрет что с одной стороны запросы 1С все буквально сотканы из временных таблиц.
С другой стороны, именно работа с временными таблицами является ахилесовой пятой postgresql.
За последние несколько лет сделано очень много оптимизаций и изменений, касающихся работы с времеными таблицами как со стороны Платформы 1с так и со стороны postgres.

Но недавно нагрузочные тесты показали еще один интересный момент, про который сегодня расскажу.

Все мы знаем, что временные таблицы сеанса postgres размещаются в оперативной памяти, пока не исчерпают обьем, указанный в параметре temp_buffers.
И конечно есть соблазн купить много оперативной памяти и выкрутить этот параметр в бОльшую сторону.
Например вместо рекомендуемых 128-256МБ поставить 2ГБ.
И конечно же ожидать от этого только плюсы и увеличение скорости работы с временными таблицами.

Оказалось что при достаточно больших значениях temp_buffers, 1ГБ и более (но как и при любом тесте многое зависит от железа и эта цифра может быть как больше так и меньше), удаление временной таблицы из памяти занимает достаточно продолжительное время.
Это происходит из-за того что при работе с памятью системе нужно последовательно пройти все страницы и освободить их, при размере временной таблицы в 1ГБ таких проходов около 0.5 млн.

В итоге пришли в неожиданному выводу, что эффективнее наоборот уменьшить temp_buffers, но при этом вынести табличное пространство для временных таблиц на диск в оперативной памяти (RAM-диск, tmpfs).
Так как удаление файла размером в 1ГБ это очень быстрая файловая операция, а не последовательный проход страниц.

Как же вынести временные таблицы в отдельное табличное пространство?

За это отвечает параметр в postgresql.conf - temp_tablespaces

Итого, нам нужно смонтировать каталог в tmpfs, дать на него права пользователю и группе postgres и указать его абсолютный путь в параметре temp_tablespaces.

Только будьте внимательны!
Размер каталога должен соответствовать профилю вашей нагрузки.

Тут проще всего ориентироваться на статистику максимального размера занимаемого места временными таблицами.
К сожалению именно такого разреза статистики в postgresql нет, но зато это знает операционная система.
Просто выносим временные таблицы в отдельное табличное пространство на диске, где работаем сейчас.
И ставим размер свободного или занятого места на этом диске на мониторинг в вашей системе мониторинга.
Ждём релевантный для вашего профиля нагрузки период и смотрим там максимальный показатель занятого места.
Прибавляем к нему 20-30% и именно столько нам нужно будет выделить место в оперативной памяти для уверенности в том что его хватит.

Опять же, это справедливо для случая когда мы не меняем параметр temp_buffers. Если же мы его уменьшаем, то таблиц на диске будет еще больше и вам нужна новая статистика для определения необходимого объема.

Это особенно актуально в системах, где вы часто видите запрос drop temptable, который длится более 1 сек.