Оптимизация хранимых процедур на PostgreSQL, мигрированных с MsSQL. Подходы к реализации, личный опыт

Привет всем!

Я — Ипатов Александр, backend‑разработчик в ГК Юзтех. Сегодня хочу поделиться своим опытом в интересном проекте, связанном с миграцией БД MsSQL на PostgreSQL в разрезе оптимизации хранимых процедур и функций (далее — хранимых процедур, так как процесс оптимизации не сильно завязан на том, что именно имеем на выходе).

Актуальность проектов, связанных с миграциями серверов и баз данных с зарубежных платных продуктов (Microsoft, Oracle) на аналогичные отечественные или зарубежные open‑source решения (в разрезе статьи будем рассматривать Postgres) в 2024 году очень велика. Те решения, которые были реализованы и поддерживались на протяжении 5–10 лет, потребовалось практически в формате «пожара» переносить на аналогичные. А бизнес, который привык к уже полностью сформированным и отработанным рабочим процессам, не готов к потере эффективности и, как следствие, потере клиентов сервисов, заказов и бизнес‑метрик.

В одном из таких проектов мне удалось поучаствовать. Из начальных условий: проект по переносу БД из MsSQL начался примерно 3 года назад.

На самом деле, проект был более обширный — перенос монолитного сервиса на микросервисы, в том числе, как один из элементов — перенос БД.

Хочется отметить, что перенос схем, таблиц, индексов и других элементов базы данных прошел относительно спокойно. Чего не скажешь о переносе хранимых процедур. Язык T‑SQL, на котором пишутся хранимые процедуры в MsSQL, конечно же имеет отличия от PL/pgSQL, который используется в PostgreSQL. В связи с чем, непосредственно миграция хранимых процедур заняла много времени: точное число хранимых процедур я не назову, но порядок — около 800 штук (среди которых 500 стали работать хуже после миграции, их то и предстояло оптимизировать).

Из интересного: самая большая хранимая процедура занимала 12.000 строк кода, поэтому миграция производилась автоматизированными средствами. Как итог — да, хранимые процедуры перенесли с MsSQL, это было уже достижение. Но всё только начиналось…

Прошла отсечка и БД Postgres стала готова к нагрузочному тестированию, поскольку были перенесены все элементы с БД MsSQL.

Ожидание: показатели работы всей системы в целом будут на 20% ниже тех, что были на продакшн версии (MsSQL).

Реальность: показатели времени выполнения хранимых процедур хуже на сотни процентов.

Действие: дополняем проект командой разработчиков, которые будут заниматься оптимизацией хранимых процедур на PostgreSQL.

Подход: от массового к точечному (от общей настройки системы администраторами БД до работы непосредственно с каждой медленной хранимой процедурой).

Здесь и появилась команда разработчиков, состоящая из 4–5 человек, среди которых мне удалось принять участие в этом довольно интересном и нетривиальном проекте.

Принцип работы команды разработки был следующий:

1. Имеется тестовый стенд для разработчиков, который максимально идентичен будущему продакшн серверу БД PostgreSQL по характеристикам самой базы данных, но по «железу» сильно от него отстает. Связано, в первую очередь, с дороговизной реализации идентичного по «железу» тестового стенда.

Здесь хочу акцентировать внимание на важном моменте. Несмотря на то, что язык SQL считается декларативным языком, то есть таким, при котором с пользователя снимается нагрузка по определению и описанию порядка выполнения непосредственно действий с множествами (не видов соединения таблиц в базе данных, а способов соединения непосредственно наборов данных из этих таблиц, с учетом имеющихся ограничений, указанных в запросе) оптимизатор в БД Postgres сам определяет лучший «путь» выполнения запроса — строит план выполнения. Этот план выполнения строится исходя из нескольких моментов:

1. Актуальность «статистики» в системе на текущий момент, то есть то, насколько статистические данные о таблицах (как временных, так и обычных) являются достоверными. За данный пункт отвечает команда analyze (либо vacuum analyze — анализ вместе с предварительной очисткой пространства, занятого удаленными данными в таблицах).

2. Идентичность параметров физического сервера: мощность и количество процессоров, объем оперативной памяти и других.

Среди этого, наш тестовый стенд удовлетворял лишь пункту 1.1., тогда как по пункту 1.2. сильно отставал от будущего продакшна. В связи с этим, возникали ситуации, когда оптимизированная и ускоренная в разы хранимая процедура на тестовом стенде не давала актуальные результаты на будущем продакшене. Выход из данной ситуации — тестирование оптимизированной хранимой процедуры на будущем продакшн сервере БД, но с обязательным выполнением условия отсутствия DML‑команд внутри (insert / update / delete). Если же хранимая процедура не могла выполнить данное требование, тогда руководитель команды разработки принимал решение — опубликовать изменение без предварительного тестирования на продакшн сервере, и на боевом запуске пользователями за период времени 2–3 дня анализировать результаты работы.

2. На тестовом стенде производится бэкап системы 1 раз в 2 недели (для того, чтобы очистить все тестовые хранимые процедуры, индексы и вью, не вошедшие в релиз, которые в результате лишь загрязняли систему и создавали «лишний шум»).

3. Оптимизация хранимых процедур заключалась в приведении характеристик по времени выполнения в БД Postgres к аналогичным результатам на текущем продакшн сервере БД MsSQL: среднее время выполнения, максимальное время выполнения, медианное время выполнения, время выполнения 80% запусков, также имело значение суммарное количество запусков за интервал времени. На основе перечисленных временных характеристик была сформирована обобщенная метрика, которая отвечала на вопрос: хранимая процедура оптимизирована и соответствует заявленным требованиям или нет. Причем, если в хранимой процедуре использовались «курсоры» на выходе, обязательно во времени выполнения учитывалось ещё и время извлечения данных из него с помощью FETCH.

4. После оптимизации хранимой процедуры разработчик обязательно проводит 5 — 10 тестовых запусков хранимой процедуры с различными параметрами для того, чтобы удостовериться в идентичности выводимых результатов оптимизированной процедуры с текущей версией. Причём, если хранимая процедура на выходе давала «курсор» — то обязательно нужно было сравнить результаты после его извлечения с помощью команды FETCH.

5. Релизы производятся в среднем 1 раз в 3–4 дня, по результатам релизов запускается нагрузочное тестирование и обновляется единая таблица со сводными данными по каждому релизу. Тогда можно корректно интерпретировать результаты оптимизации.

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

Не менее важным является то, какие именно методы позволили нам оптимизировать 500 хранимых процедур из общего числа 800 мигрированных с БД MsSQL на PostgreSQL:

1. Уход от большего числа временных таблиц процедурах в сторону CTE (обобщенное табличное выражение) / массивов.

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

Эта методология была пробной и использовалась в рамках хранимых процедур, которые не содержали в себе DML‑операторы (insert / update / delete) данных постоянных таблиц и использовались в качестве неких отчетов для пользователей по их запросам. Её причиной было то, что процессор был сильно нагружен за счёт большого количества операций с временными таблицами, хотелось снизить нагрузку на основную read‑write ноду кластера БД — и перенести часть отчетных хранимых процедур на физическую read‑only ноду. Однако, в последствии её использования, сильной разгрузки не произошло, но для разработчиков и аналитиков возникли большие проблемы по доработке и анализу отчетов. В связи с чем, данная методология была отвергнута и переродилась в следующий метод.

2. Уход от временных таблиц в сторону расширения pg_variables.

В данной статье я подробно описал основную суть и особенность применения этого расширения. Есть и плюсы, и минусы. Явный минус для тех, кто работает на Windows серверах — это расширение поддерживается лишь на Linux. Основной плюс, который и стал причиной использования этого расширения — оно может использоваться на read‑only ноде кластера БД PostgreSQL. В результате, данное расширение pg_variables успешно разгрузило read‑write ноду кластера БД от одной хранимой процедуры, которая вызывала 40 взаимосвязанных хранимых процедур внутри себя, в которой особенно часто и много использовались временные таблицы.

3. Переписывание мелких временных таблиц (с 1 столбцом) — на 1 массив.

Зачастую, в начале хранимых процедур использовались вспомогательные запросы, которые обычно содержали в себе некоторые удовлетворяющие определенным условиям идентификаторы (заказов, клиентов и прочее). Эти выборки писались в мелкие временные таблицы. На объеме в 500 хранимых процедур, с учетом больших запусков этих хранимых процедур (доходило до 5 тысяч запусков в сутки в некоторых хранимых процедурах) это значительная нагрузка на процессор, который кроме формирования выборки ещё и делал вставку этих данных во временные таблицы, не говоря уже о предварительном удалении и создании временной таблицы. Массив же формировался быстрее, и очень часто использование такого метода положительно сказывалось на общее время выполнения хранимых процедур на большом объеме запусков.

4. Переписывание временных таблиц, которые использовались внутри лишь одного запроса — на CTE у данного конкретного запроса на выборку.

Иногда от временных таблиц было сложно уйти, поскольку одна временная таблица использовалась в более чем одной последующей выборке данных. Но в случаях, когда временная таблица была создана и использовалась лишь в одном запросе — в 90% случаев использование CTE положительно сказывалось на время выполнения запроса.

5. Уход от MATERIALIZED VIEW в сторону временных таблиц и СТЕ.

Материализованное представление (MATERIALIZED VIEW) хранится в памяти и должно обновляться, если мы хотим получать корректные данные. Однако, зачем обновлять данные, если далее в выборках используются дополнительные ограничения? Во время оптимизации мы не обошли и этот момент, в результате мы ушли практически от всех MATERIALIZED VIEW и увидели хороший прирост по скорости выполнения.

6. Использование индексов у объемных временных таблиц.

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

7. Классическое переписывание SQL‑запросов, написанных не оптимально.

Разработчику, который пишет SQL‑запросы, нужно иметь знания о том, как БД работает с этими запросами. Например, использование после обширного количества джоинов в запросе блока WHERE, в котором данные ограничиваются очень узким срезом (например, всего несколько строк на выходе). Это не касается, например, выборок лишь с LEFT JOIN, однако в конструкциях с JOIN (INNER JOIN) это первоочередная часть оптимизации запросов. Для этого, конечно, нужно уметь читать планы запросов, знать способы доступа к данным (последовательное сканирование, индексный доступ, сканирование по битовой карте) и способы соединений (вложенным циклом, хэшированием и слиянием).

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

Проект по оптимизации хранимых процедур на PostgreSQL успешно завершился и позволил достичь поставленных перед новой БД целей — система стала работать даже лучше, чем работала до перехода (что подтверждают и пользователи).

Заключение

На мой взгляд, сейчас вопросы, связанные с миграцией систем с подсанкционных сервисов на open‑sourse решения, стоят очень остро. Говоря об этом в срезе миграций баз данных, всегда будут появляться вопросы, описанные в статье — вопросы ускорения процессов, которые годами отлаживались и настраивались под одну систему, с одним движком, на новую систему.

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