Как ускорить массовую загрузку данных

Макс только собирался налить себе кофе, как в мессенджере всплыло сообщение от Саши:

— Привет, нужна консультация.

— Что случилось?

— Ничего критичного, просто загрузка данных в analytics.daily_events всё медленнее и медленнее.

— Значит, пришло время провести техобслуживание, — улыбнулся Макс. — Показывай, как грузишь.

Саша расшарил экран: обычные INSERT ... VALUES (...) в батче.

— Логично и просто, — заметил Макс. — Но PostgreSQL — не любит «по чуть-чуть». Если хочешь, чтобы он ел быстро, подавай ему целыми блюдами, а не по крошкам.

1. COPY — главный ускоритель

— Для загрузки данных у PostgreSQL есть специнструмент — COPY. Он пишет сразу большими блоками, минимизируя транзакционные накладные расходы.

— То есть быстрее, чем INSERT?

— В разы. Главное — подготовить файл CSV, но вам, программистам, это как два байта переслать. Дальше — магия расположения. Если пишешь COPY в SQL-скрипте, то файл должен физически лежать на сервере с PostgreSQL, и у базы должны быть права на его чтение. А если ты, как настоящий джедай, работаешь через консоль psql и используешь команду \copy (с косым слешем), то файл может лежать прямо у тебя под рукой — на той машине, где ты эту команду выполняешь.

Макс набросал пример вызова:

psql -h db_host -U etl_user -d analytics_db -c "\copy analytics.daily_events FROM '/data/events.csv' WITH (FORMAT csv, HEADER)"

— А можно как-то мониторить прогресс?

— Конечно, через запрос к pg_stat_progress_copy.

2. Не обходите триггеры вслепую

— Отключение пользовательских триггеров иногда ускоряет контролируемую загрузку, но это уже не обычная оптимизация, — продолжил Макс. — Триггеры могут обеспечивать аудит, денормализацию и бизнес-инварианты. Сначала лучше грузить данные в staging-таблицу, проверить их и выполнить обычную вставку в целевую таблицу.

— Если команда всё же осознанно выбрала отключение, нужны согласованное окно, точный список пропущенной логики, план повторной проверки и гарантированное включение триггеров даже после ошибки:

ALTER TABLE analytics.daily_events DISABLE TRIGGER USER;

— TRIGGER USER не отключает внутренние триггеры ограничений, но отключает все пользовательские — не только «дорогие». После загрузки их нужно включить обратно и доказать, что данные соответствуют логике, которая временно не выполнялась:

ALTER TABLE analytics.daily_events ENABLE TRIGGER USER;

3. Индексы: не всегда зло, но иногда тормоз

Саша нахмурился:

— А индексы мешают?

— Зависит от объёма, числа и типа индексов, доступного диска, WAL, репликации и требований к доступности, — ответил Макс. — Для небольшой порции пересоздание индексов почти наверняка дороже. Для первоначальной загрузки пустой таблицы часто выгоднее сначала загрузить данные, а затем построить индексы.

— А как понять, что объем большой?

— Универсального порога в процентах нет. Замерь оба сценария на копии реальных данных. На действующей таблице удаление индекса ещё и лишает обычные запросы нужного пути доступа, поэтому цена решения — не только время загрузки.

Финальный штрих

— После загрузки обязательно обнови статистику, — добавил Макс.

ANALYZE analytics.daily_events;

— Ага. Что-то еще?

— Для воспроизводимых промежуточных данных можно рассмотреть UNLOGGED-таблицы. Изменения их данных не проходят обычное WAL-журналирование, они не реплицируются на физические стендбаи и очищаются после аварийного восстановления. Для единственной копии ценных данных они не подходят.

Саша кивнул:

— Понял. Значит, сначала COPY, потом — отключаем триггеры при необходимости, а индексы только если объём реально большой?

— Именно. Сначала измеряем COPY, затем проверяем узкие места. Любое отключение защиты или удаление индексов — отдельное архитектурное решение, а не пункт универсального рецепта.

Через пару дней Саша снова написал:

«Макс, теперь заливка за 18 минут! Спасибо!»

Макс улыбнулся. Иногда, чтобы ускорить процесс, достаточно просто знать, в каком порядке открывать двери.