Проблемы при переходе с MS SQL на PostgreSQL.Активное использование временных таблиц

Сначала я думал просто перечислить наиболее распространенные проблемы, возникающие при переходе с MS SQL на PostgreSQL. Но решил, что только перечисление, без углубления в способы решения этих проблем, будет мало информативным. Поэтому, пока ограничился наиболее частой проблемой, приводящей к деградации производительности при переходе с MS SQL на PostgreSQL. Если статья окажется нужной, то продолжу рассматривать остальные проблемы.

Особенности временных таблиц в PostgreSQL

Выношу на первое место, как одну из ведущих причин деградации производительности при переходе с MS SQL на PostgreSQL. В MS SQL для временных таблиц имеется выделенная база данных (tempdb), работа с которой оптимизирована именно для хранения временных объектов и довольно сильно отличается от работы с любой другой БД. В PostgreSQL любые запросы могут оперировать только с объектами одной БД. Механизмы dblink() или FDW - это далеко не одновременный доступ к объектам нескольких БД на одном сервере в одном запросе, как в MS SQL. Поэтому временные таблицы не только располагаются в текущей БД, но и все их метаданные размещаются в постоянных системных таблицах текущей БД. И если доступ к данным самих временных таблиц так же оптимизирован и упрощен, как в MS SQL, то операции с метаданными временных и постоянных таблиц, по большому счету, ничем не отличаются.

Поэтому в PostgreSQL активное создание, модификация и удаление временных таблиц (включая их индексы), как в MS SQL - не лучшая идея. Создавать в PostgreSQL временные таблицы стоит только в тех случаях, когда иного выхода нет.

На самом деле не все так плохо и ниже рассмотрены несколько вариантов решения проблемы, позволяющих минимизировать использование временных таблиц.

Массивы композитных типов

В отличии от табличных переменных в MS SQL, переменные PostgreSQL типа массива композитных типов не поддерживают индексации. Заменить временные таблицы они могут только если в них заведомо небольшой объем данных и при разворачивании unnest() устраивает полное сканирование. Возможно, с вычислением хешей для присоединения к другим таблицам.

Common table expression (CTE)

СTE (WITH ...) в PostgreSQL так же не поддерживает индексации, но, в отличии от переменных массивов композитного типа, не накладывают ограничения на объем данных.

В отличии от MS SQL, в PostgreSQL CTE может содержать не только SELECT, но так же INSERT/UPDATE/DELETE с RETURNING. А вот это уже несколько развязывает руки.

Например, в MS SQL заполняются таблицы вида заголовки-детали вида

CREATE TABLE Hdr (
  id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  doc_num INT NOT NULL,
  some_data VARCHAR(255) NULL);
CREATE TABLE Det (
  id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  hdr_id INT NOT NULL,
  some_data VARCHAR(255) NULL,
  CONSTRAINT FK_Det__hdr_id_Hdr FOREIGN KEY (hdr_id) REFERENCES Hdr(id));

При заполнении этих таблиц в MS SQL нам необходимо сначала во временной таблице или табличной переменной сохранить пары (id, doc_num) полученные из INSERT ... OUTPUT. А уже зная, какой id был присвоен каждому doc_num, можно будет заполнить таблицу Det с корректным полем hdr_id.

В PostgreSQL аналогичное действие можно произвести без временных объектов, благодаря CTE:

WITH HdrCTE AS (
  INSERT INTO Hdr AS H (doc_num, some_data)
  SELECT ...
  FROM ...
  RETURNING H.id, H.doc_num)
INSERT INTO Det (hdr_id, some_data)
SELECT C.id, ...
FROM HdrCTE C
JOIN ...

pg_variables

Описывать это свободно доступное расширение от PostgresPro я не буду, ограничившись ссылкой на статью о нем.

Важно то, что это расширение позволяет успешно заменить временную таблицу с первичным ключом без иных индексов. Более того, делает это заметно эффективней, чем механизм временных таблиц. Только не забывайте указывать флаг is_transactional => TRUE для автоматической очистки созданных переменных по окончании транзакции.

Нежурналируемые таблицы

Как было указано выше, основная проблема временных таблиц в PostgreSQL заключается не в работе с данными в них, а в создании и удалении этих таблиц. Использование постоянных таблиц в качестве временных не эффективно. Но PostgreSQL предоставляет нам альтернативу в виде нежурналируемых (UNLOGGED) таблиц.

Вместо того, чтобы создавать временную таблицу, можно заранее создать нежурналируемую таблицу той же структуры, но с полем

process_id integer NOT NULL DEFAULT pg_backend_pid()

Все необходимые индексы нужно будет предварять полем process_id, а выборки дополнять условием process_id=pg_backend_pid().

Наиболее существенным недостатком такого решения является необходимость вставлять данные уже в индексированную таблицу. Кроме того, перед началом работы с ней необходимо обязательно удалить из нее все записи по тому же условию process_id=pg_backend_pid(). Желательно так зачищать данные за собой по этому же условию. Но это все равно не позволяет обойтись без периодической очистки таких таблиц подобным запросом:

DELETE FROM our_unlogged_table
WHERE process_id <> ALL (SELECT pid FROM pg_stat_activity)

Чем больше индексов нам потребуется для такой таблицы, тем менее эффективна она будет по сравнению с временной таблицей, которую можно проиндексировать уже после вставки данных. Динамически создавать и удалять частичные индексы (с WHERE process_id=...) по такой таблице не рекомендую, так как это такая же нагрузка на системные таблицы, как и при работе с временными таблицами.

И все же временные таблицы

Если вышеперечисленные варианты не подходят, то стоит учесть, что создание одной универсальной временной таблицы с ворохом полей, возможно композитного типа, вместо десятка специализированных временных таблиц, может существенно снизить нагрузку на системные таблицы. Все же NULL значения в кортежах места не занимают.