Upsert в витрине данных: обновление без дубликатов

Прямой ответ

Upsert объединяет вставку и обновление в одной операции, поэтому повторная загрузка того же набора данных не создаёт дубликатов. Условием корректности является уникальность ключа.

INSERT INTO mart.customer_orders AS m (customer_id, orders_cnt, revenue_minor, updated_at)
SELECT c.id, COUNT(o.id), COALESCE(SUM(o.amount_minor), 0), now()
FROM dwh.customers c
LEFT JOIN dwh.orders o ON o.customer_id = c.id
GROUP BY c.id
ON CONFLICT (customer_id) DO UPDATE
SET orders_cnt    = EXCLUDED.orders_cnt,
    revenue_minor = EXCLUDED.revenue_minor,
    updated_at    = EXCLUDED.updated_at
WHERE m.revenue_minor IS DISTINCT FROM EXCLUDED.revenue_minor
   OR m.orders_cnt    IS DISTINCT FROM EXCLUDED.orders_cnt;

Условие WHERE в блоке обновления исключает запись строки, значения которой не изменились.

Уникальный индекс по ключу конфликта обязателен: без него условие ON CONFLICT не находит соответствие и операция завершается ошибкой. О построении индексов — в статье про индексы PostgreSQL.

Условие обновления, параллельные загрузки и объём записи

Три аспекта определяют поведение upsert в эксплуатации.

Условие обновления. Обновление строки с неизменными значениями выполняет запись без изменения данных. При большом числе строк это увеличивает объём записи и нагрузку на очистку. Сравнение через IS DISTINCT FROM учитывает значения NULL, в отличие от обычного сравнения.

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

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

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

Альтернативный синтаксис — MERGE, который описывает сопоставление явно и позволяет задавать несколько ветвей обработки; о выборе между вставкой с конфликтом и явным сопоставлением — в статье про инкрементальную загрузку.

Отдельный аспект — частичный индекс. Если уникальность требуется только для части строк, индекс строится с условием, а условие конфликта ссылается на тот же предикат.

О поведении повторных операций в прикладном коде — в статье про идемпотентность.

Объём записи и обслуживание базы

Upsert записывает строку при каждом обновлении, поэтому объём записи определяет нагрузку на обслуживание базы.

Мёртвые версии строк. Обновление создаёт новую версию строки, а прежняя остаётся до очистки. При frequent обновлении большого числа строк объём мёртвых версий растёт, и таблица занимает больше места, чем содержат актуальные данные.

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

Размер порции. Загрузка всего объёма в одной транзакции удерживает блокировки длительное время и увеличивает объём журнала. Порционная загрузка сокращает длительность блокировок.

Практическое следствие: условие сравнения в блоке обновления сокращает число записываемых строк, поэтому время операции и объём мёртвых версий снижаются.

Практика: применение upsert при загрузке витрины

1. Убедитесь в уникальности ключа. Уникальный индекс по ключу конфликта обязателен, иначе операция завершается ошибкой.

2. Используйте условие обновления. Сравнение значений исключает запись строк, которые не изменились.

3. Сравнивайте через IS DISTINCT FROM. Обычное сравнение не учитывает значения NULL, поэтому строка с NULL не обновляется.

4. Применяйте данные в порядке изменения. Позднее применённое значение перезаписывает актуальное, поэтому порядок определяется отметкой изменения.

5. Ограничивайте размер порции. Загрузка всего объёма в одной транзакции удерживает блокировки длительное время.

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

7. Логируйте число вставленных и обновлённых строк. Соотношение показывает долю фактических изменений в объёме загрузки.

Рекомендации:

1. Различайте вставку и обновление в статистике. Число обновлённых строк без изменения значений указывает на избыточную загрузку.
2. Учитывайте стоимость индексации. Индекс по ключу конфликта используется при поиске строки, поэтому его отсутствие приводит к полному сканированию.
3. Не смешивайте полную и инкрементальную загрузку. Полное обновление проще реализовано через замену таблицы, а не через upsert.
4. Проверяйте результат после загрузки. Сравнение агрегатов с источником выявляет пропуск либо дублирование данных.
5. Фиксируйте отметку изменения в витрине. Поле с отметкой последнего обновления позволяет определить актуальность строки.

Практический сценарий: загрузка занимала часы при малом объёме изменений

Контекст: загрузка витрины выполнялась несколько часов, хотя фактически изменялась небольшая доля строк.

1. Диагностика. Загрузка читала весь набор и выполняла обновление по каждой строке без условия изменения. Объём записи соответствовал полному размеру таблицы, хотя значения изменялись у нескольких процентов строк.
2. Причина. Блок обновления не содержал условия сравнения, поэтому каждая строка записывалась заново. Запись без изменения данных увеличивает объём и нагрузку на очистку, а время операции растёт пропорционально полному объёму.
3. Действия. (а) Добавили условие сравнения через IS DISTINCT FROM по полям, входящим в витрину; (б) добавили поле с отметкой последнего обновления, чтобы определять актуальность строки; (в) разбили загрузку на порции по диапазону ключей, чтобы сократить длительность блокировок; (г) включили учёт числа вставленных и обновлённых строк в журнал загрузки; (д) проверили, что повторное выполнение загрузки с тем же набором не меняет результат.
4. Результат. Время загрузки сократилось, поскольку записываются только изменённые строки, а объём записи соответствует доле фактических изменений.

Вывод: upsert без условия сравнения записывает все строки, включая неизменённые, что увеличивает объём записи и время операции. Сравнение значений перед обновлением сокращает запись до фактических изменений.

FAQ

Обязателен ли уникальный индекс для upsert? Да, условие конфликта опирается на уникальный индекс. Без индекса соответствующая строка не находится и операция завершается ошибкой.

Почему сравнение через IS DISTINCT FROM, а не <>? Обычное сравнение с NULL не даёт истинного результата, поэтому строка со значением NULL не обновляется. IS DISTINCT FROM учитывает NULL.

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

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

Источники: PostgreSQL INSERT, MERGE, partial indexes — ссылки в шапке.