PrepBro
← Назад к вопросам

В каком случае ALTER TABLE будет медленно выполнятся

2.7 Senior🔥 61 комментариев
#Базы данных (SQL)

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI22 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

# Когда ALTER TABLE выполняется медленно

ALTER TABLE — это одна из самых опасных операций в production. Может зависнуть на часы или даже дни на больших таблицах. Разберём когда и почему.

1. На больших таблицах (миллионы строк)

Проблема: Перестройка всей таблицы

-- Таблица: 500 миллионов строк, ~100GB
ALTER TABLE users ADD COLUMN last_login TIMESTAMP DEFAULT NOW();

Что происходит:

  1. PostgreSQL создаёт копию таблицы
  2. Копирует все 500M строк
  3. Добавляет новый столбец с default значением
  4. Переименовывает таблицы

Время: 30-60 минут, в зависимости от железа.

Почему долго

Timing для таблицы 1M rows (1GB):
- ADD COLUMN with DEFAULT: ~1 сек

Timing для таблицы 100M rows (100GB):
- ADD COLUMN with DEFAULT: ~100 сек

Timing для таблицы 500M rows (500GB):
- ADD COLUMN with DEFAULT: ~500+ сек (8+ минут)

Масштабируемость: O(n) — линейна от размера таблицы.

2. Конкретные операции, которые перестраивают таблицу

✅ Быстрые операции (не требуют перестройки)

-- PostgreSQL 11+ : Добавить столбец с DEFAULT (без переписи данных)
ALTER TABLE users ADD COLUMN email VARCHAR(255) DEFAULT '';
-- Время: < 1 сек (даже для 1B rows!)

-- PostgreSQL 12+ : Изменить order по умолчанию
ALTER TABLE users ALTER COLUMN age SET DEFAULT 18;
-- Время: < 1 сек

-- Переименование столбца
ALTER TABLE users RENAME COLUMN old_name TO new_name;
-- Время: < 1 сек

-- Добавить CHECK constraint
ALTER TABLE users ADD CHECK (age > 0);
-- Время: < 1 сек (если не валидирует существующие данные)

❌ Медленные операции (требуют перестройки)

-- Изменить тип столбца (требует преобразование ВСЕХ значений)
ALTER TABLE users ALTER COLUMN age TYPE BIGINT;
-- Таблица 100M rows: ~50 минут

-- Добавить столбец без DEFAULT (требует занулить/переписать все строки)
ALTER TABLE users ADD COLUMN updated_at TIMESTAMP NOT NULL;
-- Таблица 100M rows: ~30 минут
-- ЛУЧШЕ:
ALTER TABLE users ADD COLUMN updated_at TIMESTAMP NOT NULL DEFAULT NOW();
-- Таблица 100M rows: < 1 сек!

-- Удалить столбец (требует перестройка)
ALTER TABLE users DROP COLUMN legacy_field;
-- Таблица 100M rows: ~20 минут

-- Установить NOT NULL на существующий столбец
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
-- ПРОБЛЕМА: требует валидировать ВСЕ строки
-- Таблица 100M rows: ~5-10 минут

-- Изменить PRIMARY KEY
ALTER TABLE users DROP CONSTRAINT users_pkey;
ALTER TABLE users ADD PRIMARY KEY (id);
-- Таблица 100M rows: ~1 час

3. Реальные примеры

Case 1: Добавить обязательный столбец

-- ❌ ПЛОХО
ALTER TABLE products ADD COLUMN sku VARCHAR(255) NOT NULL;
-- Зависнет на 30+ минут на больших таблицах

-- ✅ ХОРОШО
ALTER TABLE products ADD COLUMN sku VARCHAR(255) DEFAULT '';
-- < 1 сек (даже на 1B rows)

-- Потом отдельно заполняем значения
UPDATE products SET sku = generate_sku() WHERE sku = '';
-- Это займёт 30 минут, но таблица не будет LOCKED!

-- И только потом
ALTER TABLE products ALTER COLUMN sku SET NOT NULL;
-- < 1 сек (просто проверяет, что нет NULL)

Case 2: Изменить тип столбца

-- ❌ ПЛОХО (Таблица с 500M rows)
ALTER TABLE events ALTER COLUMN event_id TYPE VARCHAR(50);
-- Зависнет на 1-2 часа

-- ✅ ХОРОШО (с использованием USING)
ALTER TABLE events 
ALTER COLUMN event_id TYPE VARCHAR(50) 
USING event_id::VARCHAR(50);
-- Может даже быстрее, но всё ещё требует перестройки

-- ✅ ЛУЧШЕ (создаём новую колонку, копируем, удаляем старую)
ALTER TABLE events ADD COLUMN event_id_new VARCHAR(50);
UPDATE events SET event_id_new = event_id::VARCHAR(50);
-- Можно делать батчами, не LOCKENING всю таблицу

ALTER TABLE events DROP COLUMN event_id;
ALTER TABLE events RENAME COLUMN event_id_new TO event_id;

4. Блокировка (Locking) проблемы

Эксклюзивная блокировка таблицы

ALTER TABLE users ADD COLUMN email VARCHAR(255);

Что происходит:

Время: 0s   - START
       0.1s - Получаем EXCLUSIVE LOCK на users
       0.1-30s - Перестраиваем таблицу
       30s - RELEASE LOCK
       30s - END

В течение этих 30 секунд:
- SELECT запросы: ЖДУТ
- INSERT запросы: ЖДУТ
- UPDATE запросы: ЖДУТ
- DELETE запросы: ЖДУТ

Если таблица большая (300M rows), это может быть 30+ минут БЛОКИРОВКИ!

Проблема в production

[20:00:00] Начинаем ALTER TABLE
[20:05:00] SELECT от пользователей ждёт 5 минут
[20:10:00] Timeout! Приложение падает
[20:30:00] ALTER TABLE завершился
           но пользователи видели ошибку

5. Ограничения и проблемы

PostgreSQL 9.x (старые версии)

-- Даже простые операции медленные в старых версиях
ALTER TABLE users ADD COLUMN flag BOOLEAN DEFAULT FALSE;
-- PostgreSQL 9.6: ~30 минут на 100M rows
-- PostgreSQL 13+: < 1 сек на 100M rows

MySQL

-- MySQL медленнее PostgreSQL в ALTER operations
-- По умолчанию требует ALGORITHM=COPY для многих операций

-- COPY требует перестройки всей таблицы
ALTER TABLE users ADD COLUMN email VARCHAR(255), ALGORITHM=COPY;
-- 100M rows: ~1 час

-- INPLACE быстрее, но работает не всегда
ALTER TABLE users ADD INDEX idx_email (email), ALGORITHM=INPLACE;
-- 100M rows: ~5 минут

6. Инструменты для безопасного ALTER TABLE

pt-online-schema-change (от Percona)

# Безопасно меняем схему без полной блокировки
pt-online-schema-change --alter "ADD COLUMN email VARCHAR(255)" D=mydb,t=users

Как работает:

  1. Создаёт новую таблицу с новой схемой
  2. Копирует данные в фоне (в батчах)
  3. Применяет все изменения
  4. Переименовывает таблицы
  5. Удаляет старую таблицу

Liquibase / Flyway с онлайн миграциями

<!-- безопасная миграция -->
<changeSet>
    <sql>
        ALTER TABLE users ADD COLUMN email VARCHAR(255) DEFAULT '';
    </sql>
</changeSet>

Практические правила для production

✅ Безопасно на ЛЮБОМ размере таблицы

-- Добавить столбец с DEFAULT
ALTER TABLE users ADD COLUMN last_seen TIMESTAMP DEFAULT NOW();

-- Переименовать столбец
ALTER TABLE users RENAME COLUMN old_name TO new_name;

-- Добавить индекс (CONCURRENTLY в PostgreSQL)
CREATE INDEX CONCURRENTLY idx_email ON users(email);

⚠️ Осторожно на больших таблицах (> 10M rows)

-- Может заблокировать на 5+ минут
ALTER TABLE users ALTER COLUMN status SET NOT NULL;
ALTER TABLE users DROP COLUMN legacy_field;
ALTER TABLE users ADD CONSTRAINT check_age CHECK (age > 0);

❌ НИКОГДА не делай без плана на большой таблице

-- Перестроит таблицу за 1+ часа
ALTER TABLE users ALTER COLUMN id TYPE BIGINT;
ALTER TABLE users ADD COLUMN new_col TEXT NOT NULL;
ALTER TABLE users DROP PRIMARY KEY, ADD PRIMARY KEY (id);

Скорость ALTER TABLE (бенчмарки)

Таблица: 100 миллионов строк (~30GB)

Операция                          | Время     | Требует перестройку
----------------------------------|-----------|--------------------
ADD COLUMN with DEFAULT           | < 1 сек   | НЕТ
RENAME COLUMN                     | < 1 сек   | НЕТ
ADD INDEX CONCURRENTLY            | ~5 мин    | НЕТ (параллельно)
ALTER COLUMN SET DEFAULT          | < 1 сек   | НЕТ
ADD COLUMN NOT NULL (no default)  | ~45 мин   | ДА
ALTER COLUMN TYPE                 | ~1 час    | ДА
DROP COLUMN                       | ~30 мин   | ДА
ADD CHECK CONSTRAINT              | ~10 мин   | ДА (валидация)

Заключение

ALTER TABLE медленно выполняется когда:

  1. Таблица очень большая (100M+ rows)
  2. Операция требует перестройки (изменение типа, удаление столбца)
  3. Требуется валидировать существующие данные
  4. Получается EXCLUSIVE LOCK на таблицу
  5. Базу часто обновляют (INSERT/UPDATE конкурируют с ALTER)

Как избежать:

  1. Используйте DEFAULT значения при добавлении столбцов
  2. Разделяйте изменение на несколько шагов
  3. Используйте онлайн-инструменты (pt-online-schema-change)
  4. Планируйте ALTER TABLE на maintenance window
  5. Для критичных таблиц используйте blue-green deployment

Это важное знание для production-ready разработчика.