re:re:actable
Урок 044 минуты чтения

Как безопасно изменить большую таблицу

На этой странице
Кратко

Изменение схемы большой таблицы опасно не столько длительностью, сколько блокировками: многие 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.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам