Как правильно готовить batch вставку данных из Java в PostgreSQL?

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

Для вольных разговоров на тему батч операций, давайте возьмём супер синтетический пример. Рассмотрим Web-сервис, состоящий из двух частей. Первая — это Web UI. Тут у нас будет кнопка, по нажатию на которую генерируются 200 тысяч UUID строк, которые шлются на серверное API. Вторая часть нашей системы — это, очевидно, API, которое принимает на вход массив строк (сгенерированных ранее UUID-ов), и сохраняет это дело сразу в PostgreSQL, без какой-либо обработки.

Таблица, в которую мы хотим вставить — самая простая, из тех, что могли быть:

Мерить будем время между нажатием на кнопку «отправить» и получением ответа от сервера (да-да, это довольно глупо для benchmark, в котором мы хотим замерить время вставки в базу данынх, но зато это похоже на некоторый реалистичный сценарий работы с API).

Наивная вставка данных в БД

Как бы поступил программист, которому нужно вставить массив данных в БД, если он ничего бы не знал про batch операции? Правильно — он просто бы в цикле сделал N запросов, где N — количество элементов в массиве.

На Spring это могло бы выглядеть примерно так:

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

Плюсы — наивная простота. Ну, и оно работает.

JDBC batch API

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

Однако, тут не всё так просто. Если вы возьмёте этот кусок кода и посмотрите в логи PostgreSQL, то увидите там N запросов. Это как-то не сочитается со словом «batch» в названии этого API.

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

Об этой оссобенности данного API написал Владимирhttps://stackoverflow.com/a/48349524/1756750.

JDBC batch API + reWriteBatchedInserts=true

Как вы могли прочитать выше у Владимира, для того чтобы JDBC начал сворачивать запросы во едино, нужно добавить reWriteBatchedInserts=true в адрес подключения. То есть, для спринта это будет выглядеть примерно так:

Код остается ровно таким же, как в примере выше. Плюсы — очевидно, что довольно быстро (на некоторых тестах). Минусы — такие тонкости JDBC API могут уже не работать на базах данных, которые внутри себя не являются PostgreSQL, а лишь предоставляют API этой базы (например, Amazon Aurora PostgreSQL). То есть, для NewSQL баз это надо мерить, а не слепо верить описанию.

Ручное составление запросов

PostgreSQL позволяет за один INSERT запрос вставить сразу несколько значений. То есть, вот такой запрос вполне валиден:

Поэтому можно взять условный StringBuilder, и руками составлять запросы. Главное, надо помнить, что в одном запросе не следует передавать больше 1000 элементов.

Плюсы — возможно, это быстро, но это надо тестировать (об этом далее). Кроме того, не требует каких-то специфичных настроек (типа, reWriteBatchedInserts=true).

Минусы — неудобное API. Кроме того, легко наломать дров. Есть разные corner cases, где такие запросы с множественными значениями могут не работать.

PostgreSQL — COPY

В PG есть замечательная команда COPY, которая позволяет невеятно быстро заливать в базу данные. Нашёлся умелец, который убернул эту команду в удобное Java API. Так и появился проект PgBulkInsert.

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

Плюсы — потенцилаьно, очень быстро.

Минусы- супер завязано на PostgreSQL, то есть, может не работать в некоторых облачных базах данных. Кроме того, в Java библиотеке поддерживаются не все типы данынх (например, нет VARCHAR). Ну, и не особо минус, но, это никак не дружит со спрингом. То есть, у в вас в коде часть запросов может быть написано на JOOQ, или jdbcTemplate, а часть — на этой либе.

Результаты сравнения

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

Получилось примерно так (каждая строка с числом — отдельный эксперимент):

1. 200000 data, batch update (jdbc template):

  • 2445 ms
  • 2056 ms
  • 2073 ms
  • 1883 ms
  • 1901 ms
  • 2056 ms
  • 2175 ms
  • 1940 ms
  • 2133 ms
  • 1882 ms

2. 200000 data, single update (jdbc template):

  • 16699 ms
  • 17513 ms
  • 15883 ms

3. 200000 data, COPY with https://github.com/bytefish/PgBulkInsert

  • 1182 ms
  • 721 ms
  • 757 ms
  • 834 ms
  • 708 ms
  • 670 ms
  • 824 ms

4. 200000 data, manual build SQL

  • 2410 ms
  • 1949 ms
  • 1726 ms
  • 1534 ms
  • 1575 ms
  • 1700 ms
  • 1678 ms
  • 1509 ms
  • 1535 ms
  • 1584 ms

5. 200000 data, manual build SQL + reWriteBatchedInserts=true

  • 2189 ms
  • 1821 ms
  • 1678 ms
  • 1602 ms
  • 1468 ms
  • 1625 ms

6. 200000 data, batch update (jdbc template) + reWriteBatchedInserts=true:

  • 1550 ms
  • 1214 ms
  • 1024 ms
  • 1029 ms
  • 1131 ms
  • 976 ms
  • 1197 ms
  • 1011 ms
  • 1050 ms

Выводы

Собственно, никаких сюрпризов на данном тесте нет:

  1. COPY — самый быстрый API. Однако всё может измениться, если в схеме вашей таблицы есть много индексов.
  2. JDBC batch API — относительно быстрое, но настройка reWriteBatchedInserts=true крайне важна.
  3. Из интересного — запросы, которые я составлял руками, работают довольно медленно. Примерно, как JDBC batch API, без reWriteBatchedInserts=true. C ходу не понял, почему так — вроде бы, это должны быть два эквивалетных по времени теста (возможно, в тесте как раз и проблема — пока не понял до конца).

Что использовать в реальных проектах? Первое, что нужно сказать — в реальных проектах нужно всё сначала поменить, перед тем как выбирать инструмент. Может быть, у вас Сеть будет проблемным местом, или диски Базы Данных. Или еще что-то. И в том случае так может оказаться, что никакой разницы от используемого API не будет.

Если давать совет в сферическом вакуме — на мой вкус, JDBC batch API — хороший компромисс между скоростью API и надёжностью и стабильностью API. На COPY я бы полез только, если бы это был главный критический участок проекта, и без этого я бы не смог завершить мою задачу.

Категории: Программирование