Заполнение новых полей
Новая колонка difficulty создана, а старые курсы по-прежнему содержат уровень в properties. Теперь нужно перенести значения и подготовить обязательность поля. На большой таблице одна массовая команда может долго удерживать изменения и создавать значительную работу сопровождения. Backfill делит заполнение на ограниченные пакеты, сохраняя явный критерий оставшегося набора.
Используем PostgreSQL 17.11 и снимок catalog-db-advanced/lesson-28 из архива продолжения. Он уже содержит состояние миграции два: difficulty существует и у всех шести курсов NULL. SQL не запускался. Маленький размер нужен для объяснения ожидаемых пакетов, а не для измерения поведения миллионов строк.
Условия начала переноса
Сначала приложение должно перейти на согласованных писателей: новый уровень сохраняется и в difficulty, и в прежнем JSON-ключе, пока старые читатели ещё используют его. Если старый писатель после backfill меняет только properties, новая колонка устареет. Пакетный перенос не исправляет несовместимый протокол приложения автоматически.
Исходные JSON-уровни в нашем снимке допустимы. В настоящем наборе перед заполнением проверяют неизвестные значения, отсутствие ключа и неверный тип. Отдельная диагностическая выборка должна объяснить такие строки редактору. Не подставляем произвольный beginner вместо неизвестного состояния только ради исчезновения NULL.
Планируем один worker для понятного порядка. Он выбирает только незаполненные допустимые строки, упорядочивает по ключу и ограничивает пакет двумя объектами. Число два — учебный параметр, не рекомендация одинакового размера для любого production-сервера.
Один подтверждённый пакет
Полный backfill-batch.sql содержит guard и следующий блок:
BEGIN;
WITH batch AS (
SELECT id FROM catalog.courses
WHERE difficulty IS NULL
AND properties->>'level' IN ('beginner', 'intermediate', 'advanced')
ORDER BY id LIMIT 2 FOR UPDATE
)
UPDATE catalog.courses AS c
SET difficulty = c.properties->>'level'
FROM batch
WHERE c.id = batch.id AND c.difficulty IS NULL
RETURNING c.id, c.difficulty;
COMMIT;После свежего reset первый пакет должен заполнить ключи один и два. Второй — три и четыре. Третий — пять и шесть. В четвёртом вызове возвращённых строк не ожидается. Порядок строк самого RETURNING не требуется считать порядком интерфейса: ключи показывают состав пакета независимо от оформления ответа.
Колонка меняется только у незаполненного объекта. Повторный запуск после уже подтверждённого пакета не переписывает сохранённый уровень новой формы. Условие дополнительно повторено в UPDATE, а строки выбраны с блокировкой внутри короткой транзакции. Значение берётся из текущего защищённого объекта по согласованному протоколу писателей.
Это техническое копирование одинакового видимого уровня, а не новое решение редактора о сложности курса. Поэтому пример не увеличивает версию формы ради изменения представления хранения. Если приложение меняет сам уровень, оно должно соблюдать прежний редакторский version-протокол. Смысл версии определяется защищаемым представлением, а не каждым служебным UPDATE без разбора.
Прогресс по данным
После каждого пакета можно прочитать число difficulty IS NULL. Ожидаются четыре, два, ноль. Эти числа относятся к фиксированным шести строкам и выведены из условий, а не измерены запущенной программой. lesson.sql содержит чтение состояния и отдельную выборку неподходящих старых значений.
Если пакет вернул ноль, это не всегда означает полный успех. Могли остаться строки с NULL и недопустимым properties.level, которые не проходили отбор. Поэтому завершение определяется общей диагностикой, а не только пустым RETURNING. Неразрешённые случаи должны остаться видимыми, пока не принято предметное решение.
Сохраняемый курсор максимального id тоже требует осторожности при параллельных worker и пропуске занятых строк. Если курсор уйдёт вперёд, исключённая старая строка может остаться позади. В нашем варианте нет SKIP LOCKED и единственный worker каждый раз выбирает актуальные незаполненные строки. Это проще объяснить и не создаёт такого скрытого пропуска.
Пакеты уменьшают одну границу фиксации, но не гарантируют отсутствие влияния на нагрузку. Размер, пауза и число worker выбираются по наблюдению ресурса. В серии нет фактического rate, времени обновления или количества WAL, поэтому мы не обещаем определённую скорость по длине CTE.
Проверка новых записей отдельно
Когда все старые значения перенесены и писатели готовы, добавим правило присутствия без немедленной проверки всех старых строк:
BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE catalog.courses
ADD CONSTRAINT course_difficulty_present
CHECK (difficulty IS NOT NULL) NOT VALID;
COMMIT;Это отдельный install-presence-check.sql. NOT VALID не означает игнорирование правила для будущих изменений. Оно откладывает проверку существующего набора; новые затрагиваемые строки должны соответствовать ограничению. Поэтому вводить его при неподготовленных писателях опасно для пользовательского сценария, даже если файл очень короткий.
После отдельной фиксации выполняется validate-presence.sql:
ALTER TABLE catalog.courses
VALIDATE CONSTRAINT course_difficulty_present;Команды разделены, чтобы структурная блокировка добавления не удерживалась вместе с полной проверкой в одном длинном блоке. Но это не обещание вообще отсутствующих блокировок: режимы и конкуренция конкретной операции всё равно имеют значение. Правила NOT VALID и VALIDATE описаны в справочнике ALTER TABLE PostgreSQL.
Повтор пакета после неизвестного результата
Пакет переноса отличается от повторной редакторской правки. Он выбирает только строки с отсутствующей difficulty и копирует уже допустимый уровень из текущего документа. Если предыдущий пакет успел зафиксироваться, но ответ клиенту потерялся, повторный запуск не обязан выбирать те же идентификаторы: заполненные строки больше не подходят условию. Так намерение «заполнить ещё незаполненное» сохраняется без счётчика последнего id в памяти.
Это свойство относится к показанному предикату, а не ко всем UPDATE автоматически. Если бы пакет каждый раз увеличивал цену или version, повтор после неизвестного результата давал бы дополнительный эффект. Поэтому перенос не увеличивает предметную version: видимый уровень курса не меняется, меняется место его хранения. Обычное редактирование уровня остаётся другой операцией и должно выполнять свой договор версии.
Рассмотрим строку с level равным неизвестному значению. Она не проходит фильтр допустимых уровней, поэтому четвёртый пустой пакет ещё не доказывает завершения всего переноса. Диагностическая выборка оставшихся NULL должна объяснить, почему они остались. Исправление данных проводится осознанно, а не подстановкой beginner всем неподходящим документам ради успешного NOT NULL.
Ужесточение происходит отдельными этапами. Сначала новый CHECK NOT VALID ограничивает новые изменения, затем validation рассматривает сохранённые строки, и лишь потом устанавливается окончательный договор колонки. Если проверка не проходит, финальный шаг не объявляется готовым. Такой порядок позволяет отделить работу пакетов от изменения обязательности поля и не скрывать проблему неприменённой миграцией в истории.
Обязательная колонка
Только после успешной валидации finalize.sql устанавливает NOT NULL и записывает историю версии три:
BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE catalog.courses ALTER COLUMN difficulty SET NOT NULL;
INSERT INTO catalog.schema_migrations(version, name)
VALUES (3, 'filled-difficulty');
COMMIT;В PostgreSQL подходящее уже валидное CHECK может позволить избежать повторного сканирования для доказательства отсутствия NULL. Это конкретное правило системы, а не универсальный обход проверки. Мы не измеряли ни один этап и не обещаем нулевую длительность структурного изменения.
История три появляется вместе с финальным состоянием, поэтому клиент не считает заполнение законченным только по успешному первому пакету. Если проверка не проходит, задача остаётся незавершённой и требует чтения оставшихся строк. Не отключайте ограничение ради красивой отметки в migration history.
После будущего завершения шесть уровней должны совпадать с прежним JSON. Ключи, планы и двенадцать учебных глав остаются прежними. Новый читатель может опираться на difficulty, а удаление старого использования properties.level рассматривается отдельной поздней фазой совместимости. Теперь перенос данных имеет ограниченный пакет, измеримый по состоянию критерий окончания и подтверждённую границу обязательности, сохраняя предметный смысл каталога.