🗄️🚀 Как не облажаться при переезде на PostgreSQL: инструкция по спасению базы данных

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

Привет всем!

Я — Ипатов Александр, backend‑разработчик в ГК Юзтех. Сегодня хочу поделиться мыслями о том, как подойти к вопросам реализации оптимизации хранимых процедур на PostgreSQL (PL/pgSQL). Такие вопросы часто возникают при миграциях баз данных с Oracle (PL/SQL) и Microsoft (T-SQL).

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

Хочется отметить, что перенос схем, таблиц, индексов и других элементов баз данных зачастую проходит относительно спокойно. Чего не скажешь о переносе хранимых процедур. Языки T‑SQL и PL/SQL, на которых пишутся хранимые процедуры в Microsoft и Oracle, конечно же, имеют отличия от PL/pgSQL, который используется в PostgreSQL. В связи с чем непосредственно миграция хранимых процедур занимает немало времени, так как автоматизированные средства переноса работают некачественно. Поэтому без ручного труда в переносе не обойтись.

Из своего опыта хочу отметить, что однажды работал с хранимой процедурой размером в 12 000 строк кода. Так что даже относительно несложные правки в таких «гигантах» могут быть очень долгими по реализации.

Как подойти к работе, если в результате миграции мы имеем несколько сотен хранимых процедур и функций на языке PL/pgSQL, которые стали работать в разы медленнее, чем до миграции?

• Нужно дополнить проект командой разработчиков, которые будут заниматься оптимизацией хранимых процедур на PostgreSQL Количество участников зависит от объема работ и сроков сдачи проекта.
• Подход от массовой к точечной настройке: от общей настройки системы администраторами БД до работы непосредственно с каждой медленной хранимой процедурой.

Какие принципы работы команды могут помочь для решении такой задачи:

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

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

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

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

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

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

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

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

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

Всем успехов в выполнении проектов!