PostgreSQL index bloat: найти раздутые индексы

Категория: Postgresql · Язык: SQL · Обновлено:

B-tree индексы PostgreSQL могут занимать больше места, чем нужно, после массовых обновлений и удалений. Освободившиеся страницы остаются в индексе и не возвращаются файловой системе. VACUUM переиспользует их, но не уменьшает файл; для сжатия нужен REINDEX или pg_repack. Сначала измерьте проблему, а не пересоздавайте всё подряд.

Найти крупные и редко используемые индексы

SQL
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 10;

SELECT *
FROM pgstatindex('public.users_email_idx');

pg_stat_user_indexes показывает размер и число сканирований; индекс с idx_scan = 0 и большим размером — кандидат на удаление, если он не нужен для ограничений. pgstatindex из pgstattuple возвращает leaf_fragmentation, avg_leaf_density и размеры страниц. Высокая фрагментация и низкая плотность листьев указывают на раздувание.

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

SQL
REINDEX INDEX CONCURRENTLY public.users_email_idx;

CONCURRENTLY строит новый индекс рядом и переключает его, не блокируя записи на всё время операции. Команда не может выполняться внутри транзакции, требует больше диска и времени и может ждать долгие транзакции. При ошибке остаётся невалидный индекс — проверьте pg_index.indisvalid и удалите его перед повторной попыткой.

REINDEX TABLE CONCURRENTLY пересобирает все индексы таблицы. VACUUM FULL тоже сжимает индексы, но блокирует таблицу; pg_repack делает это онлайн и требует расширения. Не используйте REINDEX как регулярную замену VACUUM: для переиспользования страниц достаточно autovacuum.

Проверяйте bloat на реплике или в низкую нагрузку: pgstatindex читает индекс и создаёт I/O. Не удаляйте индекс только по idx_scan = 0 — счётчик может быть сброшен или индекс нужен для уникальности/внешнего ключа. Перед удалением проверьте зависимости и план запросов.

Документация: pgstattuple, REINDEX.