Производительность запроса INSERT ON CONFLICT UPDATE, когда часть обновления имеет большое количество условий CASE WHEN

CREATE TABLE employee (
  emp_id INTEGER,
  name TEXT,
  dept TEXT,
  PRIMARY KEY (emp_id)
);

INSERT INTO
  employee
VALUES
  (1, 'John', 'Sales');

MERGE INTO
  employee   AS e
USING
(
  VALUES
    (1, NULL,   'IT'),
    (2, 'Jack', 'Sales')
)
  AS t (emp_id, name, dept)
    ON e.emp_id = t.emp_id
WHEN MATCHED THEN
  UPDATE SET
    name = COALESCE(t.name, e.name),
    dept = COALESCE(t.dept, e.dept)
WHEN NOT MATCHED THEN
  INSERT
    (  emp_id,   name,   dept)
  VALUES
    (t.emp_id, t.name, t.dept);

SELECT * FROM employee

рабочий пример

Я столкнулся с точно такой же ситуацией, когда использую PostgreSQL 14, я использовал INSERT ON CONFLICT UPDATE.

1. Можно ли это сделать (используя INSERT ON CONFLICT UPDATE с условиями CASE WHEN)?

Да, выполнение условных обновлений с использованием предложения INSERT ON CONFLICT UPDATE вместе с CASE WHEN является одним из допустимых подходов к условным обновлениям при обновлении в PostgreSQL. Это позволяет вам контролировать обновление столбцов на основе некоторых условий для каждой строки. С другой стороны, этот подход достаточно сложен, и дальнейшее увеличение условий приводит к ухудшению производительности.

2. Есть ли проблемы с производительностью при выполнении этого способа, как показано ниже? Повлияет ли размер запроса на производительность? Или использовать эти условия CASE в части обновления? Другие проблемы с производительностью?

Да, и вот некоторые из вероятных проблем с производительностью:

• Размер и сложность запроса. Чем больше условий CASE WHEN в запросе, тем больше и сложнее он становится, что потенциально приводит к увеличению времени анализа, планирования и выполнения.
• Условия перепроверки: существует вероятность того, что для каждой строки одно и то же условие проверяется снова и снова, например, emp_id. Это приведет к проблемам с производительностью, если пакеты будут огромными.
• Увеличение использования памяти. Более крупные запросы потребляют больше памяти, что снижает производительность, что особенно ощущается в средах с высоким трафиком.

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

Параметры обычно передаются дважды, если запрос использует параметры как в частях INSERT, так и в UPDATE. Использование именованных параметров не снижает накладные расходы, поскольку механизму базы данных по-прежнему приходится анализировать и планировать обе части запроса независимо. Очевидного способа обойти это дублирование не существует, но вы можете уменьшить его влияние, упростив запрос, например, используя COALESCE.

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

Другой способ, который может быть еще лучше:

• COALESCE с EXCLUDED: упрощает логику UPDATE, обновляя столбцы только в том случае, если входящие значения не равны NULL, и сохраняют свое существование в противном случае.
• Использование Common Table Expression (CTE) для подготовки данных перед выполнением обновления поможет.
• JSONB/JSON: если изменения в столбце очень динамичны, это одно из тех мест, где вы можете хранить свои данные в столбце JSONB и управлять частью приложения с частичными обновлениями.
• Использование внешних инструментов. Для больших пакетных обновлений использование таких инструментов, как pg_bulkload или COPY, в сочетании с временными таблицами может быть быстрее.