badcasedaily1 10 фев в 13:18

MERGE и её улучшение производительности с помощью work_mem

5 мин

6.4K

С выходом 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 расширяет этот функционал.

Синтаксис 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 позволяют повысить производительность и эффективность работы с данными.

Напоследок напомню о том, что для тех, кто интересуется образованием в сфере IT мои друзья из OTUS разработали ряд онлайн-курсов от экспертов индустрии. Ознакомиться с каталогом можно тут.

Теги:

Хабы: