Как ускорить вставку 100 млн строк в PostgreSQL в 40 раз

Почему ваша массовая вставка в PostgreSQL работает так медленно?

Представьте: вы загружаете 100 миллионов строк в две связанные таблицы. Вы написали очевидный цикл, запустили его и пошли за кофе. Возвращаетесь — а он выполнен на 4%. Никаких блокировок, пропущенных индексов или N+1 запросов. План запроса идеален, код делает ровно то, что должен, но завершится он только завтра после обеда. А вы уже пообещали команде, что всё будет готово до ланча. Знакомая ситуация? В этой статье я покажу, как сократить время загрузки с 15 часов до 14 минут, изменив подход к генерации первичных ключей.

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

Рассмотрим типичный сценарий: таблицы band и song, связанные внешним ключом. Вы вставляете группу, сбрасываете сессию, чтобы получить её ID, и только потом вставляете песни. Этот flush() — не просто дополнительная задержка, это зависимость. Без ID песню не создать, а значит, вы не можете распараллелить загрузку или начать вставку песен, пока не вставлены все группы. Вы написали не медленный цикл, а цикл, который останавливается, чтобы спросить разрешения 100 миллионов раз. На моём ноутбуке это дало 1790 строк в секунду.

Быстрый фикс: INSERT ... RETURNING с батчингом

Первая мысль — ускорить запрос. SQLAlchemy предлагает insertmanyvalues, который группирует вставки в несколько больших запросов INSERT ... RETURNING и возвращает тысячу ID за раз:

band_ids = session.scalars(
    insert(Band).returning(Band.id, sort_by_parameter_order=True),
    [{"name": name} for name in names],
).all()
session.execute(
    insert(Song),
    [{"band_id": band_ids[i], "title": t}
     for i, tracks in enumerate(tracklists) for t in tracks]
)

sort_by_parameter_order=True гарантирует соответствие между входными строками и возвращёнными ID. Это ускоряет загрузку в 15 раз, но не решает корневую проблему: всё ещё есть фаза ожидания ответа, и вы не можете запустить две таблицы параллельно.

Решение: не спрашивайте ключ у базы

Есть два способа знать ключ до вставки. Первый — генерировать его на клиенте. Используйте uuid7 вместо uuid4 для первичного ключа, чтобы сохранить порядок в индексе. Пример на Python:

class Band(Base):
    __tablename__ = "band"
    id: Mapped[uuid.UUID] = mapped_column(primary_key=True, default=uuid.uuid7)
    name: Mapped[str]
    songs: Mapped[list["Song"]] = relationship(back_populates="band")

class Song(Base):
    __tablename__ = "song"
    id: Mapped[uuid.UUID] = mapped_column(primary_key=True, default=uuid.uuid7)
    band_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("band.id"))
    band: Mapped[Band] = relationship(back_populates="songs")
    title: Mapped[str]

session.add_all([
    Band(name=name, songs=[Song(title=t) for t in tracks])
    for name, tracks in lineup
])
session.commit()

Теперь нет ни flush(), ни RETURNING. SQLAlchemy заполняет дефолты на клиенте и пакетно вставляет все строки. На моей машине это 25 000 строк за 25 INSERT-запросов.

Второй способ — зарезервировать блок ID из последовательности. Если вы используете bigint identity, получите блок значений заранее:

SELECT nextval('band_id_seq'); -- получаете 1, значит блок 1..250000 ваш
-- ... строим граф объектов с этими ID ...
SELECT setval('band_id_seq', 250000); -- двигаем последовательность дальше

Это работает, если вы единственный, кто пишет в таблицу. Иначе используйте ALTER SEQUENCE ... INCREMENT BY 250000, чтобы раздавать блоки атомарно. Избегайте классического hi-lo, так как он конфликтует с прямым чтением последовательности.

Сравнение скорости: 15 часов → 36 минут

Вот результаты замеров на моём ноутбуке (100 млн строк):

• Цикл с flush(): 1790 строк/с — 15 часов
• INSERT ... RETURNING с батчингом: 26 746 строк/с — 1 час 2 минуты
• UUID4 на клиенте: 46 334 строк/с — 36 минут
• Зарезервированный блок bigint: 46 120 строк/с — 36 минут

Обратите внимание: последние два варианта почти одинаковы по скорости, но UUID4 требует больше места в индексе. Если заменить UUID4 на UUID7, скорость вырастет ещё больше.

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

COPY — самый быстрый способ загрузки данных в PostgreSQL, но он не возвращает сгенерированные ключи. Поэтому он недоступен, пока вы зависите от генерации ключей на стороне базы. С клиентскими ключами это ограничение исчезает:

with cursor.copy("COPY band (id, name) FROM STDIN") as copy:
    for band in bands:
        copy.write_row((band.id, band.name))

Результаты с COPY:

• UUID4: 72 599 строк/с — 23 минуты
• UUID7: 106 118 строк/с — 16 минут
• Зарезервированный bigint: 116 322 строк/с — 14 минут

15 часов превратились в 14 минут. И это не предел: четыре параллельных воркера дают 225 940 строк/с (7 минут), но это уже сложнее: нужна обработка ошибок и фрагментация индекса.

Почему UUID7 лучше UUID4

UUID4 — это 16 случайных байт, поэтому каждая вставка попадает в случайное место индекса, вызывая его фрагментацию. UUID7 содержит временную метку в старших битах, поэтому ключи сортируются в порядке создания, и вставки идут в горячую правую страницу индекса. При нехватке памяти на индексе разница огромна: 8 блоков чтения с диска вместо 325 278, и индекс на 20% меньше.

Что не покрыто в этой статье

Мы не обсуждали транзакции, обработку ошибок, логирование и параллельные воркеры. Эти аспекты важны для продакшена, но выходят за рамки темы. Также помните: COPY — это не просто ускоренный INSERT, это другой механизм, который не поддерживает RETURNING.

Практический вывод

Прямо сейчас откройте свой проект и посмотрите, генерируете ли вы первичные ключи на стороне базы при массовой вставке. Если да — переходите на UUID7 (или резервирование блоков) и используйте COPY для загрузки. Это может сократить время ваших миграций с часов до минут. Начните с малого: возьмите одну таблицу и измерьте разницу. Вы удивитесь, насколько это просто и эффективно.