Что такое ALTER TABLE в SQL? Изменение структуры таблицы для начинающих

ALTER TABLE — это команда «поменять структуру таблицы, которая уже есть». Не данные внутри (для этого UPDATE), а сами колонки и правила: добавить колонку, переименовать, поменять тип, навесить или снять constraint.

Вернёмся к аналогии с бланком анкеты. CREATE TABLE — это момент, когда ты придумал, какие в анкете будут поля. ALTER TABLE — это «давайте добавим в анкету ещё одно поле — телефон» через два месяца после запуска. Все уже заполненные анкеты надо как-то обработать (поле новое, у старых анкет его нет — NULL?), а будущие — заполнять с учётом нового поля.

Зачем нужен ALTER TABLE

Схема никогда не остаётся такой, как ты придумал в первый день:

• Появилась новая фича — нужна новая колонка.
• Колонка стала ненужной — её надо убрать.
• Сменился формат хранения (VARCHAR(50) → TEXT) — надо мигрировать тип.
• Появилось новое правило (amount не должен быть отрицательным) — надо повесить CHECK.

ALTER TABLE — это инструмент эволюции схемы.

ADD COLUMN — добавить колонку

ALTER TABLE users
ADD COLUMN phone VARCHAR(20);

Просто и быстро. На большой таблице (миллионы строк) тоже быстро — Postgres 11+ умеет добавлять nullable-колонку без переписывания каждой строки.

С дефолтом:

ALTER TABLE users
ADD COLUMN tier VARCHAR(20) NOT NULL DEFAULT 'free';

Тут с Postgres 11+ тоже быстро, если default — литерал. Если default — NOW() или gen_random_uuid() (значение зависит от строки) — Postgres перезапишет каждую строку. На миллиарде строк это часы блокировки.

Безопасный паттерн «добавили колонку, сделали NOT NULL потом»:

-- Шаг 1 (быстро): добавили nullable
ALTER TABLE users ADD COLUMN signup_source VARCHAR(50);

-- Шаг 2 (батчами): заполнили данные
UPDATE users SET signup_source = 'web' WHERE signup_source IS NULL AND id BETWEEN 1 AND 100000;
-- ... повторить по диапазонам ...

-- Шаг 3 (быстро — все строки уже не NULL): сделали NOT NULL
ALTER TABLE users ALTER COLUMN signup_source SET NOT NULL;

DROP COLUMN — удалить колонку

ALTER TABLE users
DROP COLUMN phone;

Postgres помечает колонку как «удалена» — данные физически освободятся при VACUUM FULL или следующем full table rewrite. Команда быстрая, но необратимая: данные ушли.

Перед DROP COLUMN на проде:

1. Убедись, что приложение не пишет в эту колонку (deploy без неё).
2. Подожди несколько дней — на случай rollback'а.
3. Только потом — DROP.

RENAME COLUMN — переименовать колонку

ALTER TABLE users
RENAME COLUMN phone TO phone_number;

Быстро, бесплатно. Но это breaking change для приложения — все запросы с phone упадут с ошибкой column does not exist. На проде перенос делают в несколько шагов: добавили новую → синкают данные → переключают код → удалили старую.

ALTER COLUMN TYPE — поменять тип

-- Был VARCHAR(50), стал TEXT
ALTER TABLE products
ALTER COLUMN name TYPE TEXT;

Postgres попробует автоматически сделать каст. Если каст не очевиден — нужно USING:

-- price был VARCHAR, перевести в NUMERIC
ALTER TABLE products
ALTER COLUMN price TYPE NUMERIC(12,2) USING price::NUMERIC;

Ловушка: смена типа = full table rewrite. На таблице с миллиардом строк — часы блокировки. Безопасный путь — добавить новую колонку нужного типа, перенести данные батчами, переключить код, удалить старую.

ADD/DROP CONSTRAINT

-- Добавить FK
ALTER TABLE orders
ADD CONSTRAINT orders_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES customers(id);

-- Удалить FK
ALTER TABLE orders
DROP CONSTRAINT orders_customer_id_fkey;

-- Добавить CHECK
ALTER TABLE products
ADD CONSTRAINT products_price_positive CHECK (price > 0);

-- Сделать колонку NOT NULL
ALTER TABLE users
ALTER COLUMN email SET NOT NULL;

Postgres проверит constraint на всех существующих строках. На большой таблице — долго и под блокировкой. Облегчает боль двухшаговый паттерн:

-- Шаг 1: добавили как NOT VALID — не проверяет старые строки, быстро.
ALTER TABLE orders
ADD CONSTRAINT orders_amount_positive CHECK (amount > 0) NOT VALID;

-- Шаг 2: валидируем в фоне, отдельной командой (берёт более слабый lock).
ALTER TABLE orders VALIDATE CONSTRAINT orders_amount_positive;

То же для FK: ADD CONSTRAINT … NOT VALID; ... VALIDATE CONSTRAINT …. Это снимает большую часть боли с миграциями на больших таблицах.

Add → Backfill → Drop — паттерн для безопасных миграций

Когда нужно поменять что-то «грубо» (тип колонки, переименовать, разнести в две таблицы) — никогда не делай это одной ALTER-командой на проде. Вместо этого:

-- 1. ADD: быстрая операция, новая колонка nullable.
ALTER TABLE orders ADD COLUMN customer_email TEXT;

-- 2. BACKFILL: заполнить данные батчами в фоне.
--    Каждый batch — короткая транзакция, не держит блокировок.
UPDATE orders SET customer_email = (SELECT email FROM customers WHERE id = orders.customer_id)
WHERE customer_email IS NULL AND id BETWEEN 1 AND 10000;
-- ... повторить по диапазонам ...

-- 3. Включить запись в новую колонку из приложения.
-- 4. (Опционально) сделать NOT NULL после полного бэкфила.
ALTER TABLE orders ALTER COLUMN customer_email SET NOT NULL;

-- 5. (Если убираем старую колонку) deploy кода без неё, потом:
-- ALTER TABLE orders DROP COLUMN <old_column>;

Этот паттерн повторяется при переименовании, изменении типа, выносе колонки в отдельную таблицу. Долго, но не ломает прод.

Почему ALTER страшен на проде — про блокировки

ALTER TABLE берёт ACCESS EXCLUSIVE lock — самый строгий. Пока он держится, никто не читает и не пишет в таблицу. Если ALTER короткий (миллисекунды) — никто не заметит. Если долгий (секунды-минуты) — приложение видит timeout'ы, очередь запросов растёт, всё валится.

Долгий lock дают:

• ALTER COLUMN TYPE (full rewrite таблицы).
• ADD COLUMN с не-литеральным дефолтом (тоже full rewrite).
• ADD CONSTRAINT без NOT VALID (полный скан таблицы под блокировкой).
• CREATE INDEX без CONCURRENTLY.

Короткий lock (метаданные, не данные):

• ADD COLUMN без дефолта или с литеральным дефолтом.
• DROP COLUMN (помечает, не переписывает).
• RENAME COLUMN / RENAME TABLE.
• ADD CONSTRAINT … NOT VALID (отложенная проверка).
• CREATE INDEX CONCURRENTLY (берёт слабый lock, разрешает чтение и запись параллельно).

Правило: на проде ALTER должен занимать миллисекунды или ~1 секунду. Если миграция требует обработки данных — выноси в отдельные UPDATE батчами вне ALTER.

Частые ошибки новичков

1. ALTER без понимания locks. Поменял тип на проде → таблица заблокирована на 40 минут → SLA полетел. Перед ALTER на проде — проверь на staging: какой lock берёт, как долго.

2. NOT NULL за один шаг на непустую таблицу. ALTER ADD COLUMN x INT NOT NULL упадёт, если в таблице есть строки — нет значения для них. Правильно: добавить nullable → backfill → SET NOT NULL.

3. ALTER COLUMN TYPE без USING для несовместимых типов. Postgres откажется кастить string → date без явного USING price::DATE. Ошибка простая, но на staging поймать важнее, чем на проде в 3 ночи.

4. CASCADE в DROP CONSTRAINT. DROP CONSTRAINT … CASCADE снесёт всё, что зависит — FK с других таблиц, view'хи. Перед CASCADE — проверь зависимости.

5. CREATE INDEX без CONCURRENTLY. CREATE INDEX берёт SHARE lock — блокирует записи. На большой таблице минуты. CREATE INDEX CONCURRENTLY — слабый lock, можно параллельно писать. Но не в транзакции.

6. ALTER в одной транзакции с долгим DML. BEGIN; ALTER TABLE …; UPDATE миллион строк; COMMIT; — внутри открытой транзакции lock держится до COMMIT, даже если ALTER уже отработал. Разделяй: ALTER в одной короткой транзакции, DML — в другой.

Мини-резюме

• ALTER TABLE меняет структуру таблицы, не данные.
• Самые безопасные операции: ADD COLUMN (nullable), DROP COLUMN, RENAME, ADD CONSTRAINT … NOT VALID.
• Опасные на больших таблицах: ALTER COLUMN TYPE, ADD COLUMN с не-литеральным дефолтом, ADD CONSTRAINT без NOT VALID, CREATE INDEX без CONCURRENTLY.
• Для крупных миграций — паттерн «add → backfill → drop»: добавили nullable, заполнили данные батчами, включили NOT NULL.
• Перед ALTER на проде — тестировать на staging с реальными объёмами, чтобы понимать длительность lock'а.