Go и PostgreSQL: быстрая вставка большого объёма данных

Когда вы вставляете в PostgreSQL сотни строк по одной — каждый INSERT это отдельный сетевой round-trip. На 10 000 строк это 10 000 обращений к базе. В статье про драйверы PostgreSQL я упоминал batch и COPY как способ ускорить вставку. Но на практике часто нужен не просто INSERT, а upsert — ON CONFLICT ... DO UPDATE. COPY этого не умеет.

В этой статье сравним два SQL-паттерна для массовой вставки с upsert: multi-row VALUES и UNNEST. С кодом на pgx, ограничениями и бенчмарками на объёмах от 100 до 10 000 строк.

Задача

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

CREATE TABLE events (
    bid TEXT NOT NULL,
    date DATE NOT NULL,
    app_type INT NOT NULL,
    event_count BIGINT NOT NULL,
    PRIMARY KEY (bid, date, app_type)
);

Требование — ON CONFLICT (bid, date, app_type) DO UPDATE: если запись с таким ключом уже есть, обновляем event_count.

Все примеры ниже используют pgx v5 — нативный Go-драйвер для PostgreSQL.

Подход 1: отдельные INSERT в цикле

Самый простой и самый медленный способ:

func insertNaive(ctx context.Context, pool *pgxpool.Pool, events []Event) error {
    for _, e := range events {
        _, err := pool.Exec(ctx,
            `INSERT INTO events (bid, date, app_type, event_count)
             VALUES ($1, $2, $3, $4)
             ON CONFLICT (bid, date, app_type) DO UPDATE SET
               event_count = EXCLUDED.event_count`,
            e.Bid, e.Date, e.AppType, e.EventCount,
        )
        if err != nil {
            return err
        }
    }
    return nil
}

100 строк = 100 сетевых round-trip. На 10 000 строках такой запрос занимает уже более 6 секунд. В продакшене так делать не стоит, но для базового подхода сгодится.

Подход 2: multi-row VALUES

Вместо 100 отдельных INSERT используем один запрос с 100 строками в VALUES:

INSERT INTO events (bid, date, app_type, event_count)
VALUES ($1, $2, $3, $4), ($5, $6, $7, $8), ...
ON CONFLICT (bid, date, app_type) DO UPDATE SET
  event_count = EXCLUDED.event_count

Реализация на Go с динамической сборкой запроса:

func insertMultiRowValues(ctx context.Context, pool *pgxpool.Pool, events []Event) error {
    // 4 параметра на строку
    valueStrings := make([]string, 0, len(events))
    args := make([]interface{}, 0, len(events)*4)

    for i, e := range events {
        base := i * 4
        valueStrings = append(valueStrings,
            fmt.Sprintf("($%d, $%d, $%d, $%d)",
                base+1, base+2, base+3, base+4))
        args = append(args, e.Bid, e.Date, e.AppType, e.EventCount)
    }

    query := fmt.Sprintf(
        `INSERT INTO events (bid, date, app_type, event_count)
         VALUES %s
         ON CONFLICT (bid, date, app_type) DO UPDATE SET
           event_count = EXCLUDED.event_count`,
        strings.Join(valueStrings, ", "))

    _, err := pool.Exec(ctx, query, args...)
    return err
}

Получаем один round-trip вместо ста. Но есть ограничение: PostgreSQL поддерживает максимум 65 535 параметров в одном запросе. При 4 колонках это 16 383 строки — дальше нужно разбивать на чанки:

const maxParams = 65535
const colCount = 4

func insertInChunks(ctx context.Context, pool *pgxpool.Pool, events []Event) error {
    chunkSize := maxParams / colCount // 16383

    for i := 0; i < len(events); i += chunkSize {
        end := i + chunkSize
        if end > len(events) {
            end = len(events)
        }
        if err := insertMultiRowValues(ctx, pool, events[i:end]); err != nil {
            return err
        }
    }
    return nil
}

Есть ещё один нюанс. Для каждого размера батча генерируется уникальный SQL-запрос. Это означает, что PostgreSQL каждый раз заново его парсит и планирует. При частых вызовах с разными размерами батчей это создаёт нагрузку на планировщик. Если последний чанк отличается по размеру от остальных, то это уже два разных запроса в кэше.

Подход 3: UNNEST

UNNEST принимает массивы и разворачивает их в строки:

INSERT INTO events (bid, date, app_type, event_count)
SELECT * FROM unnest($1::text[], $2::date[], $3::int[], $4::bigint[])
ON CONFLICT (bid, date, app_type) DO UPDATE SET
  event_count = EXCLUDED.event_count

Реализация на Go:

func insertUnnest(ctx context.Context, pool *pgxpool.Pool, events []Event) error {
    bids := make([]string, len(events))
    dates := make([]time.Time, len(events))
    appTypes := make([]int32, len(events))
    eventCounts := make([]int64, len(events))

    for i, e := range events {
        bids[i] = e.Bid
        dates[i] = e.Date
        appTypes[i] = int32(e.AppType)
        eventCounts[i] = e.EventCount
    }

    _, err := pool.Exec(ctx,
        `INSERT INTO events (bid, date, app_type, event_count)
         SELECT * FROM unnest(
           $1::text[],
           $2::date[],
           $3::int[],
           $4::bigint[]
         )
         ON CONFLICT (bid, date, app_type) DO UPDATE SET
           event_count = EXCLUDED.event_count`,
        bids, dates, appTypes, eventCounts,
    )
    return err
}

Количество параметров всегда 4. Хоть 100 строк, хоть 100 000. SQL-запрос один и тот же, так что PostgreSQL может его закэшировать как prepared statement. И ограничение на 65 535 параметров здесь не действует.

Подход 4: COPY

COPY передаёт данные потоком в бинарном формате, без разбора SQL. На чистой загрузке (без upsert) он быстрее всех остальных подходов:

func insertCopy(ctx context.Context, pool *pgxpool.Pool, events []Event) error {
    rows := make([][]interface{}, len(events))
    for i, e := range events {
        rows[i] = []interface{}{e.Bid, e.Date, e.AppType, e.EventCount}
    }

    _, err := pool.CopyFrom(ctx,
        pgx.Identifier{"events"},
        []string{"bid", "date", "app_type", "event_count"},
        pgx.CopyFromRows(rows),
    )
    return err
}

Проблема одна: COPY не поддерживает ON CONFLICT. Если нужен upsert, то в чистом виде он не подходит.

В качестве обходного пути можно загрузить данные через COPY во временную таблицу, а потом выполнить INSERT ... SELECT ... ON CONFLICT из неё:

func insertCopyUpsert(ctx context.Context, pool *pgxpool.Pool, events []Event) error {
    tx, err := pool.Begin(ctx)
    if err != nil {
        return err
    }
    defer tx.Rollback(ctx)

    // Создаём временную таблицу с той же структурой
    _, err = tx.Exec(ctx, `CREATE TEMP TABLE events_staging (LIKE events) ON COMMIT DROP`)
    if err != nil {
        return err
    }

    // Загружаем данные через COPY
    rows := make([][]interface{}, len(events))
    for i, e := range events {
        rows[i] = []interface{}{e.Bid, e.Date, e.AppType, e.EventCount}
    }

    _, err = tx.CopyFrom(ctx,
        pgx.Identifier{"events_staging"},
        []string{"bid", "date", "app_type", "event_count"},
        pgx.CopyFromRows(rows),
    )
    if err != nil {
        return err
    }

    // Переносим в основную таблицу с upsert
    _, err = tx.Exec(ctx,
        `INSERT INTO events (bid, date, app_type, event_count)
         SELECT bid, date, app_type, event_count FROM events_staging
         ON CONFLICT (bid, date, app_type) DO UPDATE SET
           event_count = EXCLUDED.event_count`)
    if err != nil {
        return err
    }

    return tx.Commit(ctx)
}

Кода больше: временная таблица, транзакция, два этапа. Но на сотнях тысяч строк COPY+staging может обогнать UNNEST за счёт скорости загрузки. В бенчмарках ниже COPY измеряется без upsert — просто как ориентир по скорости чистой загрузки.

Бенчмарки

Методология

• PostgreSQL 16 в Docker (testcontainers-go)
• Go 1.25, pgx v5, AMD Ryzen 5 PRO 4650U, Linux
• Каждый бенчмарк запускался 3 раза (-count=3)
• Перед каждой итерацией таблица очищается (TRUNCATE)
• COPY — без ON CONFLICT, остальные — с upsert

Подробнее о бенчмарках в Go: Бенчмарки и оптимизация в Go.

Результаты

100 строк

Даже на 100 строках разница между поштучной вставкой и батчевой — в 8 раз. UNNEST и VALUES примерно одинаковы по скорости, но UNNEST расходует вдвое меньше памяти на стороне Go-клиента (48 KB против 91 KB) — ему не нужно формировать длинный SQL-запрос со всеми placeholder’ами.

1 000 строк

На тысяче строк UNNEST обгоняет VALUES на 25%, а разрыв по памяти растёт: 455 KB у UNNEST против 1 163 KB у VALUES. Формирование строки запроса с тысячей строк в VALUES дорого обходится и по CPU, и по памяти.

5 000 строк

UNNEST быстрее VALUES почти вдвое. На 5 000 строках VALUES формирует SQL-запрос с 20 000 placeholder’ов — PostgreSQL тратит заметное время на его разбор. UNNEST передаёт те же данные в 4 параметрах-массивах.

10 000 строк

UNNEST вдвое быстрее VALUES и расходует втрое меньше памяти. 16 МБ у VALUES уходит на формирование SQL-строки с 40 000 placeholder’ов — это уже ощутимо для GC.

Сводная таблица: во сколько раз быстрее Naive

Чем больше данных, тем заметнее разрыв. У UNNEST запрос не растёт с количеством строк, и PostgreSQL проще обработать 4 массива, чем распарсить VALUES на 40 000 placeholder’ов.

Транзакции и обработка ошибок

Все батчевые подходы выполняются атомарно: если в середине батча возникает ошибка (нарушение ограничений, дедлок), откатывается весь батч. VALUES и UNNEST — это один SQL-запрос, PostgreSQL откатит его сам. Для COPY+staging атомарность обеспечивает транзакция.

Это стоит учитывать при выборе размера батча. Если один из 10 000 элементов нарушает ограничение — вы теряете весь батч. Самый надёжный вариант — валидировать данные на стороне Go до вставки. Если ошибка всё-таки прилетела, можно разбить упавший батч из 1 000 строк на 10 по 100 и повторить — так вы быстро найдёте проблемную запись. Для более точного контроля есть SAVEPOINT: оборачиваете вставку в транзакцию с savepoint и откатываете только часть батча.

Отдельная история — конкурентный upsert. Если два процесса одновременно вставляют строки с одинаковым ключом, ON CONFLICT DO UPDATE может вызвать дедлок. Мы решали это сортировкой строк по ключу (bid, date, app_type) перед вставкой — одинаковый порядок блокировок исключает взаимное ожидание.

Когда что использовать

Если нужен upsert, я бы по умолчанию брал UNNEST. Фиксированное число параметров, нет лимита на 65 535, запрос кэшируется как prepared statement, памяти ест меньше. Работает одинаково хорошо и на 100 строках, и на 100 000.

Multi-row VALUES проще в написании и на небольших батчах (до нескольких сотен строк) работает не хуже. Если вам не нужно вставлять тысячи строк за раз и лимит параметров не грозит, это вполне рабочий вариант.

Если upsert не нужен, COPY будет быстрее всего. Для upsert можно использовать схему с временной таблицей (пример выше), но код получается сложнее.

Поштучные INSERT — только для единичных записей. Если вставляете больше десятка строк за раз, любой из батчевых подходов будет в разы быстрее.

Заключение

На 10 000 строк разница между поштучным INSERT и UNNEST — 30 раз. Между VALUES и UNNEST — 2 раза. При этом UNNEST ещё и памяти ест втрое меньше.

Код с UNNEST чуть более многословный — нужно формировать отдельный слайс для каждой колонки. Но запрос не зависит от размера батча и не упирается в лимит параметров. В нашем проекте мы перешли на UNNEST с VALUES примерно полгода назад и ни разу не пожалели.

FAQ

Теги:

• golang