Обслуживание индексов и раздувание
Диагностируйте раздувание индексов, перестраивайте их с помощью REINDEX CONCURRENTLY и безопасно удаляйте неиспользуемые индексы
«Обслуживание индексов и раздувание» — бесплатный урок SQL Academy на CoddyKit. Это урок 4 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.
Почему индексы разрастаются
PostgreSQL использует MVCC: операция UPDATE записывает новую версию строки, оставляя старую доступной для уже выполняющихся транзакций. Индексные записи существуют для обеих версий. Со временем:
- В таблицах с частыми обновлениями накапливаются мёртвые индексные записи
- Индексы становятся больше необходимого
- Поиск замедляется, поскольку дерево B-tree становится глубже
Диагностика разрастания
Запросы для проверки разрастания нетривиальны. Распространённые инструменты:
- Расширение
pgstattuple - Отчёты pg_repack
- Запросы для проверки разрастания из check_postgres и инструментов мониторинга
CREATE EXTENSION pgstattuple;
SELECT * FROM pgstatindex('orders_user_id_idx');REINDEX
Перестраивает индекс. Классическая форма устанавливает блокировку ACCESS EXCLUSIVE — это плохо для рабочей среды:
REINDEX INDEX orders_user_id_idx; -- blocks writes!REINDEX CONCURRENTLY (PG 12+)
Неблокирующий вариант — чтение и запись продолжаются во время перестроения:
REINDEX INDEX CONCURRENTLY orders_user_id_idx;Неиспользуемые индексы
Неиспользуемый индекс замедляет каждую операцию записи, но не ускоряет ни одной операции чтения. Найдите такие индексы:
SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;Удаление неиспользуемых индексов
Удалите их, но сначала проверьте все среды и временные интервалы. Индексы, используемые для периодических отчётов, большую часть времени выглядят неиспользуемыми.
DROP INDEX CONCURRENTLY old_unused_idx;Дублирующиеся индексы
Иногда один и тот же индекс создаётся и ограничением, и ручной командой CREATE INDEX. Проверьте pg_indexes на наличие дубликатов и удалите лишний:
SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;Увеличение затрат на запись из-за индексов
Каждая операция INSERT/UPDATE/DELETE обновляет все подходящие индексы. Три индекса в активно используемой таблице = тройные затраты на запись. Добавляйте только те индексы, которые действительно окупаются.
Список отложенных операций GIN
Индексы GIN группируют обновления в списке отложенных операций. Выполните сброс вручную или положитесь на автоматическую очистку:
SELECT gin_clean_pending_list('events_data_gin');VACUUM очищает индексные записи
VACUUM (рассмотренный в уроке о MVCC) удаляет мёртвые индексные записи со страниц кучи. Без автоматической очистки индексы растут бесконечно.
Мониторинг размера индексов
Отслеживайте размер индексов со временем:
SELECT pg_size_pretty(pg_indexes_size('orders')) AS index_size,
pg_size_pretty(pg_total_relation_size('orders')) AS total_size;pg_repack: перестройка таблиц в рабочем режиме
При сильном разрастании pg_repack перестраивает таблицу и индексы в рабочем режиме — без полной блокировки таблицы. Установите его как пакет OS и расширение PostgreSQL.
Итоги
Индексы требуют внимания.
- Разрастание из-за MVCC — нормальное явление; управляйте им с помощью VACUUM и REINDEX CONCURRENTLY
- Удаляйте неиспользуемые индексы
- Избегайте дубликатов
- Каждый новый индекс замедляет запись — добавляйте его обдуманно
Быстрая проверка
Какая команда PostgreSQL перестраивает индекс WITHOUT блокировки записи?
Часто задаваемые вопросы
Урок «Обслуживание индексов и раздувание» бесплатный?
Да — полный текст урока «Обслуживание индексов и раздувание» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.
Чему я научусь в уроке «Обслуживание индексов и раздувание»?
Диагностируйте раздувание индексов, перестраивайте их с помощью REINDEX CONCURRENTLY и безопасно удаляйте неиспользуемые индексы Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.
Нужен ли мне опыт, чтобы начать SQL Academy?
Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 4 из 4.
Сколько времени занимает урок «Обслуживание индексов и раздувание»?
Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.
Можно ли писать и запускать код в этом уроке SQL Academy?
Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.
Все уроки этого курса
- Индексы B-tree, Hash, GiST и GIN
- Составные индексы и порядок столбцов
- Частичные индексы и индексы по выражениям
- Обслуживание индексов и раздувание