Миграция больших таблиц: пакетная обработка и ограничение нагрузки

Прямой ответ

Таблица в сотни миллионов строк не переносится одной командой: транзакция удерживает блокировки, требует места для журнала записи и прерывается по таймауту. Перенос выполняется пакетами с ограничением нагрузки.

Выборка пакета выполняется по ключу, а не смещением:

-- корректно: выборка по диапазону ключа
SELECT id, customer_id, total
  FROM orders
 WHERE id > 15000000
 ORDER BY id
 LIMIT 5000;

-- некорректно: OFFSET требует просмотра пропущенных строк
SELECT id, customer_id, total
  FROM orders
 ORDER BY id
 OFFSET 15000000 LIMIT 5000;

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

Запись выполняется пакетом в отдельной транзакции:

BATCH = 5000
PAUSE = 0.15

last_id = read_checkpoint()
while True:
    rows = fetch_batch(last_id, BATCH)
    if not rows:
        break
    with db.transaction():
        bulk_insert(target, rows)
        save_checkpoint(rows[-1].id)
    last_id = rows[-1].id
    time.sleep(PAUSE)

Три параметра определяют влияние на работающий сервис.

Размер пакета. Крупный пакет выполняется дольше и удерживает блокировки дольше. Мелкий пакет увеличивает долю накладных расходов. Обычно от тысячи до десяти тысяч строк.

Пауза между пакетами. Определяет долю ресурсов, которую занимает перенос. Пауза в 150 миллисекунд при времени пакета 50 миллисекунд означает, что перенос занимает примерно четверть времени.

Число параллельных работников. Параллельные потоки ускоряют перенос, но увеличивают нагрузку на базу и вероятность конфликтов блокировок.

Влияние на базу не ограничивается блокировками. Каждый обновлённый либо вставленный ряд создаёт запись в журнале и, при обновлении, мёртвую версию строки. Миллионы обновлений приводят к разрастанию таблицы, требующему очистки; о механизме — в статье про VACUUM и bloat.

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

Как размер пакета влияет на результат

Размер пакета — единственный параметр, который одновременно определяет длительность транзакции, объём блокировок и скорость переноса. Три режима дают качественно разные результаты.

Мелкие пакеты (до тысячи строк). Транзакция короткая, блокировки удерживаются доли секунды, продуктивная нагрузка почти не ощущается. Накладные расходы на фиксацию каждой транзакции снижают общую скорость, поэтому перенос занимает больше времени.

Средние пакеты (несколько тысяч строк). Баланс между длительностью транзакции и накладными расходами. Подходит для большинства случаев, когда таблица используется продуктивно.

Крупные пакеты (десятки тысяч строк и более). Скорость переноса максимальна, но транзакция занимает секунды и дольше, блокировки удерживаются заметное время, а рост журнала транзакций может потребовать дополнительного места.

Практическое правило: размер подбирается по допустимой длительности транзакции, а не по желаемой скорости переноса. Если продуктивные запросы не должны ждать дольше ста миллисекунд, пакет ограничивается объёмом, обрабатываемым за это время.

Параллельный перенос несколькими потоками увеличивает скорость, но требует disjoint диапазонов и учёта суммарной нагрузки. Два потока с пакетами по десять тысяч строк создают нагрузку, сопоставимую с одним потоком с пакетами по двадцать тысяч.

Практика: перенос большой таблицы

1. Оцените объём и время до начала. Число строк, размер строки и измеренная скорость пакета дают прогноз; о процессе — в статье про миграцию данных.

2. Выбирайте пакеты по диапазону ключа. Смещение деградирует с ростом позиции и делает перенос нелинейно долгим.

3. Троттлите нагрузку паузами. Доля ресурсов, занимаемая переносом, управляется отношением времени пакета к паузе.

4. Сохраняйте контрольную точку с каждым пакетом. Продолжение после сбоя экономит часы; о механизме — в статье про отслеживание прогресса.

5. Создавайте индексы после переноса. Поддержка индекса во время массовой вставки замедляет каждый пакет.

6. Используйте массовую вставку. Пакетная вставка одним запросом быстрее построчной в несколько раз.

7. Мониторьте блокировки и отставание реплик. Перенос создаёт нагрузку, которая влияет на продуктивные запросы и на репликацию.

8. Планируйте очистку после завершения. Разрастание таблицы после миллионов обновлений требует обработки.

Рекомендации:

1. Начинайте с малого размера пакета и увеличивайте. Измерение влияния на задержку продуктивных запросов показывает допустимый размер.
2. Не выполняйте перенос в часы пиковой нагрузки. Даже троттлинг создаёт нагрузку, которая суммируется с продуктивной.
3. Измеряйте скорость, а не оценивайте. Прогноз по первым пакетам точнее оценки по объёму.
4. Обрабатывайте ошибки пакетов отдельно. Сбой одного пакета не должен останавливать перенос; ошибочные строки фиксируются списком.
5. Проверяйте идемпотентность повторного пакета. Возобновление с контрольной точки может повторить частично обработанный пакет.

Практический сценарий: рост задержки продуктивных запросов во время переноса

Контекст: перенос 120 миллионов строк запустили пакетами по 50 тысяч без пауз. Через несколько минут задержка продуктивных запросов выросла втрое.

1. Диагностика. Перенос занимал почти всё время работы базы: пакеты выполнялись один за другим без пауз. Дополнительно каждый пакет обновлял индексы целевой таблицы, что увеличивало время записи.
2. Причина. Размер пакета выбран по объёму памяти, а не по влиянию на нагрузку. Паузы отсутствовали, индексы целевой таблицы существовали до начала переноса.
3. Действия. (а) Перенос приостановили и возобновили с контрольной точки; (б) размер пакета снизили до пяти тысяч строк с паузой 200 миллисекунд; (в) индексы целевой таблицы удалили на время переноса и создали после завершения; (г) добавили мониторинг задержки продуктивных запросов с автоматической приостановкой переноса при превышении порога; (д) массовую вставку перевели на единый запрос на пакет вместо построчной.
4. Результат. Перенос занял 14 часов вместо ожидаемых шести, но задержка продуктивных запросов осталась в пределах нормы. Создание индексов после завершения заняло 40 минут.

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

FAQ

Почему нельзя использовать OFFSET для выборки пакетов? Смещение требует просмотра пропущенных строк, поэтому каждый следующий пакет выполняется дольше. Выборка по диапазону ключа использует индекс.

Какой размер пакета выбрать? От тысячи до десяти тысяч строк, при условии что транзакция завершается за доли секунды. Размер уточняется измерением влияния на задержку.

Когда создавать индексы новой таблицы? После завершения переноса. Поддержка индекса во время массовой вставки замедляет каждый пакет.

Что делать с разрастанием таблицы после переноса? Очистка выполняется автоматически либо принудительно при значительном объёме мёртвых версий.

Источники: PostgreSQL docs (COPY, EXPLAIN, Routine Vacuuming) — ссылки в шапке.