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

Мне нужно загрузить данные в базу данных PostgreSQL из приложения с высоким трафиком, которое необходимо оптимизировать для производительности записи. Различные строки, добавляемые в пакете, будут иметь значения для разных столбцов. Это не полные обновления всех столбцов. И это будут обновления, поэтому строки необходимо будет вставить или обновить.

Моя идея состоит в том, чтобы выполнить INSERT ON CONFLICT UPDATE, где в части обновления я бы использовал условия CASE WHEN для записи в часть обновления данных для обновления для каждой строки на основе идентификаторов. Я не могу просто установить для столбцов значение EXCLUDED.column_name, поскольку не для всех входящих строк будут установлены все строки. Некоторые будут иметь NULL.

Я хотел бы знать:

  1. Это нормальный способ сделать это (см. пример ниже)?
  2. Есть ли проблемы с производительностью при этом, как показано ниже? Повлияет ли размер запроса на производительность? Или использовать эти условия CASE в части обновления? Другие проблемы с производительностью?
  3. Есть ли способ передать параметры в базу данных только один раз, а не повторять их для частей вставки и обновления? Будет ли для этого использоваться именованные параметры или они тоже передаются дважды?
  4. Если это не лучший способ сделать это, как бы вы это сделали? Существует ли стандартный способ пакетного обновления данных такого типа с разными столбцами, предназначенными для вставки разных строк?

Пример моей идеи для этого:

Схема (PostgreSQL v16)

CREATE TABLE employees (emp_id INTEGER, name TEXT, department TEXT,
PRIMARY KEY (emp_id));

Запрос №1

INSERT INTO employees VALUES (1, 'john', 'sales');

Запрос №2

INSERT INTO employees (emp_id, name, department) VALUES (1, DEFAULT, 'it'), 
(2, 'jack', 'sales') 
ON CONFLICT (emp_id) DO UPDATE SET name = CASE 
WHEN employees.emp_id=1 THEN employees.name 
WHEN employees.emp_id=2 THEN 'jack' END, 
department = CASE WHEN employees.emp_id=1 THEN 'it' 
WHEN employees.emp_id=2 THEN 'sales' END
WHERE employees.emp_id IN (1, 2);

Ожидается, что запрос № 2 вставит нового сотрудника и обновит отдел сотрудника Джона до «это».

Запрос №3

SELECT * FROM employees;
emp_id имя отделение 1 Джон это 2 Джек продажи

🤔 А знаете ли вы, что...
SQL позволяет создавать ограничения целостности данных, такие как уникальность и внешние ключи.


59
2

Ответы:

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
emp_id имя отдел 1 Джон ЭТО 2 Джек Продажи

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


Решено

Я столкнулся с точно такой же ситуацией, когда использую 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, в сочетании с временными таблицами может быть быстрее.

Интересные вопросы для изучения

Проверка SQL-кода на работу с SOAP, проблема с получением данныхКак получить значение столбца с соответствующим максимальным значением другого столбцаSQL-запрос для группировки параметров в один столбец с одинаковым идентификаторомКак суммировать результаты двух разных запросов по разным столбцам?Посчитайте только рабочие дни (исключая выходные и праздничные дни) и добавьте фильтр, соответствующий запросуPostgreSQL: запрос с ORDER BY, LIMIT и OFFSET последовательно возвращает одну и ту же запись в конце набора результатов независимо от значения OFFSETКак я могу избежать «ОШИБКИ: нет уникального ограничения, соответствующего заданным ключам для ссылочной таблицы» при ссылке на первичный ключ комбинации в Postgres?Использование TG_TABLE_NAME в триггерной функции postgresql в запросе SELECTЕсть ли в Postgresql способ сгладить или агрегировать вложенные массивы JSONB, которые можно использовать с сгенерированными сохраненными столбцами?Использование Postgres 16 с Spring Boot 3.3.0