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

Пройдите тест, узнайте какой профессии подходите и получите бесплатную карьерную консультацию
В конце подарим скидку до 55% на обучение
Я предпочитаю
0%
Работать самостоятельно и не зависеть от других
Работать в команде и рассчитывать на помощь коллег
Организовывать и контролировать процесс работы

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

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

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

Пройдите тест и узнайте подходит ли вам сфера IT
Пройти тест

Применение unnest() для массовых вставок

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

SQL
Скопировать код
INSERT INTO your_table(id, column1, column2)
SELECT * FROM unnest(
  array[1, 2], 
  array['ValueA', 'ValueC'], 
  array['ValueB', 'ValueD']
);

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

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

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

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

SQL
Скопировать код
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 позволяет добиваться значительных успехов:

Java
Скопировать код
@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-данных:

SQL
Скопировать код
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

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

SQL
Скопировать код
INSERT INTO your_table(id, column1, column2)
VALUES 
  (3, 'ValueE', 'ValueF'),
  (4, 'ValueG', 'ValueH'),
  (5, 'ValueI', 'ValueJ'); 

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

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

Используйте блок DO для сложных операций

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

SQL
Скопировать код
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. Пока цикл идет – процесс обновления не прекращается.

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

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

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

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

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

Markdown
Скопировать код
🏭: Каждая коробка проходит проверку, при необходимости обновляется новыми деталями или остается без изменений, после чего все изменения сохраняются одновременно.
  • 🔄🔍: Поиск места для коробки.
  • 📦🆕📦: Обновление коробки или добавление новой.
  • 💾: Массовое сохранение всех изменений.

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

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

Java
Скопировать код
List<YourEntity> entities = session.createQuery("FROM YourEntity WHERE id IN :ids")
.setParameter("ids", idList)
.list();

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

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

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

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

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.