Здесь я представляю результаты работы над первым шагом к внедрению временных таблиц в схему параллельного выполнения запросов в PostgreSQL - тестирование последовательной записи и чтения temp buffers. В системный каталог PostgreSQL добавлены функции измерения операций сброса буферов, и проведены тесты. Для целей оптимизатора выполнена оценка коэффициента стоимости сброса буфера временной таблицы на диск относительно уже существующего DEFAULT_SEQ_PAGE_COST. Выполнена оценка стоимости простого обхода temp buffers (dry-run). Измерения показывают, что последовательная запись примерно на 30% медленнее чтения. На основе этих результатов предложена формула оценки стоимости. Выполнена оценка скорости записи для случая, когда блоки таблицы распределены по страницам памяти случайным образом.

Начиная с самого момента появления временные таблицы в PostgreSQL остаются parallel restricted. С моей точки зрения, причина проста: временные таблицы в первую очередь закрывают потребность в реляционных переменных, и по соображениям как производительности, так и сопровождения кода они должны оставаться максимально простыми. Поскольку параллельные воркеры PostgreSQL являются по-сути отдельными бэкендами, они не имеют доступа к локальному состоянию процесса-лидера, где располагаются временные таблицы. Поддержка параллельных операций над временными таблицами значительно усложнила бы этот механизм.

Тем не менее, уже существует как минимум две рабочие реализации поддержки параллельности в операциях над временными таблицами: Postgres Pro и Tantor. Ещё один довод за - обсуждение логической репликации DDL в коммьюнити. Это совершенно точно ставит вопрос развития инструментария временных таблиц ребром - по аналогии с SELECT, до репликации нам нужно точно знать о временных объектах, которые могут быть закопаны на произвольную глубину в UTILITY-командах, не так ли? Это говорит о том, что, возможно, пришло время развить эту тему в PostgreSQL community.

После различных доработок кода, связанного с временными объектами (а это и временные типы, индексы, функции и т.д.) на протяжении лет остаётся только одна фундаментальная проблема: страницы временных буферов являются локальными для ведущего процесса. Если эти страницы не согласованы с состоянием таблицы на диске, параллельные воркеры не могут получить доступ к данным. Перенос временных таблиц в shared buffers выглядит нерациональным, поскольку добавит значитеные накладные расходы. Это может иметь смысл при появлении поддержки SQL-директивы GLOBAL TEMP TABLE, о чём пока речи нет. Однако, кое-что можно сделать прямо сейчас и с сохранение локальности буферов временных таблиц.

Комментарий в коде (80558c1), сделанный Robert Haas в 2015 году, проясняет текущее положение дел:

/*
 * Currently, parallel workers can't access the leader's temporary
 * tables.  We could possibly relax this if we wrote all of its
 * local buffers at the start of the query and made no changes
 * thereafter (maybe we could allow hint bit changes), and if we
 * taught the workers to read them.  Writing a large number of
 * temporary buffers could be expensive, though, and we don't have
 * the rest of the necessary infrastructure right now anyway.  So
 * for now, bail out if we see a temporary table.
 */

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

[!NOTE] Можно легко представить себе случай, когда такая функциональность будет неэффективна: множественные простые запросы на сканирование временных таблиц совмещенные с большой величиной temp_buffers и непрерывным потоком DML (пусть процент затронутых строк и невелик), изменяющим одну или несколько временных таблиц. В таком случае, сканирование каждого из temp buffers, запись на диск перед выполнением запроса, может понизить производительность системы.

На пути к реализации параллельного сканирования временных таблиц аргумент о стоимости сброса буферов выглядит фундаментальным и должен быть рассмотрен в первую очередь. Мы можем решить эту проблему, предоставив оптимизатору правильную модель стоимости. В этом случае он сможет выбрать между параллельным сканированием с накладными расходами на сброс буферов и последовательным сканированием без параллельных воркеров. Таким образом, мы ищем константу наподобие DEFAULT_SEQ_PAGE_COST, только на запись. Давайте обратимся к реальным данным и измерим, сколько на самом деле стоит сбросить временные буферы. Моя цель — определить, является ли этот оверхед реальным барьером для параллельного выполнения или эта проблема слегка переоценена.

Инструментарий бенчмаркинга

В настоящее время PostgreSQL не предоставляет прямого доступа к локальным буферам для целей измерения или манипулирования их состоянием. Поэтому я добавил в pg_proc несколько функций. Ветка temp-buffers-sandbox, основанная на текущем мастере PostgreSQL, содержит все модификации, необходимые для наших тестов.

Реализация состоит из двух ключевых коммитов:

№1: Инфраструктура статистики

Этот коммит вводит две новые внутренние статистики, отслеживающие состояние локальных буферов:

  • allocated_localbufs - отслеживает общее количество выделенных под временные таблицы буферов.

  • dirtied_localbufs - подсчитывает, сколько локальных буферов содержат т.н. DIRTY (не синхронизированные с диском) страницы.

Эти статистики потенциально могут стать основой для будущей модели стоимости, давая оптимизатору запросов возможность определять текущее состояние временных буферов для оценки стоимости их сброса.

№2: UI-функции

Этот коммит добавляет SQL-вызываемые функции, которые позволяют прямую манипуляцию и инспекцию локальных буферов:

  • pg_allocated_local_buffers() - возвращает количество выделенных в данный момент локальных буферов.

  • pg_flush_local_buffers() - явно сбрасывает все dirty pages на диск.

  • pg_read_temp_relation(relname, randomize) - последовательно (или в случайном порядке) читает все блоки временной таблицы в temp_buffers.

  • pg_temp_buffers_dirty(relname) - помечает все страницы данной таблицы как DIRTY.

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

Методология

Тестовые скрипты можно найти здесь.

К счастью, операции с локальными буферами довольно просты: они не захватывают блокировки, не требуют записи в WAL и других дорогостоящих манипуляций. Это устраняет изучение вопросов конкурентного доступа и упрощает логику тестирования. Для построения модели оценки стоимости нужно измерить три вещи: скорость последовательной записи, скорость последовательного чтения и накладные расходы на сканирование буферов, когда не требуется I/O (т.н. dry-run).

Соотношение между скоростью чтения и записи позволит нам получить параметр стоимости последовательной записи страниц на основе значения DEFAULT_SEQ_PAGE_COST, используемого в PostgreSQL core. Оптимизатор может использовать этот параметр для оценки стоимости сброса грязных локальных буферов перед параллельными операциями.

Последовательность операций теста следующая:

  1. Создать временную таблицу и заполнить её данными, которые помещаются в пул локальных буферов (все страницы будут грязными в памяти).

  2. Вызвать pg_flush_local_buffers() для записи всех грязных буферов на диск. Измерить I/O.

  3. Вызвать pg_flush_local_buffers() снова для измерения накладных расходов на сканирование буферов без реального сброса (dry-write-run).

  4. Вытеснить страницы тестовой таблицы путём создания новой вспомогательной таблицы, которая заполняет весь пул буферов, затем удалить её.

  5. Вызвать pg_read_temp_relation() для чтения всех блоков тестовой таблицы с диска в буферы. Измерить I/O.

  6. Вызвать pg_read_temp_relation() снова для измерения накладных расходов на сканирование буферов без реального чтения (dry-read-run).

  7. Вытеснить страницы тестовой таблицы из temp_buffers.

  8. Записать страницы тестовой таблицы в temp_buffers по случайным адресам.

  9. Вызвать pg_temp_buffers_dirty() и маркировать страницы тестовой таблицы в temp_buffers как DIRTY.

  10. Вызвать pg_flush_local_buffers(), который запишет все страницы тестовой таблицы на диск.

Пункты 7 - 10 весьма относительно моделируют `random access`. Здесь я использую это только для того, чтобы попытаться ощутить различие между последовательным и случайным обращением к диску.

Все измерения выполняются с использованием EXPLAIN (ANALYZE, BUFFERS), который записывает время выполнения и статистику буферного I/O (локальное чтение, локальная запись, blocks hit/read). Время планирования незначительно (обычно < 0.02 мс) и исключается из анализа. Хотя можно полностью избежать EXPLAIN и связанных с ним накладных расходов на операции с Instrumentation-структурами, я полагаю, что эти накладные расходы минимальны и одинаковы для операций записи и чтения. Использование EXPLAIN предоставляет удобный способ проверить время выполнения и подтвердить реальное количество затронутых блоков.

Тесты охватывают размеры пула буферов в степенях 2 от 128 до 262,144 блоков (от 1 МБ до 2 ГБ), с 30 итерациями на размер для статистической надёжности. Каждый тест выделяет 110% от количества блоков, необходимых для помещения таблицы. Дополнительные 10% используются для размещения метаданных Free Space Map и Visibility Map. Большее количество буферов вызывает (вероятно) свопинг памяти и приводит к ненадёжным результатам.

Результаты бенчмарка

Сырые данные по результатам тестирования представлены здесь. На моём ноутбуке наиболее стабильная производительность наблюдается в диапазоне 4-512 МБ:

nblocks

bufsize

Write (мс)

Dry-run (write) (мс)

Read (мс)

Dry-run (read) (мс)

512

4 МБ

0.54

0.002

0.58

0.016

1,024

8 МБ

1.07

0.003

1.13

0.028

2,048

16 МБ

3.02

0.004

2.42

0.054

4,096

32 МБ

6.36

0.007

4.81

0.107

8,192

64 МБ

12.34

0.013

9.79

0.210

16,384

128 МБ

24.63

0.026

19.35

0.421

32,768

256 МБ

49.60

0.051

38.72

0.838

65,536

512 МБ

98.93

0.102

77.46

1.681

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

nblocks

bufsize

Write (мс)

Dry-run (write) (мс)

Read (мс)

Dry-run (read) (мс)

131,072

1 ГБ

283.15

0.204

180.06

3.353

262,144

2 ГБ

728.18

0.413

373.46

6.725

Сканирование без I/O (dry-run) минимально: 0.002-0.240 мс.

На основе результатов эксперимента можно заключить, что стоимость записи временной таблицы должна быть близка к стоимости последовательной страницы. Точнее, я бы рекомендовал следующую формулу:

DEFAULT_WRITE_TEMP_PAGE_COST = 1.20 × DEFAULT_SEQ_PAGE_COST

Ограниченное моделирование random access режима демонстрирует 10-24% замедления относительно sequential-режима, поэтому для верности, я бы рекомендовал ввести поправку в коэффициент стоимости записи буферных страниц на диск и использовать константу 1.3 вместо 1.2.

Низкая стоимость записи объясняется спецификой временных таблиц: нет ни локов, ни записи в WAL. Некоторые вопросы всё же пока остаются. Я не уверен, для какого типа накопителей предназначен текущий дефолтный seq_page_cost; мои измерения были проведены на NVMe SSD. Будет ли отношение read/write иным на HDD? Важно ли в целом исследовать разные типы систем хранения или всем достаточно универсального значе��ия? Кроме того, я моделировал случайную запись страниц в весьма ограниченном режиме - будет ли в общем случае у random write другой кост?

Также тесты показывают, что мы можем учесть размер temp_buffers: накладные расходы на сканирование буферов (dry-run) составляют примерно 1% от стоимости записи. Следовательно, полная формула для предварительного сброса временных буферов может выглядеть следующим образом (DEFAULT_SEQ_PAGE_COST = 1):

flush_cost = 1.3 × dirtied_localbufs + 0.01 × allocated_localbufs

Что дальше?

Имея cost model, следующие шаги достаточно очевидны, хотя и не тривиальны:

  1. Добавить в planner флаг, сигнализирующий о наличии временных объектов в поддереве плана - собственно paralle_safe и consider_parallel - хорошие кандидаты для добавления данной ( и не только) логики.

  2. Добавить в executor возможность обращения в воркере к дисковому хранилищу временной таблицы процесса лидера - в основном эта работа состоит в удалении ассертов и сообщений об ошибках ;).

  3. Добавить операцию сброса временных буферов на диск - видимо в ноду Gather/GatherMerge, непосредственно перед запуском параллельных воркеров.

  4. Добавить кост-модель, чтобы planner мог сам решать, разрешать ли параллельное сканирование временных таблиц. Здесь ещё будет вопрос с размером temp buffers в воркерах - нужно ли делить максимальный размер на всех, или разрешать выделять temp_buffers блоков в каждом воркере, или ещё как-то?

Собственно, это всё, что нужно сделать, чтобы заставить Postgres распространить параллелизм на временные таблицы.

Заключение

  • Проведено 360 измерений, по 30 повторений для каждого размера временных буферов. Средние датасеты (16-512 МБ) показывают коэффициент вариации стабильно ниже 6%, что указывает на стабильность результатов. Большой размер temp_buffers (1-2 ГБ) показывают более высокую вариативность (CV >150% для записей), вызванную вероятно swapping'ом или иными эффектами ОС, и к ним стоит относиться аккуратнее.

  • Последовательная запись в локальные буферы примерно на 30% медленнее, чем последовательное чтение.

  • Для целей оптимизации дефолтную стоимость записи можно определить как 1.3*DEFAULT_SEQ_PAGE_COST.

THE END.
Испания, Мадрид, 02 января 2026 года.