14.11.2023 23:31
ptr128 20 9200 Источник

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

Комментарии (20)


  1. zVadim
    15.11.2023 04:01
    #26160302

    В postgres при работе с временными таблицами нужно избегать долгих скриптов вида:

    select a, b
into temp_table
from...

    Они блокируют схему БД на все время выполнения, не давая создавать-удалять временные таблицы параллельным потокам


    1. ptr128 Автор
      15.11.2023 04:01
      #26160656
      +2

      Можете уточнить конкретным примером и указать версию PostgreSQL, на которой это проявляется?

      Проверил как на древней 12.4, так и на свеженькой 16.1. В одной сессии запускаю длительный запрос вида:

      DROP TABLE IF EXISTS tmp_tmp;
CREATE TEMP TABLE tmp_tmp AS
SELECT id
FROM generate_series(1,100000000) G(id);

      В другой - такой же запрос, но уже не со 100 миллионами, а со 100 тысячами. Никаких блокировок не наблюдаю.


  1. Ulrih
    15.11.2023 04:01
    #26160414

    "Круто", как же 1с-ные базы с этим живут? Там без временных таблиц вообще жизни нет в принципе.


    1. ptr128 Автор
      15.11.2023 04:01
      #26160518
      +1

      Так и живут с распухшими системными таблицами и деградацией производительности при операциях с системным каталогом. Просто соотношение длительности работы с системным каталогом к длительности обработки остальных данных очень мало. И то, что из-за временных таблиц оно с условных 0.001 на MS SQL увеличилось до условных 0.01 на PostgreSQL - не видно на общем фоне.


      1. Ulrih
        15.11.2023 04:01
        #26160568

        Возможно 1с чтото оптимизировало в этом плане, есть же сборки Постгре "адаптированные" для 1С


        1. ptr128 Автор
          15.11.2023 04:01
          #26160598

          Вряд ли, потому что если бы придумали, как этот момент исправить со стороны СУБД, то такие патчи бы предлагались далеко не только для 1С. А я ничего подобного на CommitFest очень давно не видел.


          1. sizovmn
            15.11.2023 04:01
            #26161710

            У postgrespro в сборке есть fasttrun, он сильно уменьшает распухание.


            1. ptr128 Автор
              15.11.2023 04:01
              #26161752

              Вообще-то он предотвращает только один сценарий распухания, предоставляя альтернативу TRUNCATE. На практике же, очистка временных таблиц требуется редко. Множественные TRUNCATE на временные таблицы - это специфика 1С.


              1. CrushBy
                15.11.2023 04:01
                #26162876

                Это не специфика 1С. У нас в lsFusion - точно также. Просто этом часто и есть смысл временных таблиц - записать туда данные, очистить, записать снова и т.д. Схема таблицы то, как правило, одинаковая. Зачем постоянно их создавать и удалять, если можно просто truncat'ить ?


                1. ptr128 Автор
                  15.11.2023 04:01
                  #26163170

                  Тут палка о двух концах. Одна не используемая сейчас временная таблица - это не только ворох записей в системном каталоге, но и несколько файлов в файловой системе. Одно дело, если на одном соединении десяток временных таблиц из которых в один момент используется пять. И совсем другое дело, когда их тысяча, а в один момент используется все равно пять.

                  Я вот, ради интереса, залез в git репозиторий БД MS SQL одного из проектов, которому только предстоит миграция на PostgreSQL, и крошечным скриптом на Perl нашел создание 1051 различных временных таблиц. Точно нужен миллион временных таблиц и 3-5 миллионов файлов в одной директории?


  1. CrushBy
    15.11.2023 04:01
    #26161112
    +1

    Хорошо бы все-таки прежде чем принимать какие-то решения по работе со временными таблицами, замерять результаты при помощи perf. Да, с ними в PostgreSQL не все хорошо (мой опыт работы с ними - в этой статье). У нас в пике бывают сотни тысяч временных таблиц (а используем мы их также активно как и тот же 1С). Так вот работа с ними (я имею ввиду непосредственно выполнение DDL команд и TRUNCATE) - это все равно не более 15% общего CPU. А если вынести их всех в память, то диск они не трогают. Так что не все так плохо с ними, хотя конечно могло быть лучше.

    Ну и чуть что, в Postgres Pro вроде как как что-то улучшили, но я особо не тестировал.


    1. ptr128 Автор
      15.11.2023 04:01
      #26161186

      Так вот работа с ними (я имею ввиду непосредственно выполнение DDL команд и TRUNCATE) - это все равно не более 15% общего CPU.

      15% - это очень много, по сравнению с MS SQL. Только массивами композитных типов и pg_variables это значение можно существенно уменьшить.

      К тому же - это лишь вершина айсберга. Ведь при активном выполнении DDL еще порождается множество dead tuples в системных таблицах, отчего они распухают и теряют в производительности уже при любом обращении к ним. В том числе при компиляции каждого SQL предложения. Поэтому perf, конечно, хорошо и правильно, но всей картины в нем так просто не увидеть.


      1. CrushBy
        15.11.2023 04:01
        #26161254

        15% - это очень много, по сравнению с MS SQL. Только массивами композитных типов и pg_variables это значение можно существенно уменьшить.

        Ну 15% все-таки это не так много. И я посмотрел точные цифры- там все-таки на практике у нас меньше 10%. Композитные типы и pg_variables тоже имеют свои проблемы, в частности планы с ними могут быть хуже за счет отсутствия по ним статистики (на времянки мы всегда делаем ANALYZE после их заполнения) и индексов.

        Опять же не знаю, как в MSSQL сделана работа с временными, но думаю, что там тоже есть свои нюансы (вот, например, тут товарищи извращались). Так что не факт, что эти 10% по сравнению с MS SQL - это чистые потери. Может деградация будет процентов на 5.

        Ведь при активном выполнении DDL еще порождается множество dead tuples в системных таблицах, отчего они распухают и теряют в производительности уже при любом обращении к ним

        Есть такое, но во-первых обращение идет к ним в основном при планировании, что значительно меньше, чем время выполнения. Во-вторых, системные каталоги все время в памяти, и достаточно часто вакуумятся (я бы сказал почти всегда). То есть да, таблицы разросшиеся (у нас до нескольких гигабайт). Да, там много мертвых туплов. Но в целом, затраты на все это на общем фоне незначительны.

        Во-первых, я специально смотрел производительность до и после VACUUM FULL системных таблиц, и разницы особо не увидел. А во-вторых, вот я смотрю на рабочий perf, там планирование - это 12% времени CPU где-то. Из них считывание из системных каталогов - лишь незначительная часть. То есть на общую загрузку CPU это тоже не так сильно влияет.


  1. ptr128 Автор
    15.11.2023 04:01
    #26161702

    Композитные типы и pg_variables тоже имеют свои проблемы, в частности планы с ними могут быть хуже за счет отсутствия по ним статистики (на времянки мы всегда делаем ANALYZE после их заполнения) и индексов.

    А никто не спорит, что "золотого молотка" не существует. Вопрос в том, насколько чаще план запроса возникает не оптимальный и насколько это вообще критично в данном случае. Применяйте перечисленные мной в статье методы к каждому запросу и по отдельности смотрите на результат. Ну и есть немало методов направить планировщик на путь истинный. Я временные таблицы в PostgreSQL использую редко, в 95% случаев один из перечисленных выше методов работает. Лидируют CTE (несколько INSERT/UPDATE/DELETE ... RETURNING в одном запросе) и UNLOGGED TABLE (часто в сочетании с ручным параллелизмом через dblink), так как объемы данных, у меня, обычно, весьма солидные.

    не знаю, как в MSSQL сделана работа с временными

    Так как их метаданные тоже в tempdb, то и методы работы с системными таблицами ничем не отличаются, от методов работы с такими объектами, как глобальные временные таблицы. Нет необходимости делать sync на диск ни для данных, ни для журнала транзакций.

    тоже есть свои нюансы

    Баги есть везде. Не вижу смысла заострять на них внимание.

    обращение идет к ним в основном при планировании, что значительно меньше, чем время выполнения

    Но значительно чаще, чем создание временных таблиц.

    я специально смотрел производительность до и после VACUUM FULL системных таблиц, и разницы особо не увидел

    Если загрузка была типовая - то и не увидите. Лишние обращения с страницам данным, только для того, чтобы убедиться, что индекс ссылается на мертвый кортеж начинают происходить сразу же. AUTOVACUUM это подчищает на периодической основе. Но и сам при этом создает нагрузку на БД.


    1. CrushBy
      15.11.2023 04:01
      #26162902

      Вопрос в том, насколько чаще план запроса возникает не оптимальный и насколько это вообще критично в данном случае.

      Это, кстати, очень критично. Так как в случае кривого плана вы легко можете войти в Nested Loop, который будет иметь сложность много миллиардов, и который будет вам насиловать CPU, память и диск, и никогда при этом не выполнится.

      Если загрузка была типовая - то и не увидите. Лишние обращения с страницам данным, только для того, чтобы убедиться, что индекс ссылается на мертвый кортеж начинают происходить сразу же. AUTOVACUUM это подчищает на периодической основе. Но и сам при этом создает нагрузку на БД.

      Так поэтому и бессмысленно говорить без конкретных цифр. В любом случае, это часть планирования запросов, и в perf конкретно часть обращения к системным таблицам незначительна. Все, что создает значительную нагрузку на ЦП, как правило, видно в perf.


      1. ptr128 Автор
        15.11.2023 04:01
        #26163262

        Так как в случае кривого плана вы легко можете войти в Nested Loop

        Я потому и написал "в данном случае". Например, если только одна табличная функция или материализованный CTE в запросе не фигурирует в WHERE, а все остальные связываются с ней по индексам, то ничего планировщик тут изобретать не будет.

        часть обращения к системным таблицам незначительна

        Сюда надо прибавить труды VACUUM, которые в противном случае не потребовались бы. У меня autovacuum_naptime стоит по умолчанию в одну минуту и никаких проблем от этого не испытываю.


  1. ALexKud
    15.11.2023 04:01
    #26168688

    Использование временных таблиц нужно избегать, если есть такая возможность. В mssql я их не применяю. Использую либо таблицы в памяти либо СТЕ. Иногда вместо СТЕ лучшую производительность показывают вложенные запросы. Есть практически идентичные реализации сложных запросов для mssql и sqlite ( сетевой и локальный варианты). Всё переносится без проблем. С постгресом наверно тоже проблем бы не было. Но мои приложения перенести в постгесс было бы сложно из-за использования синонимов mssql, которые отсутствуют в постгесс.


    1. ptr128 Автор
      15.11.2023 04:01
      #26168840

      В mssql я их не применяю. Использую либо таблицы в памяти либо СТЕ.

      А как решаете проблему невозможности использования статистик в этом случае? Ведь явно указывают, что "you should be cautious about using a table variable if you expect a larger number of rows (greater than 100). Temp tables may be a better solution in this case"

      из-за использования синонимов mssql, которые отсутствуют в постгесс

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


      1. ALexKud
        15.11.2023 04:01
        #26170204

        Насчёт таблиц в памяти-использую набор записей не более 32. В реальности больше не надо, это количество датчиков в тестовом пуле. Использую встроенную процедуру. Идея простая - в таблицу в памяти добавляю данные расчёта из реальной таблицы данных по ключевому полю. Прохожу агрегатом min to min по каждой записи реальной таблицы , расчитываю кучу параметров для каждой записи и вычисляю референсные значения, рассчитанные по формулам через динамический sql, а затем добавляю в таблицу в памяти. Когда пул пройден, в памяти хранится весь набор рассчитанных параметров записей пула. Остаётся его вернуть финальным select из процедуры.

        Насчёт синонимов постгресс. Идея синонимов это не идея представлений. Это для того, чтобы допустим перенести таблицы и процедуры в другую БД, допустим это БД общих ресурсов и рабочая и тестовая БД. Если весь функционал у меня в процедурах, то код в обоих базах одинаковый, нет необходимости использовать указатели на базу и схему в процедурах или добавлять представления и процедуры как в постгресс. Достаточно только создать синонимы. Это понятно. В постгресс пришлось бы в представления использовать указатели на БД и схему. К тому же думаю использование представлений повлияло бы на производительность. Ну а синонимов функций в по стрессе тоже нет и пришлось бы опять как-то выкручиваться. Зачем усложнять себе жизнь?.

        Таблицы


        1. ptr128 Автор
          15.11.2023 04:01
          #26170236

          использую набор записей не более 32

          У Вас совершенно не типовой и редкий сценарий использования БД. В подавляющем большинстве сценариев речь идет от тысячах и миллионах записей. Для примера, только вагонов у меня в справочнике свыше миллиона. А под управлением из них в конкретный момент времени - 100 тыс.

          чтобы допустим перенести таблицы и процедуры в другую БД

          Вообще-то это задача CI/CD, который в зависимости от зоны подменяет значения ряда переменных при сборке из GIT и разворачивании.