Быстрая массовая вставка данных в Postgres: лучшие решения

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

Если вашей целью является достижение максимальной скорости при массовом добавлении данных в Postgres, стоит использовать команду COPY. Она представляет собой наиболее эффективный метод обработки больших объемов данных. Воспользуйтесь следующим синтаксисом:

COPY ваша_таблица FROM '/путь/до/data.csv' WITH (FORMAT csv);

Данные в формате CSV должны соответствовать схеме таблицы в базе данных, чтобы их можно было успешно добавить. При работе с приложениями на Python и использовании библиотеки Psycopg2, процесс массового добавления можно упростить, применив метод copy_from():

import psycopg2

with psycopg2.connect("dbname=ваша_бд user=ваш_пользователь") as conn:
    with conn.cursor() as cur, open('/путь/до/data.csv', 'r') as f:
        cur.copy_from(f, 'ваша_таблица', sep=",")  # Вас ждёт максимальная скорость!

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

Подготовка для максимальной скорости

Для ускорения процесса подготовьте вашу систему:

• Индексы: Отключите их на время массового добавления данных, а в конце процесса снова включите. Это позволит операциям проходить быстрее, обходя затратный процесс обновления индексов.

• Ограничения внешнего ключа: Их временное отключение также способствует ускорению процесса. После добавления данных, включите ограничения обратно, чтобы гарантировать целостность данных.

Автоматизация для массового добавления

Множественная вставка с использованием INSERT и объединение в потоки

Команда множественного INSERT может стать хорошей альтернативой команде COPY для выполнения небольших задач:

INSERT INTO ваша_таблица (колонки)
VALUES
(значение1_строки1, значение2_строки1),
(значение1_строки2, значение2_строки2),
...
(значение1_строкиN, значение2_строкиN); # Отправляем всё сразу!

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

Подробнее об этом расскажет наш спикер на видео

Ускорение с использованием массивов данных

Сочетание функции UNNEST и многорядной конструкции VALUES обеспечивает высокую производительность при работе с массивами:

INSERT INTO ваша_таблица (кол1, кол2)
SELECT * FROM UNNEST (массив1, массив2); # Разворачиваем возможности UNNEST!

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

Плавная интеграция после массового добавления

Временные таблицы

Использование временных таблиц обеспечивает безопасное хранение промежуточных данных, это особенно удобно при работе с форматами CSV или JSON:

CREATE TEMP TABLE временная_таблица AS
SELECT * FROM ваша_таблица WITH NO DATA; # Временное решение, но очень эффективное!

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

Pgloader – перенос данных на максимальной скорости

Инструмент Pgloader обеспечивает высокую скорость, быстро перекачивая данные через STDIN, при этом поддерживая работу с сжатыми файлами:

pgloader /путь/до/data.csv postgresql:///ваша_бд?tablename=ваша_таблица # Вы на экспрессе Pgloader!

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

Настраиваем двигатель для максимальной производительности

Регулировка буферов журнала WAL и использование подготовленных выражений

Увеличение размера буфера wal_buffers до 16МБ поможет обработать большие объемы данных при массовом добавлении. Больший размер буфера для журнала предварительной записи (WAL) облегчает операции добавления.

Настройте лимиты на подготовленные выражения чтобы они соответствовали масштабу Вашей операции добавления данных. Это позволит Postgres эффективно повторно использовать планы запросов для похожих пакетов данных.

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

Массовое добавление данных можно представить как штурм крепости Postgres:

Способы массовой вставки:       | Скорость:
--------------------------------|----------
Команда COPY (🚅)               | Мгновенная
INSERT INTO ... VALUES (🚌)     | Надежная, но не очень быстрая
Пакетный INSERT (🚗✨)           | Эффективный на коротких дистанциях
Внешние инструменты (🚀)         | Идеальны для крупногабаритных задач

С помощью метода COPY вы достигнете супер скорости!

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

Обеспечение целостности данных и управление ошибками

Чтобы процесс массового добавления данных проходил без ошибок, следуйте следующим рекомендациям:

• Предварительная проверка: Убедитесь, что ваши данные соответствуют структуре и ограничениям целевой таблицы.
• Транзакционность: Используйте транзакции, чтобы иметь возможность откатить все изменения в случае возникновения ошибок.
• Логгирование ошибок: Ведите журнал операций добавления, чтобы иметь информацию о возможных сбоях и нарушениях ограничений.

Обязательные шаги после добавления данных

Не забудьте выполнить следующие действия после окончания процесса добавления данных:

• Восстановление индексов: Это критически важный шаг для обеспечения быстродействия при работе с таблицами.
• Очистка: Выполните команду VACUUM ANALYZE, чтобы оптимизировать работу базы данных.
• Проверка данных: Убедитесь, что все данные были добавлены корректно и их целостность не была нарушена.

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

1. Документация PostgreSQL: COPY — подробное руководство по использованию команды COPY.
2. Наиболее быстрый способ добавления данных в PostgreSQL с использованием Python — пошаговое руководство по написанию скриптов на Python.
3. Производительность добавления данных – Вики PostgreSQL — советы по оптимизации процесса добавления данных.
4. Связь между многорядовым добавлением и WAL – 2ndQuadrant — взгляд на взаимосвязь между многорядной вставкой и буфером предварительной записи.
5. PostgreSQL: Документация: psql — инструмент psql для управления массовыми операциями добавления данных.
6. Как ускорить процесс добавления данных в PostgreSQL – Stack Overflow — сборник советов по оптимизации процесса добавления данных.
7. PostgreSQL: CREATE TABLE — используйте незалогированные таблицы для ускоренной обработки данных.