Распараллеливаем процесс вставки данных в PostgreSQL при помощи Spring с сохранением атомарности всей операции

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

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

В этой статье расскажу о том, как сделать вставку данных одновременно многопоточной и атомарной.

Меня зовут Дмитрий Фатов, я разработчик в Газпромбанке — создаю с командой платформу G2, на которой мы строим свои Enterprise-решения. 

В прошлой своей статье я показывал, как добиться высокой скорости массовой вставки данных в PostgreSQL при использовании Spring. В конце разбирал, как распараллелить сам проц��сс вставки — и именно распараллеливание дало самый большой прирост производительности.

Но у этого подхода есть недостаток: многопоточная вставка перестает быть атомарной. В этой статье разберемся, как сделать вставку и многопоточной, и атомарной одновременно. Мы посмотрим: 

• как организовать многопоточную вставку данных в PostgreSQL при помощи Spring;

• какими способами можно сделать ее атомарной;

• поговорим про update. Почему update — это тяжелая операция в Hibernate и PostgreSQL и как можно ускориться;

• с какими проблемами можно столкнуться на пути реализации;

• и почему было бы здорово, если бы PostgreSQL взял на себя сохранение атомарности многопоточных вставок. 

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

Тестировать будем 4 млн записей, вставку по 4 млн записей в три итерации прогрева, пять итераций замеров. 

На чем производились замеры. Оборудование: Intel(R) Core(TM) i7-10750H CPU @ 2,60 ГГц, 12 ядер, 32 Гб ОЗУ; окружение: java 17, PostgreSQL 14.5. Именно эта версия PostgreSQL использовалась у нас в продакшн на момент проведения замеров. Отличия от PostgreSQL 17.4 отмечу далее по тексту.

Ускоряем вставку данных на Spring

Сначала пройдемся по настройкам, которые must have для вставки данных.

1. Включаем батчинг: jdbc.batch_size

Данная настройка существует для того, чтобы собирать наши insert в памяти приложения и отправлять их в БД ��ачками, а не бегать туда с каждым запросом. В моем случае я мерил батчами по 5 тыс. записей, хотя пробовал от 100 до 100 тыс. На моих БД и оборудовании батч в 5 тыс. записей оказался самым эффективным, поэтому с ним и будем производить замеры. Он дает около 23% буста по производительности.

2. Настройка sequence`ов

Если вы генерируете айдишники на стороне базы данных, их обязательно нужно запрашивать батчами. В моем примере я запрашиваю их по 50 штук. Для этого добавляем к sequence INCREMENT BY 50 и указываем в @SequenceGenerator значение allocationSize = 50. Приложение сходит в БД, получит значение, дальше Spring будет знать, что от этого значения у него есть 50 штук, которые можно использовать для всех вставок в рамках всего приложения. Эта настройка дает около 30% прироста производительности.

3. Настройка reWriteBatchedInserts

Эта настройка позволяет превращать обычные инсерты в мультистрочные. Вся магия происходит на уровне драйвера, т. е. сам Hibernate продолжает отправлять данные построчно, при этом драйвер отлавливает эти инсерты и переделывает в мультистрочные. Данная настройка дает нам около 23% прироста производительности.

Итак, Spring настроили, получили первый результат — 4 минуты 37 секунд. Столько дает атомарная однопоточная вставка данных. 

Делаем вставку данных многопоточной

Spring поможет нам сделать вставку данных асинхронной. Для этого включаем возможность асинхронного вызова методов в конфигурации при помощи аннотации @EnableAsync. Для асинхронных вставок я создал отдельный ThreadPoolExecutor, чтобы они не фигурировали ни в каких других ThreadPool'ах и не мешались там. 

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

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

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

Получили 1 минуту 56 секунд, ускорились больше чем в два раза — очень неплохой результат. 

Делаем многопоточную вставку данных атомарной

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

Первая идея для атомарности: флаг ready_to_read

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

Spring нам поможет отобрать валидные данные. Для этого добавим аннотацию @where над нашей Entity с условием ready_to_read = true. При получении данных че��ез JPA-репозиторий к самому запросу это условие будет добавляться в конце, где ready_to_read = true. 

Теперь посмотрим, как будет выглядеть процесс многопоточной и в тоже время атомарной вставки данных. Мы создаем рандомные Entity с флагом readyToRead = false, затем асинхронно, в разных транзакциях, отправляем их в БД. Дожидаемся, пока все данные сохранятся, переводим флаг readyToRead в true и через метод saveAll JPA-репозитория обновляем эти данные в другой транзакции, т. е. делаем их доступными для чтения. 

Обновление — это быстрая операция?

На этом этапе очень важно подумать, сколько времени у нас занимает обновление. Начнем с простого изолированного примера. Возьмем подготовленный список айдишников из БД. Генерируем рандомные реквизиты для обновления по этим объектам и пробуем сохранить. 

Результат — 10 минут 21 секунда… Обновление весит в два раза больше и по скорости, и по памяти относительно обычных инсертов. 

Давайте разберемся, почему обновление получается таким дорогим. 

Почему получили просадку по скорости

Для начала включим логи и посмотрим, что происходит под капотом.

По логам видно, что под каждый update создается еще один дополнительный select.

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

Hibernate работает с Entity в состоянии Managed. Если Entity с заполненным ID пришла к нам откуда-то извне, то есть мы не получали ее из БД в этой же транзакции, то она будет в состоянии Detached. 

Для того чтобы вернуть Entity в состояние Managed, нужно выполнить метод merge. Теперь давайте заглянем под капот метода save и посмотрим, как это реализовано. Тут есть два метода, которые непосредственно касаются обновления данных.

Первый метод, isNew, достаточно простой: в нашем случае он проверит, что айдишник заполнен, и вызовет метод merge. Внутри merge, собственно, происходит вся магия — идет дополнительный запрос в БД. 

Помимо всего прочего, Hibernate при помощи этого запроса определяет, какой оператор вызвать. Если данные есть, он делает update, если нет — делает insert.

Почему update потребляет в два раза больше памяти

Давайте снимем Heap Dump и разберемся в причинах.

Мы видим, что наших платежных документов 4 млн — то есть, как мы и планировали. Но данных к этим платежным документам — 8 млн. Мы помним, что сходили в БД и забрали оттуда старые данные, но в наших Entity мы заменили их новыми. То есть по-хорошему их ничего не должно держать, и сборщик мусора их должен был подчистить. Однако он этого не делает, значит, что-то все-таки держит их.

И держит их хибернетовский кэш, он сохраняет загруженные данные в LoadState. Помимо всего прочего, Hibernate использует его также для того, чтобы определить, поменялись обновленные данные или нет. Если не поменялись — то update не будет. 

Как избавиться от select при update

Чтобы решить эту проблему, для обновления данных можно использовать StatelessSession. Внутри есть метод update, при использовании которого Hibernate не будет делать дополнительных select, он просто выполнит то, о чем мы его просим. 

Давайте посмотрим, сколько будет весить обновление данных через StatelessSession. Получили выигрыш по времени и по памяти почти в два раза. Время сократилось до 5 минут 59 секунд. 

Однако наши Update по-прежнему обходятся нам дорого… Нужно подумать, что еще можно сделать для оптимизации. 

Изменение только одного поля через JdbcTemplate

По сути, нам нужно всего лишь установить один флаг. У нас есть легковесный JdbcTemplate, и в целом мы можем написать простой запрос для изменения только одного поля. JdbcTemplate также умеет отправлять данные батчами, для этого достаточно передать параметр batchSize. 

Посмотрим, как это сказалось на результате. По памяти все хорошо, но вот время сократилось несущественно… 5 минут 51 секунда. 

Пока это не то, что нам нужно, так что думаем дальше.

Изменение только одного поля через Any([])

Батчи — это, конечно, хорошо, но давайте попробуем выполнить обновление одним запросом. Для этого передадим список ID массивом через Any([]). 

В результате мы сократили время обновления до 4 минут 5 секунд — на 30%. И впервые обогнали наш пресловутый insert. 

Тем не менее такой результат нас на устраивает, давайте подумаем, что еще можно сделать со стороны бизнес-логики, чтобы ускорить процесс. 

Добавление идентификатора транзакции

В целом мы можем не использовать флаг ready_to_read, а взять некий айдишник транзакции, который добавим к нашей таблице. Это будет индексированное поле в самом платежном документе. Update в этом случае выглядит достаточно просто, одна маленькая строчка. В ней будем делать set transaction_id = null там, где он равен какому-то параметру.

Отбирать данные теперь будем те, где transaction_id is null. 

И сейчас посмотрим, что из этого получилось. Результат составил 2 минуты 50 секунд, время обновления сократили еще на 30%.

Время получилось еще лучше, но его по-прежнему недостаточно, чтобы использовать этот подход для сохранения атомарности. 

Настало время перейти к PostgreSQL. Посмотрим, насколько обновление в нем — быстрая операция.

Update в PostgreSQL: это быстро?

Сначала небольшое отступление: в свое время хорошо разобраться, как работает PostgreSQL под капотом, мне помогла книга Егора Рогова «PostgreSQL изнутри». Скачать ее можно свободно на сайте Postgres Professional. Спасибо коллегам за такую прекрасную книгу.

Вернемся к нашей теме — разберем, как работает PostgreSQL под капотом. 

• PostgreSQL использует MVCC (Multiversion Concurrency control) — многоверсионное управление конкурентным доступом. Для нас это означает, что каждая строка с данными имеет свою версию. 

• Эта версия хранится в заголовке строки, которая весит 24 байта.

• Посмотреть информацию со страниц индексов и данных можно с помощью таких запросов, как на картинке ниже. Для этого потребуется установить расширение pageinspect.

Посмотрим, как работает MVCC в PostgreSQL, на примере обновления индексного поля.

Обновление индексного поля

Вставляем данные в БД и два раза обновляем transaction_id. Это не совсем наша история, но она поможет разобраться, как это все работает. 

На странице с данными я вывел номер строки, а также идентификатор транзакции, создавшей строку, и транзакции, которая изменила строку, то есть сделала ее невалидной. Важно понимать, что идентификатор транзакции в PostgreSQL — это некий счетчик, который постоянно увеличивается. 

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

Однако на этот случай у PostgreSQL есть оптимизация HOT-обновлений. Давайте разберемся, что это такое.

Как работает оптимизация HOT-обновлений

Оптимизация HOT (Heap-Only Tuple) позволяет не создавать дополнительные ссылки индексов на строки данных, когда:

• на странице есть место для новой строки;

• на обновляемых столбцах нет индексов;

• обновляемые столбцы есть в индексах, но их значения не меняются. 

Посмотрим, как работает эта оптимизация, на примере установки флага ready_to_read. Это у нас не индексное поле. 

На картинке выше я вывел ссылку на следующую строку в цепочке. 

И что мы видим? На странице с индексом сохранилась одна версия, которая указывает на первую версию строки на странице с данными. При поиске строки по индексу PostgreSQL понимает, что текущая строка была обновлена через HOT, далее PostgreSQL по ссылке переходит к следующей версии, пока не упрется в актуальную. Так как PostgreSQL данные в памяти читает страницами, то вся эта операция перехода по ссылкам в цепочке должна быть достаточно быстрой.

А теперь самое время подумать, были ли наши обновления HOT. 

Были ли наши обновления HOT?

В самом начале мы обновляли флаг ready_to_read, который не был индексным полем. Нам осталось проверить, было ли у нас место на странице. Поможет в этом вот такой запрос:.

Заголовки есть не только у строк — они также есть и у страниц. В запросе нам интересны два показателя — lower и upper. Разница между ними — это и есть свободное место на этой странице. На картинке выше видно, что на странице осталось места на 144 байта, а сами строки весят 225 байт. То есть места им явно не хватает. 

Как оставить место под обновления? 

Fillfactor для HOT-обновлений

У PostgreSQL есть такой замечательный параметр — fillfactor. Это процент наполнения страниц при вставке данных. То есть при достижении указанного порога данные будут вставляться на новую страницу. Незаполненное пространство остается для HOT-обновлений. Ниже на картинке указан способ, как его применить. Если вы хотите, чтобы данный параметр применился ко всей таблице, то нужно использовать vacuum.

Давайте вернем флаг ready_to_read, при этом оставим transaction_id, потому что обновление по общему полю работает быстрее. Нюансы я опишу чуть ниже. 

Как fillfactor влияет на время обновления и размер страницы

Ниже привожу табличку, которая показывает влияние fillfactor`а на размер таблицы и время обновления. 

На что хочется обратить внимание. При fillfactorе, равном 100%, обновление длилось 2 минуты 50 секунд, а при fillfactorе, равном 50%, — 16 секунд. Получился очень хороший буст по производительности. Мы в несколько раз увеличили скорость обновления, но заплатили за это размером таблицы — он вырос с 31 до 58 Гб. 

Почему именно при 50% мы получили такой буст? В нашем случае мы точно уверены, что каждая строка будет как минимум один раз обновлена. Именно поэтому, когда мы оставили ровно половину места на странице, абсолютно все обновления у нас стали HOT.

Еще есть один интересный момент, это различие в скорости обновлений по общему полю transaction_id и по списку ИД. Я тестировал эти два подхода с fillfactor`ом в 50% в версии PostgreSQL 14.5 и получил такие результаты:

Картинка выше демонстрирует необходимость добавления общ��го индексного поля, по которому мы будем обновлять данные. Однако замеры в версии PostgreSQL 17.4 показали, что разницы между этими двумя подходами практически нет. PostgreSQL не стоит на месте, при использовании свежих версий добавление общего индексного поля transaction_id не имеет смысла.

Давайте теперь сделаем нашу многопоточную вставку данных атомарной. 

Атомарная вставка с fillfactor = 50

Генерируем transaction_id и устанавливаем флаг ready_to_read = false. После этого дожидаемся, пока все потоки закончат свою работу, и устанавливаем флаг ready_to_read = true. Посмотрим на результат:

Нам удалось в два раза увеличить скорость относительно первого результата (4 минуты 37 секунд) и при этом сохранить атомарность. Мы получили 2 минуты 22 секунды.

Подведем небольшой итог по реализации атомарности через обновление дополнительного поля:

• Hibernate для detached entity загружает ее состояние из БД и хранит его до окончания транзакции. 

• Обновление по общему полю работает быстрее в версии PostgreSQL 14.5. В версии PostgreSQL 17.4 эти два подходы равны по скорости.

• Fillfactor помогает HOT-обновлениям, но требует дополнительного места на диске.

• В два раза удалось ускорить вставку данных в БД с сохранением атомарности.

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

Признак атомарности в отдельной таблице

Здесь я покажу два варианта.

1.  Transaction ID в отдельной (одной) таблице

Создадим отдельную таблицу, назовем ее active_transaction. Данная таблица будет иметь два поля:

• ID – первичный ключ блокируемой от чтения записи (ID платежного документа);   

• transaction_id – индексное поле, по которому мы будемт искать и удалять записи с ID платежных документов. Таким образом, после удаления записи будут доступны для чтения.

2. Transaction ID в двух таблицах

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

Чем отличаются эти два способа? 

В первом варианте, когда мы блокируем по айдишнику, мы будем иметь на каждую нашу 4-миллионную вставку данных дополнительные 4 миллиона строк в таблице active_transaction. При этом у каждой строки есть заголовки, у страниц есть заголовки, индексы и прочее. Итого на 4 млн строк набегает около 342 Мб дополнительной дисковой нагрузки. 

Второй вариант более легкий — есть только одна запись в таблице active_transaction. Но так как мы в основную таблицу тоже добавили айдишник, соответственно, здесь будем иметь 91 Мб дополнительной дисковой нагрузки.

Разница между этими двумя подходами — 250 Мб. Каждый из них имеет право на жизнь, так что разберем их более подробно.

Вариант № 1: Transaction ID в отдельной таблице

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

На картинке показал, как будет выглядеть PaymentDocumentActiveTransactionEntity в этом варианте. 

Здесь над полем ID нет никаких генераторов, мы будем этот айдишник брать сразу из PaymentDocumentEntity. Spring нам в этом поможет, делается все достаточно просто. Помечаем аннотацией @MapsId нашу связанную сущность PaymentDocumentEntity, и больше делать ничего не нужно. Айдишник платежного документа промапится на наш ID. Также мы используем CascadeType.PERSIST, чтобы одним методом сохранять сразу две сущности в БД. 

Как это будет выглядеть? К нам приходит список PaymentDocumentEntity и идентификатор транзакции transaction_id. Мы создаем PaymentDocumentActiveTransactionEntity и сохраняем его в базу данных через один репозиторий. То есть один метод сохранит в БД две сущности.

Как будет выглядеть сама атомарная вставка? Генерируем айдишник, передаем в наш асинхронный метод. Ждем, пока данные сохранятся в БД, затем дропаем 4 млн записей из таблицы active_transaction по transaction_id.

Самое время разобраться, насколько удаление у PostgreSQL — быстрая операция. Для этого вновь вспомним, он как работает с MVCC.

По факту, для удаления записи PostgreSQL всего лишь нужно изменить данные в заголовке строки. А именно — установить значение транзакции, которая инвалидировала текущую строку (поле t_xmax) и установить флаг xmax_aborted в false. Давайте посмотрим, сколько будет весить удаление 4 млн строк из таблицы active_transaction.

Операция удаления 4 млн строк на моем примере занимает чуть больше двух секунд, что достаточно быстро. Посмотрим, каким получится общее время многопоточной и атомарной вставки данных в этом варианте реализации.

Результат — 2 минуты 1 секунда. Этот метод быстрее, чем обновление данных в основной таблице, примерно на 15%. При этом мы никак не тюнили постгрес и не добавляли данные в основную таблицу. 

Вариант № 2: Transaction ID в двух таблицах

Второй вариант подразумевает добавление поля transaction_id в две таблицы. В таблице active_transaction данное поле будет выступать в качестве первичного ключа, следовательно, будет единственным для данной таблицы. Таким образом, на всю 4-миллионную вставку в таблице active_transaction окажется всего одна запись. 

Entity будет выглядеть следующим образом:

Как будет выглядеть сам процесс вставки? Генерируем transaction_id, записываем его в таблицу active_transaction, затем выполняем многопоточную вставку данных с указанием этого transaction_id в таблице платежных документов. Далее дожидаемся, когда все потоки закончат свою работу, после чего удаляем строку с данным transaction_id из таблицы active_transaction.

Результат — 1 минута 57 секунд. Это метод оказался немного быстрее предыдущего варианта — на 4 секунды, что вполне ожидаемо, т. к. мы снижаем дисковую нагрузку на 250 Мб.

Как мне кажется, оба подхода имеют право на существование — кому-то больше подойдет первый, кому-то второй.

Что ж, давайте посмотрим, как сделать так, чтобы в выборку попадали только актуальные данные. 

Как сделать чтение только актуальных данных? 

Разберем эти два варианта — они будут отличаться не слишком сильно. Единственная разница в том, что во втором варианте мы будем соединять таблицы не по ID, а по transaction_id. 

Вариант № 1: условие по NOT IN

Начнем с самого плохого варианта, когда для отбора валидных записей мы будем использовать условие not in. В данное условие мы передадим выборку всех ID из таблицы active_transaction. Реализовать данный вариант мы можем при помощи аннотации @Where. Условие начнём с id, к которому Hibernate добавит алиас текущей таблице. 

Вариант с NOT IN более показателен для первого подхода из предыдущего раздела — там, где у нас на одну вставку приходится 4 млн записей. Если посмотреть на план запросов, то видно, что PostgreSQL его никак не оптимизирует.

Он делает Seq Scan по таблице active_transaction, то есть индекс в данном случае не работает. Доступ по первичному ключу, как видно, занимает более чем одну секунду. Это слишком много.

Давайте попробуем переписать данный запрос так, чтобы PostgreSQL мог использовать индексы. И первым вариантом будет NOT EXISTS.

В данном случае выборка данных из таблицы active_transaction заключается в скобки оператора NOT EXISTS, где условии WHERE происходит соединение с основной таблицей. Hibernate умеет обрабатывать такие условия и подставит ALIAS к нашему ID в конце условия. Давайте посмотрим на план запроса.

Здесь мы видим, что этот метод называется Nested Loop Anti Join. У нас используются индексы, доступ по первичному ключу составляет 0,157 миллисекунд, что достаточно неплохо.

Вариант № 2: Условие по NOT EXISTS

Во втором варианте соединяемся по transaction_id. 

Почему мы вообще разбираем этот вариант? Потому что это все же немножко другие запросы, и хочется посмотреть на план. 

Так как таблица у нас маленькая (там всего лишь одна запись), PostgreSQL проще всю ее забрать в память. Поэтому он использует Seq Scan и не применяет индекс. По времени этот запрос выходит на 0,106 миллисекунд.

Какие еще есть варианты помимо NOT EXISTS: условие с LEFT JOIN

Мы можем соединиться с таблицей active_transaction по первичному ключу и указать в условиях к самой таблице, что мы отбираем только те записи, где из присоединенной таблицы transaction_id is null. Сделать это в Spring достаточно просто — через аннотацию @SecondaryTable. Там мы указываем таблицу, с которой соединяемся, ключи, по которым соединяемся, — в нашем случае это айдишники — и выводим наше поле transaction_id.

Аннотация @Column имеет параметр table, в котором мы указываем, из какой таблицы хотим достать данное поле. 

Однако с этим вариантом не все так просто. В блоке select Hibernate формирует запрос нормально, то есть выводит наше поле из нужного места. А вот в блоке Where почему-то подставляет ALIAS основной таблицы. 

По данной проблеме в Hibernate заведена бага HHH-4246, и, возможно, в будущем это поправят, но пока этого не сделали. Давайте посмотрим, как мы сами можем исправить данную ошибку.

Используем Placeholder

Использование Placeholder`а в аннотации @Where — это один из вариантов, как можно решить проблему.  

По этому Placeholder`у мы можем определять запросы, которые будем отлавливать, а также место, где нам следует добавить правильное условие. Делать это будем следующим образом: 

Создадим класс SQL-интерсептор, отнаследуемся от StatementInspector и реализуем метод inspect. В данный метод нам будут прилетать тексты запросов от Hibernate перед тем, как он отправит их в БД. По нашему Placeholderу находим нужный запрос и вместо самого Placeholderа добавляем наше условие transaction_id is null с нужным алиасом таблицы. В application.yml укажем путь до нашего класса, чтобы данный механизм заработал.

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

Мы видим, что запрос скомпилировался, т. е. стал валидным. Посмотрим на план его выполнения.

Здесь уже используется Nested Loop Left Join, а также индексы. Время получилось хорошее — 0,137 миллисекунд на доступ по первичному ключу. 

Условие с LEFT JOIN. Вариант 2

Во втором варианте, когда для соединения используется transaction_id, план запросов абсолютно такой же, как и с Anti Join. 

Бенчмарки чтения

Для того чтобы измерить влияние этих подходов на получение данных из БД, воспользуемся утилитой pgbench. Данная утилита работает с голыми запросами, поэтому оставим только те запросы, у которых отличаются планы выполнения. Измерять будем запросы: с флагом ready_to_read, Anti Join с соединением по transaction_id, Anti Join и Left Join с соединением по id.

Выборка одной записи по первичному ключу (ID)

Начнем с первого примера, с доступа к данным по первичному ключу, когда выбираем только одну запись. 

Замеры я делал в двух разрезах по количеству незафиксированных данных, в 4 млн и в 32 млн. Это необходимо, чтобы понять, как сильно количество мусорных данных мешает выборке валидных данных. Сверху синей линией я подчеркнул целевое количество tps без дополнительного фильтра и незафиксированных строк. Также стоит отметить, что данное количество tps включает в себя сетевые задержки. Дополнительно я сделал замеры без сетевых задержек, общая картина коррелирует с тем, что вы увидите в статье. Данные графики созданы лишь для того, чтобы показать разницу в подходах. 

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

Выборка 10 тыс. записей по индексному полю (order_dt)

Во втором примере количество незафиксированных данных, которые попадают в эту выборку, напрямую влияет на само количество tps. 

То есть чем больше PostgreSQL приходится отфильтровывать лишних данных, тем меньше количество tps. Лучше всего с этим, конечно, справляется подход через флаг ready_to_read, потому что ему не приходится обращаться к дополнительным таблицам. 

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

Подведем итоги по теме выноса признака атомарности (transaction_id) в смежные таблицы:

• Это удобно — не нужно ничего тюнить в PostgreSQL.

• Можно обойтись без внесения изменений в основную таблицу (когда блокируем запись по ID основной таблицы).

• Работает более чем на 15% быстрее, чем с дополнительным полем в основной таблице. 

• Anti Join и Left Join дают примерно одинаковый результат по производительности.

• Важно следить за количеством незафиксированных данных.

Какие проблемы могут быть с @Where

Проблема № 1: пропадает условие при соединении по первичному ключу

Допустим, у нас есть некая выписка, и мы с ней соединяемся по айдишнику через FetchType.EAGER.

Если мы создадим запрос через JPA-репозиторий с получением записи не по первичному ключу, например findByPaymentDocument, или используем  FetchType.LAZY, то Hibernate сначала вытащит из БД statement и дальше вторым запросом заберет платежный документ.

В этом случае Hibernate отработает корректно, наше условие на месте, и мы получим только валидные данные. А вот с  FetchType.EAGER и getByID мы получим другой запрос, так как Hibernate делает оптимизацию и пытается подтянуть из базы данных все одним запросом. И тут мы видим, что наше условие теряется. 

Почему так сделано, я не очень понимаю, могу только сказать, где это сделано.

Если мы соединяемся по первичному ключу (isReferenceToPrimaryKey), то никаких условий добавлено не будет. Благо, этот баг пофиксили в версии Hibernate 6.2. (Spring Boot 3.1.0).

Проблема № 2: ошибка в запросе при соединении НЕ по первичному ключу

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

Тогда при получении записи через getById с Anti Join у нас не будет проблем, описанных выше. Условие вставилось куда нужно, и мы получим только валидные данные.

Но проблема будет с соединением через Left Join из второго способа. Hibernate почему-то соединяет нашу таблицу active_transaction с основной таблицей statement, а не с payment_dociment, как должно быть. 

Соответственно, данное условие мы не можем применить к таблице payment_document. Этот баг тоже пофиксили в версии Hibernate 6.1. (Spring Boot 3.0.0)

Если посмотреть на пример запроса в Hibernate 6.2. (Spring Boot 3.1.0), то можно увидеть, что наше условие взято в скобки и расположено в нужном месте. Данный запрос скомпилируется и отработает корректно. Также ребята хорошо поработали над самим текстом запроса — убрали лишнее, сократили ALIAS, запросы стали лучше читаться и меньше весить.

Может ли PostgreSQL взять на себя сохранение атомарности?

В конце хочу немного порассуждать о том, как здорово было бы, если PostgreSQL взял на себя процесс сохранения атомарности. У PostgreSQL есть такой замечательный метод, как Prepare Transaction. Он служит для того, чтобы отвязать текущую транзакцию от текущего сеанса. Таким образом, вставить данные мы можем в одном сеансе, а вот закоммитить в другом. Это так называемая двухфазная фиксация.

У данного подхода есть свои ограничения, которые описаны в документации.

По своей сути prepare transaction — это такая же транзакция, только растянутая во времени. PostgreSQL не любит длинные транзакции, поэтому для данного подхода обязателен внешний менеджер, который будет следить за отвалившимися транзакциями.

Посмотреть информацию о подготовленных транзакциях можно при помощи следующего запроса:

Хотел бы обратить внимание на колонку “prepared”, там содержится время, когда данная транзакция была создана. По этому времени мы можем следить за данным типом транзакций и, например, откатывать все транзакции, которые висят больше 20 минут.

Давайте посмотрим на небольшом примере, как этот подход работает. В первом сеансе мы вставляем какие-то данные, устанавливаем идентификатор prepare transaction. Потом, во втором сеансе, вставляем другие данные и также устанавливаем идентификатор prepare transaction. В третьем сеансе мы пытаемся все эти данные закоммитить через команду commit prepared.

Но не все так просто, как кажется. Мы не можем использовать команду commit prepared в одном транзакционном блоке, что не позволяет применить данный подход для сохранении атомарности.

Также мы не можем использовать один и тот же Transaction ID для нескольких сеансов, это ограничение самого PostgreSQL. 

По этому поводу я подходил к ребятам из Postgres Professional — говорил, что было бы классно, если можно было либо передавать этот Transaction ID списком, либо использовать его в одном транзакционном блоке. Коллеги вернулись ко мне с обратной связью и с патчем, который мне удалось протестировать. Давайте вкратце расскажу, в чем его смысл.

Мы по-прежнему сохраняем каждую вставку с отдельным transaction id. А вот закоммитить данные мы теперь можем атомарно, передав в команду commit prepared список наших transaction id, перечисленных через запятую.  

Какие плюсы и ожидания от такого подхода?

Большим плюсом данного подхода является то, что нам не нужно изменять структуру наших таблиц для сохранения атомарности. Конечно же, следить за этим процессом все равно придется — на уровне бизнес-логики. Также, как я писал выше, PostgreSQL не любит длинных транзакций, и нам потребуется внешний обработчик, который бы следил за отвалившимися prepare transaction.

Еще одним плюсом является то, что нам не нужно писать дополнительных условий к выборкам. Можно предположить что, раз уж фильтрацию данных мы отдаем на сторону PostgreSQL, то и получать актуальные данные мы будем быстрее, однако это не так. Неважно, каким образом мы будем отфильтровывать валидные данные. Вручную, через наложение дополнительного условия в операторе Where, или же PostgreSQL сам отфильтрует незакоммиченные данные через prepare transaction. Разница в TPS между этими двумя подходами будет минимальной. 

Подведем итоги

• Для реализации атомарности через дополнительный флаг в основной таблице нужно тюнить PostgreSQL. При этом за скорость мы платим местом на диске.

• Реализация атомарности в смежных таблицах, на мой взгляд, удобнее. Не нужно тюнить PostgreSQL, плюс он на 15% быстрее, чем первый вариант. Можно обойтись без внесения изменений в основную таблицу, а также написать универсальный обработчик и добавлять к данному механизму другие таблицы без изменения кода и структуры данных.

• @Where корректно работает начиная с версии Spring Boot 3.1.0. Не забывайте обновляться!

• Все native query должны учитывать реализацию атомарности на уровне бизнес-логики.

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

И напоследок еще раз продублирую ссылку на репозиторий вот здесь. Если заинтересовало, можете запустить и протестировать на своем оборудовании и своих данных. Делитесь в комментариях, что получилось, задавайте вопросы, если что-то непонятно, буду рад обратной связи :-)