Оптимизация batch update/upsert в PostgreSQL 8.4 и Django

Быстрый ответ

Для массового добавления данных с возможностью слияния в PostgreSQL используйте конструкцию INSERT ... ON CONFLICT. Она позволяет не только добавлять новые строки, но и обновлять уже существующие строки на основе уникальных ограничений, например, id. Показательный пример:

INSERT INTO your_table(id, column1, column2)
VALUES 
  (1, 'ValueA', 'ValueB'),
  (2, 'ValueC', 'ValueD')
ON CONFLICT (id) DO UPDATE 
  SET column1 = EXCLUDED.column1,
      column2 = EXCLUDED.column2;

В представленном примере при наличии значения id в базе данных обновляются значения column1 и column2. Если такого значения нет, произойдет вставка новых строк. Псевдотаблица EXCLUDED используется для обозначения значений, которые предполагалось вставить.

Применение

Со времени выхода версии PostgreSQL 8.4 стала доступна функция unnest(), предназначенная для преобразования массивов в строки. Это идеально подходит для массовых вставок. Вот как это работает:

INSERT INTO your_table(id, column1, column2)
SELECT * FROM unnest(
  array[1, 2], 
  array['ValueA', 'ValueC'], 
  array['ValueB', 'ValueD']
);

-- Вы только что успешно использовали "unnest" и получили кучу данных вместо шоколадки.

С помощью этого подхода с массивами можно сократить количество SQL-операций и повысить эффективность работы с большим объемом данных.

Используйте

Если вам нужно выполнить обновление при более сложных условиях, UPDATE...FROM станет прекрасным решением:

UPDATE your_table
SET column1 = data.column1,
    column2 = data.column2
FROM (VALUES
  (1, 'NewValueA', 'NewValueB'),
  (2, 'NewValueC', 'NewValueD')
) AS data(id, column1, column2)
WHERE your_table.id = data.id;

-- Это почти как "убить двух зайцев одним выстрелом", только зайцы остаются невредимыми.

Использование UPDATE...FROM позволяет сократить количество операций и ускорить процесс работы.

Оптимизация с Hibernate Batch

Если вы используете JDBC в связке с Hibernate, стратегический выбор момента очистки сессии и верной настройки параметра @BatchSize в Hibernate позволяет добиваться значительных успехов:

@BatchSize(size = 50) // Устанавливаем размер пакета
public class YourEntity { ... }
...

session.save(newEntity); 

if (i % 50 == 0) { 
  session.flush();
  session.clear();
}

// Hibernate позволяет сделать такой же резонанс в SQL-операциях, как Netflix в мире телесериалов.

Такой подход значительно повышает эффективность SQL-операций.

Внедрение JSON-данных при массовом обновлении

Для пользователей PostgreSQL 9.3 функция json_array_elements() станет настоящим подарком. Ее можно использовать для массового обновления с использованием JSON-данных:

UPDATE your_table
SET column1 = data.value->>'column1',
    column2 = data.value->>'column2'
FROM json_array_elements('[
  {"id": 1, "column1": "JsonValA", "column2": "JsonValB"},
  {"id": 2, "column1": "JsonValC", "column2": "JsonValD"}
]') AS data
WHERE your_table.id = (data.value->>'id')::int;

-- Когда JSON встречается с PostgreSQL: SQL-сказка для разработчиков.

Этот метод преобразует JSON-массив в последовательность строк, облегчая процесс обновления данных.

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

С помощью ключевого слова VALUES можно произвести сразу несколько вставок одним запросом:

INSERT INTO your_table(id, column1, column2)
VALUES 
  (3, 'ValueE', 'ValueF'),
  (4, 'ValueG', 'ValueH'),
  (5, 'ValueI', 'ValueJ'); 

-- Это SQL-аналог акции "купи одно, получи два": можно вставить три строки единым запросом!

Такой подход существенно упрощает массовые операции и делает вашу базу данных довольной.

Используйте блок

Если требуется выполнить сложное обновление, включающее использование циклов, то в PostgreSQL для этого есть блок DO:

DO $$
DECLARE 
  rec RECORD;
BEGIN
  FOR rec IN SELECT * FROM your_table WHERE condition LOOP
    UPDATE your_table
    SET column1 = rec.column1 || '_updated'
    WHERE id = rec.id;
  END LOOP;
END $$;

-- Цикл в SQL. Пока цикл идет – процесс обновления не прекращается.

Следует помнить, что внедрение сложной логики в базу данных может увеличивать сложность ее обслуживания.

Визуализация

Представьте себе процесс как работу на конвейере производства, где каждая коробка — это отдельная запись для обновления:

📦📦📦    🔄🔍     📦🆕📦     💾
[Исходные]  [Проверка]   [Обновление]   [Сохранение]

Массовое слияние – это эффективная сборочная линия.

🏭: Каждая коробка проходит проверку, при необходимости обновляется новыми деталями или остается без изменений, после чего все изменения сохраняются одновременно.

• 🔄🔍: Поиск места для коробки.
• 📦🆕📦: Обновление коробки или добавление новой.
• 💾: Массовое сохранение всех изменений.

Оптимизация SELECT-запросов с помощью оператора 'IN' в Hibernate

Вместо того чтобы выполнять множество отдельных запросов SELECT, воспользуйтесь оператором IN, чтобы минимизировать их количество:

List<YourEntity> entities = session.createQuery("FROM YourEntity WHERE id IN :ids")
.setParameter("ids", idList)
.list();

// Умное использование Hibernate и оператора "IN" позволяет уменьшить количество запросов SELECT и выделить больше времени на просмотр любимых сериалов.

Это позволяет сократить число запросов, что особенно важно, когда список id известен заранее.

Обновление конкретных столбцов с использованием синтаксиса списка столбцов

Синтаксис списка столбцов в PostgreSQL позволяет точно настроить обновления с помощью строчных выражений, что оптимизирует выполнение SQL-запросов:

UPDATE your_table 
SET (column1, column2) = 
(row_data.column1, row_data.column2)
FROM (VALUES
  (1, 'TargetValueA', 'TargetValueB'),
  (2, 'TargetValueC', 'TargetValueD') 
) AS row_data(id, column1, column2)
WHERE your_table.id = row_data.id;

-- Обновление с точностью до конкретного столбца без затрагивания остальных.

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

Полезные материалы

1. PostgreSQL: Documentation: 16: INSERT — Описание конструкции ON CONFLICT в официальной документации PostgreSQL.
2. How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL? – Stack Overflow — Советы сообщества по выполнению операций UPSERT.
3. Inserting data – PostgreSQL wiki — Рекомендации по эффективной массовой вставке строк.
4. PostgreSQL: Documentation: 16: COPY — Официальная документация команды COPY для массовой обработки данных.
5. Insert, on duplicate update in PostgreSQL? – Stack Overflow — Дополнительные сведения о эффективном использовании подхода UPSERT.