С выходом PostgreSQL 15 мы застали появление долгожданной команды MERGE, которая позволяет реализовывать эффективные способы синхронизации обновлений.
Суть MERGE заключается в ее универсальности: она позволяет объединить операции INSERT, UPDATE и DELETE в одном запросе, автоматически выбирая нужное действие в зависимости от того, существует ли соответствующая запись в целевой таблице.
Вместо нескольких отдельных запросов INSERT, UPDATE, DELETE MERGE сокращает накладные расходы на сетевой трафик и уменьшает количество обращений к диску. MERGE облегчает реализацию шаблонов SCD и других сложных сценариев управления данными.
MERGE в PostgreSQL работает с соблюдением строгих стандартов SQL, обеспечивая совместимость и переносимость кода. Также PostgreSQL обрабатывает конфликты на уровне строк, позволяя тонко настраивать логику обработки данных с использованием условий WHEN MATCHED и WHEN NOT MATCHED.
Сравнивая с предшествующим подходом INSERT ON CONFLICT, MERGE предлагает больше возможностей для оптимизации и управления данными. INSERT ON CONFLICT был ориентирован преимущественно на обработку конфликтов при вставке, в то время как MERGE расширяет этот функционал.
ON condition — это условие, на основании которого происходит сравнение строк из источника USING и целевой таблицы. Если условие удовлетворяется, то есть строки совпадают, то выполняется одно из действий, указанных в WHEN MATCHED или WHEN NOT MATCHED.
WHEN MATCHED — в этой части указывается, что делать с данными, если условие ON обнаружило соответствие между строками в источнике и целевой таблице. Здесь можно задать обновление данных UPDATE или их удаление DELETE, в зависимости от бизнес-логики приложения.
WHEN NOT MATCHED — эта часть описывает действия, которые должны быть выполнены, если строка из источника не нашла соответствия в целевой таблице. Обычно здесь используется операция INSERT, чтобы добавить новую строку в целевую таблицу.
MERGE INTO employees AS e
USING new_employees AS ne
ON e.id = ne.id
WHEN MATCHED THEN
UPDATE SET e.name = ne.name, e.position = ne.position
WHEN NOT MATCHED THEN
INSERT (id, name, position) VALUES (ne.id, ne.name, ne.position);
Если существует сотрудник с таким же ID в обеих таблицах, его данные обновляются. Если же сотрудник есть только в new_employees, он добавляется в employees.
Рассмотрим задачу, где нужно обновить зарплаты сотрудников на основе данных из другой таблицы salary_updates, содержащей ID сотрудников и новые зарплаты:
MERGE INTO employees AS e
USING salary_updates AS su
ON e.id = su.employee_id
WHEN MATCHED THEN
UPDATE SET e.salary = su.new_salary;
Запрос обновляет зарплату сотрудников в таблице employees, если найден соответствующий ID в таблице salary_updates.
Предположим, у нас есть таблица employees и таблица retired_employees с ID ушедших на пенсию сотрудников. Мы хотим удалить записи из employees на основании совпадения с retired_employees:
MERGE INTO employees AS e
USING retired_employees AS re
ON e.id = re.id
WHEN MATCHED THEN
DELETE;
Запрос удаляет записи о сотрудниках, которые ушли на пенсию, из основной таблицы employees.
Допустим, есть таблица temporary_employees для временных сотрудников, которых нужно перенести в основную таблицу employees после окончания их проекта. При этом, если сотрудник уже существует в основной таблице, его данные должны быть обновлены:
MERGE INTO employees AS e
USING temporary_employees AS te
ON e.id = te.id
WHEN MATCHED THEN
UPDATE SET e.project = te.project, e.status = 'Permanent'
WHEN NOT MATCHED THEN
INSERT (id, name, project, status) VALUES (te.id, te.name, te.project, 'Permanent');
Запрос обновит статус и данные проекта для сотрудников, которые уже есть в таблице employees, и добавит новые записи для тех, кто там отсутствует, помечая их как постоянных сотрудников.
Представим ситуацию, где нужно обновить данные в таблице inventory на основе поставок из таблицы new_shipments. Если количество товара в новой поставке равно 0, соответствующая запись должна быть удалена из inventory:
MERGE INTO inventory AS i
USING new_shipments AS ns
ON i.product_id = ns.product_id
WHEN MATCHED AND ns.quantity = 0 THEN
DELETE
WHEN MATCHED THEN
UPDATE SET i.quantity = i.quantity + ns.quantity
WHEN NOT MATCHED THEN
INSERT (product_id, quantity) VALUES (ns.product_id,# ns.quantity);
Если поставка содержит товары с количеством 0, эти товары удаляются из инвентаря. В противном случае, если товар уже существует в инвентаре, его количество обновляется. Новые товары добавляются в инвентарь.
Увеличение значения work_mem позволяет выполнять больше операций в памяти, что значительно быстрее, чем обращение к диску.
При выполнении MERGE, особенно с большими объемами данных, PostgreSQL может использовать операции сортировки и хеширования для сопоставления строк из исходных таблиц. Если work_mem недостаточно для выполнения этих операций в памяти, PostgreSQL будет вынужден использовать временное дисковое хранение, что не всегда хорошо.
Далее нужно увеличиватьwork_mem постепенно, чтобы избежать чрезмерного потребления памяти, которое может привести к swap'у и, как следствие, к снижению общей производительности системы. Все это мониторится с помощью инструментов мониторинга.
PostgreSQL позволяет настраивать work_mem для индивидуальной сессии:
-- Установка work_mem для текущей сессии
SET work_mem = '256MB';
-- увеличение work_mem перед выполнением MERGE
BEGIN;
SET LOCAL work_mem = '256MB';
MERGE INTO target_table USING source_table
ON target_table.id = source_table.id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;
COMMIT;
Оптимизация с динамической настройкой work_mem:
-- Динамическое управление work_mem в процедуре
DO $$
BEGIN
IF (SELECT count(*) FROM source_table) > 100000 THEN
SET work_mem = '1GB';
ELSE
SET work_mem = '128MB';
END IF;
MERGE INTO target_table USING source_table
ON target_table.id = source_table.id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;
END $$;
work_mem и MERGE позволяют повысить производительность и эффективность работы с данными.
habr.com
Суть MERGE заключается в ее универсальности: она позволяет объединить операции INSERT, UPDATE и DELETE в одном запросе, автоматически выбирая нужное действие в зависимости от того, существует ли соответствующая запись в целевой таблице.
Вместо нескольких отдельных запросов INSERT, UPDATE, DELETE MERGE сокращает накладные расходы на сетевой трафик и уменьшает количество обращений к диску. MERGE облегчает реализацию шаблонов SCD и других сложных сценариев управления данными.
MERGE в PostgreSQL работает с соблюдением строгих стандартов SQL, обеспечивая совместимость и переносимость кода. Также PostgreSQL обрабатывает конфликты на уровне строк, позволяя тонко настраивать логику обработки данных с использованием условий WHEN MATCHED и WHEN NOT MATCHED.
Сравнивая с предшествующим подходом INSERT ON CONFLICT, MERGE предлагает больше возможностей для оптимизации и управления данными. INSERT ON CONFLICT был ориентирован преимущественно на обработку конфликтов при вставке, в то время как MERGE расширяет этот функционал.
Синтаксис Merge
USING — этот элемент указывает на источник данных, которые будут использоваться для слияния. Источником может быть таблица, подзапрос или временный набор данных, созданный на лету. USING определяет, откуда берутся данные для сравнения с целевой таблицей.ON condition — это условие, на основании которого происходит сравнение строк из источника USING и целевой таблицы. Если условие удовлетворяется, то есть строки совпадают, то выполняется одно из действий, указанных в WHEN MATCHED или WHEN NOT MATCHED.
WHEN MATCHED — в этой части указывается, что делать с данными, если условие ON обнаружило соответствие между строками в источнике и целевой таблице. Здесь можно задать обновление данных UPDATE или их удаление DELETE, в зависимости от бизнес-логики приложения.
WHEN NOT MATCHED — эта часть описывает действия, которые должны быть выполнены, если строка из источника не нашла соответствия в целевой таблице. Обычно здесь используется операция INSERT, чтобы добавить новую строку в целевую таблицу.
Примеры использования
Представим, что есть две таблицы: employees и new_employees. Мы хотим обновить данные в employees, используя информацию из new_employees, а также добавить новые записи, которых ещё нет в employees:MERGE INTO employees AS e
USING new_employees AS ne
ON e.id = ne.id
WHEN MATCHED THEN
UPDATE SET e.name = ne.name, e.position = ne.position
WHEN NOT MATCHED THEN
INSERT (id, name, position) VALUES (ne.id, ne.name, ne.position);
Если существует сотрудник с таким же ID в обеих таблицах, его данные обновляются. Если же сотрудник есть только в new_employees, он добавляется в employees.
Рассмотрим задачу, где нужно обновить зарплаты сотрудников на основе данных из другой таблицы salary_updates, содержащей ID сотрудников и новые зарплаты:
MERGE INTO employees AS e
USING salary_updates AS su
ON e.id = su.employee_id
WHEN MATCHED THEN
UPDATE SET e.salary = su.new_salary;
Запрос обновляет зарплату сотрудников в таблице employees, если найден соответствующий ID в таблице salary_updates.
Предположим, у нас есть таблица employees и таблица retired_employees с ID ушедших на пенсию сотрудников. Мы хотим удалить записи из employees на основании совпадения с retired_employees:
MERGE INTO employees AS e
USING retired_employees AS re
ON e.id = re.id
WHEN MATCHED THEN
DELETE;
Запрос удаляет записи о сотрудниках, которые ушли на пенсию, из основной таблицы employees.
Допустим, есть таблица temporary_employees для временных сотрудников, которых нужно перенести в основную таблицу employees после окончания их проекта. При этом, если сотрудник уже существует в основной таблице, его данные должны быть обновлены:
MERGE INTO employees AS e
USING temporary_employees AS te
ON e.id = te.id
WHEN MATCHED THEN
UPDATE SET e.project = te.project, e.status = 'Permanent'
WHEN NOT MATCHED THEN
INSERT (id, name, project, status) VALUES (te.id, te.name, te.project, 'Permanent');
Запрос обновит статус и данные проекта для сотрудников, которые уже есть в таблице employees, и добавит новые записи для тех, кто там отсутствует, помечая их как постоянных сотрудников.
Представим ситуацию, где нужно обновить данные в таблице inventory на основе поставок из таблицы new_shipments. Если количество товара в новой поставке равно 0, соответствующая запись должна быть удалена из inventory:
MERGE INTO inventory AS i
USING new_shipments AS ns
ON i.product_id = ns.product_id
WHEN MATCHED AND ns.quantity = 0 THEN
DELETE
WHEN MATCHED THEN
UPDATE SET i.quantity = i.quantity + ns.quantity
WHEN NOT MATCHED THEN
INSERT (product_id, quantity) VALUES (ns.product_id,# ns.quantity);
Если поставка содержит товары с количеством 0, эти товары удаляются из инвентаря. В противном случае, если товар уже существует в инвентаре, его количество обновляется. Новые товары добавляются в инвентарь.
Оптимизация с помощью параметра work_mem
work_mem — это параметр конфигурации, который определяет максимальный объем памяти, который может быть использован для операций сортировки и хеширования в рамках одного запроса, не включая операции с дисковым хранением. Иными словами, это объем памяти, выделяемый для каждой операции, требующей временного хранения данных, таких как сортировка результатов запроса или выполнение хеш-таблиц при агрегации.Увеличение значения work_mem позволяет выполнять больше операций в памяти, что значительно быстрее, чем обращение к диску.
При выполнении MERGE, особенно с большими объемами данных, PostgreSQL может использовать операции сортировки и хеширования для сопоставления строк из исходных таблиц. Если work_mem недостаточно для выполнения этих операций в памяти, PostgreSQL будет вынужден использовать временное дисковое хранение, что не всегда хорошо.
Советы по настройке
Все начинается с анализа сложности запросов, которые обычно выполняются. Большие и сложные запросы с большим количеством операций сортировки и хеширования могут требовать увеличения work_mem.Далее нужно увеличиватьwork_mem постепенно, чтобы избежать чрезмерного потребления памяти, которое может привести к swap'у и, как следствие, к снижению общей производительности системы. Все это мониторится с помощью инструментов мониторинга.
PostgreSQL позволяет настраивать work_mem для индивидуальной сессии:
-- Установка work_mem для текущей сессии
SET work_mem = '256MB';
Примеры с work_mem в коннекте с MERGE:
Настройка work_mem для улучшения производительности MERGE:-- увеличение work_mem перед выполнением MERGE
BEGIN;
SET LOCAL work_mem = '256MB';
MERGE INTO target_table USING source_table
ON target_table.id = source_table.id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;
COMMIT;
Оптимизация с динамической настройкой work_mem:
-- Динамическое управление work_mem в процедуре
DO $$
BEGIN
IF (SELECT count(*) FROM source_table) > 100000 THEN
SET work_mem = '1GB';
ELSE
SET work_mem = '128MB';
END IF;
MERGE INTO target_table USING source_table
ON target_table.id = source_table.id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;
END $$;
work_mem и MERGE позволяют повысить производительность и эффективность работы с данными.
MERGE и её улучшение производительности с помощью work_mem
С выходом PostgreSQL 15 мы застали появление долгожданной команды MERGE , которая позволяет реализовывать эффективные способы синхронизации обновлений. Суть MERGE заключается в ее универсальности:...
habr.com