Каки подводные камни есть в добавление индексов на большие таблицы

Подводные камни добавления индексов на большие таблицы

Добавление индексов — это критическая операция в производстве. На больших таблицах она может вызвать серьёзные проблемы. Разберу основные риски и решения.

1. Блокировка таблицы (Table Locking)

В большинстве БД создание индекса требует эксклюзивной блокировки таблицы:

-- В PostgreSQL: без CONCURRENTLY блокирует всю таблицу
CREATE INDEX idx_user_email ON users(email);  -- Опасно!

-- Правильно: создание индекса без блокировки
CREATE INDEX CONCURRENTLY idx_user_email ON users(email);

Проблема: Пока строится индекс, все INSERT, UPDATE, DELETE операции ждут, приложение зависает.

2. Долгое время выполнения

На таблице с миллионами строк создание индекса может занять часы:

-- Таблица с 100M строк
CREATE INDEX CONCURRENTLY idx_user_active ON users(is_active);  -- Может занять 2-3 часа

Решение:

• Создавайте индексы в off-peak hours (ночью)
• Используйте CONCURRENTLY для минимизации блокировок
• Мониторьте прогресс через pg_stat_progress_create_index

3. Использование дискового пространства

Индекс занимает дополнительное место на диске:

-- Проверить размер таблицы и индексов
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Проблема: Индекс может быть 30-40% размера таблицы. На диске может не хватить места.

4. Деградация производительности (Indexing on Hot Table)

Когда индекс создаётся на активно используемой таблице, это замедляет все операции:

# Во время создания индекса:
# - Записи идут медленнее (писатель ждёт индекса)
# - Чтения идут нормально (старый индекс всё ещё используется)
# - CPU скачет до 100%

5. Статистика БД становится неверной

-- После создания индекса нужно обновить статистику
ANALYZE users;  -- Обновить stats для оптимизатора

Без этого оптимизатор может не использовать новый индекс.

6. Выбор неправильного типа индекса

-- Для полнотекстового поиска нужен GIN или GIST, не B-tree
CREATE INDEX CONCURRENTLY idx_search ON articles USING GIN(to_tsvector('russian', content));

-- Для JSON нужен JSONB индекс
CREATE INDEX CONCURRENTLY idx_json ON data USING GIN(metadata);

-- B-tree индекс по LIKE '%pattern' неэффективен
SELECT * FROM users WHERE name LIKE '%john%';  -- Не использует индекс

7. Кластеризация индекса

Новый индекс может быть неупорядоченным, что замедлит запросы:

-- Переупорядочить физическую таблицу по индексу
CLUSTER users USING idx_user_email;

Внимание: CLUSTER также требует эксклюзивной блокировки!

Правильный процесс добавления индекса

В production:

-- Шаг 1: Создать индекс без блокировки
CREATE INDEX CONCURRENTLY idx_new ON users(column);

-- Шаг 2: Проверить, что индекс валиден
SELECT * FROM pg_stat_user_indexes WHERE indexrelname = 'idx_new';

-- Шаг 3: Обновить статистику
ANALYZE users;

-- Шаг 4: Проверить, что он используется
EXPLAIN ANALYZE SELECT * FROM users WHERE column = value;

Мониторинг процесса

-- Посмотреть ход создания индекса
SELECT 
    pid,
    phase,
    round(100.0 * blocks_done / blocks_total, 2) as progress
FROM pg_stat_progress_create_index;

-- Включить логирование долгих запросов
SET log_min_duration_statement = 60000;  -- Логировать запросы > 60s

Практические рекомендации

• Всегда используйте CONCURRENTLY в production
• Создавайте индексы в off-peak hours
• Проверяйте место на диске перед созданием
• Обновляйте статистику после создания
• Мониторьте влияние на производительность
• Не создавайте избыточные индексы (каждый индекс замедляет INSERT/UPDATE)
• Используйте правильный тип индекса для вашего кейса

Неправильное добавление индекса может крашнуть production и привести к downtime. Используйте инструменты мониторинга и тестируйте на staging перед production.