Изменение схемы Postgres без блокировок: add column, backfill батчами, NOT VALID и validate constraint, create index concurrently и оценка времени lock.
Под «долгой блокировкой» в Postgres обычно понимают не то, что команда выполняется долго, а то, что она держит такой lock, из-за которого другие запросы начинают ждать. База при этом выглядит «живой», но ключевые операции встают в очередь, и время ответа растет по цепочке.
DDL опасен тем, что часто требует блокировку уровня таблицы. Даже если само изменение схемы занимает доли секунды, оно может долго ждать подходящего окна, пока завершатся текущие транзакции. А когда lock наконец взят, уже новые запросы начинают ждать этот DDL. Так и получается эффект «минуты простоя» без явной ошибки в логах приложения.
На тестовой базе это редко заметно: меньше данных, почти нет длинных транзакций, слабее конкуренция и нет настоящих пиков. В проде же одновременно идут отчеты, фоновые задачи, импорты и запросы из приложения, и любой DDL попадает в плотный поток. Поэтому «миграции без простоя» начинаются не с особой команды, а с понимания, кто кого может остановить.
Типичные симптомы:
Важно различать, какие блокировки мешают чтению, а какие записи. «Мягкие» блокировки могут не мешать SELECT, но блокировать INSERT/UPDATE/DELETE. Тогда пользовательские действия вроде «сохранить» или «оформить» начинают зависать. «Жесткие» блокировки могут остановить и чтение, и запись, особенно когда операции нужен эксклюзивный доступ к таблице.
Главная проблема в том, что задержка часто состоит из ожидания. DDL может минуту ждать завершения одной длинной транзакции, а потом еще держать lock, потому что в очереди уже стоят другие запросы. Поэтому риск стоит оценивать не по тому, как быстро команда отрабатывает на «чистой» базе, а по конкуренции транзакций и их длительности.
Причина внезапного простоя при миграциях обычно не в том, что DDL «долго выполняется», а в том, что он не может быстро получить нужный lock. Пока lock не взят, команда ждет, а система накапливает очередь запросов.
Самые неприятные сюрпризы дают операции, которым нужен сильный lock на таблицу. Тут важно различать два случая:
Прикидка по риску:
Размер таблицы важен, но чаще главный враг быстрых миграций - долгие транзакции. Одна забытая сессия, открытая на минуты, может удерживать цепочку блокировок так, что DDL не стартует.
Пример: вы ожидаете, что ADD COLUMN пройдет за секунды. Но пара отчетных запросов держит транзакции 10 минут. Итог - команда стоит в ожидании lock все 10 минут, хотя сама операция заняла бы доли секунды.
Перед запуском полезно быстро проверить: есть ли транзакции старше нескольких минут, нет ли активных запросов к этой таблице с большим временем, насколько таблица большая и как часто в нее пишут, а также переписываются ли данные или меняются только метаданные.
Самый безопасный способ добавить поле в большую таблицу - начать с nullable-колонки без DEFAULT. Команда ALTER TABLE ... ADD COLUMN new_col type; обычно берет короткую блокировку для изменения метаданных и не переписывает все строки. Для продакшена это почти всегда лучший первый шаг.
Опасность начинается, когда вы добавляете DEFAULT или NOT NULL в одном действии. Исторически ADD COLUMN ... DEFAULT ... мог заставить Postgres пройти по таблице и записать значение в каждую строку, то есть переписать таблицу. На крупных таблицах это превращается в долгую операцию и держит блокировки заметно дольше.
В PostgreSQL 11+ добавление колонки с константным DEFAULT часто делается без переписывания: значение хранится в метаданных и подставляется при чтении. Но это работает не всегда. Например, если DEFAULT зависит от нестабильной функции или от текущего времени, поведение будет другим. Поэтому надежнее разделять шаги.
Практичный порядок:
new_col IS NULL, используем старый источник);NOT NULL отдельными командами, когда данные заполнены.Пример: вы добавляете status_v2. Сначала колонка пустая, код читает COALESCE(status_v2, status). После выката вы начинаете записывать status_v2 для новых заказов. Риск простоя ниже, а откат проще: совместимость сохраняется.
Backfill - это заполнение новой колонки (или пересчет данных) для уже существующих строк. Частая ошибка - запустить один большой UPDATE на всю таблицу. Он держит транзакцию долго, раздувает WAL, создает много «мертвых» версий строк и провоцирует очередь из ожиданий.
Рабочий подход - делать backfill батчами: быстро обновили небольшой кусок, зафиксировали транзакцию, сделали паузу и повторили.
На практике батчи режут по id, по времени создания, по ключам (tenant_id, префикс, хеш), либо просто «порциями по LIMIT», пока еще есть что обновлять.
Простой вариант на LIMIT, чтобы не держать длинную транзакцию:
-- повторяйте этот блок из джобы/скрипта, пока обновлено > 0 строк
WITH batch AS (
SELECT id
FROM big_table
WHERE new_col IS NULL
ORDER BY id
LIMIT 1000
)
UPDATE big_table t
SET new_col = /* вычисление */
FROM batch
WHERE t.id = batch.id;
Нагрузку контролируют дисциплиной: небольшие транзакции, паузы 50-500 мс между батчами и понятная метрика прогресса (сколько строк осталось с new_col IS NULL). Если база начинает «задыхаться» (растут задержки, очередь по I/O, autovacuum не успевает), уменьшайте размер батча и увеличивайте паузу.
Если приложение параллельно пишет в те же строки, возможны конфликты и ожидания. Обычно помогают три вещи: обновлять только «пустые» строки (WHERE new_col IS NULL), ставить таймаут ожидания блокировок для сессии миграции и повторять позже, а при нескольких воркерах выбирать батч через FOR UPDATE SKIP LOCKED.
Такой backfill идет дольше по часам, но почти не мешает пользователям - и это обычно важнее.
Когда нужно добавить правило к уже большой таблице, самый рискованный момент - проверка старых данных. ALTER TABLE ... ADD CONSTRAINT без оговорок часто вынуждает сканировать всю таблицу и из-за этого держит блокировку дольше, чем ожидается.
Идея простая: разделить операцию на два шага. Сначала добавить ограничение как NOT VALID, а затем отдельно запустить VALIDATE CONSTRAINT. В режиме NOT VALID Postgres начинает применять правило только к новым строкам и изменениям, но не проверяет исторические данные сразу. Поэтому блокировка обычно короткая.
VALIDATE CONSTRAINT выполняет полную проверку существующих строк. На больших объемах это может занять минуты или часы, но обычно не блокирует обычные чтения и записи (SELECT/INSERT/UPDATE/DELETE). Зато блокирует конкурирующие изменения схемы на этой таблице - это важно заложить в план.
Рабочий порядок:
NOT VALID, чтобы быстро зафиксировать правило на будущее;VALIDATE CONSTRAINT в более спокойное окно.Частый выигрыш:
CHECK (age >= 0) NOT VALID, затем VALIDATE CONSTRAINT.Для FOREIGN KEY логика такая же: ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID, потом валидация. При валидации Postgres сверяет таблицы, поэтому на ключах должны быть индексы, иначе проверка затянется.
С NOT NULL часто делают обходной путь: добавляют CHECK (col IS NOT NULL) NOT VALID, делают backfill, валидируют, и только потом ставят ALTER TABLE ... ALTER COLUMN ... SET NOT NULL. После этого временный CHECK можно удалить.
Обычный CREATE INDEX берет блокировку, из-за которой записи в таблицу могут ждать. На живой системе это легко превращается в очередь: приложение продолжает читать, но вставки и обновления «подвисают» и упираются в таймауты.
CREATE INDEX CONCURRENTLY строит индекс так, чтобы не останавливать обычные операции записи. Но есть правила: команду нельзя выполнять внутри транзакции. И по времени она обычно дольше, потому что Postgres делает несколько проходов по данным и отдельно «догоняет» изменения, которые произошли во время построения.
Перед запуском стоит прикинуть ресурсы. Индекс потребует места на диске и даст нагрузку на CPU и I/O (особенно если идет сортировка). При ошибке из-за отмены запроса, рестарта или нехватки места может понадобиться повтор.
Быстрые проверки перед стартом: хватает ли места под индекс и временные файлы, какая скорость записи в таблицу, как ведет себя диск в часы пик, какие таймауты стоят у миграций.
Уникальность добавляйте осторожно. Часто безопаснее сначала создать уникальный индекс конкурентно, а потом привязать его как constraint: ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX .... Так вы заранее увидите проблемы с дублями: индекс просто не построится.
Частая ошибка - думать, что риск зависит только от размера таблицы. На практике простои чаще определяют блокировки и очереди: одна долгая транзакция удерживает метаданные, а миграция встает и ждет, пока пользователи продолжают генерировать нагрузку.
В изменениях схемы встречаются разные уровни блокировок. Самый опасный класс - когда операция требует Access Exclusive: он не пускает ни чтения, ни записи. Мягче - Share/Share Update Exclusive: обычно мешают конкурентному DDL, но могут конфликтовать с частью служебных операций. Есть и операции, которые «быстрые» по сути (например, добавить колонку без дефолта), но все равно берут короткий сильный lock на смену метаданных. Если в этот момент есть очередь запросов и открытые транзакции, короткий lock превращается в секунды или минуты ожидания.
Перед запуском составьте карту риска: насколько активно в таблицу пишут, есть ли долгие транзакции (отчеты, фоновые задачи, «забытые» сессии), когда пики трафика и есть ли окно, а также переписывает ли операция данные или трогает только метаданные.
Лучший способ оценить время - измерить. Для батчевого backfill прогоните пробный апдейт на небольшом диапазоне (10-50 тысяч строк) и посмотрите, сколько занимает порция и как меняются задержки обычных запросов. Для индексов прикиньте время на копии или на похожей таблице. Помните: CONCURRENTLY уменьшает блокировки, но обычно идет дольше и сильнее зависит от нагрузки.
Защитные настройки лучше включать заранее. lock_timeout спасает от бесконечного ожидания сильной блокировки (лучше быстро упасть и перенести запуск), statement_timeout ограничивает слишком долгие шаги, а логирование ожиданий блокировок помогает понять, кто именно держит очередь. Полезная привычка: перед миграцией проверить длинные транзакции и заранее договориться, кто может их остановить, если они появятся во время запуска.
Рабочий процесс начинается не с DDL, а с кода. Частый источник простоя - когда база уже изменилась, а приложение еще не умеет жить с новой реальностью (или наоборот). Если цель - изменения без долгих блокировок, сначала обеспечьте совместимость версий.
Удобно думать о миграции как о серии маленьких безопасных шагов:
После этого включайте «строгий режим» в приложении: начинайте обязательно писать новое поле, переставайте читать старое, добавляйте проверки на уровне бизнес-логики. И только когда новые данные заполняются стабильно, убирайте временные обходы и фиксируйте финальную схему (NOT NULL, удаление старых колонок или триггеров).
Допустим, вы добавляете status_v2 и хотите постепенно уйти от старого status. Сначала приложение пишет оба значения. Затем вы дозаполняете status_v2 порциями, валидируете ограничения и параллельно создаете индекс на status_v2. И только в конце удаляете поддержку status в коде. Такой порядок особенно полезен в сервисах на PostgreSQL с частыми релизами, где важно не держать длинные блокировки в часы пик.
Причина простоя чаще всего одна: попытка сделать все «в лоб» - один DDL, один большой update, один релиз. В Postgres это легко заканчивается тем, что маленькая по виду команда держит блокировку дольше, чем ожидали.
Ошибки, которые встречаются чаще всего:
DEFAULT и NOT NULL на большой таблице. Безопаснее разделять: добавить, заполнить, затем постепенно «закрутить гайки».UPDATE перегружает диск и CPU, раздувает WAL и создает длинный хвост ожиданий.CONCURRENTLY. Индекс конкурентно нельзя создавать внутри транзакции. Если инструмент миграций оборачивает все в одну транзакцию, команда упадет.Пример: команда добавляет user_status в таблицу на десятки миллионов строк и ставит NOT NULL DEFAULT 'active'. На тесте проходит, а в проде запросы начинают ждать. Обычно это сочетание причин: длинные транзакции из фоновых задач плюс тяжелый backfill «в один заход».
Если вы генерируете миграции автоматически (например, в TakProsto в режиме планирования), все равно полезно вручную проверить, нет ли транзакционной обертки, не смешаны ли DDL и backfill в одном шаге, и можно ли разбить работу на короткие этапы с контролем времени.
Представьте: в таблице orders нужно добавить поле client_region, чтобы строить отчеты и включить фильтры в приложении. Данные уже есть косвенно: регион можно вычислить по client_id через таблицу clients.
Безопасный план обычно растягивают на 2-3 релиза, чтобы каждый шаг был коротким и откатываемым.
Добавляем новую колонку без значения по умолчанию и без NOT NULL. Это быстрый шаг: база не должна переписывать всю таблицу.
Параллельно выкатываем код приложения: при создании или обновлении заказа оно заполняет client_region сразу. Старые строки пока остаются пустыми, и это нормально.
Заполняем старые строки батчами, например по 1 000-10 000 записей за проход, с паузами между проходами. Смысл простой: лучше много маленьких обновлений, чем одно большое.
Во время backfill полезно следить за сигналами: p95/p99 задержек, ростом времени UPDATE, количеством ожиданий по блокировкам, лагом репликации (если есть), ростом размера таблицы и частотой autovacuum, а также дедлоками.
Если метрики поползли вверх, уменьшайте размер батча и добавляйте паузы.
Понять, что пора включать NOT NULL или уникальность, можно по двум проверкам: нет ли NULL и нет ли дублей. Практичный критерий - несколько часов или дней (зависит от нагрузки), когда значение стабильно заполняется новым кодом, а backfill довел «хвост» до нуля.
После этого ограничения вводят поэтапно: сначала через мягкую проверку, затем отдельно запускают валидацию, и только потом включают NOT NULL или уникальный индекс.
Такой подход хорошо подходит и для проектов, которые собирают и разворачивают в TakProsto: вместо одного «страшного» релиза вы двигаетесь маленькими, контролируемыми шагами.
Лучший способ снизить риск - сделать миграции похожими друг на друга: одинаковые проверки, одинаковые стоп-сигналы и понятный порядок шагов.
Короткий чеклист:
lock_timeout и statement_timeout, проверьте длинные транзакции и незакрытые сессии.ANALYZE, уберите временные обходы в коде и миграциях.Дальше важно превратить это в командный шаблон, а не разовую памятку: короткий план, прогон на стейджинге, затем прод. Полезно хранить решения рядом с кодом: почему выбрали NOT VALID, почему такой размер батча, какой индекс делали CONCURRENTLY.
Если вы используете TakProsto (takprosto.ai) для сборки и деплоя приложений через чат, удобно фиксировать шаги миграции в planning mode и повторять их одинаково от релиза к релизу. А для осторожных изменений выручает привычка делать снимки и держать понятный план отката.
DDL в Postgres часто требует блокировку на уровне таблицы, и проблема обычно не в длительности самой команды, а в ожидании подходящего окна. Если в этот момент есть длинные транзакции, команда может стоять в очереди минуты, а затем уже новые запросы начнут ждать DDL.
Чаще всего вы увидите таймауты на обычных запросах, рост количества запросов в состоянии ожидания блокировок и «подвисание» операций записи. База выглядит живой, но критичные действия начинают упираться в очередь ожидания и задержки расползаются по всей системе.
Самый безопасный старт — добавить nullable-колонку без DEFAULT: обычно это меняет только метаданные и держит блокировку недолго. Дальше уже проще обновлять приложение и заполнять данные постепенно, не заставляя базу переписывать всю таблицу сразу.
Опасно, когда добавление колонки приводит к переписыванию данных или длительной проверке существующих строк. Комбинация DEFAULT и NOT NULL в одном шаге, а также тяжелые преобразования типа могут сделать операцию долгой и блокирующей, особенно на больших таблицах.
Один большой UPDATE держит транзакцию долго, раздувает WAL и создает много мертвых версий строк, из-за чего растут задержки и очередь ожиданий. Практичнее обновлять маленькими порциями с короткими транзакциями и паузами, чтобы нагрузка была ровной и управляемой.
Начните с небольшого размера батча и смотрите на задержки приложения, I/O и время выполнения порции. Если метрики ухудшаются, уменьшайте батч и увеличивайте паузу; цель — чтобы миграция шла медленнее, но почти незаметно для пользователей.
Добавьте ограничение как NOT VALID, чтобы оно сразу применялось к новым изменениям, но не проверяло исторические данные. Затем приведите старые строки в порядок и отдельно запустите VALIDATE CONSTRAINT в более спокойное время, понимая, что это может занять долго, но обычно не блокирует обычные записи и чтение.
Обычный CREATE INDEX может сильно мешать записям, потому что берет блокировку, из-за которой INSERT/UPDATE/DELETE начинают ждать. CREATE INDEX CONCURRENTLY обычно позволяет продолжать записи, но идет дольше и его нельзя выполнять внутри транзакции, поэтому заранее проверьте, как ваш инструмент миграций запускает DDL.
Проверьте наличие транзакций, которые живут минуты и дольше, и оцените конкуренцию на целевой таблице: сколько записей, какие пики, какие фоновые задачи. Полезно заранее выставить lock_timeout, чтобы миграция быстро падала вместо бесконечного ожидания, и запускать тяжелые шаги отдельными, контролируемыми командами.
Сначала сделайте приложение совместимым с будущей схемой: чтение с fallback и двойную запись, если нужно. Затем добавляйте изменения маленькими шагами, делайте backfill батчами, вводите ограничения через NOT VALID и отдельно валидируйте, а индексы стройте конкурентно; такой план проще откатывать и он лучше переносит продовую нагрузку.