Массовая загрузка данных в PostgreSQL: COPY и bulk INSERT

Загрузить миллион строк построчными INSERT — значит ждать часами. PostgreSQL предоставляет несколько механизмов массовой загрузки, которые работают в десятки и сотни раз быстрее. Разберём их от простого к сложному.

Почему одиночные INSERT медленные

Каждый INSERT — это отдельная транзакция: разбор запроса, планирование, запись в WAL, обновление индексов, commit. При 100 тысячах строк это 100 тысяч циклов записи на диск.

-- Сгенерируем тестовые данные для экспериментов
CREATE TABLE events (
    id         bigserial PRIMARY KEY,
    user_id    bigint,
    event_type text,
    payload    jsonb,
    created_at timestamptz DEFAULT now()
);

-- Медленно: 100к строк построчно может занять минуты
INSERT INTO events (user_id, event_type, payload)
VALUES (1, 'click', '{"page": "/home"}');
-- ... ещё 99 999 раз

-- Для тестирования: быстрая генерация данных через generate_series
INSERT INTO events (user_id, event_type, payload)
SELECT
    (random() * 10000)::bigint,
    (ARRAY['click', 'view', 'purchase', 'scroll'])[ceil(random() * 4)::int],
    jsonb_build_object('page', '/page-' || (random() * 100)::int)
FROM generate_series(1, 100000);
-- Это уже значительно быстрее — один запрос вместо 100к

Multi-row INSERT: батчи значений

Вместо одиночных вставок объединяйте данные в один запрос. Накладные расходы на парсинг и планирование платятся один раз:

-- Хорошо: один запрос, несколько строк
INSERT INTO events (user_id, event_type, payload)
VALUES
    (1,    'click',    '{"page": "/home"}'),
    (2,    'view',     '{"page": "/catalog"}'),
    (3,    'purchase', '{"amount": 1500}'),
    (4,    'scroll',   '{"depth": 80}');

-- Оптимальный размер батча: 100–1000 строк за один INSERT
-- Слишком большой батч (10k+) может занять много памяти

В Python с psycopg2 используйте execute_values для эффективной пакетной вставки:

import psycopg2.extras

data = [(user_id, event_type, payload), ...]  # список кортежей

psycopg2.extras.execute_values(
    cur,
    "INSERT INTO events (user_id, event_type, payload) VALUES %s",
    data,
    page_size=500  # размер батча
)

COPY FROM: максимальная скорость

COPY — самый быстрый способ загрузки данных в PostgreSQL. Он обходит механизм индивидуального планирования запросов и работает напрямую с хранилищем.

-- Загрузка из CSV файла
COPY events (user_id, event_type, payload)
FROM '/tmp/events.csv'
WITH (
    FORMAT csv,
    HEADER true,
    DELIMITER ',',
    NULL ''
);

-- Загрузка из TSV (tab-separated)
COPY events (user_id, event_type)
FROM '/tmp/events.tsv'
WITH (FORMAT text, DELIMITER E'\t');

-- Экспорт данных в CSV
COPY (SELECT * FROM events WHERE created_at > '2026-01-01')
TO '/tmp/events_export.csv'
WITH (FORMAT csv, HEADER true);

Из Python через copy_expert (данные не касаются файловой системы сервера):

import io

# Подготовить данные в памяти как CSV
buffer = io.StringIO()
for row in data:
    buffer.write(f"{row['user_id']}\t{row['event_type']}\t{row['payload']}\n")
buffer.seek(0)

cur.copy_expert(
    "COPY events (user_id, event_type, payload) FROM STDIN WITH (FORMAT text, DELIMITER E'\\t')",
    buffer
)
conn.commit()

Настройки сессии для ускорения ETL

Несколько параметров, которые можно изменить только для текущей сессии, не трогая глобальную конфигурацию:

-- Отключить синхронный коммит: данные записываются в WAL асинхронно
-- Риск: при сбое сервера последние ~0.5 сек транзакций могут потеряться
-- Приемлемо для ETL-загрузки некритичных данных
SET synchronous_commit = off;

-- Увеличить объём памяти для операций сортировки и хеш-соединений
SET work_mem = '256MB';

-- Увеличить размер WAL буфера для текущей сессии (Pg 14+)
-- SET wal_level = minimal; -- только если нет репликации!

-- После загрузки — вернуть настройки
RESET synchronous_commit;
RESET work_mem;

UNLOGGED таблицы для промежуточных данных

Если таблица используется как временная площадка для ETL — например, staging-таблица перед финальным INSERT — сделайте её UNLOGGED. Такие таблицы не пишут в WAL и работают в несколько раз быстрее:

-- Создать unlogged staging таблицу
CREATE UNLOGGED TABLE events_staging (
    LIKE events INCLUDING ALL
);

-- Загружаем данные максимально быстро
COPY events_staging FROM '/tmp/events.csv' WITH (FORMAT csv, HEADER true);

-- Обрабатываем и переносим в основную таблицу
INSERT INTO events
SELECT * FROM events_staging
WHERE user_id IS NOT NULL
  AND event_type IS NOT NULL;

-- Очищаем
TRUNCATE events_staging;

Важно: при сбое сервера UNLOGGED таблица будет автоматически очищена — это нормально для staging-данных, которые можно загрузить повторно.

Отключение индексов для массовой загрузки

Индексы обновляются при каждой вставке. Для одноразовой загрузки больших объёмов выгоднее удалить индексы, загрузить данные, затем создать индексы заново:

-- 1. Запомнить и удалить индексы (кроме PRIMARY KEY)
SELECT indexdef FROM pg_indexes
WHERE tablename = 'events' AND indexname != 'events_pkey';

DROP INDEX IF EXISTS idx_events_user_id;
DROP INDEX IF EXISTS idx_events_created_at;

-- 2. Загрузить данные (без накладных расходов на индексирование)
COPY events FROM '/tmp/big_events.csv' WITH (FORMAT csv);

-- 3. Создать индексы после загрузки
-- CONCURRENTLY не нужен — данные уже загружены, таблица не используется активно
CREATE INDEX idx_events_user_id ON events(user_id);
CREATE INDEX idx_events_created_at ON events(created_at);

-- 4. Обновить статистику
ANALYZE events;

Сравнение методов загрузки

Контроль прогресса долгой загрузки

-- Следить за прогрессом COPY
SELECT
    relname,
    phase,
    tuples_processed,
    tuples_excluded
FROM pg_stat_progress_copy;

-- Следить за размером таблицы в процессе загрузки
SELECT
    relname,
    pg_size_pretty(pg_relation_size(oid)) AS size,
    n_live_tup AS live_rows
FROM pg_class
JOIN pg_stat_user_tables ON relid = oid
WHERE relname = 'events';

Итог

Для загрузки больших объёмов данных: используйте COPY FROM когда данные в файлах, execute_values или аналоги для вставки из кода, UNLOGGED таблицы для staging, SET synchronous_commit = off для некритичных данных. Комбинация этих техник ускоряет ETL-процессы в десятки и сотни раз по сравнению с построчными вставками.

Отработайте работу с большими данными в SQL-тренажёре — практические задачи на реальных датасетах без установки PostgreSQL на компьютер.