Процедура завершения крупномасштабной миграции

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

1. Ограничения пакетной обработки уровня существующего приложения (Java/Spring)

На начальном этапе проектирования мы в первую очередь рассмотрели способ реализации пакетной обработки в привычной среде Java и Spring Boot. Это связано с тем, что использование Spring Batch и аналогичных инструментов для обработки данных по частям является распространенной практикой. Однако по мере анализа архитектуры стали очевидны следующие четкие ограничения.

1-1. Серьезная сетевая нагрузка (Network Overhead)

В процессе запроса и сравнения десятков тысяч, миллионов данных, поступивших из внешней системы, произошло частое сетевое взаимодействие (Round-Trip) между WAS (Web Application Server) и DB. Ожидались не только затраты сетевой пропускной способности, но и задержка общего времени выполнения задач из-за латентности (Latency).

1-2. Нагрузка на память WAS и риск OOM (Out Of Memory)

При загрузке большого количества сущностей (Entity) или DTO в память Java (Heap) сразу может резко возрасти нагрузка на сборщик мусора (GC), и мгновенно может возникнуть java.lang.OutOfMemoryError, что создает риск падения всего приложения.

1-3. Ограничения вычислений приложений

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

[Выбранное решение: Хранимая процедура PostgreSQL]
Чтобы преодолеть эти ограничения, мы пришли к выводу, что "давайте обрабатывать логику непосредственно в пространстве, где существуют данные (БД)". Поддержка этого начала с версии PostgreSQL 11 Хранимая процедурав отличие от существующей функцииВнутри возможен явный контроль транзакций (COMMIT, ROLLBACK)Это решение было выбрано как оптимальное для минимизации давления на ресурсы базы данных (Undo, Lock и т. д.) и обеспечения целостности данных за счет разделения выполнения коммитов во время выполнения больших пакетных операций.

2. Системная архитектура и поток данных

Общая структура обработки данных спроектирована как двухступенчатая.

2-1. Этап Staging

Данные в сыром формате из внешних систем загружаются в промежуточную временную таблицу (staging_table) в режиме bulk.

2-2. Этап Migration

Когда Java-приложение вызывает процедуру PostgreSQL с параметрами, происходит сравнение и анализ данных в staging_table и target_table, и выполняется обработка и отражение в окончательной мастер-таблице (Upsert/Update/Insert).

3. Процесс применения

Реализация состоит из обработки данных и отражения их в Процедура PostgreSQL и, соответственно, вызова ее и получения количества обработанных результатов через Java-приложение. Для упрощения понимания сложные бизнес-колонки заменены упрощенными примерами данных.

3-1. Реализация процедуры PostgreSQL

Логика обновления целевой таблицы (target_table) основана на данных временной таблицы (staging_table). С помощью предложения WITH (CTE) и выражения IS DISTINCT FROM отбираются только измененные данные для выполнения массового обновления.

3-2. Реализация кода вызова Java (Spring)

Для вызова процедуры использовался CallableStatement на основе JdbcTemplate. Параметры IN передаются, а результаты, вычисленные внутри процедуры (количество обработок), безопасно возвращаются в виде параметров OUT и преобразуются в объект Map.

4. Опыт решения проблем

4-1. Предотвращение ненужных обновлений: использование IS DISTINCT FROM

• Проблема: При вставке большого объема данных каждый раз безусловно используемого UPDATE-запроса происходит запись даже для неподвержденных изменений, что приводит к накоплению ненужного WAL (Write-Ahead Log) и ухудшению производительности индекса.

• Решение: Применен синтаксис ROW() IS DISTINCT FROM ROW() в PostgreSQL. Этот подход предотвращает исключительные ситуации при сравнении значений NULL (например, NULL = NULL это False) и позволяет выполнять обновления только тогда, когда хотя бы одно из полей отличается, улучшая производительность.

4-2. Явное управление COMMIT в процедуре

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

• Решение: Это главная причина, по которой был выбран Procedure вместо функции PostgreSQL. В конце каждого бизнес-этапа в процедуре явно указывается команда COMMIT;, что позволяет правильно освобождать системные ресурсы и обеспечивать целостность данных на каждом этапе. На стороне Java также было установлено conn.setAutoCommit(true), чтобы сохранить контроль над коммитами внутри процедуры.

5. Результаты и вывод

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

5-1. Улучшение скорости обработки

Общее время выполнения миграции сократилось по сравнению с предыдущей реализацией на Java с использованием `Loop + Bulk Insert`.

5-2. Оптимизация ресурсов

С точки зрения затрат на инфраструктуру, доля использования CPU/Memory WAS стабилизировалась, а благодаря эффективному сканированию индексов и минимизации управления кортежами, диск I/O показал стабильную нисходящую кривую.

PYS