Пакетная обработка в PostgreSQL

Привет, Хабр!

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

В PostgreSQL пакетная обработка реализуется с помощью массовых вставок, обновлений, удалений или переноса данных между таблицами .

В этой статье рассмотрим, как реализовать пакетную обработку в PostgreSQL.

Способы пакетной обработки данных

Команда COPY - самый хороший способ массового ввода и вывода данных. Она позволяет быстро загружать данные из файла в табличку БД или экспортировать данные из таблицы в файл. COPY использует высокопроизводительный протокол для перемещения данных между файлом и таблицей:

COPY my_table (column1, column2, column3)
FROM '/path/to/file.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

Тут данные из файла file.csv загружаются в таблицу my_table. Опции FORMAT csv, HEADER true и DELIMITER ',' указывают на формат файла, наличие заголовков и разделитель столбцов соответственно.

COPY поддерживает различные форматы файлов, а также может использоваться в транзакциях.

Временные таблицы TEMPORARY или TEMP могут быть использованы для промежуточного хранения данных.

Пример:

BEGIN;

-- создание временной таблицы
CREATE TEMP TABLE temp_table AS
SELECT id, new_value
FROM source_table;

-- обновление основной таблицы на основе временной
UPDATE main_table
SET column_name = temp_table.new_value
FROM temp_table
WHERE main_table.id = temp_table.id;

-- удаление записей на основе временной таблицы
DELETE FROM main_table
USING temp_table
WHERE main_table.id = temp_table.id;

COMMIT;

Временная таблица temp_table используется для обновления и удаления записей в основной таблице main_table.

Для вставки большого количества записей можно использовать многоразовые вставки, которые выполняются в одном запросе, например:

INSERT INTO my_table (column1, column2, column3)
VALUES 
  (value1_1, value1_2, value1_3),
  (value2_1, value2_2, value2_3),
  (value3_1, value3_2, value3_3);

Для операций посложней можно использовать процедуры на PL/pgSQL:

DO $$
BEGIN
  FOR i IN 1..1000 LOOP
    INSERT INTO my_table (column1, column2)
    VALUES (i, i * 2);
  END LOOP;
END $$;

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

Преимущества использования многоразовых вставок и PL/pgSQL:

• Сокращение накладных расходов на обработку транзакций.

• Повышение производительности за счет пакетной обработки данных.

Как все это дело оптимизировать

Перед выполнением массовых операций рекомендуется временно отключить индексы на целевой табличке, т.к обновление индексов при каждой вставке или обновлении строки существенно замедляет процесс. После завершения операции индексы можно пересоздать.

Отключаем индексы:

DROP INDEX index_name1, index_name2, ...;

Выполняем массовую операцию, например вставку данных.

Восстанавливаем индексы:

CREATE INDEX index_name1 ON table_name(column1);
CREATE INDEX index_name2 ON table_name(column2);

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

Отключаем триггеры:

ALTER TABLE table_name DISABLE TRIGGER ALL;

После выполнения массовых операций включаем с помощью ENABLE.

Плюсом можно подключить нелогируемые таблицы и поработать с настройкой work_mem.

Примеры

Массовая вставка с использованием команды COPY и временной деактивации индексов:

-- отключение индексов перед вставкой данных
DROP INDEX IF EXISTS idx_column1, idx_column2;

-- массовая вставка данных с использованием команды COPY
COPY target_table (column1, column2)
FROM '/path/to/your/file.csv'
WITH (FORMAT csv, HEADER true);

-- восстановление индексов после вставки данных
CREATE INDEX idx_column1 ON target_table (column1);
CREATE INDEX idx_column2 ON target_table (column2);

Пакетное обновление данных с использованием временной таблички:

BEGIN;

-- создание временной таблицы
CREATE TEMP TABLE temp_updates AS
SELECT id, new_value FROM source_table;

-- обновление основной таблицы на основе данных из временной таблицы
UPDATE target_table
SET column_to_update = temp_updates.new_value
FROM temp_updates
WHERE target_table.id = temp_updates.id;

COMMIT;

Пакетное удаление данных с PL/pgSQL:

BEGIN;

-- отключение всех триггеров на целевой таблице
ALTER TABLE target_table DISABLE TRIGGER ALL;

-- пакетное удаление данных с использованием PL/pgSQL
DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT id FROM source_table WHERE condition) LOOP
        DELETE FROM target_table WHERE id = r.id;
    END LOOP;
END $$;

-- включение всех триггеров на целевой таблице
ALTER TABLE target_table ENABLE TRIGGER ALL;

COMMIT;

---

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