Как безопасно изменить большую таблицу
На этой странице
Изменение схемы большой таблицы опасно не столько длительностью, сколько блокировками: многие ALTER TABLE берут ACCESS EXCLUSIVE, при которой ждут даже SELECT, а пока команда сама стоит в очереди за долгим запросом, за ней выстраиваются все остальные — сервис «ложится». Правила: ставить lock_timeout и повторять при неудаче; знать, какие операции быстрые (в PostgreSQL 11+ ADD COLUMN даже с константным DEFAULT — мгновенный, без перезаписи), а какие переписывают таблицу (ALTER COLUMN TYPE, DEFAULT с волатильной функцией вроде clock_timestamp()); индексы создавать CONCURRENTLY; ограничения добавлять NOT VALID и проверять отдельно VALIDATE; данные переносить пачками в фоне; переименования и удаления делать по схеме expand → migrate → contract.
Вопрос
Как изменить схему большой таблицы без простоя?
Сначала — lock_timeout
SET lock_timeout = '5s'; -- не ждать блокировку дольше 5 секунд
SET statement_timeout = '30s';
ALTER TABLE orders ADD COLUMN source text;Если таймаут сработал — повторить позже; лучше упавшая миграция, чем очередь из тысяч запросов.
Быстрые и медленные операции (PostgreSQL)
| Операция | Цена |
|---|---|
ADD COLUMN без DEFAULT / с константным DEFAULT (PG 11+) | Мгновенно, только метаданные |
ADD COLUMN ... DEFAULT clock_timestamp() / gen_random_uuid() | Перезапись таблицы |
DROP COLUMN | Мгновенно (столбец помечается удалённым) |
ALTER COLUMN TYPE (большинство случаев) | Перезапись таблицы и индексов |
ALTER COLUMN TYPE varchar(n) → больший n или → text | Мгновенно |
SET NOT NULL | Полный скан (быстро, если есть проверенный CHECK (col IS NOT NULL), PG 12+) |
ADD CONSTRAINT FK/CHECK | Полный скан под блокировкой → NOT VALID + VALIDATE |
CREATE INDEX | Блокирует запись → CONCURRENTLY |
RENAME COLUMN | Мгновенно, но ломает старый код |
Добавить обязательный столбец
-- 1. быстро, константный DEFAULT
ALTER TABLE orders ADD COLUMN currency char(3) NOT NULL DEFAULT 'RUB';Если значение нужно вычислить из других данных:
-- 1. nullable столбец
ALTER TABLE orders ADD COLUMN region_id bigint;
-- 2. приложение начинает заполнять его для новых строк
-- 3. backfill пачками (скрипт, повторять до 0 строк)
UPDATE orders o SET region_id = u.region_id
FROM users u
WHERE o.user_id = u.id
AND o.id IN (SELECT id FROM orders WHERE region_id IS NULL LIMIT 5000);
-- 4. NOT NULL без долгой блокировки
ALTER TABLE orders ADD CONSTRAINT orders_region_nn CHECK (region_id IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_region_nn;
ALTER TABLE orders ALTER COLUMN region_id SET NOT NULL; -- использует проверенный CHECK
ALTER TABLE orders DROP CONSTRAINT orders_region_nn;Индекс
CREATE INDEX CONCURRENTLY orders_region_idx ON orders (region_id);
-- при ошибке индекс останется INVALID:
-- DROP INDEX CONCURRENTLY orders_region_idx; и создать зановоВнешний ключ
ALTER TABLE orders ADD CONSTRAINT orders_region_fk
FOREIGN KEY (region_id) REFERENCES regions (id) NOT VALID; -- только новые строки
ALTER TABLE orders VALIDATE CONSTRAINT orders_region_fk; -- без блокировки записиСмена типа
1. ADD COLUMN amount_new numeric(14,2)
2. Приложение пишет в оба столбца
3. Backfill пачками
4. Приложение читает новый столбец
5. DROP COLUMN amount (отдельным релизом)Частный случай int → bigint для PK обычно решают так же или инструментами вроде pg-osc.
Переименование без простоя
Expand → migrate → contract: новый столбец, двойная запись, перенос, переключение чтения, удаление старого. Подробнее — миграции.
Чек-лист
lock_timeoutиstatement_timeoutв миграции.- Нет долгих транзакций перед запуском (
pg_stat_activity). - Индексы —
CONCURRENTLY, ограничения —NOT VALID+VALIDATE. - Backfill — пачками, с паузами, вне миграции.
- Миграция обратно совместима с текущей версией кода.
- Проверено на копии продовой базы по объёму.
Частые ошибки
ALTER TABLEбезlock_timeoutна нагруженной таблице.- Один
UPDATEна 100 млн строк. RENAME/DROPв том же релизе, что и изменение кода.DEFAULT now()считать переписывающим таблицу —now()стабильна в транзакции и вычисляется один раз, аclock_timestamp()— нет.
Что важно помнить
- Опасность — в блокировках, а не только во времени.
- Многие операции в современном PostgreSQL мгновенны — знайте, какие.
- Тяжёлые изменения — несколькими шагами и пачками.
Мини-шпаргалка
SET lock_timeout = '5s'.CREATE INDEX CONCURRENTLY.ADD CONSTRAINT ... NOT VALID→VALIDATE CONSTRAINT.CHECK (col IS NOT NULL)→SET NOT NULL.- Backfill:
UPDATE ... WHERE id IN (SELECT ... LIMIT n).
Документация
PostgreSQL: ALTER TABLE, PostgreSQL: Building Indexes Concurrently.