Сценарий: pandas-to-SQL bulk insert 50 миллионов строк

Условие

Нужно загрузить 50M строк из pandas DataFrame в PostgreSQL. Наивный df.to_sql идёт 3 часа. Как ускорить?

Решение

Замеры

Базовое ускорение

from sqlalchemy import create_engine
 
engine = create_engine('postgresql+psycopg2://user:pwd@host/db')
 
df.to_sql(
    'events',
    engine,
    if_exists='append',
    index=False,
    method='multi',
    chunksize=10_000,
)

method='multi' пакует строки в один INSERT ... VALUES (...), (...), ....

Лучшее — COPY

PostgreSQL COPY — единственный по-настоящему быстрый способ.

import io
import psycopg2
 
buf = io.StringIO()
df.to_csv(buf, index=False, header=False, sep='\t', na_rep='\\N')
buf.seek(0)
 
conn = psycopg2.connect('...')
with conn.cursor() as cur:
    cur.copy_expert(
        "COPY events FROM STDIN WITH (FORMAT csv, DELIMITER E'\t', NULL '\\N')",
        buf,
    )
conn.commit()

Подводные оптимизации

• Снять индексы на время загрузки, потом построить.
• ALTER TABLE events SET UNLOGGED; и обратно (если можно потерять при крэше).
• Загружать в staging-таблицу, потом INSERT ... SELECT в основную с дедупом.

ClickHouse

df.to_csv('events.csv', index=False, header=False)
# clickhouse-client --query "INSERT INTO events FORMAT CSV" < events.csv

или clickhouse_connect с прямым client.insert_df(df).

Подводные камни

1. to_sql(method=None) шлёт строку за строкой через round-trip — катастрофически медленно.
2. chunksize без method='multi' ничего не даёт — это просто разбиение на батчи, но всё ещё построчно.
3. NaN в pandas vs NULL в SQL — без na_rep='\\N' в COPY превратится в строку «NaN» в колонке.
4. Даты должны быть в pg-совместимом формате (YYYY-MM-DD HH:MM:SS) — df.to_csv обычно справляется.
5. Индексы на таблице замедляют COPY в 5-10×. Снимать до и строить после.

Эталонный ответ

Для PostgreSQL — psycopg2.copy_expert с CSV-stream-ом (3 мин против 3 ч). Если нельзя — to_sql(method='multi', chunksize=10_000). Снять индексы на время загрузки.