Компромиссы построчной вставки в БД и батчинга

[Dhwtj]

Без нефункциональных требований по ОЗУ, скорости, размеру файла ничего нельзя сказать однозначно.

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

[myshkin_does_it]

полностью согласен! я его обошел и как-то сразу в сторону батчевой вставки ушел, но это как раз подтверждает идею: для каждой реализации есть свои кейсы

[tuxi]

В таком примере, батч не очень хорошая идея потому, что если в пакете будет один или два инсерта, который БД не примет (например там uni ключ сработает), вам придется откатываться и что-то делать - искать эту строку инсерта и как то решать, что с ней делать. А такие ситуации обязательно будут, такие внешние источники данных, как ексель, обязательно принесут вместе с собой какой-либо бардак и нарушение контракта на формат данных.
Ну и порядок в 1000 строк - нет смысла батч использовать, в моей практике дешевле построчно вставлять.

[myshkin_does_it]

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

[Akina]

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

create table test (id int, check (id < 10));

begin;
    begin;
        insert into test values (1);  -- запись вставляется
        commit;                       
    begin;
        insert into test values (11); -- ошибка ограничения
        commit;                       -- субтранзакция откатывается
    begin;
        insert into test values (5);  -- запись вставляется
        commit;
    commit;                           -- фиксируется вставка 2 записей
select * from test;

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

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

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

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

[myshkin_does_it]

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

# Логика валидации
def foo() -> bar:
  ...

with SessionLocal() as db, db.begin():
  try:
    with db.begin_nested(): # Создаем savepoint для отката
      stmt = insert(model).values(bar)
      db.execute(bar)
  except SQLALchemyError as e:
    ...

[Akina]

??? Вот сейчас не понял, причём совсем. И, кмк, потому, что вы меня поняли неправильно.

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

[myshkin_does_it]

подход понятен, имеет место быть

[ALexKud]

А не проще ли весь файл залить в таблицу а потом с ней работать средствами sql? Я бы делал так. Кстати, есть вроде расширение для sqlite, работающее с csv файлами как с таблицей. Можно тоже попробовать.

[myshkin_does_it]

а в чем преимущества? у нас и так есть хранилка, где лежит входящий файл. просто лишний шаг добавляем

[Akina]

а в чем преимущества?

На самом деле преимущества-то, наверное, имеются.

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

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

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

PostgreSQL умеет импортировать CSV-файлы в таблицу простейшим запросом COPY. Причём для исключения проблем импорта (особенно если исходный файл потенциально ну очень кривой и может организовать проблемы вроде несовпадения типов или там кодировок) можно просто тянуть всю строку в один блоб и потом на стороне же SQL парсить. Кстати, именно по причине возможности таких недостатков исходных данных и не советую использовать врапперы для подключения внешнего CSV как таблицы.

А для снижения вероятности блокировок рабочих таблиц - импорт в блоб, парсинг, валидация и вставка в структурную копию рабочей таблицы, вставка обработанных и валидированных данных в рабочую таблицу. Минимум интерференций с другими процессами.