В 2023 году я писал о временных таблицах в PostgreSQL и о том, как перенести их на RAM-диск. Это помогло: в наших измерениях доля ext4 в профиле процессора упала с 13,5% до 1,6%, и про диск мы с тех пор не вспоминали. Однако создание, очистка и удаление временных таблиц остались обычным DDL — с изменениями системного каталога и сильными блокировками, которые держатся до конца транзакции. Оказалось, что это отдельная цена, и платить её приходится в самых неожиданных местах.

На одном сервере активные бэкенды периодически застревали в ожиданиях LWLock:LockManager. На другом отставала логическая репликация и накапливался WAL. Выглядело это совершенно по-разному, а разбираться в обоих случаях пришлось в одном и том же: в том, что происходит в PostgreSQL, когда временные таблицы создаются и очищаются тысячами. Начнём с блокировок — там обнаружился массив из 1024 счётчиков, который занимает четыре килобайта, не настраивается и не менялся с 2011 года, и выяснилось, что временные таблицы одной сессии могут лишать обычные запросы всех остальных сессий быстрого пути получения блокировок.

С чего всё началось

Инсталляция, с которой начался разбор, выглядит так: PostgreSQL 18.4 на Debian, max_connections 2500, shared_buffers 256 ГБ, temp_buffers 96 МБ, подключено 1777 сессий. В момент торможения выгрузка pg_stat_activity содержала 155 строк: 150 активных client backend, ещё три idle in transaction, один autovacuum worker и один walsender. Из активных клиентов 96 ждали LWLock:LockManager.

Это оказался не поток одного типа запросов. Среди 96 ожидавших были:

То есть DDL был хорошо виден, но большую часть очереди составляли обычные SELECT и INSERT. Это как раз похоже не на конфликт SQL-блокировок между двумя транзакциями, а на конкуренцию внутри общей хеш-таблицы менеджера блокировок: слабые блокировки обычных запросов массово пошли медленным путём.

При этом у машины не была сильно нагружен CPU. dstat показывал 27–35% user CPU, 4–5% system CPU, 56–64% idle и 2–7% iowait. Чтение колебалось примерно от 435 МБ/с до 1,1 ГБ/с, запись — от 40 до 222 МБ/с, а число переключений контекста доходило до 238–398 тысяч в секунду. Картина больше напоминала convoy из коротких ожиданий, чем нехватку процессорных ядер: если считать возраст запроса относительно самой поздней метки в выгрузке, у 96 ожидавших LockManager медиана составляла около 8 мс, 90-й перцентиль — около 83 мс, максимум — 444 мс. Это приближённые значения, потому что pg_stat_activity не является трассировкой событий, но масштаб коротких ожиданий они передают.

Мы сняли pg_locks в один из таких моментов: 47 592 записи, и только у 108 из них fastpath = true. Один бэкенд держал 843 блокировки AccessExclusiveLock, и все они были на временных отношениях.

Чтобы понять, что это значит, надо вспомнить, как в PostgreSQL устроены блокировки на таблицы и что такое fast path. Ссылки на код ниже ведут на конкретный коммит ветки REL_18_STABLE, чтобы номера строк не разъехались со временем.

Как устроен fast path

Каждый оператор берёт блокировку на каждое отношение, которого касается. Режимов табличных блокировок восемь, их совместимость задаёт матрица конфликтов, а в lockdefs.h они пронумерованы:

#define AccessShareLock          1  /* SELECT */
#define RowShareLock             2  /* SELECT FOR UPDATE/FOR SHARE */
#define RowExclusiveLock         3  /* INSERT, UPDATE, DELETE */
#define ShareUpdateExclusiveLock 4  /* VACUUM (non-FULL), ANALYZE, CREATE INDEX CONCURRENTLY */
#define ShareLock                5  /* CREATE INDEX (WITHOUT CONCURRENTLY) */
#define ShareRowExclusiveLock    6
#define ExclusiveLock            7
#define AccessExclusiveLock      8  /* ALTER TABLE, DROP TABLE, VACUUM FULL, and unqualified LOCK TABLE */

Первые три режима — слабые. Между собой они не конфликтуют никогда, и именно их берёт обычная работа с данными. Режимы с пятого по восьмой — сильные: каждый из них конфликтует хотя бы с одним из первых трёх, и берёт их в основном DDL. Четвёртый стоит особняком, мы к нему ещё вернёмся.

До появления fast path новые блокировки оформлялись через общие хеш-таблицы менеджера блокировок в shared memory. Они разбиты на 16 партиций (lwlock.h), каждая под своим LWLock, и получение блокировки, которой у бэкенда ещё нет, — это обращение к таблице под LWLock партиции. Когда бэкендов сотни и все они непрерывно берут и отпускают блокировки на одни и те же справочники, шестнадцать партиций превращаются в узкое место. Это и есть то самое ожидание LWLock:LockManager.

В 2011 году Роберт Хаас добавил для этого fast path, и с версии 9.2 слабые блокировки, при выполнении некоторых условий, бэкенд учитывает в своём собственном небольшом массиве, не добавляя их в общую хеш-таблицу. Пока нет конфликтующей сильной блокировки, этого достаточно.

Когда кто-то запрашивает сильную блокировку, PostgreSQL обходит бэкенды и переносит подходящие fast-path-блокировки на это отношение в общую таблицу (FastPathTransferRelationLocks) — иначе ALTER TABLE прошёл бы поверх работающего SELECT. Но остаётся обратный вопрос: как бэкенду, который собирается взять слабую блокировку, быстро узнать, можно ли ещё пользоваться fast path, или на это отношение уже кто-то претендует всерьёз? Для этого разработчики предусмотрели общий массив счётчиков. Он описан в комментарии в lock.c, и один оборот в нём стоит запомнить:

We partition the locktag space into FAST_PATH_STRONG_LOCK_HASH_PARTITIONS, and maintain an integer count of the number of “strong” lockers in each partition. When any “strong” lockers are present (which is hopefully not very often), the fast-path mechanism can’t be used, and we must fall back to the slower method of pushing matching locks directly into the main lock tables.

Сама структура — десятью строками ниже:

#define FAST_PATH_STRONG_LOCK_HASH_BITS        10
#define FAST_PATH_STRONG_LOCK_HASH_PARTITIONS  (1 << FAST_PATH_STRONG_LOCK_HASH_BITS)

typedef struct
{
    slock_t     mutex;
    uint32      count[FAST_PATH_STRONG_LOCK_HASH_PARTITIONS];
} FastPathStrongRelationLockData;

То есть массив из 1024 счётчиков по четыре байта: сам массив занимает 4096 байт, кроме него в структуре есть только спинлок. Каждое отношение хешем своего locktag попадает в одну из 1024 корзин. Бэкенд, который запрашивает сильную блокировку, увеличивает счётчик корзины ещё до того, как её получит (BeginStrongLockAcquire). Бэкенд, который хочет взять слабую блокировку через fast path, сначала проверяет, что у него есть свободный слот в нужной группе (строка 987), а потом — что счётчик корзины его отношения равен нулю (строка 999). Если не равен, идёт обычным путём, в общую таблицу. Когда сильная блокировка освобождается, счётчик уменьшается (строки 1496–1497).

Важно, что счётчик ничего не знает про конкретное отношение. Он знает только, что для этой корзины есть сильная блокировка — уже выданная или ещё ожидающая. Новые слабые блокировки на любое другое отношение, попавшее в ту же корзину, тоже приходится оформлять через общую таблицу. Разработчиков можно понять: пока сильных блокировок мало, такие ложные совпадения редки, и никто их не замечает. Собственно, это и записано в комментарии — hopefully not very often.

При чём тут временные таблицы

CREATE TEMPORARY TABLE берёт AccessExclusiveLock на создаваемое отношение. TRUNCATE — тоже. DROP — тоже. И полученные ими блокировки удерживаются до конца транзакции, а не до конца оператора (откат к savepoint может отпустить их раньше, но в обычной работе это редкость).

Возьмём транзакцию, которая создаёт двадцать временных таблиц, наполняет их, а в конце очищает. Даже если считать только сами таблицы, без индексов и TOAST, она до своего конца держит сильные блокировки примерно в двадцати корзинах из 1024. Полторы сотни таких транзакций одновременно — это уже несколько тысяч сильных блокировок на разные отношения. Если считать хеш равномерным и независимым для этих locktag, ожидаемая доля занятых корзин считается как в задаче о заполнении корзин, 1 − (1 − 1/1024)^N ≈ 1 − e^(−N/1024), где N — число различных сильно заблокированных отношений:

Обратите внимание, что страдают уже не временные таблицы, а обычные. У справочника, который читают 1600 сессий и который никто никогда не блокирует сильно, корзина всегда одна и та же, и в модели с пятью тысячами сильно заблокированных отношений вероятность, что в ней окажется чья-нибудь временная таблица, выше 99%. Пока корзина занята, каждое новое получение слабой блокировки на справочник идёт в общую хеш-таблицу под LWLock одной из 16 партиций. Тот самый LWLock:LockManager, с которого мы начали.

Дальше может включиться механизм обычной дорожной пробки. Чем больше сильных блокировок накопилось, тем чаще запросы теряют fast path и конкурируют за внутренние локи менеджера блокировок. Ожидания удлиняют транзакции, а те всё это время продолжают удерживать уже полученные блокировки. Если новые транзакции продолжают приходить, одновременно удерживаемых блокировок становится ещё больше, конкуренция усиливается, и транзакции замедляются ещё сильнее. Так кратковременный всплеск нагрузки может превратиться в затяжную пробку, которая сама себя поддерживает.

Честно говоря, расчёт выше — модель, а не восстановленный инцидент. Снимок pg_locks ей не противоречит: fast-path-записей в нём почти нет, а у одного бэкенда накопилось 843 AccessExclusiveLock на временных отношениях. Но pg_locks не показывает значения этих счётчиков и причины обхода fast path. Штатной метрики заполненности этих 1024 корзин в PostgreSQL 18 нет, поэтому приведённые проценты — расчётная оценка, а не прямое измерение во время инцидента.

Здесь стоит задержаться на числе 1024. Это 1 << 10, где 10 — константа в исходниках. Не параметр postgresql.conf, не что-то, что вычисляется из max_connections или shared_buffers. Массив занимает четыре килобайта и был таким с 2011 года. При FAST_PATH_STRONG_LOCK_HASH_BITS = 20 он занимал бы четыре мегабайта, чего на сервере с сотнями гигабайт памяти никто бы не заметил, а в той же модели пять тысяч отношений заняли бы около 0,5% корзин вместо 99%. Вероятность случайно отключить fast path конкретному справочнику уменьшилась бы примерно в двести раз, и массовое отключение fast path из-за коллизий — именно тот механизм, который мы описали, — практически исчезло бы. Не все ожидания LockManager вообще: сами сильные блокировки, их перенос и нагрузка на обычный путь никуда не денутся. Но фон из тысяч посторонних SELECT, которым fast path выключили за компанию, — да. Любопытно, что второе ограничение fast path, число слотов у бэкенда, которых долгие годы было 16, в PostgreSQL 18 наконец стали выводить из max_locks_per_transaction. А 1024 корзины остались 1024 корзинами.

Не всякая работа с временными таблицами приводит к этому эффекту. Четвёртый режим, ShareUpdateExclusiveLock, который берут ANALYZE и обычный VACUUM, устроен иначе: через fast path он не выдаётся, но и пользоваться fast path другим не мешает, потому что в число “сильных” с точки зрения этой схемы не входит. Так что сам по себе ANALYZE временной таблицы fast path соседям по корзине не отключает. И TRUNCATE в autocommit выполняется в отдельной короткой транзакции, так что его сильная блокировка живёт гораздо меньше. Страшно не то, что сильные блокировки есть, а то, сколько их одновременно удерживается и как долго.

А нельзя ли просто не считать временные таблицы?

Первое, что приходит в голову: временная таблица видна только своему бэкенду, зачем ей вообще участвовать в общей схеме блокировок? Но в текущей реализации временные отношения участвуют в общем протоколе блокировок на отношения наравне с остальными. Проверка на строке 999 смотрит только на счётчик корзины, и признака временности в locktag, который сюда приходит, попросту нет. Исключить их — значит отдельно доказать, что это корректно, а определение временной таблицы при этом живёт в общем каталоге, который видят все бэкенды.

Один из обсуждаемых вариантов — global temporary tables: определение создаётся заранее и переиспользуется, а данные остаются приватными для сессии. Патч был предложен в 2019 году, несколько лет переходил между commitfest-ами и в июле 2022 года получил статус Returned with feedback. В PostgreSQL 18 его нет.

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

Про разрастание каталога мы писали в первой части. Напомним, откуда оно берётся, а затем разберём, как постоянные изменения каталога могут тормозить логическую репликацию.

Каждый CREATE временной таблицы — это строки в pg_class, pg_attribute, pg_type, pg_depend и их индексах; если у таблицы есть первичный ключ, отношений уже два, а с TOAST — до четырёх. Каждый DROP — удаление всего этого. Каталог PostgreSQL — такие же MVCC-таблицы, как и остальные: удалённые строки становятся мёртвыми и ждут автовакуума. На загруженном сервере это непрерывный поток мёртвых кортежей в самых горячих таблицах базы. На той инсталляции, с которой мы начали, в pg_class было 445 тысяч строк — это число видимых строк, мёртвые версии сверх того. Статистика временных таблиц тоже живёт в общем каталоге: первый ANALYZE добавляет строки в pg_statistic, повторные и последующий DROP оставляют там мёртвые версии.

Ещё одно последствие проявилось на другой инсталляции, где через логическую репликацию попробовали читать данные для хранилища. Репликация отставала, накапливался WAL. В профиле perf наверху оказались pg_qsort — 38,22% и xidComparator — 35,90%: сортировка и сравнение идентификаторов транзакций. Вероятный источник этой нагрузки — построение исторических снимков каталога. Чтобы правильно прочитать изменения из WAL, декодированию нужно знать, как каталог выглядел в тот момент. Для этого оно хранит массив завершённых транзакций, менявших каталог, и при построении нового снимка сортирует его целиком.

Сами временные данные не реплицируются, но создание, удаление и обычный TRUNCATE временных таблиц меняют общий каталог. Такие транзакции пополняют массив и заставляют строить новые снимки. При интенсивном потоке таких изменений повторные сортировки могут стать узким местом и без длинных запросов. На той инсталляции каталоги pg_class и pg_statistic сильно выросли, а автовакуум не справлялся. То есть даже когда временные данные не попадают в репликацию, частые изменения их описаний в каталоге могут заметно её тормозить.

У обычного VACUUM есть и своя ловушка. В конце работы он вызывает vac_update_datfrozenxid, а та, как честно сказано в комментарии, “must seqscan pg_class to find the minimum Xid, because there is no index that can help us here”. Если каждую временную таблицу вакуумить отдельной командой, каждая такая команда заканчивается сканированием pg_class, и на 445 тысячах строк это уже заметно. С PostgreSQL 16 есть VACUUM (SKIP_DATABASE_STATS), который этот шаг пропускает; обновлять общебазовые сведения о замороженных XID тогда надо отдельно и периодически, например командой VACUUM (ONLY_DATABASE_STATS).

Итого после переноса файлов на tmpfs остаётся вторая цена: постоянные CREATE, DROP и транзакционный TRUNCATE дают поток сильных блокировок и оборот версий в каталоге. Даже маленькие таблицы обходятся дорого, если их много, DDL идёт непрерывно, а транзакции долго не заканчиваются.

Что делать

Часто нам говорят, что мы слишком активно пользуемся временными таблицами и можно было бы обойтись без них. Мы отвечали на это в первой части и повторяться не будем. Диск там же и разобран: tmpfs для временных таблиц убирает файловую систему из профиля, но ни блокировки, ни каталог не трогает. Если от временных таблиц отказаться нельзя, остаётся несколько вещей, и мы перечислим их от простого к сложному.

Первое — меньше бэкендов. Конкуренция на партициях менеджера блокировок растёт с числом бэкендов, которые в него ходят, и ограничить число одновременно работающих бэкендов совместимым с приложением пулом соединений — это не лечение причины, но заметное облегчение симптома.

Второе — DELETE вместо TRUNCATE внутри транзакций. Сам DELETE берёт RowExclusiveLock и счётчик корзины не увеличивает, но если таблицу создали в этой же транзакции, AccessExclusiveLock от CREATE уже висит и никуда не денется. Платить придётся мёртвыми строками, которые внутри транзакции убрать нельзя (VACUUM там не работает). Для маленьких таблиц дополнительные затраты на DELETE могут оказаться меньше, чем сильная блокировка до конца транзакции.

Третье — создавать таблицы заранее, в отдельных коротких транзакциях, и переиспользовать. Сильная блокировка от CREATE тогда освобождается в конце короткой подготовительной транзакции, а создавать и удалять определение таблицы при каждом использовании больше не приходится. Правда, учитывать свободные таблицы и организовывать их повторное использование тогда придётся приложению.

И наконец, можно ждать global temporary tables или попробовать пересобрать PostgreSQL с FAST_PATH_STRONG_LOCK_HASH_BITS = 20: по памяти разница невелика, расчётное число случайных коллизий уменьшается примерно в двести раз, а насколько это скажется на кеше процессора и общем спинлоке — как раз и покажет эксперимент. Объём памяти сервера может расти, но размер этого массива от него не зависит, и при нескольких тысячах одновременно сильно заблокированных отношений почти все его корзины могут оказаться заняты.

Проверять же увеличение числа корзин в production, пересобрав PostgreSQL с другой константой, нам, честно говоря, страшновато. И даже если такая правка поможет, распространять её на все наши инсталляции неудобно: везде вместо ванильного PostgreSQL придётся ставить собственную сборку, переносить изменение на новые версии, собирать и тестировать обновления. По сути, получится отдельный вариант PostgreSQL под приложение — примерно как специальные сборки для 1С. Ради одной константы брать на себя ещё и сопровождение собственного PostgreSQL совсем не хочется.

Мы у себя в lsFusion пошли по третьему пути: сервер приложений держит пул UNLOGGED-таблиц, внутри рабочих транзакций очищает их через DELETE, между транзакциями — через TRUNCATE, а изношенные заменяет. В отличие от TEMP, эти таблицы общие, их данные теряются после аварийного завершения и не реплицируются. На стенде четыре сохранения потребовали ноль CREATE вместо сорока.

В описанных механизмах PostgreSQL просматривается одно и то же предположение: сильные блокировки и изменения каталога — относительно редкие события. Для базы с почти неизменной схемой это понятно. Но временные таблицы нужны для промежуточной работы: их создают, используют и очищают по мере выполнения задач. Когда таких задач много, “редкое” DDL становится обычной частью нагрузки, а ограничения этих механизмов начинают затрагивать посторонние запросы и логическую репликацию.

Описанные проблемы с fast path и логическим декодированием создают впечатление, что активная работа с временными таблицами просто не считается сценарием, на который стоит рассчитывать. Почему — непонятно. Это штатная возможность PostgreSQL, и желание пользоваться ею часто само по себе не выглядит ошибкой приложения. Однако приложению приходится самостоятельно строить пулы таблиц, заменять TRUNCATE на DELETE или думать о собственной сборке СУБД. Хорошим первым шагом была бы настройка количества корзин: их число можно было бы подобрать под нагрузку без пересборки PostgreSQL. Хотелось бы, чтобы такой сценарий учитывался в самом PostgreSQL, а комментарий “hopefully not very often” перестал быть условием его нормальной работы под нагрузкой.