Backfill: как заполнить новую колонку в таблице с миллионами строк

Прямой ответ

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

Наивный вариант:

ALTER TABLE orders ADD COLUMN currency text NOT NULL DEFAULT 'RUB';
UPDATE orders SET currency = 'RUB' WHERE currency IS NULL;

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

Рабочий вариант — пакетное заполнение:

UPDATE orders
   SET currency = 'RUB'
 WHERE id IN (
     SELECT id FROM orders
      WHERE currency IS NULL
      ORDER BY id
      LIMIT 5000
 )

Каждый пакет выполняется отдельной транзакцией, поэтому блокировки удерживаются короткое время, а сбой прерывает только текущий пакет.

Отдельный случай — добавление колонки со значением по умолчанию. Начиная с PostgreSQL 11 добавление колонки с постоянным значением по умолчанию не переписывает таблицу: значение хранится в метаданных и подставляется при чтении. Поэтому явный UPDATE после такого добавления часто не нужен.

Три решения, которые нужно принять до начала.

1. Значение по умолчанию или вычисляемое. Постоянное значение заполняется быстро. Вычисляемое из других полей требует чтения каждой строки и занимает существенно больше времени.

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

3. Ограничение NOT NULL сразу или позже. Добавление ограничения требует проверки всех строк, поэтому при пакетном заполнении ограничение добавляется после завершения.

Скорость заполнения ограничивают намеренно, чтобы не повлиять на работающий сервис:

for batch in iter_batches():
    update(batch)
    time.sleep(0.2)   # пауза между пакетами

Пауза снижает среднюю нагрузку на базу; о влиянии на блокировки — в статье про deadlock-и.

Что влияет на длительность заполнения

Время backfill определяется не только числом строк, но и четырьмя факторами, которые стоит оценить до начала.

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

Наличие индекса для поиска незаполненных строк. Без индекса каждый пакет выполняет полное сканирование таблицы. Частичный индекс по условию пустого значения устраняет сканирование, но требует создания до начала.

Число индексов на таблице. Каждый индекс обновляется при каждой вставке либо обновлении. Таблица с пятью индексами заполняется заметно медленнее таблицы без них.

Параллельная нагрузка. Заполнение делит ресурсы базы с продуктивными запросами. В часы пиковой нагрузки допустимая скорость заполнения ниже, чем ночью.

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

Оценка длительности выполняется по измеренной скорости первых пакетов, а не по среднему времени запроса. Первые пакеты выполняются на холодном кеше, поэтому оценка по ним консервативна.

Практика: выполнение backfill

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

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

3. Используйте индекс для поиска незаполненных строк. Частичный индекс по условию WHERE column IS NULL ускоряет выборку очередного пакета.

4. Троттлите нагрузку. Пауза между пакетами снижает влияние на продуктивные запросы.

5. Сохраняйте прогресс. Продолжение после сбоя с последней позиции вместо повторного сканирования; о механизме — в статье про отслеживание прогресса.

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

7. Добавляйте ограничение NOT NULL после заполнения. Проверка ограничения на незаполненной таблице завершится ошибкой.

8. Планируйте очистку после завершения. Миллионы обновлённых строк создают мёртвые версии, требующие обработки.

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

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

Практический сценарий: деградация сервиса во время заполнения колонки

Контекст: требовалось добавить колонку категории и заполнить её вычисляемым значением для 25 миллионов заказов. Заполнение запустили одним запросом.

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

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

FAQ

Нужен ли UPDATE после добавления колонки со значением по умолчанию? Для постоянного значения — обычно нет: значение хранится в метаданных. Для вычисляемого значения заполнение требуется.

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

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

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

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