Как спроектировать идемпотентный инкрементальный загрузочный паттерн (MERGE/UPSERT) для одной и той же логической таблицы, учитывая различия между PostgreSQL, ClickHouse и BigQuery?

Короткий ответ

Идемпотентность означает одинаковое логическое состояние после одного и нескольких запусков одного batch. Сначала определите бизнес-ключ и правило победы версии; лишь затем выбирайте диалект и физическую модель.

Как это работает подробнее

В PostgreSQL уникальный индекс делает конфликт атомарным, но условие обновления должно запрещать старой версии перетирать новую. В BigQuery source MERGE обязан иметь максимум одну строку на target key, иначе запрос завершится ошибкой или логика станет неоднозначной.

ClickHouse оптимизирован для append. ReplacingMergeTree(version) хранит новые версии, а физическое удаление старых происходит асинхронно; корректное чтение требует argMax, materialized view или осознанного FINAL.

Пример кода

postgres_upsert.sqlКопировать

INSERT INTO customer_state (customer_id, version, status, updated_at)
VALUES (:customer_id, :version, :status, :updated_at)
ON CONFLICT (customer_id) DO UPDATE
SET version = EXCLUDED.version,
    status = EXCLUDED.status,
    updated_at = EXCLUDED.updated_at
WHERE EXCLUDED.version > customer_state.version;

Что использовать на практике

• Используйте закрытый watermark. Фиксируйте нижнюю и верхнюю границы batch и продвигайте watermark только после успеха.
• Оставляйте lookback. Повторно читайте недавнее окно для поздних данных, полагаясь на идемпотентную запись.
• Проверяйте конкуренцию. Два ретрая не должны позволять старой версии победить новую.

Подводные камни

• MERGE с дублями source. Сначала примените ROW_NUMBER по ключу и версии.
• Watermark по времени обработки. Позднее бизнес-событие может навсегда выпасть.
• Одинаковая модель для всех СУБД. ClickHouse не является строковой OLTP-системой с дешёвым UPDATE.