Как безопасно добавить колонку или индекс на большую таблицу без долгой блокировки?

Почему это сложно

Большинство DDL-операций в PostgreSQL требуют AccessExclusiveLock — самую сильную блокировку, которая не совместима ни с чем. На таблице с миллионами строк и интенсивным трафиком такая блокировка может висеть минуты, полностью останавливая работу приложения.

Безопасное добавление колонки

С PostgreSQL 11+ добавление колонки с DEFAULT без VOLATILE функции не переписывает таблицу. Это безопасная операция с кратким lock.

-- PostgreSQL 11+: мгновенно, не переписывает строки
ALTER TABLE orders ADD COLUMN notes TEXT DEFAULT '';

-- PostgreSQL 10 и старше: DEFAULT переписывал всю таблицу!
-- Безопасный способ для старых версий:
ALTER TABLE orders ADD COLUMN notes TEXT;         -- без DEFAULT — быстро
UPDATE orders SET notes = '' WHERE notes IS NULL;  -- batch update
ALTER TABLE orders ALTER COLUMN notes SET DEFAULT '';
ALTER TABLE orders ALTER COLUMN notes SET NOT NULL; -- после заполнения всех строк

Добавление NOT NULL колонки — осторожно

-- ОПАСНО в старых версиях: переписывает всю таблицу
-- ALTER TABLE orders ADD COLUMN status TEXT NOT NULL DEFAULT 'pending';

-- Безопасный паттерн (PostgreSQL 12+ поддерживает NOT VALID constraint)
-- Шаг 1: добавить колонку nullable с DEFAULT
ALTER TABLE orders ADD COLUMN status TEXT DEFAULT 'pending';

-- Шаг 2: заполнить данные батчами, не блокируя таблицу
DO $$
DECLARE
  batch_size INT := 10000;
  last_id BIGINT := 0;
BEGIN
  LOOP
    UPDATE orders
    SET status = 'pending'
    WHERE id > last_id AND id <= last_id + batch_size AND status IS NULL;
    EXIT WHEN NOT FOUND;
    last_id := last_id + batch_size;
    PERFORM pg_sleep(0.01);  -- дать другим транзакциям шанс
  END LOOP;
END;
$$;

-- Шаг 3: добавить NOT NULL constraint (PostgreSQL 12+ проверяет через CHECK NOT VALID)
ALTER TABLE orders
  ADD CONSTRAINT orders_status_not_null CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null;  -- ShareUpdateExclusiveLock

Создание индекса без блокировки

-- ОПАСНО: обычный CREATE INDEX берёт ShareLock (блокирует writes)
-- CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Безопасно: CONCURRENTLY делает два прохода, только ShareUpdateExclusiveLock
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

-- Уникальный индекс тоже можно
CREATE UNIQUE INDEX CONCURRENTLY idx_orders_external_id
  ON orders(external_id)
  WHERE external_id IS NOT NULL;

-- Если CONCURRENTLY упал (частично созданный «invalid» индекс):
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders' AND indexname NOT IN (
  SELECT indexname FROM pg_indexes
  JOIN pg_index ON pg_index.indexrelid = (schemaname || '.' || indexname)::regclass
  WHERE indisvalid = true
);
-- Удалить невалидный и повторить:
DROP INDEX CONCURRENTLY idx_orders_user_id;
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

Добавление внешнего ключа

-- Обычный FK проверяет все существующие строки — долгий ShareRowExclusiveLock
-- ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

-- Безопасный способ: сначала без проверки, потом validate отдельно
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id)
  NOT VALID;            -- не проверяет существующие строки, берёт кратный lock

ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;  -- ShareUpdateExclusiveLock

Переименование колонки и изменение типа

-- Переименование — кратко, но всё же AccessExclusiveLock
-- Используйте в окно обслуживания или zero-downtime через expand-contract:

-- 1. Добавить новую колонку
ALTER TABLE orders ADD COLUMN user_uuid UUID;

-- 2. Синхронизировать через триггер или приложение
-- 3. Переименовать старую в _deprecated, новую в правильное имя
-- 4. Удалить старую после деплоя всех сервисов

-- Изменение типа — почти всегда переписывает таблицу:
-- ALTER TABLE orders ALTER COLUMN amount TYPE NUMERIC(20,4);  -- ОПАСНО
-- Безопасно: новая колонка + migrate + переключение

Подводные камни

• lock_timeout не выставлен. DDL без lock_timeout будет ждать сколько угодно, блокируя в очереди все последующие запросы. Всегда оборачивайте: SET lock_timeout = '2s'; ALTER TABLE ...; RESET lock_timeout;
• CREATE INDEX CONCURRENTLY нельзя в транзакции. Запуск внутри BEGIN/COMMIT вызовет ошибку. Выполняйте вне транзакции.
• CONCURRENTLY требует двух проходов. Это означает двойную нагрузку на I/O и занимает в 2–3 раза больше времени, чем обычный CREATE INDEX.
• Невалидный индекс после прерванного CONCURRENTLY. Индекс создаётся в pg_indexes, но помечается как invalid. Он не используется планировщиком, но занимает место на диске. Нужно удалить и пересоздать.
• ALTER TABLE в транзакции с другими операциями. Несколько DDL в одной транзакции могут накапливать блокировки и создавать deadlock.
• Batch update без sleep. Массовый UPDATE без паузы между батчами создаёт непрерывную нагрузку на I/O и может нагнать autovacuum.
• VALIDATE CONSTRAINT занимает время. Хотя он берёт более слабый ShareUpdateExclusiveLock, на очень большой таблице он всё равно может занять минуты — планируйте в период низкой нагрузки.

• Отвечать определением без production-сценария.
• Не называть runtime boundary, security boundary или failure mode.
• Игнорировать версию API, observability и тестовую проверку.

• Объясняет механизм своими словами и без выдуманных API.
• Называет реальные риски, диагностику и критерий корректности.
• Связывает ответ с текущей документацией и миграционными ограничениями.