Мне нужно загрузить данные в базу данных PostgreSQL из приложения с высоким трафиком, которое необходимо оптимизировать для производительности записи. Различные строки, добавляемые в пакете, будут иметь значения для разных столбцов. Это не полные обновления всех столбцов. И это будут обновления, поэтому строки необходимо будет вставить или обновить.
Моя идея состоит в том, чтобы выполнить INSERT ON CONFLICT UPDATE, где в части обновления я бы использовал условия CASE WHEN для записи в часть обновления данных для обновления для каждой строки на основе идентификаторов. Я не могу просто установить для столбцов значение EXCLUDED.column_name, поскольку не для всех входящих строк будут установлены все строки. Некоторые будут иметь NULL.
Я хотел бы знать:
Пример моей идеи для этого:
Схема (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;
🤔 А знаете ли вы, что...
SQL позволяет создавать ограничения целостности данных, такие как уникальность и внешние ключи.
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 в части обновления? Другие проблемы с производительностью?
Да, и вот некоторые из вероятных проблем с производительностью:
3. Есть ли способ передать параметры в базу данных только один раз, а не повторять их для частей вставки и обновления? Будет ли для этого использоваться именованные параметры или они тоже передаются дважды?
Параметры обычно передаются дважды, если запрос использует параметры как в частях INSERT, так и в UPDATE. Использование именованных параметров не снижает накладные расходы, поскольку механизму базы данных по-прежнему приходится анализировать и планировать обе части запроса независимо. Очевидного способа обойти это дублирование не существует, но вы можете уменьшить его влияние, упростив запрос, например, используя COALESCE.
4. Если это не лучший способ сделать это, как бы вы это сделали? Существует ли стандартный способ пакетного обновления данных такого типа с разными столбцами, предназначенными для вставки разных строк?
Другой способ, который может быть еще лучше: