Модуль Python для обновления данных в БД — DBMerge

[ptr128]

Имя временной таблицы будет таким же как и у целевой таблицы, но к нему будет прибавлен случайный хэш. При выходе из конструкции with или при завершении выполнения exec(), эта таблица будет автоматически удалена.

Для PostgreSQL это самоубийственный подход. В случае, когда нет возможности заливать данные сразу в целевую таблицу (есть не только вставки, но и удаления, и модификации записей), лучше использовать постоянную нежурналируемую буферную таблицу, в которую сначала заливаем данные COPY ... FROM STDIN, а потом уже при помощи MERGE или DELETE + INSERT ... ON CONFLICT обновляем целевую таблицу. При этом часто необходимо ещё выполнять дедупликацию данных, так как при прямой переливке данных из одной БД в другую может оказаться слишком сложно обрабатывать недоступность исходной или целевых БД. В этом случае проще разделить процессы, буферизируя данные между ними в Kafka.

[SobolevP]

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

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

В любом случае? модуль не создает лишние версии строк в целевой таблице, т.к. он проверяет отличия в значениях полей, когда делает update, а лишние WAL postgres может и почистить (хотя лучше бы без них).

Жаль, что unlogged таблицу нельзя сделать на чистом sqlalchemy. Эта идея мне нравится. Подумаю про такой вариант.

Спасибо за комментарий!

[ptr128]

писать чисто под postgresql и использовать его merge

MERGE - это уже лирика. Главное понимать, что в PostgreSQL противопоказано активное использование временных таблиц из-за особенностей его MVCC, с одной стороны, и реализации временных таблиц в общей INFORMATION SCHEMA - с другой. Ну не завезли туда до сих пор tempdb. Поэтому и приходится изгаляться либо с pg_variables, либо с нежурналируемыми таблицами.

Дедубликацию специально не стал делать, т.к. она создает слепой риск потери данных.

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

[SobolevP]

Спасибо за совет! Попробую сделать alter таблицы, созданной чарез sqlalchemy, и поставить в случае постгреса unlogged.

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

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

[SobolevP]

Сделал временную таблицу как unlogged и дописал об этом в статье.