Миграция из JSON и TEXT-колонок в нормализованные таблицы оправдана в одном случае: колонка хранит связанные сущности (позиции заказа, теги, участников, историю статусов), а не свободные метаданные. Признак простой: в WHERE уже появляются выражения вида jsonb_array_elements(items), JOIN строится по элементам массива, а уникальность внутри JSON поддерживается вручную в коде.
Ниже рабочий план из шести этапов: проектирование целевой схемы, двойная запись, пакетный перенос, сверка, канареечное переключение чтения, обслуживание индексов. Каждый этап обратим до момента полного переключения чтения, и именно это делает миграцию безопасной для продуктивной среды.
Ориентир по инструментам: онлайн-операции со схемой удобно делать на PostgreSQL 12 и новее, где есть REINDEX CONCURRENTLY и зрелые CONCURRENTLY-индексы. Для сравнения: Temporal Server v1.20 и выше с PostgreSQL 12 и выше использует отдельную базу под Visibility store и требует аккуратной работы со схемой и индексами, потому что она нагружена постоянно. Тот же подход применим к любой таблице, которая читается и пишется круглосуточно.
Когда пора мигрировать из JSON/TEXT в нормализованную схему
Колонка items типа TEXT с сериализованным массивом выдерживает десятки тысяч строк. На миллионах строк фильтр по элементу массива читает всю таблицу: планировщик не может использовать обычный b-tree, потому что значение внутри TEXT для него неделимо. Отсюда рост времени ответа, растущий I/O и жалобы на «тормозящий отчёт».
Симптомы, которые нельзя игнорировать
- Seq Scan по TEXT-колонке. Проверяется одним запросом:
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id
FROM orders o
WHERE EXISTS (
SELECT 1
FROM jsonb_array_elements(o.items::jsonb) AS item
WHERE item->>'sku' = 'SKU-8841'
);
В плане увидите Seq Scan on orders и строку Filter, которая выполняется для каждой строки. Стоимость растёт линейно вместе с таблицей, и никакой b-tree по items тут не поможет.
- Время ответа растёт быстрее объёма данных. Если таблица выросла в 5 раз, а отчёт стал медленнее в 20 раз, дело в плане, а не в железе.
- Невозможно поставить ON CONFLICT по элементу массива. Уникальность позиции заказа обеспечивается кодом, и рано или поздно в базе появляются дубли SKU внутри одного заказа.
- Нет referential integrity. Внешний ключ на справочник товаров не построить, потому что значение спрятано внутри строки JSON.
- Аналитика дублирует логику распаковки. Каждый дашборд содержит свой jsonb_array_elements, и формула выручки расходится между отчётами.
- Частичное обновление элементов дорого. Изменение одной позиции требует чтения, разбора, правки и записи всего массива целиком, что даёт лишний WAL и конфликты при параллельных апдейтах.
Перед срочной миграцией проверьте GIN-индекс: jsonb_path_ops ускоряет оператор содержания @> и может снять часть боли без перестройки схемы. Если запросы уже строятся вокруг ->> по элементам, GIN не спасёт, потому что оператор извлечения значения индексом такого типа не покрывается.
Когда нормализация не нужна
JSON остаётся правильным выбором для произвольных метаданных, payload событий, конфигураций с нестабильной структурой и полей, которые читают целиком и никогда не фильтруют по внутренним ключам. Миграция стоит денег: двойная запись, backfill, сверка, откат, поддержка двух схем в коде. Если структура меняется каждые пару месяцев, вы выиграете на GIN-индексе и валидации через CHECK (jsonb_typeof(items) = 'array'), а не на новой таблице.
Отдельный случай - MySQL: там выбор между нативным JSON, TEXT с сериализацией, таблицей-связкой и генеративными колонками разобран отдельно в материале про хранение массивов в MySQL, включая multi-valued индексы и пошаговую миграцию между подходами. Логика решения та же, отличается только синтаксис.
Проектирование целевой нормализованной схемы
Целевая схема должна допускать повторный запуск backfill без дублей. Добивается это естественным или составным уникальным ключом, а не проверками в коде.
Ключи, констрейнты и идемпотентность
CREATE TABLE order_items (
id BIGSERIAL PRIMARY KEY,
order_id BIGINT NOT NULL,
sku TEXT NOT NULL,
qty INTEGER NOT NULL CHECK (qty > 0),
price NUMERIC(12,2),
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT order_items_order_sku_uniq UNIQUE (order_id, sku)
);
ALTER TABLE order_items
ADD CONSTRAINT order_items_order_fk
FOREIGN KEY (order_id) REFERENCES orders(id) NOT VALID;
Первичный ключ id нужен для батчинга и точечных правок. Уникальный индекс (order_id, sku) превращает backfill в идемпотентную операцию: повторный INSERT ... SELECT с ON CONFLICT (order_id, sku) DO NOTHING не создаст дублей, даже если вы перезапустите его после сбоя.
CHECK-констрейнт ловит грязные данные сразу: qty > 0 отсекает нулевые и отрицательные позиции, jsonb_typeof на исходной колонке подтверждает, что вы разбираете массив, а не объект. Внешний ключ добавляется NOT VALID, чтобы не блокировать запись в orders на время проверки существующих строк, и валидируется позже отдельной командой:
ALTER TABLE order_items VALIDATE CONSTRAINT order_items_order_fk;
Если внутри JSON встречаются элементы без SKU, добавьте отдельную таблицу migration_errors и пишите туда проблемные строки вместо падения всей миграции. Схему и черновики миграционных скриптов удобно обкатывать на отдельном стенде: облачный сервер или managed PostgreSQL поднимается за минуты, например через Timeweb Cloud, и повторяет прод по версии СУБД.
Индексы под будущие запросы
Индексы создавайте CONCURRENTLY, иначе CREATE INDEX заблокирует запись в таблицу на всё время построения.
CREATE INDEX CONCURRENTLY idx_order_items_order_id
ON order_items (order_id);
CREATE INDEX CONCURRENTLY idx_order_items_sku
ON order_items (sku) INCLUDE (qty);
Два предупреждения. CONCURRENTLY не работает внутри транзакции, поэтому запускать его нужно отдельным psql-вызовом или миграцией с отключённым transaction wrapping. Второе: построение индекса читает всю таблицу и создаёт вторую копию индекса на диске, что даёт заметный рост I/O. На большой таблице закладывайте окно и свободное место.
Организация двойной записи без остановки сервиса
Сразу после включения двойной записи новые заказы попадают в order_items, поэтому backfill перестаёт гнаться за убегающими данными. Без этого этапа сверка никогда не сойдётся.
Триггеры vs двойная запись в приложении
Триггер внедряется без релиза приложения и работает для всех писателей сразу, включая администраторов и старые сервисы. Минусы: логика скрыта от разработчиков, добавляется нагрузка на каждую запись, ошибка в триггере откатывает всю транзакцию пользователя.
CREATE OR REPLACE FUNCTION sync_order_items() RETURNS trigger AS $$
BEGIN
INSERT INTO order_items (order_id, sku, qty, price)
SELECT NEW.id,
item->>'sku',
COALESCE((item->>'qty')::int, 1),
(item->>'price')::numeric(12,2)
FROM jsonb_array_elements(NEW.items::jsonb) AS item
WHERE item->>'sku' IS NOT NULL
ON CONFLICT (order_id, sku) DO UPDATE
SET qty = EXCLUDED.qty,
price = EXCLUDED.price;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER orders_sync_items
AFTER INSERT OR UPDATE OF items ON orders
FOR EACH ROW EXECUTE FUNCTION sync_order_items();
Application-level dual write даёт явный контроль: запись в обе схемы видна в коде, покрывается тестами, логируется и легко отключается флагом. Требует релиза и дисциплины, потому что любой новый код обязан писать в обе таблицы. Для активно развивающегося сервиса это предпочтительный путь, для legacy без релизного цикла - триггеры.
Третий вариант, если нужна развязка по времени: outbox-таблица и воркер, который разбирает события и раскладывает их в нормализованную схему. Он же закрывает случай, когда приложение пишет в MySQL, а целевая схема живёт в PostgreSQL: туда данные доезжают логической репликацией, как описано в руководстве по кешированию и синхронизации данных через логическую репликацию PostgreSQL.
Мониторинг отставания backfill
Backfill lag считается тремя числами. Снимайте их по cron каждые 5 минут и держите на дашборде:
SELECT
(SELECT COUNT(*) FROM orders
WHERE items IS NOT NULL AND items != '') AS orders_with_items,
(SELECT COUNT(DISTINCT order_id) FROM order_items) AS orders_backfilled,
(SELECT COUNT(*) FROM migration_errors) AS failed_rows;
Разница первых двух значений и есть лаг. Порог для решения о переключении чтения: лаг равен нулю и не растёт в течение нескольких циклов backfill, а failed_rows стабилен. Если лаг начал расти после завершения backfill, значит двойная запись теряет данные: триггер падает или приложение пишет только в старую схему.
Пакетный перенос JSON-массивов в строки
Основной инструмент распаковки - jsonb_array_elements в LATERAL JOIN. Он превращает один массив в набор строк, который дальше вставляется обычным INSERT ... SELECT.
jsonb_array_elements и LATERAL JOIN
- jsonb_array_elements(items) возвращает строки типа jsonb. Подходит, когда элементы - объекты и вы читаете поля через ->>.
- jsonb_array_elements_text(items) возвращает text. Подходит для массива скаляров: теги, роли, идентификаторы.
- jsonb_to_recordset(items) разворачивает массив объектов сразу в колонки заданной структуры. Быстрее и читаемее, когда схема элементов фиксирована.
Без LATERAL коррелированный подзапрос не сможет ссылаться на колонку внешней таблицы в списке FROM. CROSS JOIN LATERAL ставит функцию в зависимость от строки orders и вызывается для каждой строки отдельно.
INSERT INTO order_items (order_id, sku, qty, price)
SELECT o.id,
item->>'sku',
COALESCE((item->>'qty')::int, 1),
(item->>'price')::numeric(12,2)
FROM orders o
CROSS JOIN LATERAL jsonb_array_elements(o.items::jsonb) AS item
WHERE o.id > 100000
AND o.id <= 110000
AND o.items IS NOT NULL
AND o.items != ''
AND item->>'sku' IS NOT NULL
ON CONFLICT (order_id, sku) DO NOTHING;
Вариант для фиксированной структуры элементов, когда нужна типизация на уровне разбора:
SELECT o.id, r.sku, r.qty, r.price
FROM orders o
CROSS JOIN LATERAL jsonb_to_recordset(o.items::jsonb)
AS r(sku TEXT, qty INTEGER, price NUMERIC(12,2))
WHERE o.id > 100000 AND o.id <= 110000;
Батчинг и контроль нагрузки
Батч по 5-10 тысяч идентификаторов безопаснее одного INSERT на всю таблицу. Одна большая транзакция держит снимок, раздувает WAL, мешает autovacuum и блокирует слоты репликации. Серия коротких транзакций даёт планировщику передышку и позволяет перезапустить backfill с последнего обработанного id.
#!/usr/bin/env bash
BATCH=5000
LAST_ID=0
MAX_ID=$(psql -At -c "SELECT COALESCE(MAX(id),0) FROM orders")
while [ "$LAST_ID" -lt "$MAX_ID" ]; do
psql -v ON_ERROR_STOP=1 -c "INSERT INTO order_items (order_id, sku, qty, price)
SELECT o.id, item->>'sku',
COALESCE((item->>'qty')::int, 1),
(item->>'price')::numeric(12,2)
FROM orders o
CROSS JOIN LATERAL jsonb_array_elements(o.items::jsonb) AS item
WHERE o.id > $LAST_ID AND o.id <= $((LAST_ID + BATCH))
AND o.items IS NOT NULL AND o.items != ''
AND item->>'sku' IS NOT NULL
ON CONFLICT (order_id, sku) DO NOTHING;"
LAST_ID=$((LAST_ID + BATCH))
sleep 1
done
Пауза в секунду между батчами сглаживает пики I/O. Если база реплицируется, следите за replication lag: backfill конкурирует с репликацией за WAL sender. Общая логика переноса между системами, включая выбор между прямой загрузкой и переносом через staging, разобрана в статье про архитектуру миграции данных и ETL-процессы.
Обработка NULL, дублей и битых данных
Разбор падает на трёх вещах: NULL вместо массива, объект вместо массива, отсутствие обязательного поля в элементе. Закрывается условиями WHERE и отдельной таблицей ошибок:
CREATE TABLE migration_errors (
id BIGSERIAL PRIMARY KEY,
order_id BIGINT,
payload JSONB,
reason TEXT,
created_at TIMESTAMPTZ DEFAULT now()
);
Логируйте строки, где jsonb_typeof(items) не равен 'array', где элемент не объект, где отсутствует sku. Разбор сообщений из migration_errors удобно автоматизировать: выгрузка причин через API языковой модели и группировка по типам ошибок экономят часы ручного просмотра, для этого подойдёт агрегатор вроде AiTunnel с единым интерфейсом к разным моделям. Дубли внутри одного массива схлопываются сами: ON CONFLICT (order_id, sku) DO NOTHING оставит первую запись и не сломает батч.
Сверка целостности после переноса
Сверка нужна до переключения чтения, а не после. Доказательство состоит из двух частей: ни одна строка не потеряна и ни одно значение не искажено.
Анти-джойн для поиска пропусков
SELECT o.id FROM orders o LEFT JOIN order_items i ON i.order_id = o.id WHERE o.items IS NOT NULL AND o.items != '' AND i.order_id IS NULL LIMIT 100;
Пустой результат - обязательное условие перехода к следующему этапу. На больших таблицах ограничивайте проверку диапазоном id, чтобы запрос не читал всю базу целиком. Второй способ, который ловит расхождения в обе стороны:
SELECT DISTINCT o.id FROM orders o WHERE o.items IS NOT NULL EXCEPT SELECT DISTINCT i.order_id FROM order_items i;
Сверка агрегатов и контрольных сумм
COUNT(*) старой и новой таблиц не сойдётся, если в JSON были дубли позиций. Сравнивайте агрегаты по смыслу:
SELECT
(SELECT COUNT(DISTINCT order_id) FROM order_items) AS orders_new,
(SELECT COUNT(*) FROM order_items) AS rows_new,
(SELECT COALESCE(SUM(qty),0) FROM order_items) AS qty_new,
(SELECT COALESCE(SUM((item->>'qty')::int),0)
FROM orders o, LATERAL jsonb_array_elements(o.items::jsonb) AS item
WHERE o.items IS NOT NULL AND o.items != '') AS qty_old;
Для выборочной проверки конкретных заказов используйте checksum: md5(string_agg(sku || ':' || qty, ',' ORDER BY sku)) по order_items и такое же выражение по распакованному JSON для того же order_id. string_agg на миллионах строк дорогой, поэтому проверяйте по 200-500 заказов на батч или начните с заказов с наибольшей суммой qty. Для отчётных выгрузок дополнительно сверяйте контрольные суммы файлов: подробный разбор методов и готовые скрипты есть в руководстве по тестированию миграции данных.
Переключение чтения на новую схему
Переключение делается флагом или конфигом, а не релизом кода. Выкатка нового релиза откатывается минутами, а смена значения переменной окружения - секундами.
Канареечное переключение и сравнение ответов
Схема ступеней: 1% трафика, затем 10%, 50% и 100% с паузой на каждой ступени не меньше часа. Пока идут первые ступени, приложение выполняет оба запроса, отдаёт клиенту ответ старого пути и сравнивает результаты нового:
CREATE TABLE read_mismatches (
id BIGSERIAL PRIMARY KEY,
order_id BIGINT,
old_hash TEXT,
new_hash TEXT,
created_at TIMESTAMPTZ DEFAULT now()
);
Хеш ответа считается по отсортированному списку sku и qty, чтобы порядок строк не давал ложных расхождений. Критерий перехода на следующую ступень: за последние N часов ни одной записи в read_mismatches, latency нового пути не выше старого, ошибок SQL нет.
План отката на каждом этапе
- До включения двойной записи. Откат мгновенный: новая таблица пуста или drop без последствий, старая схема не менялась.
- После включения двойной записи, до запуска backfill. Отключить триггер или флаг dual write, удалить новые таблицы. Данные не потеряны.
- Во время backfill. Остановить скрипт, вернуться на последний успешный id. ON CONFLICT делает повторный запуск безопасным.
- После сверки, до переключения чтения. Откат всё ещё безболезненный: чтение идёт из старой схемы, новая только наполняется.
- После переключения чтения. Это точка невозврата для простого отката. Если старая схема перестала обновляться, придётся либо вернуть двойную запись в обратную сторону, либо собрать JSON обратно агрегатом jsonb_agg.
Старую колонку не удаляйте до полной уверенности. Храните её минимум один релизный цикл, лучше два, и только потом отдельной миграцией делайте ALTER TABLE ... DROP COLUMN. Перед стартом работ обязательны бэкап и snapshot: pg_dump для логического снимка, снапшот диска или managed-бэкап провайдера для быстрого восстановления всего кластера.
Обслуживание индексов и производительность в проде
Backfill и построение индексов добавляют I/O и WAL, а старые индексы после массовых вставок раздуваются. Отсюда два типовых вопроса: что запускать и когда.
REINDEX CONCURRENTLY и VACUUM: что и когда
VACUUM возвращает место в куче таблицы, но не устраняет раздувание b-tree индексов. Удаление строк по retention тоже не уменьшает bloat индексов. Для перестроения индекса используется REINDEX:
REINDEX INDEX CONCURRENTLY idx_order_items_order_id;
REINDEX CONCURRENTLY не блокирует чтение и запись, поэтому подходит для продуктивной среды. Требует примерно размер индекса в дополнительном дисковом пространстве и сопоставимый объём I/O, поэтому запускать его лучше в окно низкой нагрузки. Если места на диске мало, вариант без CONCURRENTLY заблокирует таблицу и на нагруженной системе приведёт к простою.
Окно низкой нагрузки и мониторинг
Минимальный набор метрик на время миграции:
- pg_stat_activity: количество активных запросов, ожидания на блокировках, длительность транзакций.
- pg_stat_user_indexes: idx_scan и idx_tup_read - показывают, пользуется ли приложение новыми индексами.
- Размер таблиц и индексов, чтобы видеть рост и вовремя заметить bloat.
- Скорость генерации WAL и lag реплик: backfill не должен обгонять репликацию.
- Свободное место на диске: REINDEX и новые индексы требуют запаса.
Для систем с постоянной высокой нагрузкой на схему (пример - Visibility store в Temporal Server v1.20 и выше) практика такая: тяжёлые операции обслуживания выносятся в отдельное окно, а для продакшена на масштабе выделяются отдельные ресурсы. Тяжёлую аналитику по распакованным данным тоже стоит уводить на реплику, а не выполнять на мастере во время миграции. Инструменты переноса между СУБД, включая pgloader и Flyway, и чек-лист рисков разобраны в статье про миграцию баз данных в 2026 году.
Чек-лист миграции и типичные ошибки
Список ниже рассчитан на один проход и содержит критерии перехода между этапами. Пропуск любого пункта увеличивает вероятность инцидента на проде.
Чек-лист по этапам
- Бэкап и snapshot продовой базы. Проверить восстановление на стенде, а не только факт создания копии.
- Аудит исходных данных: сколько строк, сколько элементов массива, сколько NULL, сколько записей без обязательных полей.
- Спроектировать целевую схему с первичным ключом, уникальным индексом и CHECK-констрейнтами.
- Создать таблицы, добавить внешний ключ с NOT VALID, затем VALIDATE CONSTRAINT в окно низкой нагрузки.
- Построить индексы CONCURRENTLY, каждый отдельной транзакцией.
- Включить двойную запись (триггер или код приложения) и убедиться, что новые заказы появляются в новой таблице.
- Запустить backfill батчами, начиная с малого батча для замера времени.
- Критерий завершения backfill: анти-джойн возвращает 0 строк, лаг не растёт, migration_errors не увеличивается.
- Сверить агрегаты и выборочные контрольные суммы по 200-500 заказам.
- Включить чтение из новой схемы на 1% трафика, сравнивать ответы, затем 10%, 50%, 100%.
- Критерий полного переключения: ни одного расхождения за N часов, latency не деградировала.
- Отключить двойную запись только после стабильной работы чтения, старую колонку удалить отдельным релизом позже.
Что делать, если что-то пошло не так
- Backfill упал на середине. Возьмите минимальный незаполненный id и продолжите цикл заново: уникальный индекс и ON CONFLICT DO NOTHING гарантируют идемпотентность.
- Триггер падает и роняет пользовательские транзакции. Немедленно отключите триггер, откатитесь к варианту с кодом приложения, разберите проблемные payload из migration_errors.
- Лаг backfill растёт, хотя backfill завершён. Значит двойная запись не покрывает часть путей записи: найдите писателей, которые не затронуты триггером или флагом.
- Чтение из новой схемы даёт расхождения. Уменьшите долю трафика до нуля сменой флага и вернитесь к сверке: обычно дело в дублях внутри JSON или в пустом массиве вместо NULL.
- Реплика отстаёт. Снизьте размер батча, увеличьте паузу, проверьте WAL sender и сетевой канал.
- Диск заканчивается во время REINDEX CONCURRENTLY. Прервите операцию: невалидный индекс останется и его нужно удалить DROP INDEX CONCURRENTLY перед повтором.
Старую колонку держите до тех пор, пока новый путь не отработает как минимум один полный бизнес-цикл: закрытие месяца, отчётный период, сезонный пик. Только после этого удаление TEXT-колонки перестаёт быть риском и становится обычной уборкой схемы.