PostgreSQL index bloat: найти раздутые индексы
B-tree индексы PostgreSQL могут занимать больше места, чем нужно, после массовых обновлений и удалений. Освободившиеся страницы остаются в индексе и не возвращаются файловой системе. VACUUM переиспользует их, но не уменьшает файл; для сжатия нужен REINDEX или pg_repack. Сначала измерьте проблему, а не пересоздавайте всё подряд.
Найти крупные и редко используемые индексы
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 и размеры страниц. Высокая фрагментация и низкая плотность листьев указывают на раздувание.
Пересобрать индекс без блокировки записи
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.