Изменения индексов в PostgreSQL без простоя: практический план действий

Почему изменения индексов вызывают простой (и как этого избежать)

Работа с индексами кажется безобидной — вы «просто» добавляете вспомогательную структуру. В PostgreSQL же создание, удаление или замена индекса могут брать блокировки, которые блокируют другие сессии. Если таблица нагружена, ожидания накапливаются, и приложение начинает «чувствовать» себя сломанным.

Простой редко выглядит как аккуратное уведомление об отключении. Чаще это зависающие страницы, фоновые задачи, которые отстают, и растущая очередь запросов, застрявших в ожидании базы. Кто‑то нажимает «Поиск» и получает таймаут, а инструменты поддержки и админки внезапно становятся медленными, потому что простые запросы не могут получить нужную блокировку.

«Просто запустите ночью» терпит неудачу по двум частым причинам. Многие системы никогда по‑настоящему не бывают тихими (глобальные пользователи, пакетные задания, ETL, бэкапы). И операции с индексами могут занять больше времени, чем вы ожидаете, потому что они читают много данных и конкурируют за CPU и диск. Если окно закрывается в середине сборки, вам придётся выбирать между продолжением ожидания или прерыванием работы.

Изменения индексов без простоя — это не магия. Это выбор наименее блокирующей операции, настройка защитных мер (таймауты и проверки диска) и наблюдение за базой во время выполнения.

Этот план действий сосредоточен на практических привычках для продакшена:

• Отдавайте предпочтение конкурентной сборке индексов, когда чтения и записи должны продолжаться.
• Следите за блокировками и прогрессом сборки, чтобы реагировать вовремя.
• Имейте план отката, если изменение вызывает регрессии или выполняется слишком долго.

Что не рассматривается: глубокая теория проектирования индексов, общее тюнингование запросов или рефакторинг схем, перекладывающий много данных.

Простая модель блокировок при работе с индексами

PostgreSQL использует блокировки, чтобы сохранять корректность данных, когда множество сессий работают с одной таблицей. Блокировка — это правило, кто сейчас может читать или писать объект, а кто должен ждать.

Чаще всего вы не замечаете блокировок, потому что PostgreSQL применяет лёгкие режимы, позволяющие обычным запросам выполняться. DDL отличается: при создании или удалении индекса PostgreSQL нужен достаточный контроль над таблицей, чтобы поддержать консистентность каталога и данных. Чем больше контроля требуется, тем больше другие сессии могут быть вынуждены ждать.

Создание индекса vs использование индекса

Использование индекса обычно дешево с точки зрения блокировок. SELECT, UPDATE и DELETE могут читать или поддерживать индексы, пока другие сессии делают то же самое.

Создание индекса другое: PostgreSQL должен просканировать таблицу, отсортировать или захешировать ключи и записать новую структуру на диск. Эта работа занимает время, а именно время превращает «малые блокировки» в «большие проблемы» в продакшене.

Что меняет CONCURRENTLY (и что не меняет)

Обычный CREATE INDEX берет сильную блокировку, которая блокирует записи на всё время сборки. CREATE INDEX CONCURRENTLY спроектирован так, чтобы обычные чтения и записи продолжали работать во время сборки индекса.

Но «concurrent» не значит «без блокировок». Короткие окна блокировок есть в начале и в конце, и сборка может упасть или ждать, если что‑то ещё держит несовместимые блокировки.

Ключевые исходы:

• Неконкурентные сборки могут блокировать вставки, обновления и удаления в таблице.
• Конкурентные сборки обычно позволяют читать и писать, но могут замедляться или останавливаться из‑за долгих транзакций.
• Финальные шаги всё равно требуют кратких блокировок, поэтому на очень занятых системах возможны короткие ожидания.

Выберите подход: concurrent или обычный

При изменении индексов у вас два основных варианта: собрать индекс обычным способом (быстро, но блокирующе) или с CONCURRENTLY (обычно не блокируя трафик, но медленнее и чувствительнее к долгим транзакциям).

Когда CONCURRENTLY — правильный выбор

Используйте CREATE INDEX CONCURRENTLY, если таблица обслуживает реальный трафик и вы не можете приостановить записи. Это обычно безопаснее, когда:

• Таблица достаточно большая, и обычная сборка может занять минуты или часы.
• В таблицу идут постоянные записи, а не только чтения.
• Вы не можете запланировать реальное окно обслуживания.
• Вам нужно сначала построить индекс, проверить его, а затем удалить старый.

Когда обычная сборка приемлема

Обычный CREATE INDEX подойдёт, если таблица небольшая, трафик низкий или у вас есть контролируемое окно. Он часто завершается быстрее и проще в запуске.

Рассмотрите обычный подход, если сборка стабильно быстра в стейджинге и вы можете временно остановить записи (даже на короткое время).

Если нужна уникальность — решайте заранее. CREATE UNIQUE INDEX CONCURRENTLY работает, но упадёт при наличии дубликатов. Во многих продакшен-системах поиск и исправление дубликатов — это отдельный проект.

Предполетные проверки перед работой в продакшене

Большинство проблем случается ещё до запуска команды. Несколько проверок помогут избежать двух больших сюрпризов: неожиданных блокировок и слишком длительной (или ресурсоёмкой) сборки индекса.

1. Убедитесь, что вы не в транзакции. CREATE INDEX CONCURRENTLY упадёт, если вы выполните его после BEGIN, и некоторые GUI‑инструменты тихо оборачивают команды в транзакцию. Если сомневаетесь, откройте новую сессию и выполните только команду индекса.

2. Оцените время и место на диске. Конкурентные сборки обычно дольше обычных и требуют дополнительного рабочего пространства. Планируйте место для нового индекса плюс временные накладные расходы и подтвердите комфортный запас свободного диска.

3. Установите таймауты, которые соответствуют вашей цели. Вы хотите, чтобы сборка быстро проваливалась, если не может получить блокировку, но не хотите, чтобы сессия умирала посередине из‑за агрессивного statement_timeout.

4. Снимите базовую метрику. Вам нужны доказательства, что изменение помогло, и быстрый способ заметить регрессии. Запишите снимок до изменений: время медленных запросов, представительный EXPLAIN (ANALYZE, BUFFERS) и краткий обзор CPU, IO, соединений и свободного диска.

Безопасные параметры сессии, которые многие команды используют как отправную точку (подстройте под свои правила):

-- Run in the same session that will build the index
SET lock_timeout = '2s';
SET statement_timeout = '0';

Пошагово: создание индекса с CONCURRENTLY

Используйте CREATE INDEX CONCURRENTLY, когда нужно, чтобы приложение продолжало работать и вы готовы к более долгой сборке.

Сначала определите точно, что собираетесь строить:

• Точно укажите порядок колонок (это важно).
• Подумайте, не хватит ли частичного индекса. Если большинство запросов фильтруют по status = 'active', частичный индекс может быть меньше, быстрее и дешевле в поддержке.

Безопасный запуск выглядит так: запишите цель и имя индекса, запустите сборку вне транзакционного блока, наблюдайте до завершения, затем проверьте, что план может его использовать, прежде чем что‑то удалять.

-- Example: speed up searches by email for active users
CREATE INDEX CONCURRENTLY idx_users_active_email
ON public.users (email)
WHERE status = 'active';

-- Validate it exists
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users';

-- Check the plan can use it
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM public.users
WHERE status = 'active' AND email = '[email protected]';

Для заметок о прогрессе (полезно для аудита) фиксируйте время начала, окончания и любые ожидания. Пока идёт сборка, вы можете в другой сессии опрашивать pg_stat_progress_create_index.

Валидация — это не просто «индекс существует». Убедитесь, что план выбирает его, затем наблюдайте реальное время запросов после деплоя. Если новый индекс не используется, не спешите удалять старый: сначала исправьте запрос или определение индекса.

Пошагово: заменить или удалить индексы без блокировок

Самая безопасная схема — сначала добавить, дать трафику воспользоваться новым индексом, и лишь затем удалить старый. Так у вас остаётся рабочая страховка.

Поменять старый индекс на новый (безопасный порядок)

1. Создайте новый индекс через CREATE INDEX CONCURRENTLY.

2. Убедитесь, что он используется. Проверьте EXPLAIN по медленным запросам и наблюдайте использование индексов со временем.

3. Только после этого удаляйте старый индекс командой DROP INDEX CONCURRENTLY. Если риск велик, держите оба индекса в течение полного бизнес‑цикла.

Удаление индексов: когда CONCURRENTLY работает (и когда нет)

Для обычного индекса, созданного вами, DROP INDEX CONCURRENTLY чаще всего правильный выбор. Две оговорки: он не может выполняться внутри транзакционного блока, и ему всё ещё нужны краткие блокировки в начале и в конце, поэтому его может задержать долгая транзакция.

Если индекс был создан для PRIMARY KEY или UNIQUE ограничения, вы обычно не можете просто так удалить его. Нужно менять ограничение через ALTER TABLE, что может брать более сильные блокировки. Отнеситесь к этому как к отдельной запланированной операции.

Переименование индексов для понятности

Переименование (ALTER INDEX ... RENAME TO ...) обычно быстрое, но избегайте его, если инструменты или миграции ссылаются на имена индексов. Лучше сразу выбрать понятное имя.

Если старый индекс всё ещё нужен

Иногда разные шаблоны запросов требуют разных индексов. Если важные запросы всё ещё зависят от старого индекса — оставьте его. Подумайте о корректировке нового индекса (порядок колонок, частичное условие), вместо того чтобы насильно удалять старый.

Наблюдение за блокировками и прогрессом во время сборки

Даже при CREATE INDEX CONCURRENTLY стоит следить за происходящим в реальном времени. Большинство неожиданных инцидентов вызвано либо незамеченной блокирующей сессией, либо долгой транзакцией, которая удерживает сборку в ожидании.

Как найти блокирующие сессии (кто кого блокирует)

Начните с поиска сессий, ожидающих блокировок:

SELECT
  a.pid,
  a.usename,
  a.application_name,
  a.state,
  a.wait_event_type,
  a.wait_event,
  now() - a.xact_start AS xact_age,
  left(a.query, 120) AS query
FROM pg_stat_activity a
WHERE a.wait_event_type = 'Lock'
ORDER BY xact_age DESC;

Если нужен точный блокирующий PID, сопоставляйте blocked_pid и blocking_pid:

SELECT
  blocked.pid  AS blocked_pid,
  blocking.pid AS blocking_pid,
  now() - blocked.xact_start AS blocked_xact_age,
  left(blocked.query, 80)  AS blocked_query,
  left(blocking.query, 80) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY (pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

Следите за прогрессом сборки и признаками «застревания»

PostgreSQL показывает прогресс сборки индекса. Если вы долго не видите движения, ищите долгую транзакцию (часто это idle‑сессия, держащая старый снимок).

SELECT
  pid,
  phase,
  lockers_total,
  lockers_done,
  blocks_total,
  blocks_done,
  tuples_total,
  tuples_done
FROM pg_stat_progress_create_index;

Также следите за давлением на систему: дисковым IO, отставанием репликации и ростом времени запросов. Конкурентные сборки дружелюбнее к аптайму, но всё равно читают много данных.

Простые правила, которые хорошо работают в проде:

• Ждите, если прогресс движется и влияние на пользователей невелико.
• Отменяйте и перепланируйте, если сборка застряла из‑за долгой транзакции, которую вы не можете безопасно завершить.
• Делайте паузу в пиковое время, если IO мешает пользовательским запросам.
• Прекращайте только в крайнем случае и только после подтверждения, что делает сессия.

Для командной коммуникации держите краткие обновления: время старта, текущая фаза, что блокирует (если есть) и когда будет следующий чек.

План отката: как безопасно откатиться

Изменения индексов остаются низкорисковыми, если вы запланировали выход ещё до старта. Самый безопасный откат часто — не драматичное возвращение назад, а просто остановить новую работу и оставить старый индекс.

Частые причины неудач при работе с индексами

Большинство продакшен‑сбоев предсказуемы: сборка попала под таймаут, кто‑то отменил её во время инцидента, сервер остался без дискового места, или сборка конкурировала с обычным трафиком так, что повысилась задержка для пользователей.

При CREATE INDEX CONCURRENTLY отмена обычно безопасна для приложения, потому что запросы продолжают работать. Платой за это является уборка: отменённая или упавшая конкурентная сборка может оставить невалидный индекс.

Безопасные правила отмены и очистки

Отмена конкурентной сборки не откатывается как обычная транзакция. PostgreSQL может оставить индекс, который существует, но не валиден для планировщика.

-- Cancel the session building the index (use the PID you identified)
SELECT pg_cancel_backend(\u003cpid\u003e);

-- If the index exists but is invalid, remove it without blocking writes
DROP INDEX CONCURRENTLY IF EXISTS your_index_name;

Прежде чем удалять, подтвердите состояние индекса:

SELECT
  c.relname AS index_name,
  i.indisvalid,
  i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'your_index_name';

Если indisvalid = false, индекс не используется и его безопасно удалить.

Практический чеклист отката при замене индекса:

• Сохраняйте старый индекс до тех пор, пока новый полностью не построен и не валиден.
• Если новая сборка упала или была отменена — удалите невалидный индекс конкурентно.
• Если вы уже удалили старый индекс — пересоздайте его через CREATE INDEX CONCURRENTLY для восстановления прежнего состояния.
• Если неудача связана с нехваткой места на диске — сначала освободите место, затем повторите попытку.
• Если неудача связана с таймаутами — запланируйте более тихое окно вместо форсирования.

Пример: вы запускаете новый индекс для админ‑поиска, он идёт 20 минут, потом срабатывают дисковые алармы. Отмените сборку, удалите невалидный индекс конкурентно и оставьте старый индекс обслуживать трафик. Повторите попытку после освобождения места — без видимого для пользователей простоя.

Частые ошибки, которые вызывают неожиданные простои

Большинство простоев вокруг индексов вызвано не тем, что PostgreSQL «медленный». Они происходят потому, что одна мелочь превращает безопасное изменение в блокирующее.

1) Запуск конкурентной сборки внутри транзакции

CREATE INDEX CONCURRENTLY нельзя выполнять внутри транзакционного блока. Многие инструменты миграций по умолчанию оборачивают все изменения в одну транзакцию. Результат — либо жёсткая ошибка (лучший сценарий), либо запутанный деплой с повторными попытками.

Перед запуском миграции убедитесь, что ваш инструмент может выполнить оператор без внешней транзакции, или вынесите этот шаг в отдельную не‑транзакционную миграцию.

2) Запуск в пиковое время

Конкурентные сборки уменьшают блокировки, но они всё равно добавляют нагрузку: дополнительные чтения, записи и больше работы для autovacuum. Запуск в окне деплоя при пике трафика — частый способ создать замедление, которое выглядит как простой.

Выбирайте тихий период и относитесь к этому как к обычному обслуживанию.

3) Игнорирование долгих транзакций

Одна долгая транзакция может задержать финальную фазу конкурентной сборки. Индекс может идти, а потом застрять почти в конце в ожидании исчезновения старых снапшотов.

Привыкайте проверять долгие транзакции до старта и снова, если прогресс остановился.

4) Удаление не того (или нарушение ограничения)

Команды иногда удаляют индекс по имени по памяти или убирают индекс, который поддерживает ограничение уникальности. Если удалить не тот объект, можно потерять обеспечение уникальности (unique constraints) или мгновенно ухудшить производительность запросов.

Быстрая безопасность: проверьте имя индекса в каталоге, подтвердите — не поддерживает ли он ограничение, дважды проверьте схему и таблицу, и отделяйте шаги «создать новый» и «удалить старый». Держите команду для отката готовой до старта.

Реальный пример: ускорение админ‑поиска

Частая боль — админ‑поиск, который в стейджинге кажется мгновенным, а в проде ползёт. Допустим, у вас большая таблица tickets (десятки миллионов строк) за внутренней админкой, и агенты часто ищут «открытые тикеты одного клиента, самые новые вверху». Запрос выглядит так:

SELECT id, customer_id, subject, created_at
FROM tickets
WHERE customer_id = $1
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 50;

Полный индекс на (customer_id, status, created_at) помогает, но добавляет нагрузку на запись при каждом обновлении тикета, включая закрытые. Если большинство строк не open, часто выигрывает частичный индекс:

CREATE INDEX CONCURRENTLY tickets_open_by_customer_created_idx
ON tickets (customer_id, created_at DESC)
WHERE status = 'open';

Безопасный таймлайн в продакшене:

• Предполет: подтвердите стабильность формы запроса и достаточное свободное место на диске для сборки нового индекса.
• Сборка: выполните CREATE INDEX CONCURRENTLY в отдельной сессии с понятными таймаутами.
• Валидация: выполните ANALYZE tickets; и убедитесь, что план использует новый индекс.
• Очистка: когда будете уверены, удалите устаревший индекс DROP INDEX CONCURRENTLY.

Что выглядит как успех:

• Админ‑поиск падает с секунд до десятков миллисекунд для типичных клиентов.
• Обычные чтения и записи продолжают работать во время сборки.
• CPU и диск IO растут во время сборки, но остаются в пределах допустимого.
• У вас есть ясные до/после метрики: время запроса, просканированных строк и история блокировок.

Короткий чеклист и следующие шаги

Работа с индексами безопаснее, если относиться к ней как к небольшому релизу: подготовиться, наблюдать во время выполнения, затем проверить результат перед уборкой.

Перед стартом:

• Настройте таймауты, чтобы неожиданная блокировка не висела вечно.
• Убедитесь в достаточном свободном месте на диске для сборки индекса.
• Найдите долгие транзакции, которые могут замедлить сборку.
• Выберите низконагруженное окно и определите критерии «готовности».
• Запишите план отката заранее.

Во время сборки:

• Следите за блокировками и цепочками ожиданий.
• Отслеживайте прогресс с pg_stat_progress_create_index.
• Наблюдайте симптомы в приложении: рост ошибок, таймаутов и медленных эндпоинтов, связанных с таблицей.
• Будьте готовы отменить, если ожидания по блокировкам растут или пользовательские таймауты начинают срабатывать.
• Логируйте события: время старта, окончания и сработавшие алармы.

После завершения подтвердите валидность индекса, выполните один‑два ключевых запроса, чтобы увидеть улучшение плана и времени, и только потом удаляйте старые индексы безблокирующим способом.

Если вы делаете это часто, превратите процедуру в повторяемый шаг доставки: короткий ранбук, репетиция в стейджинге с данными, похожими на продакшен, и назначьте ответственного, который наблюдает за сборкой.

Если вы строите внутренние инструменты или админ‑панели с AppMaster (appmaster.io), полезно относиться к изменениям базы данных, включая сборки индексов, как к части общего чеклиста релиза: измеренно, под наблюдением и с готовым планом отката.