PostgreSQL: массовое добавление с обновлением при конфликте id

Быстрый ответ

Массовый upsert в Postgres реализуется с помощью команды INSERT ... ON CONFLICT ... DO UPDATE. Она позволяет добавлять записи, а при обнаружении конфликта по уникальному ключу переходить к их обновлению.

Рассмотрим пример для таблицы my_table с уникальным ограничением на id:

INSERT INTO my_table (id, column_a, column_b)
VALUES 
    (1, 'A', 'B'),
    (2, 'C', 'D')
ON CONFLICT (id)
DO UPDATE SET
    column_a = EXCLUDED.column_a,
    column_b = EXCLUDED.column_b;

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

Использование UPSERT для массовых операций

Postgres предоставляет удобные механизмы для работы с массовыми записями, включая условие ON CONFLICT, способствующее разрешению проблемы конфликтующих ключей.

Управление несколькими значениями в столбце

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

INSERT INTO my_table (id, column_a, column_b)
SELECT * FROM unnest(
    ARRAY[1, 2],
    ARRAY['Value1', 'Value2'],
    ARRAY['Value3', 'Value4']
) 
ON CONFLICT (id) DO UPDATE SET
    column_a = EXCLUDED.column_a, 
    column_b = EXCLUDED.column_b;

Подробнее об этом расскажет наш спикер на видео

Разрешение конфликтов при наличии нескольких уникальных ограничений

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

INSERT INTO my_table (id, column_a, column_b)
VALUES 
    ...
ON CONFLICT (id) DO UPDATE SET
    ...
WHERE my_table.column_a IS DISTINCT FROM EXCLUDED.column_a;

Частичные обновления

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

ON CONFLICT (id) DO UPDATE SET
    column_a = EXCLUDED.column_a
WHERE my_table.column_a IS DISTINCT FROM EXCLUDED.column_a;

Эти методы позволяют детально и быстро управлять массовыми операциями upsert.

Визуализация

Представим, что у нас есть склад (🏭), на который приходят разные товары (📦). Каждый товар имеет уникальный номер, и некоторые из них приходят с обновленным содержимым.

UPSERT в Postgres работает так:
- Новый товар (📦): помещаем на новое место (🆕).
- Существующий товар (📦🔍): обновляем его содержимое (🔄).

На практике это выглядит так:

- INSERT INTO warehouse (id, contents) VALUES (several packages)
🏭📦 ➡️ 🆕 | 📦🔍 ➡️ 🔄
- ON CONFLICT (id) DO UPDATE SET contents = EXCLUDED.contents

Иначе говоря:

🏭: Новая посылка 📦 – место 🆕, обновленная – обновление 🔄! Эффективность склада обеспечивается!

Справляемся со сложностями: продвинутые советы и распространенные ловушки

Значимость индексации

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

Производительность операций

Операции upsert имеют тенденцию к ресурсоемкости, особенно при больших объемах данных. Для предотвращения "зависания" системы рекомендуется разбивать операцию upsert на части.

Осторожно с конкурентными транзакциями

Блокировка строк – потенциальная угроза при upsert, особенно когда транзакции выполняются параллельно. PostgreSQL хорошо справляется с этим, но возможность взаимных блокировок требует внимания.

Ловушки и выход из них

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

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

Полезные материалы

1. PostgreSQL: Документация: INSERT — официальная документация по команде INSERT и ON CONFLICT.
2. Insert into a MySQL table or update if exists – Stack Overflow — обсуждение на Stack Overflow о операциях UPSERT в контексте PostgreSQL.
3. UPSERT – вики PostgreSQL — статья из вики, посвященная методам UPSERT в PostgreSQL.
4. Самый быстрый способ загрузки данных в PostgreSQL с помощью Python | Haki Benita — статья о том, как оптимизировать массовую вставку данных в PostgreSQL.