Устранение неисправностей поврежденных индексов: как перестроить и восстановить индексы PostgreSQL

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

Перестроить индекс несложно. Знать, когда его перестраивать, — более сложная часть. Раздутый индекс можно исправить с помощью REINDEX, но коренной причиной могут быть слабые настройки autovacuum или рабочая нагрузка, которая весь день обновляет одни и те же строки. Поврежденный индекс может потребовать срочного ремонта, но вы также должны спросить, почему вообще произошло повреждение: хранилище, память, ошибки ядра, небезопасные настройки оборудования или редкая программная ошибка.

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

Понимание индексов PostgreSQL и их распространенных проблем

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

Индексы могут стать проблемными в основном по двум причинам: раздутие и повреждение.

Раздутие индекса

Раздутие индекса относится к накоплению «мертвых кортежей» (устаревших версий данных) в структуре индекса. В PostgreSQL, когда строки обновляются или удаляются, старые версии данных (и соответствующие им записи индекса) не удаляются немедленно. Вместо этого они помечаются как «мертвые» и в конечном итоге удаляются процессом VACUUM. Если VACUUM выполняется недостаточно часто или эффективно, или если существует высокая частота обновлений/удалений, эти мертвые кортежи могут накапливаться, делая индекс больше, чем необходимо. Раздутый индекс занимает больше дискового пространства, требует больше операций ввода-вывода для сканирования и может даже стать менее эффективным для ускорения запросов.

Повреждение индекса

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

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

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

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

Выявление проблемных индексов

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

Проверка неиспользуемых или неэффективных индексов

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

SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS index_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM
    pg_stat_user_indexes
WHERE
    idx_scan = 0 -- Индексы, которые никогда не сканировались
    AND schemaname = 'public'
ORDER BY
    pg_relation_size(indexrelid) DESC;

Хотя idx_scan равный 0 может указывать на неиспользуемый индекс, важно учитывать, что некоторые индексы используются для ограничений (например, UNIQUE, PRIMARY KEY) или редко используемых отчетов. Всегда проводите расследование перед удалением.

Обнаружение раздутия индекса

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

SELECT
    relname AS table_name,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS indexes_size,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM
    pg_stat_user_tables
ORDER BY
    pg_total_relation_size(relid) DESC;

Для более продвинутого обнаружения раздутия вы можете рассмотреть возможность использования скриптов, предоставленных сообществом, или расширений, таких как pg_repack или pgstattuple (которые могут оценить раздутие, глядя на плотность кортежей).

Выявление медленных запросов с помощью `EXPLAIN ANALYZE`

Когда конкретный запрос становится медленным, EXPLAIN ANALYZE — ваш лучший друг. Он показывает план выполнения запроса и фактические статистические данные времени выполнения, включая то, как используются (или не используются) индексы.

EXPLAIN ANALYZE
SELECT * FROM your_table WHERE your_column = 'some_value';

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

Проверка на повреждение индекса

Повреждение индекса часто проявляется в виде ошибок в журналах PostgreSQL или когда запросы неожиданно завершаются с ошибкой. Ищите сообщения, содержащие фразы типа corruption, unexpected data, invalid page или bad block. Не существует единой встроенной команды SQL, которая доказывает, что каждый индекс в базе данных здоров. Для более глубоких проверок команды часто используют расширение PostgreSQL amcheck, особенно bt_index_check и bt_index_parent_check для индексов B-дерева, во время окон обслуживания.

CREATE EXTENSION IF NOT EXISTS amcheck;

SELECT bt_index_check('public.idx_products_name'::regclass);

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

Совет: Регулярно проверяйте журналы PostgreSQL на наличие сообщений об ошибках. Раннее обнаружение повреждений может предотвратить более серьезные проблемы.

Команда `REINDEX`: ваш основной инструмент

Команда REINDEX является основным инструментом для перестроения индексов PostgreSQL. Она воссоздает индекс с нуля, эффективно устраняя раздутие путем удаления мертвых кортежей и исправляя повреждения путем создания новой, валидной структуры на основе текущих данных таблицы.

Как работает `REINDEX`

Когда выполняется REINDEX, PostgreSQL перестраивает индекс из текущих данных таблицы. Результатом является новая, компактная структура индекса. Для раздутия это означает, что мертвое пространство внутри индекса удаляется. Для многих случаев повреждения индекса на уровне индекса это дает PostgreSQL новую структуру, построенную из таблицы.

Синтаксис и использование `REINDEX`

REINDEX может применяться с разной степенью детализации:

Перестроение конкретного индекса:
```
REINDEX INDEX index_name;
```
Это наиболее распространенный вариант использования, нацеленный на один проблемный индекс.
Перестроение всех индексов таблицы:
```
REINDEX TABLE table_name;
```
Полезно, когда таблица имеет несколько раздутых или поврежденных индексов.
Перестроение всех индексов в базе данных:
```
REINDEX DATABASE database_name;
```
Это более радикальная мера, обычно используемая в ситуациях, когда подозревается широкомасштабное повреждение или раздутие. Это может вызвать значительное время простоя.
Перестроение системных каталогов в базе данных:
```
REINDEX SYSTEM database_name;
```
Это перестраивает все индексы в таблицах системного каталога в указанной базе данных. Это следует использовать с крайней осторожностью и только если вы подозреваете проблемы с индексами системного каталога, так как это может повлиять на функциональность всей базы данных и требует исключительного доступа.

Предупреждение: Выполнение REINDEX без CONCURRENTLY устанавливает более строгие блокировки и может блокировать обычный трафик приложений на затронутых объектах. Относитесь к этому как к операции с простоем, если вы не протестировали точную команду и поведение блокировок для вашей версии PostgreSQL и типа объекта.

Минимизация времени простоя с помощью `REINDEX CONCURRENTLY`

Для производственных систем, где время простоя неприемлемо, REINDEX CONCURRENTLY является бесценным вариантом. Он позволяет перестраивать индексы без блокировки одновременных операций чтения и записи в таблице.

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

Он создает новое определение индекса одновременно с нормальными операциями.
Он устанавливает кратковременную блокировку SHARE UPDATE EXCLUSIVE на таблицу, которая блокирует DDL (например, ALTER TABLE), но разрешает DML (INSERT, UPDATE, DELETE) и операторы SELECT.
Затем он сканирует таблицу для создания нового индекса.
После первоначального создания он устанавливает еще одну, очень короткую блокировку SHARE UPDATE EXCLUSIVE, чтобы применить изменения, произошедшие в процессе создания.
Наконец, он заменяет старый индекс новым и удаляет старый индекс.

Синтаксис:

REINDEX INDEX CONCURRENTLY index_name;

Важные соображения относительно REINDEX CONCURRENTLY:

Более медленное выполнение: Поскольку ему нужно обрабатывать одновременные изменения, REINDEX CONCURRENTLY обычно медленнее, чем неконкурентный REINDEX.
Дисковое пространство: Для него требуется дисковое пространство для временного хранения как старой, так и новой структур индекса.
Отсутствие поддержки транзакций: REINDEX CONCURRENTLY не может быть выполнен внутри блока транзакции.
Обработка ошибок: Если REINDEX CONCURRENTLY завершается с ошибкой (например, из-за нарушения уникального ограничения для уникального индекса), он оставляет после себя недействительный индекс. Вы должны DROP этот недействительный индекс, а затем повторно выполнить команду REINDEX CONCURRENTLY.

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

Предположим, у нас есть таблица products с индексом idx_products_name.

Перестроение одного индекса (с простоем)

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

REINDEX INDEX idx_products_name;

Перестроение одного индекса (одновременно, минимальное время простоя)

Для производственных систем, где таблица products должна оставаться доступной:

-- Для индекса B-дерева:
REINDEX INDEX CONCURRENTLY idx_products_name;

-- Для индекса первичного ключа или уникального ограничения (часто требует специальной обработки, хотя REINDEX CONCURRENTLY справляется с этим):
-- Если вам нужно перестроить индекс первичного ключа или уникального ограничения, вы обычно перестраиваете базовый индекс.
-- Например, если 'products_pkey' является индексом первичного ключа:
REINDEX INDEX CONCURRENTLY products_pkey;

Перестроение всех индексов таблицы

Если вы подозреваете, что несколько индексов таблицы products проблематичны:

-- Это установит блокировку ACCESS EXCLUSIVE на таблицу 'products'.
REINDEX TABLE products;

Современные версии PostgreSQL поддерживают одновременное переиндексирование таблицы:

REINDEX TABLE CONCURRENTLY products;

Обычно это проще, чем вручную перестраивать каждый индекс, но все равно потребляет ввод-вывод, процессор и временное дисковое пространство. В старых версиях PostgreSQL, которые не поддерживают этот синтаксис, определите индексы таблицы и перестройте каждый с помощью REINDEX INDEX CONCURRENTLY.

Сначала определите все индексы для таблицы:

SELECT indexname FROM pg_indexes WHERE tablename = 'products';

Для ручного управления сначала выведите список индексов:

SELECT indexname
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'products'
ORDER BY indexname;

Перестроение всех индексов в базе данных

Это крайняя мера и требует значительного времени простоя. Ее следует выполнять только во время запланированных окон обслуживания.

REINDEX DATABASE your_database_name;

В качестве альтернативы, в поддерживаемых версиях PostgreSQL вы можете использовать REINDEX DATABASE CONCURRENTLY your_database_name;. Это позволяет избежать худшего поведения блокировок, но все равно является серьезной операцией обслуживания и не может выполняться внутри блока транзакции.

Связанные команды обслуживания и лучшие практики

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

`VACUUM` и `VACUUM FULL`

VACUUM: Освобождает пространство, занятое мертвыми кортежами, делая его доступным для повторного использования. Он не уменьшает файлы таблицы или индекса на диске, но имеет решающее значение для предотвращения раздутия. Демон autovacuum обычно обрабатывает это автоматически.
```
VACUUM your_table;
```
VACUUM FULL: Перезаписывает всю таблицу и связанные с ней индексы в новый файл на диске, освобождая максимальное пространство и устраняя раздутие. Однако он устанавливает блокировку ACCESS EXCLUSIVE на таблицу, блокируя все операции, и его следует использовать с крайней осторожностью. REINDEX часто предпочтительнее для раздутия индекса.
```
VACUUM FULL your_table;
```

`ANALYZE`

Команда ANALYZE собирает статистику о содержимом таблиц в базе данных и сохраняет ее в pg_statistic. Планировщик запросов PostgreSQL использует эту статистику для принятия интеллектуальных решений о том, как выполнять запросы, включая использование индекса или нет. Запуск ANALYZE после значительных изменений данных (или после переиндексации) гарантирует, что планировщик имеет актуальную информацию.

ANALYZE your_table;
-- Или проанализируйте всю базу данных:
ANALYZE;

Мониторинг Auto-Vacuum

Убедитесь, что демон autovacuum запущен и настроен правильно. Он отвечает за автоматическое выполнение операций VACUUM и ANALYZE, которые имеют решающее значение для предотвращения раздутия и поддержания актуальности статистики. Неправильно настроенный autovacuum является частой причиной снижения производительности.

Регулярные графики обслуживания

Профилактическое обслуживание индексов лучше, чем реактивное устранение неполадок. Установите график для:

Мониторинга использования и размера индексов: Выявление потенциального раздутия или неиспользуемых индексов.
Запуска REINDEX CONCURRENTLY: Для часто обновляемых или удаляемых таблиц или после значительных миграций данных.
Просмотра журналов и настроек autovacuum: Убедитесь, что он справляется с активностью базы данных.

Тестирование и резервное копирование

Всегда тестируйте: Перед выполнением любых серьезных операций обслуживания на рабочей базе данных тщательно протестируйте их в среде staging или разработки, которая зеркалирует вашу рабочую среду.
Всегда делайте резервную копию: Имейте свежую, надежную резервную копию вашей базы данных перед запуском любых операций REINDEX, особенно неконкурентных или нацеленных на целые таблицы/базы данных. Хотя REINDEX обычно безопасен, поврежденная резервная копия базы данных бесполезна.

Советы по устранению неполадок и предупреждения

Дисковое пространство: Операции REINDEX (особенно CONCURRENTLY) требуют значительного временного дискового пространства — потенциально до двойного размера перестраиваемого индекса. Убедитесь, что на вашем сервере базы данных достаточно свободного места.
Влияние на производительность: Даже REINDEX CONCURRENTLY будет потреблять ресурсы процессора и ввода-вывода во время своей работы. Внимательно следите за производительностью вашей системы во время его выполнения.
Выявление коренных причин: Не переиндексируйте повторно, не понимая, почему индексы становятся раздутыми или поврежденными. Исследуйте основные проблемы, такие как неэффективные настройки VACUUM, высокие частоты транзакций или проблемы с оборудованием.
Создание индекса против переиндексации: CREATE INDEX CONCURRENTLY является эквивалентом REINDEX INDEX CONCURRENTLY для создания новых индексов без блокировки. Он следует аналогичным принципам и имеет аналогичные ограничения.

Хорошее обслуживание индексов — это отчасти знание команд, отчасти сдержанность. REINDEX CONCURRENTLY — полезный инструмент для ремонта, но повторная переиндексация без понимания рабочей нагрузки обычно означает, что то же раздутие вернется. Используйте приведенные выше команды, чтобы подтвердить проблему, перестроить наименьший затронутый объект, который вы можете, а затем проверьте autovacuum, шаблоны обновлений, работоспособность диска и планы запросов, чтобы вам не пришлось выполнять тот же аварийный ремонт в следующем месяце.