Проблемы при переходе с 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 значения в кортежах места не занимают.
